FindiingObject_Privileges.

sam15
sam15 used Ask the Experts™
on
Is there a way to get the "object" grants issued by a user/schema and to that user in oracle to other users?

I have an account "SCOTT" in a 9i database.

I created the same user in a 11g database.

I checked the ROLES, SYSTEM PRIVS using the folowing and isued them to the user in 11g.

-- Get Roles assigned
select grantee, granted_role from dba_role_privs where grantee='SCOTT';

--Get sys privs assigned
select role, owner, table_name privilege from user_sys_privs where role='SCOTT';

There is a view "USER_TAB_PRIVS" but I d onto know how you can rebuid the grants needed from that.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
user_tab_privs is the right place to look.  despite "tab"  being in the name, it's not just for tables.  it applies to all objects
Most Valuable Expert 2011
Top Expert 2012
Commented:
to rebuild the privileges try something like this...


SELECT    'grant '
       || privilege
       || ' on "'
       || owner
       || '"."'
       || table_name
       || '" to "'
       || grantee
       || '";'
  FROM user_tab_privs

Author

Commented:
yes, it works. But i noticed that

1) the view includes all users (not just SCOTT).
2) table_name field include package names too.
3) import seems to run those grants if the tables for the other schema exist. If not this will take care of it.

Do you agree.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2012

Commented:
1 are you looking at the grantee column or the owner column?

SCOTT shouldn't have any privileges on objects owned by SCOTT
-  every owner implicitly has full privileges on everything they own.

2 yes, as mentioned in my first post, despite the table and column names, the view shows you privileges for all objects

3 the grants are attribute of the object, not the grantee.  So,  if you import scott that won't give you privileges on someone else's objects.   The import will only include system privileges and roles.  

if you want privileges on HR's objects for scott.  When you import HR, scott will get them provided scott was imported first

Author

Commented:
<<1 are you looking at the grantee column or the owner column?>>

Both "grantee" and "owner" and "GRANTOR".

try logging in as scott to a database and do select on the view and you wil even see the SYS owner and all other users. Same thing for grantee. I see scott, mike, etc.

It is strange because view name is USER_TAB_PRIVS.

<<When you import HR, scott will get them provided scott was imported first >>

I think they will fail, because when you import scott there are no HR objects yet.
Try grant select on a table that does not exist yet.
Most Valuable Expert 2011
Top Expert 2012

Commented:
user_tab_privs applies returns privileges where YOU are the grantee, in this case you=SCOTT,  the owner and grantor could be anybody else


>>> I think they will fail, because when you import scott there are no HR objects yet.

No, because when you import scott, you're not importing HR's grants to scott.
So there is nothing that could fail.
When you import HR, that's when the grants on HR's objects will be applied to scott.

If you import HR first, the grants will fail because scott doesn't exist.
importing scott afteward won't help because scott's import doesn't include the grants

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial