Archives of the TeradataForum

Message Posted: Tue, 11 Sep 2007 @ 20:47:02 GMT

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

The attached query will return the rights that were granted to your user id, roles that you are a member of, and nested roles for the roles that you are a member of.

The column AccessType will tell you how you hold the right. "User" is granted directly to your user id, "Role" is through the named role that you are a member of, and a rolename will be displayed for nested roles to indicate the "parent" role.

Bruce Hohman
Teradata, a division of NCR
Certified Teradata Master V2R5

     /* List rights held by a user as direct grants or through roles */
     locking row for access
     select
          UserName     (varchar(30))
         ,AccessType   (varchar(30))
         ,RoleName     (varchar(30))
         ,DatabaseName (varchar(30))
         ,TableName    (varchar(30))
         ,ColumnName   (varchar(30))
         ,AccessRight
         ,case
             when accessright='AE' then 'ALTER EXTERNALPROCEDURE'
             when accessright='AF' then 'ALTER FUNCTION'
             when accessright='AP' then 'ALTER PROCEDURE'
             when accessright='AS' then 'ABORT SESSION'
             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='I'  then 'INSERT'
             when accessright='IX' then 'INDEX'
             when accessright='MR' then 'MONITOR RESOURCE'
             when accessright='MS' then 'MONITOR SESSION'
             when accessright='PC' then 'CREATE PROCEDURE'
             when accessright='PD' then 'DROP PROCEDURE'
             when accessright='PE' then 'EXECUTE PROCEDURE'
             when accessright='RO' then 'REPLICATION OVERRIDE'
             when accessright='R'  then 'SELECT'
             when accessright='RF' then 'REFERENCE'
             when accessright='RS' then 'RESTORE'
             when accessright='SS' then 'SET SESSION RATE'
             when accessright='SR' then 'SET RESOURCE RATE'
             when accessright='U'  then 'UPDATE'
             when accessright='UU' then 'UDT Usage'
             when accessright='UT' then 'UDT Type'
             when accessright='UM' then 'UDT Method'
             else''
          end (varchar(26)) as AccessRightDesc
         ,GrantAuthority
         ,GrantorName (varchar(30))
         ,AllnessFlag
         ,CreatorName (varchar(30))
         ,CreateTimeStamp
      from
     (
     select                         -- get direct user rights
          UserName
         ,'User' (varchar(30)) as AccessType
         ,'' (varchar(30)) as RoleName
         ,DatabaseName
         ,TableName
         ,ColumnName
         ,AccessRight
         ,GrantAuthority
         ,GrantorName
         ,AllnessFlag
         ,CreatorName
         ,CreateTimeStamp
       from dbc.allrights
      where UserName = user
        and CreatorName not= User    -- 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.allrolerights r
      join dbc.rolemembers m
        on m.RoleName = r.RoleName
      where UserName = User
     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.allrolerights r
      join dbc.rolemembers m
        on m.RoleName = r.RoleName
      where m.grantee in (select rolename from dbc.rolemembers where
     grantee=user)
      ) AllRights
     -- where DatabaseName not='dbc' -- uncomment to exclude DBC objects
      order by 4,5,6,7
      ;

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