Share via:

MySQL: Create New Users and Control Privileges

How to create new users in MySQL and control their permissions for better security.

69 views

Edited: 2019-11-05 09:15

It can be a good idea to create a MySQL user for each service you are running. The service, or script, can then use the credentials for the specific user you make to log in.

You should only give each user the privileges (access to features) that is actually used by the service.

Note. By service I mean a PHP script, web server, or whatever else is using your MySQL database.

To create a new MySQL user, you should be logged in as root. When logged in, you may use the below query:

CREATE USER 'UserName'@'localhost' IDENTIFIED BY 'Password';

To grant the new user full privileges on all databases and tables:

GRANT ALL PRIVILEGES ON *.* TO 'UserName'@'localhost';

Finally flush privileges before you exit:

FLUSH PRIVILEGES;

User privileges

Instead of just giving access to everything, privileges can be be micromanaged for each user. To limit access to a specific database, you can use this query:

GRANT ALL ON database_name TO 'UserName'@'localhost';

Or, even a specific table:

GRANT ALL ON database_name.table_name TO 'UserName'@'localhost';

In addition to these controls, you can also limit the query-types that users are able to use. To prevent a user from creating and deleting database tables, simply use this:

GRANT INSERT, SELECT, UPDATE, DELETE ON database_name TO 'UserName'@'localhost';

More Privileges:

  • ALL PRIVILEGES – user can do anything.
  • INSERT – user can insert.
  • DELETE – user can delete rows in tables.
  • UPDATE – user can update tables.
  • SELECT – user can perform selects.
  • DROP – user can drop tables or tables.
  • CREATE – user can create tables or databases.

To show the grants of a user, use this query:

SHOW GRANTS FOR 'root'@'localhost';

Output

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

Links

  1. Privileges Provided by MySQL - dev.mysql.com

Comments