MySQL Create Login and Roles

Previous Page
Next Page

When working on a project in a team, there are many users. These users can have different roles and responsibilities based on designation and role in the project. For example, there is generally only one sysadmin or DBA, while there may be many developers. Developers should have read access but not create, drop, or alter access for security. On the other hand, the DBA needs to have complete access to the database to maintain it.

Traditionally, DBA could grant privileges to only one user at a time. If a team of, say, 50 users needs rights, then it becomes tedious and error-prone.

So, MySQL introduced the concept of role. A role is a collection of privileges that we can name. We can add or remove rights for each role. We can also assign users to each role.

To understand roles better, let us assume that we have a database called “employeesdb.” We have a team of 4 developers and a dba. We need to give the dba create, delete, alter and select privileges. The developers need to have the select and insert roles. Instead of giving each user the same privileges, we will assign roles.

The steps are as follows:

Create a role in MySQL

  • To create a new role, we use the “Create Role” statement. We need to create the dev and dba roles.
CREATE ROLE dev_users;

CREATE ROLE dba_users;

Generally, when creating a role, we also attach the hostname to it—for example, dev_users@localhost. If we do not connect the hostname, by default, the role is available for any server.

Grant role/user in MySQL

  • Once the role is created, we can grant privileges to it.

To grant all the privileges to a role, we do:

GRANT ALL ON employeesdb.* TO dba_users;

To grant specific roles to a Role,

GRANT SELECT,INSERT ON employeesdb.* TO dev_users;

The above commands give the specified privileges to all the tables in the schema.

  •   Assign users to a specific role.
GRANT dba_users TO dba;

To assign multiple users to the same role:

GRANT dev_users TO dev_user_1,dev_user_2,dev_user_3,dev_user_4;

This would grant the privileges that the role has to the users.

For example: If the user dev_user_1 logs into the employeesdb. If they run a Select query, they are allowed and hence the results are shown. If they try to delete from any table, then permission is not granted and an error is thrown.

If a DBA user tries to delete data from the table, they are allowed to do so since they have the privileges.

  • We can check the roles and privileges using the “Show Grants” statement.
  • Similarly, for revoking roles from users, we use the “REVOKE” statement.

Revoke role in MySQL

To revoke all privileges from the dba_users role.

REVOKE ALL ON employeesdb.* from dba_users;

To revoke specific privileges from the dev_users

REVOKE INSERT ON employeesdb.* from dev_users;

Drop role in MySQL

  • To drop a role, we use the Drop statement. For example, to drop the dba_users role
DROP ROLE dba_users;
Previous Page
Next Page