Archives of the TeradataForum
Message Posted: Mon, 13 Feb 2012 @ 09:00:55 GMT
I guess Orphan Access Rights here is the rights given directly to user account instead of roles..?
If that is true then you can list all of them and generate a revoke commands with below query.
/* Below clause is the get the grants which will be granted to self accounts when a new object is created */ select Case when a.accessright='IX' then 'revoke index on '||trim(a.databasename)||'.'||trim(a.tablename)|| ' from ' ||trim(a.username)||';' Else 'revoke all on '||trim(a.databasename)|| ' from '||trim(a.username)||';' End as ";" from dbc.allrights a where grantorname not in ('DBC') and a.DatabaseName IN (SELECT d.databasenameI from dbc.dbase d WHERE d.rowtype = 'D') and a.username=a.grantorname and accessright not in ('RF') UNION ALL /* Below clause is the get the grants which where Granted to Directly to other users not through Role */ select Case when ar.accessright='IX' then 'revoke index on '||ar.databasename||'.'||ar.tablename|| ' from '||ar.grantorname||';' Else 'revoke all on '||ar.databasename||' from '||ar.grantorname||';' End as ";" from dbc.allrights ar inner join dbc.dbase db on ar.databasename = db.databasename where ar.username <> ar.grantorname and ar.username <> ar.databasename and ar.username <> ar.creatorname and grantorname not in ('DBC') and ar.grantorname not like ('%Dropped%') and accessright not in ('RF','DU') order by 1;
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|