Download (direct link):
You can use roles and profiles to simplify the management of users and access rights, and to reduce the number of rows in the DBC.AccessRights table.
Any user except DBC must be explicitly granted the CREATE PROFILE and CREATE ROLE privileges in order to create, drop, grant, and implement a role or profile (see "Granting CREATE and Access Privileges to a New User" on page 5-8).
When you create a role or profile, you automatically receive creator privileges (ownership does not apply). This gives you the DROP ROLE or DROP PROFILE privilege on a role or profile, and the WITH ADMIN OPTION privilege on a role.
Note: Creator privileges do not give you the right to assign a default role or a profile to a user. These parameters are specified in the PROFILE clause of CREATE/MODIFY USER, for which you need the DROP USER privilege.
As long as you still have the WITH ADMIN OPTION (it has not been explicitly revoked by the user who granted you the CREATE ROLE/PROFILE privilege), creator privileges allow you to:
• Drop any role or profile you created
• Grant any role you create to other users and roles
• Grant a role you create to another user with the WITH ADMIN OPTION, enabling that user to:
• Grant that role to users
• Revoke that role from any of the grantees
• Drop that role
• Revoke any role you granted (including the WITH ADMIN OPTION privilege, if you granted it along with the role)
5 - 10
Teradata RDBMS Database AdministrationChapter 5: Setting Up Users, Profiles, Accounts and Accounting
Implementing Roles and Profiles
Roles define access privileges on database objects. When you assign a default role to a user, you give the user access to all the objects that the role has been granted privileges to. A default role that has a role as a member gives the user additional access to all the objects that the nested role has privileges to.
A newly created role does not have any associated privileges until grants are made to it. To manage user access privileges, you can:
• Create different roles for different job functions and responsibilities.
• Grant specific privileges on database objects to the roles.
• Assign default roles to users.
• Add members to the role.
• Members of a role can be users or other roles.
• Roles can only be nested one level. Thus, a role that has a role member cannot also be a member of another role.
Rules of Use
The rules for using roles are as follows:
• You can grant one or more roles to one or more users and/or roles; thus:
• A role can have many members
• A user or role can be a member of more than one role
• Only single-level nesting is allowed; that is, a role that has a member role cannot also be a member of another role.
• An access privilege granted to an existing role immediately affects any user and/or role that is specified as a recipient in the GRANT statement and currently active within in a session.
• The privileges of a role granted to another role are inherited by every user member of the grantee role.
• When a user logs on, the assigned default role is the initial current role for the session and is used to authorize access after all checks against individually granted rights have failed.
• Once the session is active, the user can submit a SET ROLE statement to change or nullify the current role.
When you grant a role to a user with the WITH ADMIN OPTION, the grantee is able to:
• Drop the role
• Grant the role to other users and roles
• Grant the role to another user with the WITH ADMIN OPTION
• Revoke the role from a grantee
5 - 10 Teradata RDBMS Database Administration
Chapter 5: Setting Up Users, Profiles, Accounts and Accounting
Implementing Roles and Profiles
Example of Using Roles
For example, assume you have created a database administration user named SYSDBA, and that user DBC has already granted ALL (every privilege) on ALL (every object in the database) to SYSDBA with the WITH GRANT OPTION.
Also assume that now the SYSDBA submits the following statements to implement roles for the Accounting database:
CREATE ROLE Rolel;
GRANT SELECT ON Accounting TO Rolel;
CREATE ROLE Role2;
GRANT SELECT, UPDATE, INSERT, DELETE ON Accounting.AccPay TO Role2;
GRANT Rolel TO Alan, Betty, Charles, David, Ellen;
GRANT Rolel, Role2 TO Charles WITH ADMIN OPTION; GRANT CREATE ROLE TO Charles;
The following table describes the privileges of user Charles:
Because SYSDBA submitted. Charles can submit.
GRANT SELECT, UPDATE, INSERT, DELETE ON Accounting.AccPay TO Role2; SELECT, UPDATE, INSERT, or DELETE on the AccPay table. But he must first set his current session role with: SET ROLE Role2;
GRANT CREATE ROLE TO Charles; CREATE ROLE Role3; GRANT Role3 TO Francis;
GRANT Rolel, Role2 TO Charles WITH ADMIN OPTION; GRANT Rolel TO Greg; REVOKE Rolel FROM David; DROP ROLE Role2; He can grant, revoke, or drop Role1 or Role2 as needed.