【Teradata】角色与权限

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