 |
 |
Archives of the TeradataForum
Message Posted: Thu, 28 May 2004 @ 00:12:48 GMT
| Subj: | | Re: Column domains in DBC... |
| |
| From: | | Coffing Christopher L |
I use this script to assist me in determining the accessrights for all users. I copy and paste this information into Excel and then use the
filters to derive who has "With Grant Option" or Create privileges.
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 grantauthority = 'n' then 0 else 1 end) AS
"grantauthority"
FROM DBC.ALLRIGHTS
group by username, databasename
order by username, databasename
Best Regards
Chris Coffing
AFKS O&M Lead Teradata DBA
Certified Teradata Master
| |
