REPLACE MACRO AllDatabaseRightsV (DATABASENAME VARCHAR(128) CHARACTER SET UNICODE ,OPTIONS VARCHAR(100) DEFAULT '' NOT CASESPECIFIC) AS ( /* List rights held by any user on a named database, either as direct grants or through roles. -- Usage The 'Options' parameter controls the display of some rows. - this is a delimited set of values, I suggest that you use a space (' ') or a comma (',') - the values are: PUB - display AccessRights on the named database granted to PUBLIC SELF - display AccessRights the named database has on itself DBC - display AccessRights that user DBC has on the database (by default none of these sets of AccessRights are displayed) The column 'Grant Type' now describes how the user has received the AccessRight: - User: the AccessRight was specifically granted to the user - Direct Role: the AccessRight was granted to a role and that role was granted to the user - Nested Role: the AccessRight was granted to a role which was granted to another role that was granted to the user - Public: the AccessRight was granted to 'Public' (by default these AccessRights are not shown, see 'OPTIONS') -- Change Log - 2015-07-24 Changed some column titles to be more descriptive. Updated to clarify Role / Nested Role AccessRights. - 2014-05-21 Updated to include TD 15.00 AccessRights values. - 2013-04-10 Changed to correct the display of the UserName column. Also changed the title of some columns to describe their content more accurately. -- Notices - The original was developed by a Teradata PS consultant - This version developed and maintained by Ward Analytics Ltd 2013-2015 - You are free to use and redistribute this code but please leave this header here. */ LOCKING ROW FOR ACCESS SELECT CAST(TGTUSER AS VARCHAR(128)) AS USERNAME ,AR_TYPE (VARCHAR(11)) (NAMED GRANTTYPE, TITLE 'Grant Type') ,ROLENAME (VARCHAR(128)) (NAMED DIRECTROLENAME, TITLE 'Direct RoleName') ,ROLENAME2 (VARCHAR(128)) (NAMED NESTEDROLENAME, TITLE 'Nested RoleName') ,DATABASENAME (VARCHAR(128)) ,TABLENAME (VARCHAR(128)) (NAMED TABLENAME, TITLE 'TableName') ,COLUMNNAME (VARCHAR(128)) (NAMED COLUMNNAME, TITLE 'ColumnName') ,ACCESSRIGHT ,CASE WHEN DT1.ACCESSRIGHT = 'AE' THEN 'ALTER EXTERNAL PROCEDURE' WHEN DT1.ACCESSRIGHT = 'AF' THEN 'ALTER FUNCTION' WHEN DT1.ACCESSRIGHT = 'AP' THEN 'ALTER PROCEDURE' WHEN DT1.ACCESSRIGHT = 'AR' THEN 'OVERRIDE RESTORE' WHEN DT1.ACCESSRIGHT = 'AS' THEN 'ABORTSESSION' WHEN DT1.ACCESSRIGHT = 'CA' THEN 'CREATE AUTHORIZATION' WHEN DT1.ACCESSRIGHT = 'CD' THEN 'CREATE DATABASE' WHEN DT1.ACCESSRIGHT = 'CE' THEN 'CREATE EXTERNAL PROCEDURE' WHEN DT1.ACCESSRIGHT = 'CF' THEN 'CREATE FUNCTION' WHEN DT1.ACCESSRIGHT = 'CG' THEN 'CREATE TRIGGER' WHEN DT1.ACCESSRIGHT = 'CM' THEN 'CREATE MACRO' WHEN DT1.ACCESSRIGHT = 'CO' THEN 'CREATE PROFILE' WHEN DT1.ACCESSRIGHT = 'CP' THEN 'CHECKPOINT' WHEN DT1.ACCESSRIGHT = 'CR' THEN 'CREATE ROLE' WHEN DT1.ACCESSRIGHT = 'CT' THEN 'CREATE TABLE' WHEN DT1.ACCESSRIGHT = 'CU' THEN 'CREATE USER' WHEN DT1.ACCESSRIGHT = 'CV' THEN 'CREATE VIEW' WHEN DT1.ACCESSRIGHT = 'CZ' THEN 'CREATE ZONE' /* Added for TD 15.00 */ WHEN DT1.ACCESSRIGHT = 'D ' THEN 'DELETE' WHEN DT1.ACCESSRIGHT = 'DA' THEN 'DROP AUTHORIZATION' WHEN DT1.ACCESSRIGHT = 'DD' THEN 'DROP DATABASE' WHEN DT1.ACCESSRIGHT = 'DF' THEN 'DROP FUNCTION' WHEN DT1.ACCESSRIGHT = 'DG' THEN 'DROP TRIGGER' WHEN DT1.ACCESSRIGHT = 'DM' THEN 'DROP MACRO' WHEN DT1.ACCESSRIGHT = 'DO' THEN 'DROP PROFILE' WHEN DT1.ACCESSRIGHT = 'DP' THEN 'DUMP' WHEN DT1.ACCESSRIGHT = 'DR' THEN 'DROP ROLE' WHEN DT1.ACCESSRIGHT = 'DT' THEN 'DROP TABLE' WHEN DT1.ACCESSRIGHT = 'DU' THEN 'DROP USER' WHEN DT1.ACCESSRIGHT = 'DV' THEN 'DROP VIEW' WHEN DT1.ACCESSRIGHT = 'DZ' THEN 'DROP ZONE' /* Added for TD 15.00 */ WHEN DT1.ACCESSRIGHT = 'E ' THEN 'EXECUTE' WHEN DT1.ACCESSRIGHT = 'EF' THEN 'EXECUTE FUNCTION' WHEN DT1.ACCESSRIGHT = 'GC' THEN 'CREATE GLOP' WHEN DT1.ACCESSRIGHT = 'GD' THEN 'DROP GLOP' WHEN DT1.ACCESSRIGHT = 'GM' THEN 'GLOP MEMBER' WHEN DT1.ACCESSRIGHT = 'I' THEN 'INSERT' WHEN DT1.ACCESSRIGHT = 'IX' THEN 'INDEX' WHEN DT1.ACCESSRIGHT = 'MR' THEN 'MONRESOURCE' WHEN DT1.ACCESSRIGHT = 'MS' THEN 'MONSESSION' WHEN DT1.ACCESSRIGHT = 'NT' THEN 'NONTEMPORAL' WHEN DT1.ACCESSRIGHT = 'OA' THEN 'OVERRIDE DUMP CONSTRAINT' WHEN DT1.ACCESSRIGHT = 'OD' THEN 'OVERRIDE DELETE CONSTRAINT' WHEN DT1.ACCESSRIGHT = 'OI' THEN 'OVERRIDE INSERT CONSTRAINT' WHEN DT1.ACCESSRIGHT = 'OP' THEN 'CREATE OWNER PROCEDURE' WHEN DT1.ACCESSRIGHT = 'OS' THEN 'OVERRIDE SELECT CONSTRAINT' WHEN DT1.ACCESSRIGHT = 'OU' THEN 'OVERRIDE UPDATE CONSTRAINT' WHEN DT1.ACCESSRIGHT = 'PC' THEN 'CREATE PROCEDURE' WHEN DT1.ACCESSRIGHT = 'PD' THEN 'DROP PROCEDURE' WHEN DT1.ACCESSRIGHT = 'PE' THEN 'EXECUTE PROCEDURE' WHEN DT1.ACCESSRIGHT = 'R' THEN 'SELECT' WHEN DT1.ACCESSRIGHT = 'RF' THEN 'REFERENCE' WHEN DT1.ACCESSRIGHT = 'RO' THEN 'REPLCONTROL' WHEN DT1.ACCESSRIGHT = 'RS' THEN 'RESTORE' WHEN DT1.ACCESSRIGHT = 'SA' THEN 'CONSTRAINT ASSIGNMENT' WHEN DT1.ACCESSRIGHT = 'SD' THEN 'CONSTRAINT DEFINITION' WHEN DT1.ACCESSRIGHT = 'SH' THEN 'SHOW' WHEN DT1.ACCESSRIGHT = 'SR' THEN 'SETRESRATE' WHEN DT1.ACCESSRIGHT = 'SS' THEN 'SETSESSRATE' WHEN DT1.ACCESSRIGHT = 'ST' THEN 'STATISTICS' WHEN DT1.ACCESSRIGHT = 'TH' THEN 'CTCONTROL' WHEN DT1.ACCESSRIGHT = 'U' THEN 'UPDATE' WHEN DT1.ACCESSRIGHT = 'UM' THEN 'UDT METHOD' WHEN DT1.ACCESSRIGHT = 'UT' THEN 'UDT TYPE' WHEN DT1.ACCESSRIGHT = 'UU' THEN 'UDT USAGE' WHEN DT1.ACCESSRIGHT = 'ZO' THEN 'ZONE OVERRIDE' /* Added for TD 15.00 */ ELSE DT1.ACCESSRIGHT END (VARCHAR(26)) (NAMED ACCESSRIGHTDESC, TITLE 'AccessRight Description') ,GRANTAUTHORITY ,GRANTORNAME (VARCHAR(128)) (NAMED GRANTORNAME, TITLE 'GrantorName') ,ALLNESSFLAG ,CREATORNAME (VARCHAR(128)) ,CREATETIMESTAMP FROM (/* Direct grants on the database */ SELECT AR1.USERNAME AS TGTUSER ,CAST( (CASE WHEN AR1.USERNAME = TGTUSER THEN 'User' ELSE 'Public' END) AS VARCHAR(11)) AS AR_TYPE ,CAST(NULL AS VARCHAR(128)) AS ROLENAME ,CAST(NULL AS VARCHAR(128)) AS ROLENAME2 ,AR1.DATABASENAME ,AR1.TABLENAME ,AR1.COLUMNNAME ,AR1.ACCESSRIGHT ,AR1.GRANTORNAME ,AR1.CREATETIMESTAMP ,AR1.GRANTAUTHORITY ,AR1.ALLNESSFLAG ,AR1.CREATORNAME (VARCHAR(128)) FROM DBC.ALLRIGHTSV AS AR1 WHERE (AR1.USERNAME <> 'PUBLIC' OR (AR1.USERNAME = 'PUBLIC' AND POSITION('PUB' IN :OPTIONS) > 0) ) -- control AR's granted to PUBLIC AND (AR1.USERNAME <> AR1.DATABASENAME OR (AR1.USERNAME = AR1.DATABASENAME AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self AND AR1.DATABASENAME = :DATABASENAME AND (ar1.USERNAME <> 'DBC' OR (ar1.USERNAME = 'DBC' AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC UNION ALL /* Grants on the database directly to roles */ SELECT RM.GRANTEE AS TGTUSER ,CAST('Direct Role' AS VARCHAR(11)) AS AR_TYPE ,AR2.ROLENAME ,CAST(NULL AS VARCHAR(128)) AS ROLENAME2 ,AR2.DATABASENAME ,AR2.TABLENAME ,AR2.COLUMNNAME ,AR2.ACCESSRIGHT ,AR2.GRANTORNAME ,AR2.CREATETIMESTAMP ,CAST(NULL AS CHAR(1)) AS GRANTAUTHORITY ,CAST(NULL AS CHAR(1)) AS ALLNESSFLAG ,CAST(NULL AS VARCHAR(128)) AS CREATORNAME FROM DBC.ROLEMEMBERSV AS RM INNER JOIN DBC.ALLROLERIGHTSV AS AR2 ON AR2.DATABASENAME = :DATABASENAME AND RM.GRANTEEKIND = 'USER' AND AR2.ROLENAME = RM.ROLENAME AND (RM.GRANTEE <> AR2.DATABASENAME OR (RM.GRANTEE = AR2.DATABASENAME AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self AND (RM.GRANTEE <> 'DBC' OR (RM.GRANTEE = 'DBC' AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC UNION ALL /* Grants on the database to nested roles */ SELECT DRM.GRANTEE AS TGTUSER ,CAST('Nested Role' AS VARCHAR(11)) AS AR_TYPE ,DRM.ROLENAME AS DIRECTROLE ,AR3.ROLENAME AS NESTEDROLE ,AR3.DATABASENAME ,AR3.TABLENAME ,AR3.COLUMNNAME ,AR3.ACCESSRIGHT ,AR3.GRANTORNAME ,AR3.CREATETIMESTAMP ,CAST(NULL AS CHAR(1)) AS GRANTAUTHORITY ,CAST(NULL AS CHAR(1)) AS ALLNESSFLAG ,CAST(NULL AS VARCHAR(128)) AS CREATORNAME FROM DBC.ALLROLERIGHTSV AS AR3 INNER JOIN DBC.ROLEMEMBERSV AS NRM ON AR3.DATABASENAME = :DATABASENAME AND AR3.ROLENAME = NRM.ROLENAME AND NRM.GRANTEEKIND = 'ROLE' INNER JOIN DBC.ROLEMEMBERSV AS DRM ON NRM.GRANTEE = DRM.ROLENAME AND DRM.GRANTEEKIND = 'USER' AND (DRM.GRANTEE <> AR3.DATABASENAME OR (DRM.GRANTEE = AR3.DATABASENAME AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self AND (DRM.GRANTEE <> 'DBC' OR (DRM.GRANTEE = 'DBC' AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC ) AS DT1 ORDER BY 1,6,7,8,2; );