Archives of the TeradataForum

Message Posted: Tue, 22 Jul 2014 @ 22:34:26 GMT

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

Hi Martin,

I think I see your point and have made a change. Below is an amended AllDatabaseRightsV. Let me know if that does what you think it should then I can update the AllDatabaseRights macro to match.

Firstly, can I make sure that we're talking about the same thing, and are expecting the same thing.

My test scenario consists of a database with a table.

     CREATE DATABASE db_roletest AS ...
     CREATE TABLE db_roletest.t1 (col1 INTEGER NOT NULL, col2 CHAR(5)); INSERT INTO
     db_roletest.t1 VALUES(1,'abc')

I then created two roles, nested one 'below' the other and granted an access right on my test database to the 'bottom' role.

     CREATE ROLE r1_top;
     CREATE ROLE r2_subrole;
     GRANT UPDATE ON db_roletest TO r2_subrole; GRANT r2_subrole TO r1_top;

When I run this amended macro (amongst others !) I get the following rows (I've eliminated some columns for display purposes):

     DATABASENAME   GRANTEE_TYPE   USER_OR_ROLE   TableName   AccessRight
     db_roletest    DBC            r2_subrole      All        U
     db_roletest    ROLE           r2_subrole      All        U
     db_roletest    r1_top         r2_subrole      All        U

To me this shows that anyone who has been granted 'r2_subrole' has UPDATE accessright to the database and because 'r2_subrole' has bene granted to 'r1_top' then anyone who has bene granted the 'r1_top' role will also have this accessright.

Is this what you were expecting?

Cheers,

Dave

     REPLACE MACRO sysadmin.AllDatabaseRightsV (DatabaseName VARCHAR(128) CHARACTER SET UNICODE)  AS (
     /* List rights held on a database as direct grants or through roles.
        -- 2014-06-12 Updated to include TD 15.00 AccessRights values.
        -- 2014-06-03 Initial coding.
        -- This is based on macro AllUserRightsV which was originally developed by a Teradata PS consultant
        -- This version created by Ward Analytics Ltd 2014
        -- You are free to use and redistribute this code but please leave this header here.

     The different ways in which a user may receive an AccessRight is shown in column
     GRANTEE_TYPE
     - USER: the accessright is granted directly to the user. USER_OR_ROLE is the name of the
     user.
     - ROLE: the accessright is granted to a role. USER_OR_ROLE is the name of the role.
     - other: GRANTEE_TYPE and USER_OR_ROLE will both be role names */ LOCKING ROW FOR ACCESS
     SELECT
          DatabaseName     (VARCHAR(128))
         ,GRANTEE_TYPE   (VARCHAR(128)) AS GRANTEE_TYPE
         ,USERNAME     (VARCHAR(128)) AS USER_OR_ROLE
         ,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 = 'CZ' THEN 'CREATE ZONE' /* Added for TD 15.00 */
           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 = 'DZ' THEN 'DROP ZONE' /* Added for TD 15.00 */
           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'
           WHEN accessright = 'ZO' THEN 'ZONE OVERRIDE' /* Added for TD 15.00 */
             ELSE accessright
          end (VARCHAR(26)) AS AccessRightDesc
         ,GrantAuthority
         ,GrantorName (VARCHAR(128))
         ,AllnessFlag
         ,CreatorName (VARCHAR(128))
         ,CreateTimeStamp
      FROM
     (
     SELECT                         -- GET DIRECT USER RIGHTS
          :DATABASENAME AS DATABASENAME
         ,'USER' (VARCHAR(128)) AS GRANTEE_TYPE
         ,USERNAME
         ,TABLENAME
         ,COLUMNNAME
         ,ACCESSRIGHT
         ,GRANTAUTHORITY
         ,GRANTORNAME
         ,ALLNESSFLAG
         ,CREATORNAME
         ,CREATETIMESTAMP
       FROM DBC.ALLRIGHTSV
      WHERE DATABASENAME = :DATABASENAME
        AND USERNAME NOT = :DATABASENAME    -- EXCLUDE RIGHTS HELD BY DATABASE ON
     ITSELF
     UNION ALL
     SELECT                          -- GET RIGHTS HELD THROUGH A ROLE
          :DATABASENAME AS DATABASENAME
         ,'ROLE' AS UR
         ,ROL.ROLENAME
         ,ROL.TABLENAME
         ,ROL.COLUMNNAME
         ,ROL.ACCESSRIGHT
         ,NULL (CHAR(1)) AS GRANTAUTHORITY
         ,ROL.GRANTORNAME
         ,NULL (CHAR(1)) AS ALLNESSFLAG
         ,NULL (CHAR(1)) AS CREATORNAME
         ,ROL.CREATETIMESTAMP
      FROM DBC.ALLROLERIGHTSV ROL
      WHERE ROL.DATABASENAME = :DATABASENAME
     UNION ALL
     SELECT                          -- GET RIGHTS HELD THROUGH A SUBROLE
          :DATABASENAME AS DATABASENAME
         ,MBRx.GRANTEE AS UR
         ,MBRx.rolename
         ,ROL.TABLENAME
         ,ROL.COLUMNNAME
         ,ROL.ACCESSRIGHT
         ,NULL (CHAR(1)) AS GRANTAUTHORITY
         ,ROL.GRANTORNAME
         ,NULL (CHAR(1)) AS ALLNESSFLAG
         ,NULL (CHAR(1)) AS CREATORNAME
         ,ROL.CREATETIMESTAMP
     FROM DBC.ALLROLERIGHTSV ROL
      JOIN DBC.ROLEMEMBERSV MBRx
        ON MBRx.ROLENAME = ROL.ROLENAME
      WHERE MBRx.ROLENAME IN (SELECT ROLENAME FROM DBC.ALLROLERIGHTSV WHERE DATABASENAME =
     :DATABASENAME)
       ) AllRights
     -- where DatabaseName not = 'dbc' -- uncomment to exclude DBC objects ORDER BY TABLENAME,
     USERNAME, ColumnName, AccessRight; );

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

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