Archives of the TeradataForum

Message Posted: Mon, 13 Feb 2012 @ 09:00:55 GMT

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

HI

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;

Thanks

Machha

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