/*
FOR MAINTENANCE OF ROLES AND PRIVILEGES */
SELECT * FROM DBC.AllRights WHERE TABLENAME = 'ORDERS';
We need to create Roles and assign privileges to the roles, rather than individual roles.
The below query will give us the list of access rights for a particular user (DBC in this case) on a particular database (retail in the example below).
select DB.databasename
, tvm1.tvmname as tvmname
, user1.databasename as usrname
,AR.*
from dbc.accessrights as AR
inner join dbc.dbase as DB
on AR.databaseid = DB.databaseid
inner join DBC.TVM as tvm1
on AR.tvmid = tvm1.tvmid
inner join dbc.dbase as user1
on AR.userid = user1.databaseid
where user1.databasename = 'dbc'
and DB.databasename = 'retail';
If we keep assigning privileges to individual users, then this table will keep growing. In addition, maintenance will be a overhead.
Individual privileges are assigned as seen in the example below:
GRANT SELECT ON RETAIL.ORDERS TO TRAINING_1;
As per Teradata:
Using
Roles simplifies the management of user privileges with
roles.
Roles help improve performance by reducing the number of rows added to
and deleted from DBC.AccessRights.
To use roles, assign privileges to a
specific role and then grant the role to users. The users can then access all
the objects on which their assigned role and nested roles have privileges.
The above can be achieved using the below list of commands:
CREATE ROLE REPORT_USER;
GRANT SELECT ON RETAIL.ORDERS TO REPORT_USER;
SELECT * FROM DBC.USERS;
CREATE USER TRAINING_1
FROM RETAIL
AS PASSWORD = "TRAINING_1"
, PERMANENT = 40000
, DEFAULT ROLE = REPORT_USER;
DROP USER TRAINING_2;
CREATE USER TRAINING_2
FROM TRAINING_1
AS PASSWORD = "TRAINING_2"
, PERMANENT = 40000
, DEFAULT ROLE = REPORT_USER
, PROFILE = HIGH_SPOOL_USER;
CREATE PROFILE HIGH_SPOOL_USER
AS SPOOL = 10000;
SELECT * FROM DBC.ROLES;
SELECT * FROM DBC.RoleInfo;
 |
| Roles and Privileges in Teradata
To manage privileges using roles:
|
- Create different roles for the different job functions and responsibilities. Roles work best when based on job functions or responsibilities. Some users may belong to more than one role.
- Decide which privileges to which objects a role should have.
- Grant specific privileges on database objects to the roles.
- Grant the role to users or other roles. However, note that roles can only be nested one level.
- Assign default roles to users. Assigning a default role to a user allows the user to automatically access all the objects to which the role has been granted privileges. It also gives the user additional privileges to all the objects its nested roles also owns
Join us in our classroom for more discussion on Teradata.
