Debug School

rakesh kumar
rakesh kumar

Posted on

How to Modify User Privileges in MySQL Databases

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'@'%';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

To grant a user only read privileges on a database, you can use the following command:

GRANT SELECT ON example_database TO 'example_user'@'%';
Enter fullscreen mode Exit fullscreen mode

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'@'%';
Enter fullscreen mode Exit fullscreen mode

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'@'%';
Enter fullscreen mode Exit fullscreen mode

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'@'%';
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

Top comments (0)