User Privileges

1
2
3
4
5
6

CREATE USER "app_user"@"localhost" IDENTIFIED BY "password123";
GRANT SELECT, INSERT, UPDATE ON mydb.* TO "app_user"@"localhost";
GRANT ALL PRIVILEGES ON mydb.* TO "admin"@"localhost";
REVOKE DELETE ON mydb.* FROM "app_user"@"localhost";
SHOW GRANTS FOR "app_user"@"localhost";
FLUSH PRIVILEGES;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Create a user
CREATE USER "app_user"@"localhost"
IDENTIFIED BY "StrongPass123!";

-- Grant privileges
GRANT SELECT, INSERT, UPDATE
ON shop.* TO "app_user"@"localhost";

-- Grant all on one database
GRANT ALL PRIVILEGES
ON shop.* TO "developer"@"localhost";

-- Revoke privileges
REVOKE DELETE ON shop.* FROM "app_user"@"localhost";

-- Show privileges
SHOW GRANTS FOR "app_user"@"localhost";

-- Drop user
DROP USER "old_user"@"localhost";

-- Role (MySQL 8+)
CREATE ROLE "read_only";
GRANT SELECT ON shop.* TO "read_only";
GRANT "read_only" TO "app_user"@"localhost";