Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: How to clear the Orphan Access rights
 
From:   Machha Narayan Rao Naidu

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023