![](//teradataforum.com/imgs/ptdfw1.gif) |
![](//teradataforum.com/imgs/ptdfw2.gif) |
Archives of the TeradataForum
Message Posted: Thu, 04 Aug 2011 @ 09:01:00 GMT
Subj: | | Re: Teradata Create object implicit privileges |
|
From: | | Machha Narayan Rao Naidu |
Hi,
You can use below query to get rid of Default Grants provided automatically to user while creating objects. You have to create some specified
roles on database level and assign them to the users for whom you want the grants not to be revoked
select a.UserName
,a.DatabaseName
,a.GrantorName
,'Acct' as GrantLevel
,a.accessright
,a.tablename
,a.CreateTimeStamp
,case when a.accessright = 'IX'
then 'revoke index on '||trim(a.databasename)||'.'||
trim(a.tablename)|| ' from ' ||trim(a.username)||';'
when a.accessright = 'RF'
then 'revoke references on '||trim(a.databasename)||'.'||
trim(a.tablename)|| ' from '||trim(a.username)||';'
--..............................................Continues for all privileges
else 'revoke all on '||trim(a.databasename)|| ' from '||
trim(a.username)||';'
end as direct
from dbc.allrights a
and grantorname = 'USERNAME'
and a.DatabaseName
in (select d.databasenameI
from dbc.dbase d
where d.rowtype = 'D')
and a.username = a.grantorname
group by 1,2,3,4,5,6,7,8
Thanks
Machha
| |