I have a user 'abc'. Now I have created new user xyz. Can I assign all privileges of user 'abc' to new user 'xyz' in one go? Please help me.
Asked
Active
Viewed 444 times
2 Answers
0
As far as I can tell - no, you can't.
But, you could if you
- created role(s)
- granted privileges to those roles
- grant roles to user
abc - then, after creating user
xyz, you'd just grant those roles to it
If you want to do it "manually", you'll first have to find out what privileges abc has, and then grant them to xyz.
Where to look at? Dictionary has a wide choice, e.g.
SQL> select table_name, substr(comments, 1, 50) || ' ...' comments
2 From dictionary where lower(table_name) like '%priv%';
TABLE_NAME COMMENTS
------------------------------ -------------------------------------------------------
ALL_COL_PRIVS Grants on columns for which the user is the granto ...
ALL_COL_PRIVS_MADE Grants on columns for which the user is owner or g ...
ALL_COL_PRIVS_RECD Grants on columns for which the user, PUBLIC or en ...
ALL_REPGROUP_PRIVILEGES Information about users who are registered for obj ...
ALL_TAB_PRIVS Grants on objects for which the user is the granto ...
ALL_TAB_PRIVS_MADE User's grants and grants on user's objects ...
ALL_TAB_PRIVS_RECD Grants on objects for which the user, PUBLIC or en ...
ALL_XSC_AGGREGATE_PRIVILEGE All privileges that make up an aggregate privilege ...
ALL_XSC_PRIVILEGE All mappings of privileges to security classes in ...
USER_AQ_AGENT_PRIVS ...
USER_COL_PRIVS Grants on columns for which the user is the owner, ...
USER_COL_PRIVS_MADE All grants on columns of objects owned by the user ...
USER_COL_PRIVS_RECD Grants on columns for which the user is the grante ...
USER_GOLDENGATE_PRIVILEGES Details about goldengate privileges ...
USER_NETWORK_ACL_PRIVILEGES User privileges to access network hosts through PL ...
USER_REPGROUP_PRIVILEGES Information about users who are registered for obj ...
USER_ROLE_PRIVS Roles granted to current user ...
USER_RSRC_CONSUMER_GROUP_PRIVS Switch privileges for consumer groups for the user ...
USER_RSRC_MANAGER_SYSTEM_PRIVS system privileges for the resource manager for the ...
USER_SYS_PRIVS System privileges granted to current user ...
USER_TAB_PRIVS Grants on objects for which the user is the owner, ...
USER_TAB_PRIVS_MADE All grants on objects owned by the user ...
USER_TAB_PRIVS_RECD Grants on objects for which the user is the grante ...
COLUMN_PRIVILEGES Grants on columns for which the user is the granto ...
ROLE_ROLE_PRIVS Roles which are granted to roles ...
ROLE_SYS_PRIVS System privileges granted to roles ...
ROLE_TAB_PRIVS Table privileges granted to roles ...
SESSION_PRIVS Privileges which the user currently has set ...
TABLE_PRIVILEGES Grants on objects for which the user is the granto ...
29 rows selected.
SQL>
Littlefoot
- 131,892
- 15
- 35
- 57
-
select * from USER_ROLE_PRIVS; – Himanshu Tiwari May 11 '20 at 08:30
-
Yes, that's one of possible sources. – Littlefoot May 11 '20 at 08:35
-
I used this query to know the privileges and i used these privileges also to my user 'xyz' still i am not able to create an object here. – Himanshu Tiwari May 11 '20 at 08:38
-
If you are saying that you can't create a table (for example) when connected as XYZ because of lack of privileges, then this kind of privileges is in **USER_SYS_PRIVS**. – Littlefoot May 11 '20 at 08:46
0
In connection to @Littlefoot answer.
you can copy the user privileges from one of the tables in the answer here How to find the privileges and roles granted to a user in Oracle? for the first user and copy them into a script that will grant the second user the desired privileges. its a bit of a workaround but it should work just fine
jackInTheBox
- 62
- 1
- 8