How to reduce duplicate Access Rights entries from Teradata database

Database

How to reduce duplicate Access Rights entries from Teradata database

HI,

As we are new to TERADATA technology, Can anyone guide us to reduce the duplicate Access Rights entries from Teradata database ??

Presently in our teradata system, there are many duplicate entries made for single purpose. We need to make these access rights unique.

Can anyone share the related scripts OR useful information then I would appreciate them.

Urgent help required.

Regards,

Deepak Dhabade

4 REPLIES

Re: How to reduce duplicate Access Rights entries from Teradata database

Access Rights can be granted directly or through Roles. The preferred way of giving access rights is though roles, as it is a better way of managing access rights(it saves space and improves query performance). Duplicate entries may occur if the user is having the same access rights acquired through a role and also having direct access on the Database object.You may use DBC.Allrolerights & DBC.Allrights to identify all such cases where redundant entries have been made.Revoke the direct access from the user/database as the access right is already mapped through roles.

This will reduce the size of DBC.Allrights and help you achieve what you want.

Re: How to reduce duplicate Access Rights entries from Teradata database

Thanks Kishore for the update.

Teradata Employee

Re: How to reduce duplicate Access Rights entries from Teradata database

Hi Kishore,

Can you please share query to identify duplicate records in Accessrights table?

Re: How to reduce duplicate Access Rights entries from Teradata database

This script helps to list ALL duplicated Direct grants in Accessrights table.

LOCKING ROW FOR ACCESS
SELECT A.Username AS Username ,
A.Databasename AS Databasename,
A.Accessright AS Accessright,
A.Tablename AS Tablename,
A.Columnname AS Columnname,
A.GrantAuthority AS GrantAuthority
FROM dbc.ALLRights A,
(
SELECT Username, Databasename, Accessright,GrantAuthority
FROM dbc.ALLRights WHERE
-- to exclude any database from report, add the database name at IN clause
Username NOT IN ('DBC','TDWM','SYSTEMFE','SYSADMIN','DBCMANAGER')
AND Tablename='ALL'
GROUP BY 1,2,3,4
INTERSECT
SELECT Username, Databasename, Accessright,GrantAuthority
FROM dbc.ALLRights WHERE
-- to exclude any database from report, add the database name at IN clause
Username NOT IN ('DBC','TDWM','SYSTEMFE','SYSADMIN','DBCMANAGER')
AND Tablename<>'ALL'
GROUP BY 1,2,3,4
) B
WHERE A.Username = B.Username
AND A.Databasename = B.Databasename
AND A.Accessright = B.Accessright
AND A.Tablename<>'ALL'
ORDER BY 1,2,3,4,5,6
;