How to identify all rights for a Teradata user?

Question

I'm looking for a way to identify the default permissions granted to a user in a particular database. I have tried this:

select *
from dbc.allrights
where username='user-id'
  and databasename='database-name'

There are two problems with the above; first, as written, the query returns one row for each permisison for each table owned by the user-id and it includes permissions that were specifically granted. Second, if the user-d has not created any tables at all, no rows are returned.

I'm hoping there is be another DBC view that contains the default permissions for a user and for a database.

Answer 1

This sql is the same but expand more of the access right:

SELECT RN.Grantee
       ,ARR.DatabaseName
       ,ARR.AccessRight
       ,CASE 
            WHEN ARR.AccessRight = 'AE' THEN 'ALTER EXTERNAL PROCEDURE'    
            WHEN ARR.AccessRight = 'AF' THEN 'ALTER FUNCTION'    
            WHEN ARR.AccessRight = 'AP' THEN 'ALTER PROCEDURE'    
            WHEN ARR.AccessRight = 'AS' THEN 'ABORT SESSION'    
            WHEN ARR.AccessRight = 'CA' THEN 'CREATE AUTHORIZATION'    
            WHEN ARR.AccessRight = 'CD' THEN 'CREATE DATABASE'    
            WHEN ARR.AccessRight = 'CE' THEN 'CREATE EXTERNAL PROCEDURE'    
            WHEN ARR.AccessRight = 'CF' THEN 'CREATE FUNCTION'    
            WHEN ARR.AccessRight = 'CG' THEN 'CREATE TRIGGER'    
            WHEN ARR.AccessRight = 'CM' THEN 'CREATE MACRO'    
            WHEN ARR.AccessRight = 'CO' THEN 'CREATE PROFILE'    
            WHEN ARR.AccessRight = 'CP' THEN 'CHECKPOINT'    
            WHEN ARR.AccessRight = 'CR' THEN 'CREATE ROLE'    
            WHEN ARR.AccessRight = 'CT' THEN 'CREATE TABLE'    
            WHEN ARR.AccessRight = 'CU' THEN 'CREATE USER'    
            WHEN ARR.AccessRight = 'CV' THEN 'CREATE VIEW'    
            WHEN ARR.AccessRight = 'D'  THEN 'DELETE'    
            WHEN ARR.AccessRight = 'DA' THEN 'DROP AUTHORIZATION'    
            WHEN ARR.AccessRight = 'DD' THEN 'DROP DATABASE'    
            WHEN ARR.AccessRight = 'DF' THEN 'DROP FUNCTION'    
            WHEN ARR.AccessRight = 'DG' THEN 'DROP TRIGGER'    
            WHEN ARR.AccessRight = 'DM' THEN 'DROP MACRO'    
            WHEN ARR.AccessRight = 'DO' THEN 'DROP PROFILE'    
            WHEN ARR.AccessRight = 'DP' THEN 'DUMP'    
            WHEN ARR.AccessRight = 'DR' THEN 'DROP ROLE'    
            WHEN ARR.AccessRight = 'DT' THEN 'DROP TABLE'    
            WHEN ARR.AccessRight = 'DU' THEN 'DROP USER'    
            WHEN ARR.AccessRight = 'DV' THEN 'DROP VIEW'    
            WHEN ARR.AccessRight = 'E'  THEN 'EXECUTE'    
            WHEN ARR.AccessRight = 'EF' THEN 'EXECUTE FUNCTION'    
            WHEN ARR.AccessRight = 'GC' THEN 'CREATE GLOP'    
            WHEN ARR.AccessRight = 'GD' THEN 'DROP GLOP'    
            WHEN ARR.AccessRight = 'GM' THEN 'GLOP MEMBER'    
            WHEN ARR.AccessRight = 'I'  THEN 'INSERT'    
            WHEN ARR.AccessRight = 'IX' THEN 'INDEX'    
            WHEN ARR.AccessRight = 'MR' THEN 'MONITOR RESOURCE'    
            WHEN ARR.AccessRight = 'MS' THEN 'MONITOR SESSION'    
            WHEN ARR.AccessRight = 'NT' THEN 'NONTEMPORAL'    
            WHEN ARR.AccessRight = 'OD' THEN 'OVERRIDE DELETE POLICY'    
            WHEN ARR.AccessRight = 'OI' THEN 'OVERRIDE INSERT POLICY'    
            WHEN ARR.AccessRight = 'OP' THEN 'CREATE OWNER PROCEDURE'    
            WHEN ARR.AccessRight = 'OS' THEN 'OVERRIDE SELECT POLICY'    
            WHEN ARR.AccessRight = 'OU' THEN 'OVERRIDE UPDATE POLICY'    
            WHEN ARR.AccessRight = 'PC' THEN 'CREATE PROCEDURE'    
            WHEN ARR.AccessRight = 'PD' THEN 'DROP PROCEDURE'    
            WHEN ARR.AccessRight = 'PE' THEN 'EXECUTE PROCEDURE'    
            WHEN ARR.AccessRight = 'R'  THEN 'SELECT'    
            WHEN ARR.AccessRight = 'RF' THEN 'REFERENCE'    
            WHEN ARR.AccessRight = 'RO' THEN 'REPLCONTROL'    
            WHEN ARR.AccessRight = 'RS' THEN 'RESTORE'    
            WHEN ARR.AccessRight = 'SA' THEN 'SECURITY CONSTRAINT ASSIGNMENT'    
            WHEN ARR.AccessRight = 'SD' THEN 'SECURITY CONSTRAINT DEFINITION'    
            WHEN ARR.AccessRight = 'SH' THEN 'SHOW'    
            WHEN ARR.AccessRight = 'SR' THEN 'SET RESOURCE RATE'    
            WHEN ARR.AccessRight = 'SS' THEN 'SET SESSION RATE'    
            WHEN ARR.AccessRight = 'ST' THEN 'STATISTICS'    
            WHEN ARR.AccessRight = 'TH' THEN 'CTCONTROL'    
            WHEN ARR.AccessRight = 'U'  THEN 'UPDATE'
            ELSE 'Unknown'
        END AS AccesRightText
  FROM DBC.RoleMembers AS RN
