All Forums Database
foxbat 27 posts Joined 06/07
20 Jul 2007
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, CreateTimeStampFROM DBC.ALLRIGHTS ALRTS LEFT OUTER JOIN AccessRightsAbbv AbbvON 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, CreateTimeStampFROM DBC.ALLROLERIGHTS ALRTS LEFT OUTER JOIN AccessRightsAbbv AbbvON ALRTS.AccessRight = Abbv.AccessRight WHERE RoleName='E_COMPANYDB_DEVELOPER' AND DatabaseName='E_COMPANYDB'Order By 2,3,4,5;

30 Oct 2007

Thank you. This is indeed useful.

Ramakrishna Vedantam, Tata consultancy services, Hyderabad

vishwajeet.pol 1 post Joined 06/13
11 Jun 2013

This was helpful... :)

-
Vishwajeet

PeterJ 3 posts Joined 07/12
30 Jun 2013

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

sh_na 4 posts Joined 09/10
04 Jul 2013

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

sh_na 4 posts Joined 09/10
04 Jul 2013

Can you please include these ones as well..
SH
OP
GD
GM
GC
 

Fred 1096 posts Joined 08/04
06 Jul 2013

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

climbamtn 4 posts Joined 11/14
03 Feb 2016

How about the following:
OA
OR

dins2k2 51 posts Joined 05/13
03 Feb 2016

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_112A/AppB.123.08.html

Thanks,
Dinesh

Fred 1096 posts Joined 08/04
03 Feb 2016

See the Appendix in the Data Dictionary Manual.
OA = Override Dump
OR= Override Restore

Roopa singu 3 posts Joined 03/16
04 May 2016

what does EX means?

You must sign in to leave a comment.