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