Link to home
Start Free TrialLog in
Avatar of sam15
sam15

asked on

FindiingObject_Privileges.

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.
Avatar of Sean Stuber
Sean Stuber

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
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sam15

ASKER

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.
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
Avatar of sam15

ASKER

<<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.
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