INNER JOIN DBC.AllRoleRights AS ARR 
    ON RN.RoleName = ARR.RoleName 
 WHERE RN.Grantee = 'User'
   AND ARR.DatabaseName IN ('Database1', 'Database2')
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 3, 4

Answer 2

You can use this query to check the accesses of a user to different databases :

SELECT               
       A.GRANTEE as ProxyID,B.DATABASENAME,
       CASE WHEN B.ACCESSRIGHT = 'D' THEN 'DELETE'
            WHEN B.ACCESSRIGHT = 'I' THEN 'INSERT'
            WHEN B.ACCESSRIGHT = 'R' THEN 'SELECT'
            WHEN B.ACCESSRIGHT = 'SH' THEN 'SHOW TABLE/VIEW'
            WHEN B.ACCESSRIGHT = 'U' THEN 'UPDATE' ELSE 'OTHER' END ACCESS_LEVEL
   FROM    
       DBC.ROLEMEMBERS A Join DBC.ALLROLERIGHTS B 
   ON          
       A.ROLENAME = B.ROLENAME 
   WHERE 
       Grantee='USER_ID'  AND B.DATABASENAME IN ('DATABASE1','DATABASE2',.....)

   GROUP BY 1,2,3
   ORDER BY 1,2,3

Hope it might help you.

Answer 3

Implicit rights at the database level would only exist if the user created the database themselves. Otherwise, implicit rights are at the object level within the database the created the object.

Explicit rights granted by the system or security admin are suggested to be managed at a role level. Role membership can be determined within the DBC.RoleMembers view. Access rights for a given role can be identified in the DBC.AllRoleRights. However, if my memory serves correctly, explicit rights granted at the database level to a user can be determined if you reference the psuedo-table 'All' in the DBC.AllRights view.