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

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
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
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Sean Stuber

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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.
Sean Stuber

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