modify-user-privileges
how-to-grant-privileges-to-users-in-mysql-80
rant-permissions-to-a-mysql-user-on-linux-via-command-line
how-do-i-change-grants-for-a-user-in-mysql
revoke
Granting Privileges
To grant all privileges on a specific database to a user, you can use the following commands:
GRANT ALL ON example_database.* TO 'example_user'@'%';
To grant a user administrative privileges for a specific database, you must also give them the GRANT OPTION privilege. Here’s an example:
GRANT ALL ON example_database.* TO 'example_user'@'%' WITH GRANT OPTION;
To grant a user only read privileges on a database, you can use the following command:
GRANT SELECT ON example_database TO 'example_user'@'%';
To grant a user privileges on only a specific table in a database, you can use the following command:
GRANT SELECT ON example_database TO 'example_user'@'%';
mysql> GRANT INSERT ON example_database.example_table TO 'example_user'@'%';
Revoking Privileges
Revoking privileges is quite similar to granting them. To revoke a user’s privileges, use the revoke command syntax. For example, to revoke all privileges on a specific database, use the following command:
REVOKE ALL ON example_database FROM 'example_user'@'%';
If a user already has privileges and you want to revoke them but still allow the user to read the database, you can use the following command:
REVOKE ALL ON example_database FROM 'example_user'@'%';
mysql> GRANT SELECT ON example_database TO 'example_user'@'%';
Viewing Privileges
To see the privileges for the current user, you can use the SHOW GRANTS; command, and will see output similar to the following, which shows the default grants for the doadmin user:
SHOW GRANTS;
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for doadmin@% |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE, DROP, PROCESS, SHOW DATABASES, REPLICATION CLIENT, CREATE USER, CREATE ROLE, DROP ROLE ON *.* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT ROLE_ADMIN ON *.* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON "defaultdb".* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT SELECT ON "mysql".* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT SELECT ON "sys".* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT SELECT ON "metrics_user_telegraf".* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT SELECT ON "performance_schema".* TO "doadmin"@"%" WITH GRANT OPTION
+---------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.07 sec)
To view the grants for another user, you can use SHOW GRANTS while specifying the username:
SHOW GRANTS FOR 'example_user';
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for example_user@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE, DROP, RELOAD, PROCESS, SHOW DATABASES, REPLICATION CLIENT, CREATE USER ON . TO "example_user"@"%" WITH GRANT OPTION |
| GRANT ROLE_ADMIN ON . TO "example_user"@"%" WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON "defaultdb".* TO "example_user"@"%" WITH GRANT OPTION |
| GRANT SELECT ON "mysql".* TO "example_user"@"%" WITH GRANT OPTION |
| GRANT SELECT ON "sys".* TO "example_user"@"%" WITH GRANT OPTION |
| GRANT SELECT ON "metrics_user_telegraf".* TO "example_user"@"%" WITH GRANT OPTION |
| GRANT SELECT ON "performance_schema".* TO "example_user"@"%" WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
To list them all, just run:
mysql> show privileges;
Linux via Command Line
Step 1: Access the MySQL Server
Open a terminal to access the MySQL server from the command line using the following command. It specifies the root user with the -u flag. The -p flag makes MySQL prompt for a password. Enter your current password to complete the login.
mysql -u root -p
The system presents the MySQL prompt. From here, you can change a password for MySQL via the command line for the root or any other user in the database here.
mysql>
Step 2: Grant Permissions to MySQL User
Below is the basic syntax for granting user permissions.
GRANT permission ON database.table TO 'user'@'localhost';
Here is a short list of commonly used permissions:
ALL - Allows complete access to a specific database. If a database is not specified, it allows complete access to the entirety of MySQL.
CREATE - Allow a user to create databases and tables.
DELETE - Allow a user to delete rows from a table.
DROP - Allow a user to drop databases and tables.
EXECUTE - Allow a user to execute stored routines.
GRANT OPTION - Allow a user to grant or remove another user's privileges.
INSERT - Allow a user to insert rows from a table.
SELECT - Allow a user to select data from a database.
SHOW DATABASES- Allow a user to view a list of all databases.
UPDATE - Allow a user to update rows in a table.
Using an asterisk (*) in the place of database or table is a completely valid option, as it implies all databases or all tables. To grant CREATE permissions for all databases and tables to the user, testuser, use the following command.
GRANT CREATE ON *.* TO 'testuser'@'localhost';
To grant testuser the ability to drop tables in the database called tutorial_database, use the DROP permission.
GRANT DROP ON tutorial_database.* TO 'testuser'@'localhost';
When you finish making your permission changes, it’s best practice to reload all the privileges with the flush command.
FLUSH PRIVILEGES;
Step 3: Confirm Grantted Permissions for the MySQL User
After granting permissions to a MySQL user, confirm them with the following command. For this tutorial, the command checks the permissions for testuser.
SHOW GRANTS FOR 'testuser'@'localhost';
Top comments (0)