1.角色权限仅允许嵌套一层，不允许多层嵌套
The following users can drop a role:
1. DBC
2. Any user given the system right DROP ROLE
3. Any user granted the role WITH ADMIN OPTION 任何授予角色管理选项的用户=>GRANT role_name TO user_name [WITH ADMIN OPTION];
4. A user whose current role has the specified role as a nested role, and the nested role was granted to the current role WITH ADMIN OPTION =>GRANT role_name TO role_name [WITH ADMIN OPTION];
2.角色使用语法
CREATE ROLE role_name;
GRANT access_rights TO role_name;
GRANT role_name TO user_name [WITH ADMIN OPTION];
– ADMIN OPTION allows grantee the right to grant or drop the role.
REVOKE ... role_name … ;
DROP ROLE role_name ;
SELECT ROLE ;
3.设定默认角色
一个用户属于多个角色时，需要将默认角色设定为ALL所有角色权限才会生效，否则只有指定的Default role的权限才有效。改动Default role需下次登录时权限才生效
SET ROLE role_name / NONE / NULL / ALL; – Assigns/changes current role for session. – Role must be granted to user before statement is valid. – SET ROLE ALL; All valid roles for user are available to user. CREATE/MODIFY USER user1 AS DEFAULT ROLE = NULL / ALL / role_name; – When the user logs on, the default role will become the session’s initial current role.
sel rolename from dbc.usersv --查看用户默认角色
参考文档：
DBA v1620.1 Student Manual.pdf