Start Free Trial
Come for the solution, stay for everything else.
Start Free Trial
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.
8/22/2022 - Mon
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
Log in or sign up to see answer
Become an EE member today
7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
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.
See how we're fighting big data
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
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
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!
<<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
Plans and Pricing
Certified Expert Program
© 1996-2022 Experts Exchange, LLC. All rights reserved. Covered by US Patent