Views for Checking User And Database Privileges in Teradata

The following views list privileges:

• DBC.AllRightsV

• DBC.UserGrantedRightsV

• DBC.UserRightsV

• DBC.RoleMembersV

• DBC.AllRoleRightsV

• DBC.UserRoleRightsV

 

DBC.AllRightsV

The DBC.AllRightsV view gives the access rights for all user on the system. The column names are: UserName, DatabaseName, TableName, FieldName, AccessRight, WithGrantOption, GrantorName and AllnessFlag.           

 

SELECT  *

FROM dbc.allrightsV

WHERE username = 'myuser';

 

• DBC.UserGrantedRightsV

The DBC.UserGrantedRightsV view provides information on access rights that the current user has granted to other users.  The column names are: DatabaseName, TableName, FieldName, Grantee, AccessRight, WithGrant and AllnessFlag. 

 

SELECT  *

FROM dbc.UserGrantedRightsV;


• DBC.UserRightsV

The DBC.UserRightsV view gives the access rights that have been granted to the current user.  The column names are: DatabaseName, TableName, FieldName, AccessRight, WithGrant and GrantorName


SELECT  *

FROM dbc.UserRightsV;

 

• DBC.RoleMembersV

The RoleMembersV view provides information about members of roles. The column names are: RoleName, Grantee, GranteeKind, Grantor, WhenGranted, DefaultRole and WithAdmin.            

SELECT * from DBC.ROLEMEMBERSV where Grantee='myuser';

 

• DBC.AllRoleRightsV

The AllRoleRightsV view provides information about the rights granted to each role. The column names are: UserName, DatabaseName, TableName, ColumnName, AccessRight, GrantAuthority, GrantorName and CreateTimeStamp.      

 

SELECT *

from DBC.ALLROLERIGHTSV

where role name in (SELECT rolename from DBC.ROLEMEMBERS where Grantee='myuser')

order by role name;

 

• DBC.UserRoleRightsV

The UserRoleRightsV view provides a list of rights granted to the current role of the user and its nested roles. The columns names are: RoleName, DatabaseName, TableName, ColumnName, AccessRight, GrantorName and CreateTimeStamp.

 

Select *

From dbc.UserRoleRightsV;

 

Privilege Abbreviations in System Views

•AE = ALTER EXTERNAL PROCEDURE

•AF = ALTER FUNCTION

•AP = ALTER PROCEDURE

•AS = ABORT SESSION

•CA = CREATE AUTHORIZATION

•CD = CREATE DATABASE

•CE = CREATE EXTERNAL PROCEDURE

•CF = CREATE FUNCTION

•CG = CREATE TRIGGER

•CM = CREATE MACRO

•CO = CREATE PROFILE

•CP = CHECKPOINT

•CR = CREATE ROLE

•CS = CREATE SERVER

•CT = CREATE TABLE

•CU = CREATE USER

•CV = CREATE VIEW

•CZ = CREATE ZONE

•D = DELETE

•DA = DROP AUTHORIZATION

•DD = DROP DATABASE

•DF = DROP FUNCTION

•DG = DROP TRIGGER

•DM = DROP MACRO

•DO = DROP PROFILE

•DP = DUMP

•DR = DROP ROLE

•DS = DROP SERVER

•DT = DROP TABLE

•DU = DROP USER

•DV = DROP VIEW

•DZ = DROP ZONE

•E = EXECUTE

•EF = EXECUTE FUNCTION

•GC = CREATE GLOP

•GD = DROP GLOP

•GM = GLOP MEMBER

•I = INSERT

•IX = INDEX

•MR = MONITOR RESOURCE

•MS = MONITOR SESSION

•NT = NONTEMPORAL

•OA = OVERRIDE DUMP CONSTRAINT

•OD = OVERRIDE DELETE CONSTRAINT

•OI = OVERRIDE INSERT CONSTRAINT

•OP = CREATE OWNER PROCEDURE

•OR= OVERRIDE RESTORE CONSTRAINT

•OS = OVERRIDE SELECT CONSTRAINT

•OU = OVERRIDE UPDATE CONSTRAINT

•PC = CREATE PROCEDURE

•PD = DROP PROCEDURE

•PE = EXECUTE PROCEDURE

•R = RETRIEVE/SELECT

•RF = REFERENCES

•RS = RESTORE

•SA = CONSTRAINT ASSIGNMENT

•SD = CONSTRAINT DEFINITION

•SH = SHOW

•SR = SET RESOURCE RATE

•SS = SET SESSION RATE

•ST = STATISTICS

•TH = CTCONTROL

•U = UPDATE

•UM = UDT METHOD

•UT = UDT TYPE

•UU = UDT USAGE

•ZO = ZONE OVERRIDE

Comments