Access Right Abbreviations

Database
Enthusiast

Access Right Abbreviations


Thought this might come in handy for those of you who had trouble understanding the Access Rights abbreviations displayed in the Data Dictionary.

The script below maintains the full descriptions for the access rights and can be joined to DBC.ALLRIGHTS/ DBC.ALLROLERIGHTS.

/************************Set up Volatile Table**************************/
DROP TABLE AccessRightsAbbv;

CREATE VOLATILE SET TABLE AccessRightsAbbv
(
Description VARCHAR(50),
AccessRight CHAR(2)

)PRIMARY INDEX(AccessRight) ON COMMIT PRESERVE ROWS;

ins AccessRightsAbbv('CHECKPOINT','CP');
ins AccessRightsAbbv('CREATE AUTHORIZATION','CA');
ins AccessRightsAbbv('CREATE MACRO','CM');
ins AccessRightsAbbv('CREATE PROCEDURE','PC');
ins AccessRightsAbbv('CREATE TABLE','CT');
ins AccessRightsAbbv('CREATE TRIGGER','CG');
ins AccessRightsAbbv('CREATE VIEW','CV');
ins AccessRightsAbbv('DELETE','D');
ins AccessRightsAbbv('DROP AUTHORIZATION','DA');
ins AccessRightsAbbv('DROP FUNCTION ','DF');
ins AccessRightsAbbv('DROP MACRO','DM');
ins AccessRightsAbbv('DROP PROCEDURE','PD');
ins AccessRightsAbbv('DROP TRIGGER','DG');
ins AccessRightsAbbv('DROP VIEW','DV');
ins AccessRightsAbbv('DUMP','DP');
ins AccessRightsAbbv('INSERT','I');
ins AccessRightsAbbv('RESTORE','RS');
ins AccessRightsAbbv('SELECT','R');
ins AccessRightsAbbv('UPDATE','U');
ins AccessRightsAbbv('CREATE DATABASE','CD');
ins AccessRightsAbbv('CREATE USER','CU');
ins AccessRightsAbbv('DROP DATABASE','DD');
ins AccessRightsAbbv('DROP USER','DU');
ins AccessRightsAbbv('ALTER EXTERNAL PROCEDURE','AE');
ins AccessRightsAbbv('ALTER FUNCTION','AF');
ins AccessRightsAbbv('ALTER PROCEDURE','AP');
ins AccessRightsAbbv('CREATE EXTERNAL PROCEDURE','CE');
ins AccessRightsAbbv('CREATE FUNCTION','CF');
ins AccessRightsAbbv('EXECUTE FUNCTION','EF');
ins AccessRightsAbbv('EXECUTE PROCEDURE','PE');
ins AccessRightsAbbv('EXECUTE','E');
ins AccessRightsAbbv('DROP TABLE','DT');
ins AccessRightsAbbv('INDEX','IX');
ins AccessRightsAbbv('REFERENCES','RF');
ins AccessRightsAbbv('ABORT SESSION','AS');
ins AccessRightsAbbv('MONRESOURCE','MR');
ins AccessRightsAbbv('MONSESSION','MS');
ins AccessRightsAbbv('SETRESRATE','SR');
ins AccessRightsAbbv('SETSESSRATE','SS');
ins AccessRightsAbbv('CREATE PROFILE','CO');
ins AccessRightsAbbv('CREATE ROLE','CR');
ins AccessRightsAbbv('DROP PROFILE','DO');
ins AccessRightsAbbv('DROP ROLE','DR');
ins AccessRightsAbbv('REPLCONTROL','RO');

/***************SQL for AccessRights held by a user***********************/

SELECT UserName, DatabaseName,
TableName,ColumnName,
CASE WHEN Abbv.AccessRight IS NOT NULL THEN Abbv.Description
ELSE ALRTS.AccessRight
END AS AccessRight,
GrantAuthority,
GrantorName,
AllnessFlag,
CreatorName,
CreateTimeStamp
FROM DBC.ALLRIGHTS ALRTS LEFT OUTER JOIN AccessRightsAbbv Abbv
ON ALRTS.AccessRight = Abbv.AccessRight
WHERE UserName='Josephm1'
AND DatabaseName='E_COMPANYDB'
Order By 2,3,4,5;

/*************SQL for AccessRights held by a ROLE************************/
SELECT RoleName, DatabaseName,
TableName,ColumnName,
CASE WHEN Abbv.AccessRight IS NOT NULL THEN Abbv.Description
ELSE ALRTS.AccessRight
END AS AccessRight,
GrantorName,
CreateTimeStamp
FROM DBC.ALLROLERIGHTS ALRTS LEFT OUTER JOIN AccessRightsAbbv Abbv
ON ALRTS.AccessRight = Abbv.AccessRight
WHERE RoleName='E_COMPANYDB_DEVELOPER'
AND DatabaseName='E_COMPANYDB'
Order By 2,3,4,5;
10 REPLIES
Enthusiast

Re: Access Right Abbreviations

Thank you. This is indeed useful.

Re: Access Right Abbreviations

This was helpful... :)

Fan

Re: Access Right Abbreviations

Thank you, foxbat!

Another one to include, for newer versions of Teradata, is 'ST' for 'COLLECT STATISTICS'.

Fan

Re: Access Right Abbreviations

I saw a new one in TD14 'SH' what does this refer to?

Fan

Re: Access Right Abbreviations

Can you please include these ones as well..

SH

OP

GD

GM

GC

Teradata Employee

Re: Access Right Abbreviations

See the Data Dictionary manual for the appropriate release for a complete list.  

SH = Show

GC = Create GLOP

GD = Drop GLOP

GM = GLOP Member

OP = Create Owner Procedure

Re: Access Right Abbreviations

How about the following:

OA

OR

Enthusiast

Re: Access Right Abbreviations

Hi,

OA = OVERRIDE DUMP CONSTRAINT

OR= OVERRIDE RESTORE CONSTRAINT

Below link has all the privilege codes.

http://www.info.teradata.com/htmlpubs/DB_TTU_14_10/index.html#page/Database_Management/B035_1100_112...

Thanks,

Dinesh

Teradata Employee

Re: Access Right Abbreviations

See the Appendix in the Data Dictionary Manual.

OA = Override Dump

OR= Override Restore