Create New Users and Grant Privileges (MySQL/MariaDB)

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

970 views

By. Jacob

Edited: 2022-07-13 10:13

It can be a good idea to create a unique user in your MariaDB or MySQL database for each service you intend to have hooked up with your database, this increases the security of you system, and confines the potential damage caused by exposed credentials to the specific service.

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 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';

Console output:

+---------------------------------------------+
| Grants for test@localhost                   |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`localhost`    |
+---------------------------------------------+
1 row in set (0.00 sec)

Revoking privileges

Finally, to revoke privileges we can use this syntax:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'UserName'@'localhost';

Create New Users and Grant Privileges (MySQL/MariaDB)

User privileges

Revoking privileges

Links

Tell us what you think: