MySQL 8 implement role based privileges:
If you are wondering about how to implement new features of MySQL 8 about role based privileges. Then here is my 2 cents.
It doesn't work like Oracle in which we create role, grant privileges to role, grant role to the user, and you are all set.
There are two options available:
01. Activate role at session level, using set role
02. Activate all roles on login globally
Role at session level:
SET ROLE is used to modify user's effective privileges in the current session.
SET ROLE DEFAULT;
SET ROLE 'role1', 'role2';
SET ROLE ALL;
SET ROLE ALL EXCEPT 'role1', 'role2';
Activate role globally:
By default activate_all_roles_on_login is OFF, check it by using SELECT VARIABLES LIKE '%activate%';
Execute statement SET GLOBAL activate_all_roles_on_login = ON;
If you are logged in then log out and log in back and user will have the privileges.
In MySQL 8, you have to activate_all_roles_on_login and re-login in to the session if you are already login.
Ref.: https://dev.mysql.com/doc/refman/8.0/en/set-role.html
If you are wondering about how to implement new features of MySQL 8 about role based privileges. Then here is my 2 cents.
It doesn't work like Oracle in which we create role, grant privileges to role, grant role to the user, and you are all set.
There are two options available:
01. Activate role at session level, using set role
02. Activate all roles on login globally
Role at session level:
SET ROLE is used to modify user's effective privileges in the current session.
SET ROLE DEFAULT;
SET ROLE 'role1', 'role2';
SET ROLE ALL;
SET ROLE ALL EXCEPT 'role1', 'role2';
Activate role globally:
By default activate_all_roles_on_login is OFF, check it by using SELECT VARIABLES LIKE '%activate%';
Execute statement SET GLOBAL activate_all_roles_on_login = ON;
If you are logged in then log out and log in back and user will have the privileges.
In MySQL 8, you have to activate_all_roles_on_login and re-login in to the session if you are already login.
Ref.: https://dev.mysql.com/doc/refman/8.0/en/set-role.html
Comments
Post a Comment