Archives of the TeradataForum

Message Posted: Tue, 04 Jan 2005 @ 13:33:36 GMT

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

Here is a script that will pull all user access rights. Perhaps this will assist you.

     SELECT
      USERNAME
     ,DATABASENAME
     ,MAX(case when accessright = 'as' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "abort_session"
     ,MAX(case when accessright = 'cd' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "create_database"
     ,MAX(case when accessright = 'cg' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "create_trigger"
     ,MAX(case when accessright = 'cm' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "create_macro"
     ,MAX(case when accessright = 'cp' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "checkpoint"
     ,MAX(case when accessright = 'ct' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "create_table"
     ,MAX(case when accessright = 'cu' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "create_user"
     ,MAX(case when accessright = 'cv' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "create_view"
     ,MAX(case when accessright = 'd'  then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "delete"
     ,MAX(case when accessright = 'dd' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "drop_database"
     ,MAX(case when accessright = 'dg' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "drop_trigger"
     ,MAX(case when accessright = 'dm' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "drop_macro"
     ,MAX(case when accessright = 'dp' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "dump"
     ,MAX(case when accessright = 'dt' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "drop_table"
     ,MAX(case when accessright = 'du' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "drop_user"
     ,MAX(case when accessright = 'dv' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "drop_view"
     ,MAX(case when accessright = 'e'  then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "execute"
     ,MAX(case when accessright = 'i'  then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "insert"
     ,MAX(case when accessright = 'ix' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "index"
     ,MAX(case when accessright = 'mr' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "monitor_resource"
     ,MAX(case when accessright = 'ms' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "monitor_session"
     ,MAX(case when accessright = 'pc' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "create_procedure"
     ,MAX(case when accessright = 'pd' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "drop_procedure"
     ,MAX(case when accessright = 'pe' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "execute_procedure"
     ,MAX(case when accessright = 'r'  then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "select"
     ,MAX(case when accessright = 'rf' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "reference"
     ,MAX(case when accessright = 'ro' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "replication_override"
     ,MAX(case when accessright = 'rs' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "restore"
     ,MAX(case when accessright = 'sr' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "set_resource_rate"
     ,MAX(case when accessright = 'ss' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "set_session_rate"
     ,MAX(case when accessright = 'u'  then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "update"
     ,MAX(case when accessright = 'AF' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "ALTER FUNCTION"
     ,MAX(case when accessright = 'AP' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "ALTER PROCEDURE"
     ,MAX(case when accessright = 'CF'  then 1 when accessright = 'all' then
     1 ELSE 0 END)AS "CREATE FUNCTION"
     ,MAX(case when accessright = 'CO' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "CREATE PROFILE"
     ,MAX(case when accessright = 'CR' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "CREATE ROLE"
     ,MAX(case when accessright = 'DF' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "DROP FUNCTION"
     ,MAX(case when accessright = 'DO' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "DROP PROFILE"
     ,MAX(case when accessright = 'DR' then 1 when accessright = 'all' then 1
     ELSE 0 END) AS "DROP ROLE"
     ,MAX(case when accessright = 'EF'  then 1 when accessright = 'all' then
     1 ELSE 0 END)AS "EXECUTE FUNCTION"
     ,MAX(case when grantauthority = 'n' then 0 else 1 end) AS
     "grantauthority"
     FROM DBC.ALLRIGHTS
     group by username, databasename
     order by username, databasename

Chris Coffing
Air Force Knowledge Services (AFKS)
Lead DBA Operations & Support
Teradata Certified Master

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