Object Privileges

[Upeshp]

Hi all,

I have 400 users created on my Oracle DB (ver. 8) on Unix. I want to drop 25 Users out of 400, before that i want a list of users who are having Object privileges on these 25 users objects (means objects owned by these 25 users).

Please can any one give me a good query for getting the above result.

Thanks,

UpeshP

[stecal]

You can do this step-by-step, or put it all together at once after some prepping.

First, you need to collect the users you want to drop. For 25 users, I'd lock their accounts with

alter user username account lock;

Check on the list of locked accounts - there may be some you don't want to drop

select username from dba_users where account_status = 'LOCKED';

To see what the soon to be dropped users have as objects:

select owner, object_name from dba_objects, dba_users
where owner in (select username from dba_users where account_status = 'LOCKED');
order by owner;

select grantor, grantee, table_name from user_tab_privs_made, dba_users where grantor in (select username from dba_users where account_status = 'LOCKED')
order by grantor;

That should be enough to get you started.

[Upeshp]

Hi Stecal,

Thanks for reply It's a great help.

bye,