15.10 - Determining Privileges for a User - Teradata Database

Teradata Database SQL Data Control Language

Product
Teradata Database
Release Number
15.10
Content Type
Programming Reference
Publication ID
B035-1149-151K
Language
English (United States)

You can create an AllUserRights macro to list all the privileges a user has on a specific database. The macro gets information from the DBC.AllRightsV and DBC.AllRolesRightsV views, and spells out the two character privilege code it finds in the AccessRightDesc field of the views.

create macro database_name.AllUserRights (UserName char(128))  as (
locking row for access select
     UserName     (varchar(128))
    ,AccessType   (varchar(128))
    ,RoleName     (varchar(128))
    ,DatabaseName (varchar(128))
    ,TableName    (varchar(128))
    ,ColumnName   (varchar(128))
    ,AccessRight
    ,case
        when accessright='AE' then 'ALTER EXTERNALPROCEDURE'
        when accessright='AF' then 'ALTER FUNCTION'
        when accessright='AP' then 'ALTER PROCEDURE'
        when accessright='AS' then 'ABORT SESSION'
        when accessright='CA' then 'CREATE AUTHORIZATION'
        when accessright='CD' then 'CREATE DATABASE'
        when accessright='CE' then 'CREATE EXTERNAL PROCEDURE'
        when accessright='CF' then 'CREATE FUNCTION'
        when accessright='CG' then 'CREATE TRIGGER'
        when accessright='CM' then 'CREATE MACRO'
        when accessright='CO' then 'CREATE PROFILE'
        when accessright='CP' then 'CHECKPOINT'
        when accessright='CR' then 'CREATE ROLE'
        when accessright='CT' then 'CREATE TABLE'
        when accessright='CU' then 'CREATE USER'
        when accessright='CV' then 'CREATE VIEW'
        when accessright='D'  then 'DELETE'
        when accessright='CZ' then 'CREATE ZONE'
        when accessright='DA' then 'DROP AUTHORIZATION'
        when accessright='DD' then 'DROP DATABASE'
        when accessright='DF' then 'DROP FUNCTION'
        when accessright='DG' then 'DROP TRIGGER'
        when accessright='DM' then 'DROP MACRO'
        when accessright='DO' then 'DROP PROFILE'
        when accessright='DP' then 'DUMP'
        when accessright='DR' then 'DROP ROLE'
        when accessright='DT' then 'DROP TABLE'
        when accessright='DU' then 'DROP USER'
        when accessright='DV' then 'DROP VIEW'
        when accessright='E'  then 'EXECUTE'
        when accessright='DZ' then 'DROP ZONE'
        when accessright='EF' then 'EXECUTE FUNCTION'
        when accessright='GC' then 'CREATE GLOP'
        when accessright='GD' then 'DROP GLOP'
        when accessright='GM' then 'GLOP MEMBER'
        when accessright='I'  then 'INSERT'
        when accessright='IX' then 'INDEX'
        when accessright='MR' then 'MONITOR RESOURCE'
        when accessright='MS' then 'MONITOR SESSION'
        when accessright='NT' then 'NONTEMPORAL'
        when accessright='OD' then 'OVERRIDE DELETE POLICY'
        when accessright='OI' then 'OVERRIDE INSERT POLICY'
        when accessright='OP' then 'CREATE OWNER PROCEDURE'
        when accessright='OS' then 'OVERRIDE SELECT POLICY'
        when accessright='OU' then 'OVERRIDE UPDATE POLICY'
        when accessright='PC' then 'CREATE PROCEDURE'
        when accessright='PD' then 'DROP PROCEDURE'
        when accessright='PE' then 'EXECUTE PROCEDURE'
        when accessright='R'  then 'RETRIEVE/SELECT'
        when accessright='RF' then 'REFERENCES'
        when accessright='RS' then 'RESTORE'
        when accessright='SA' then 'SECURITY CONSTRAINT ASSIGNMENT'
        when accessright='SD' then 'SECURITY CONSTRAINT DEFINITION'
        when accessright='ST' then 'STATISTICS'
        when accessright='SS' then 'SET SESSION RATE'
        when accessright='SR' then 'SET RESOURCE RATE'
        when accessright='TH' then 'CTCONTROL'        
        when accessright='U'  then 'UPDATE'
        when accessright='UU' then 'UDT Usage'
        when accessright='UT' then 'UDT Type'
        when accessright='UM' then 'UDT Method'
        when accessright='ZO' then 'ZONE OVERRIDE'
 else''
     end (varchar(26)) as AccessRightDesc
    ,GrantAuthority
    ,GrantorName (varchar(128))
    ,AllnessFlag
    ,CreatorName (varchar(128))
    ,CreateTimeStamp
 from
(
select                          
     UserName
    ,'User' (varchar(128)) as AccessType
    ,'' (varchar(128)) as RoleName
    ,DatabaseName
    ,TableName
    ,ColumnName
    ,AccessRight
    ,GrantAuthority
    ,GrantorName
    ,AllnessFlag
    ,CreatorName
    ,CreateTimeStamp
  from dbc.allrights
 where UserName = :username
   and CreatorName not = :username     
union all
select                           
     Grantee as UserName
    ,'Member' as UR
    ,r.RoleName
    ,DatabaseName
    ,TableName
    ,ColumnName
    ,AccessRight
    ,null (char(1)) as GrantAuthority
    ,GrantorName
    ,null (char(1)) as AllnessFlag
    ,null (char(1)) as CreatorName
    ,CreateTimeStamp
 from dbc.allrolerights r
 join dbc.rolemembers m
   on m.RoleName = r.RoleName
 where UserName = :username
union all
select                           
     User as UserName
    ,m.Grantee as UR
    ,r.RoleName
    ,DatabaseName
    ,TableName
    ,ColumnName
    ,AccessRight
    ,null (char(1)) as GrantAuthority
    ,GrantorName
    ,null (char(1)) as AllnessFlag
    ,null (char(1)) as CreatorName
    ,CreateTimeStamp
 from dbc.allrolerights r
 join dbc.rolemembers m
   on m.RoleName = r.RoleName
 where m.grantee in (select rolename from dbc.rolemembers where grantee = :username)
 ) AllRights
order by 4,5,6,7;  );

where database_name is the name of a database in your system, for which the macro checks user privileges. For example, if you create the macro in the DBAdmin database, you identify the macro as DBAdmin.AllUserRights.

Note: This macro returns all privileges granted to a user either directly or through a role. It does not return implicit (ownership) privileges.

After you create the macro, you can execute it to check access privileges for a particular user with the command:

execute database_name.AllUserRights ('username'); 

where:

  • database_name is the name of the macro, and also identifies database for which the macro checks user privileges
  • username is the name of a permanent database user for which the macro checks privileges
  • The user that executes the macro must have EXECUTE privileges on the database that contains the macro.