Archives of the TeradataForum

Message Posted: Sat, 09 Mar 2013 @ 10:38:56 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

HI all,

As a shameless example of "plagiarise with pride" here is a slightly updated version of the original supplied by Dave Clark.

It does the same job but has the following changes:

1) It uses the "V" views (i.e. DBC.AllRightsV and not DBC. AllRights) and hence allows for object names with unicode characters

2) the parameter is now VARCHAR(128) CHARACTER SET UNICODE to match with the new views

3) I have taken the list of accessrights codes from the TD14.10 documentation. To be honest I'm not sure if there are any changes here but I thought "what the heck".

4) The final "ELSE" clause in the CASE statement now returns the value of the ACCESSRIGHT column, so you can immediately tell which one I've missed out :-)

Enjoy,

Dave

     REPLACE MACRO AllUserRights (UserName VARCHAR(128) CHARACTER SET UNICODE)
     AS (
     /* List rights held by a user as direct grants or through roles */
     LOCKING ROW FOR ACCESS SELECT
          UserName     (VARCHAR(128))
         ,AccessType   (VARCHAR(128))
         ,RoleName     (VARCHAR(128))
         ,DatabaseName (VARCHAR(128))
         ,TableName    (VARCHAR(128))
         ,ColumnName   (VARCHAR(128))
         ,AccessRight
         ,CASE
           WHEN accessright = 'AE' THEN 'ALTER EXTERNAL PROCEDURE'
           WHEN accessright = 'AF' THEN 'ALTER FUNCTION'
           WHEN accessright = 'AP' THEN 'ALTER PROCEDURE'
           WHEN accessright = 'AR' THEN 'OVERRIDE RESTORE'
           WHEN accessright = 'AS' THEN 'ABORTSESSION'
           WHEN accessright = 'CA' THEN 'CREATE AUTHORIZATION'
           WHEN accessright = 'CD' THEN 'CREATE DATABASE'
           WHEN accessright = 'CE' THEN 'CREATE EXTERNAL PROCEDURE'
           WHEN accessright = 'CF' THEN 'CREATE FUNCTION'
           WHEN accessright = 'CG' THEN 'CREATE TRIGGER'
           WHEN accessright = 'CM' THEN 'CREATE MACRO'
           WHEN accessright = 'CO' THEN 'CREATE PROFILE'
           WHEN accessright = 'CP' THEN 'CHECKPOINT'
           WHEN accessright = 'CR' THEN 'CREATE ROLE'
           WHEN accessright = 'CT' THEN 'CREATE TABLE'
           WHEN accessright = 'CU' THEN 'CREATE USER'
           WHEN accessright = 'CV' THEN 'CREATE VIEW'
           WHEN accessright = 'D' THEN 'DELETE'
           WHEN accessright = 'DA' THEN 'DROP AUTHORIZATION'
           WHEN accessright = 'DD' THEN 'DROP DATABASE'
           WHEN accessright = 'DF' THEN 'DROP FUNCTION'
           WHEN accessright = 'DG' THEN 'DROP TRIGGER'
           WHEN accessright = 'DM' THEN 'DROP MACRO'
           WHEN accessright = 'DO' THEN 'DROP PROFILE'
           WHEN accessright = 'DP' THEN 'DUMP'
           WHEN accessright = 'DR' THEN 'DROP ROLE'
           WHEN accessright = 'DT' THEN 'DROP TABLE'
           WHEN accessright = 'DU' THEN 'DROP USER'
           WHEN accessright = 'DV' THEN 'DROP VIEW'
           WHEN accessright = 'E' THEN 'EXECUTE'
           WHEN accessright = 'EF' THEN 'EXECUTE FUNCTION'
           WHEN accessright = 'GC' THEN 'CREATE GLOP'
           WHEN accessright = 'GD' THEN 'DROP GLOP'
           WHEN accessright = 'GM' THEN 'GLOP MEMBER'
           WHEN accessright = 'I' THEN 'INSERT'
           WHEN accessright = 'IX' THEN 'INDEX'
           WHEN accessright = 'MR' THEN 'MONRESOURCE'
           WHEN accessright = 'MS' THEN 'MONSESSION'
           WHEN accessright = 'NT' THEN 'NONTEMPORAL'
           WHEN accessright = 'OA' THEN 'OVERRIDE DUMP CONSTRAINT'
           WHEN accessright = 'OD' THEN 'OVERRIDE DELETE CONSTRAINT'
           WHEN accessright = 'OI' THEN 'OVERRIDE INSERT CONSTRAINT'
           WHEN accessright = 'OP' THEN 'CREATE OWNER PROCEDURE'
           WHEN accessright = 'OS' THEN 'OVERRIDE SELECT CONSTRAINT'
           WHEN accessright = 'OU' THEN 'OVERRIDE UPDATE CONSTRAINT'
           WHEN accessright = 'PC' THEN 'CREATE PROCEDURE'
           WHEN accessright = 'PD' THEN 'DROP PROCEDURE'
           WHEN accessright = 'PE' THEN 'EXECUTE PROCEDURE'
           WHEN accessright = 'R' THEN 'SELECT'
           WHEN accessright = 'RF' THEN 'REFERENCE'
           WHEN accessright = 'RO' THEN 'REPLCONTROL'
           WHEN accessright = 'RS' THEN 'RESTORE'
           WHEN accessright = 'SA' THEN 'CONSTRAINT ASSIGNMENT'
           WHEN accessright = 'SD' THEN 'CONSTRAINT DEFINITION'
           WHEN accessright = 'SH' THEN 'SHOW'
           WHEN accessright = 'SR' THEN 'SETRESRATE'
           WHEN accessright = 'SS' THEN 'SETSESSRATE'
           WHEN accessright = 'ST' THEN 'STATISTICS'
           WHEN accessright = 'TH' THEN 'CTCONTROL'
           WHEN accessright = 'U' THEN 'UPDATE'
           WHEN accessright = 'UM' THEN 'UDT METHOD'
           WHEN accessright = 'UT' THEN 'UDT TYPE'
           WHEN accessright = 'UU' THEN 'UDT USAGE'
             ELSE accessright
          END (VARCHAR(26)) AS AccessRightDesc
         ,GrantAuthority
         ,GrantorName (VARCHAR(128))
         ,AllnessFlag
         ,CreatorName (VARCHAR(128))
         ,CreateTimeStamp
      FROM
     (
     SELECT                         -- get direct user rights
          UserName
         ,'User' (VARCHAR(128)) AS AccessType
         ,'' (VARCHAR(128)) AS RoleName
         ,DatabaseName
         ,TableName
         ,ColumnName
         ,AccessRight
         ,GrantAuthority
         ,GrantorName
         ,AllnessFlag
         ,CreatorName
         ,CreateTimeStamp
       FROM dbc.allrightsv
      WHERE UserName = :username
        AND CreatorName NOT = :username    -- exclude objects created by user
     UNION ALL
     SELECT                          -- get rights held through a role
          Grantee AS UserName
         ,'Member' AS UR
         ,r.RoleName
         ,DatabaseName
         ,TableName
         ,ColumnName
         ,AccessRight
         ,NULL (CHAR(1)) AS GrantAuthority
         ,GrantorName
         ,NULL (CHAR(1)) AS AllnessFlag
         ,NULL (CHAR(1)) AS CreatorName
         ,CreateTimeStamp
      FROM dbc.allrolerightsv r
      JOIN dbc.rolemembersv m
        ON m.RoleName = r.RoleName
      WHERE UserName = :username
     UNION ALL
     SELECT                          -- get rights held through a subrole
          USER AS UserName
         ,m.Grantee AS UR
         ,r.RoleName
         ,DatabaseName
         ,TableName
         ,ColumnName
         ,AccessRight
         ,NULL (CHAR(1)) AS GrantAuthority
         ,GrantorName
         ,NULL (CHAR(1)) AS AllnessFlag
         ,NULL (CHAR(1)) AS CreatorName
         ,CreateTimeStamp
      FROM dbc.allrolerightsv r
      JOIN dbc.rolemembersv m
        ON m.RoleName = r.RoleName
      WHERE m.grantee IN (SELECT rolename
                          FROM dbc.rolemembersv
                          WHERE grantee = :username)
      ) AllRights
     -- where DatabaseName not = 'dbc' -- uncomment to exclude DBC objects
     ORDER BY 4,5,6,7;
     );

Ward Analytics Ltd: Information in motion (www.ward-analytics.com)

	<Prev	Next>		<<First	<Prev	Next>	Last>>