code prettify

Monday 18 September 2017

MySQL 8 - Roles

With the next version of MySQL that is MySQL 8, there is a very nice feature of creating "roles" which can be assigned certain privileges and then these roles can be assigned to users, thus helping us in maintaining the principle of least privilege.



It makes our life as developers and DBAs easier as we do not have to remember what are the specific privileges assigned to different users. These "roles" can simply be assigned or de-assigned from users as necessary.

The setup process is pretty easy and intuitive.

1. Create Role:

CREATE ROLE 'read', 'write', 'read_write';

Which essentially creates a set of named roles which do not have any privileges assigned to them, as yet.

2. Grant/Assign Privileges to a Role:

GRANT SELECT ON sample_db.* TO 'read';

3. Assign Role to user:

GRANT 'read' TO 'john'@'localhost', 'mary'@'localhost';

Reference: https://dev.mysql.com/doc/refman/8.0/en/roles.html