REPLACE MACRO AllUserRights (USERNAME VARCHAR(128) CHARACTER SET LATIN ,OPTIONS VARCHAR(100) DEFAULT '' NOT CASESPECIFIC) AS ( /* List rights held by the specified user 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 updated 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 CHAR(30)) 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 to the user */ SELECT :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.ALLRIGHTS AS AR1 WHERE (USERNAME = :USERNAME OR (USERNAME = 'PUBLIC' AND POSITION('PUB' IN :OPTIONS) > 0) ) -- control AR's granted to PUBLIC AND (USERNAME <> AR1.DATABASENAME OR (USERNAME = AR1.DATABASENAME AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self AND (AR1.DATABASENAME <> 'DBC' OR (AR1.DATABASENAME = 'DBC' AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC UNION ALL /* Grants to the user's direct roles */ SELECT :USERNAME 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.ROLEMEMBERS AS RM INNER JOIN DBC.ALLROLERIGHTS AS AR2 ON RM.GRANTEE = :USERNAME AND RM.GRANTEEKIND = 'USER' AND AR2.ROLENAME = RM.ROLENAME AND (TGTUSER <> AR2.DATABASENAME OR (TGTUSER = AR2.DATABASENAME AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self AND (AR2.DATABASENAME <> 'DBC' OR (AR2.DATABASENAME = 'DBC' AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC UNION ALL /* Grants to the user's nested roles */ SELECT :USERNAME AS TGTUSER ,CAST('Nested Role' AS VARCHAR(11)) AS AR_TYPE ,RM1.ROLENAME ,AR3.ROLENAME ,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.ROLEMEMBERS AS RM1 INNER JOIN DBC.ROLEMEMBERS AS RM2 ON RM1.GRANTEE = :USERNAME AND RM1.GRANTEEKIND = 'USER' AND RM1.ROLENAME = RM2.GRANTEE AND RM2.GRANTEEKIND = 'ROLE' INNER JOIN DBC.ALLROLERIGHTS AS AR3 ON AR3.ROLENAME = RM2.ROLENAME AND (TGTUSER <> AR3.DATABASENAME OR (TGTUSER = AR3.DATABASENAME AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self AND (AR3.DATABASENAME <> 'DBC' OR (AR3.DATABASENAME = 'DBC' AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC ) AS DT1 ORDER BY 5,6,7,8,2; );