MySql – Manage users and privileges

Use the instructions in this section to add users for the database and grant and revoke privileges.

Add users and privileges

When applications connect to the database using the root user, they usually have more privileges than they need. You can create a new user that applications can use to connect to the new database. In the following example, a user named demouser is created.

To create a new user, run the following command in the mysql shell:

mysql>CREATE USER ‘demouser‘@’localhost’ IDENTIFIED BY ‘demopassword‘;

You can verify that the user was created by running a SELECT query again:

SELECT User, Host, Password FROM mysql.user;
+———-+———–+——————————————+
| User | Host | Password |
+———-+———–+——————————————+
| root | localhost | 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root | demohost | 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root | 127.0.0.1 | 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| demouser | localhost | 0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6 |
+———-+———–+——————————————+

Grant database user privileges

Right after you create a new user, it has no privileges. The user can be used to log in to MySQL, but it can’t be used to make any database changes.

Give the user full privileges for your new database by running the following command:

GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;

Flush the privileges to make the change take effect.

Mysql> Flush Privileges

To verify that the privileges were set, run the following command:

SHOW GRANTS FOR ‘demouser‘@’localhost’;

MySQL returns the commands needed to reproduce that user’s privileges if you were to rebuild the server. The USAGE on \*.\* part means that the user gets no privileges on anything by default. That command is overridden by the second command, which is the grant you ran for the new database.

+——————————————————————————————–+
| Grants for demouser@localhost |
———————————————————————————————+
| GRANT USAGE ON *.* TO ‘demouser’@’localhost’ IDENTIFIED BY PASSWORD ‘*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6’ |
| GRANT ALL PRIVILEGES ON `demodb`.* TO ‘demouser’@’localhost’ |
———————————————————————————————+
2 rows in set (0.00 sec)

Revoke privileges

Sometimes you might need to revoke (remove) privileges from a user. For example: suppose that you were granting ALL privileges to ‘demouser’@’localhost’, but you accidentally granted privileges to all other databases, too:

+——————————————————————————————–+ | Grants for demouser@localhost | +——————————————————————————————–+ | GRANT USAGE ON *.* TO ‘demouser’@’localhost’ IDENTIFIED BY PASSWORD ‘*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6’ | | GRANT ALL PRIVILEGES ON *.* TO ‘demouser’@’localhost’ | +——————————————————————————————–+ 2 rows in set (0.00 sec)

To correct the mistake, you can use a REVOKE statement, followed by GRANT statement to apply the correct privileges.

REVOKE ALL ON *.* FROM demouser@localhost; GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost; SHOW GRANTS FOR ‘demouser’@’localhost’; +——————————————————————————————–+ | Grants for demouser@localhost | +——————————————————————————————–+ | GRANT USAGE ON *.* TO ‘demouser’@’localhost’ IDENTIFIED BY PASSWORD ‘*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6’ | | GRANT ALL PRIVILEGES ON ‘demodb’TO ‘demouser’@’localhost’ | +——————————————————————————————–+ 2 rows in set (0.00 sec)

Now your user has the correct privileges, and therefore your database server is slightly more secure (granting privileges like ALL on *.* is deemed as a very bad practice). You should also read official MySQL documentation regarding possible privilege choices, to grant only those privileges truly needed, rather than using ALL

Leave a Reply

Your email address will not be published. Required fields are marked *