Archives of the TeradataForum

Message Posted: Wed, 04 Aug 2004 @ 13:03:20 GMT

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

This will return individual user and database acess rights on an identified database and table. You can enhance the 'where condition' to scan an entire database's tables, just specify users, etc. Hope this helps.

     SELECT distinct upper(trim(UserName))LID,trim(tablename)TBL
     ,case
        when accessright = 'AF' then 'ALTER FUNCTION'
        when accessright = 'AP' then 'ALTER PROCEDURE'
        when accessright = 'AS' then 'ABORT SESSION'
        when accessright = 'CD' then 'CREATE DATABASE'
        when accessright = 'CF' then 'CREATE FUNCTION'
        when accessright = 'CO' then 'CREATE PROFILE'
        when accessright = 'CG' then 'CREATE TRIGGER'
        when accessright = 'CU' then 'CREATE USER'
        when accessright = 'CM' then 'CREATE MACRO'
        when accessright = 'CR' then 'CREATE ROLE'
        when accessright = 'CP' then 'CHECKPOINT'
        when accessright = 'CT' then 'CREATE TABLE'
        when accessright = 'CV' then 'CREATE VIEW'
        when accessright = 'D'  then 'DELETE'
        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 = 'DU' then 'DROP USER'
        when accessright = 'DP' then 'DUMP'
        when accessright = 'DO' then 'DROP PROFILE'
        when accessright = 'DR' then 'DROP ROLE'
        when accessright = 'DT' then 'DROP TABLE'
        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 'INDEXES'
        when accessright = 'MR' then 'MONITOR RESOURCE'
        when accessright = 'MS' then 'MONITOR SESSION'
        when accessright = 'PC' then 'CREATE PROCEDURE'
        when accessright = 'PD' then 'DELETE PROCEDURE'
        when accessright = 'PE' then 'EXECUTE PROCEDURE'
        when accessright = 'R'  then 'RETRIEVE/SELECT'
        when accessright = 'RF' then 'REFERENCES'
        when accessright = 'RS' then 'RESTORE'
        when accessright = 'SS' then 'SET SESSION RATE'
        when accessright = 'SR' then 'SET RESOURCE RATE'
        when accessright = 'U'  then 'UPDATE'
        else ''
     end as accessright
     ,GrantorName
     ,GrantAuthority,AllnessFlag
     FROM dbc.AllRights a
     where databasename = 'xxxxx'  and tablename =  'yyyyyl'
     ORDER BY 1;

Fred Keiser

Informatics Systems Engineering (ISE)

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