Query USER PRIVILEGES replicated

Published by Patrizia Gardis on 6 March 20196 March 2019

With the article Query USER PRIVILEGES replicated, we can retrieve all ROLES, system PRIVILEGES, and object PRIVILEGES of a USER and construct the GRANT command assigning them to another USER.

When a user is created in the Oracle database, if no roles or privileges are assigned, the user will not be able to do anything.

For a user to be able to connect to the database, they must be assigned either the CONNECT role or the CREATE SESSION privilege.

What is the difference between ROLE and PRIVILEGE?

A ROLE is a container of privileges. We can create an ad-hoc ROLE for a specific type of user, and within it, we can assign the PRIVILEGES to perform the operations for which the user is authorized.

Instead of assigning numerous PRIVILEGES to the user, we assign numerous PRIVILEGES to a ROLE and then assign the ROLE to the user.

In addition to SYSTEM PRIVILEGES, there are OBJECT PRIVILEGES, which determine the specific DML operation (INSERT, UPDATE, DELETE, etc.) that a user can perform on another user’s objects.

In the event that we want to assign the same ROLES or PRIVILEGES of a user, we can proceed with the following dynamic queries.

Dynamic query to assign User Roles:

select 'grant ' ||granted_role || ' to &userid' ||
decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
from dba_role_privs
where grantee = '&userid';

Query USER PRIVILEGES replicated

Statement to assign Object Privileges of a user:

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' 
from dba_tab_privs where grantor = '&userid';

Dynamic query to assign User Privileges:

select 'grant ' || privilege || ' to &quserid' ||
decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
from dba_sys_privs
where grantee = '&userid';

• DDL for User Creation Roles and Privileges
• Query USER PRIVILEGES replicated
• Query for Oracle User Object Privileges
• Query for Oracle User Roles
• Assignment of DEFAULT_TABLESPACE Quota
• Query for Oracle User Password Status

To view my guides on CRS Commands, click here!
At this LINK, instead, you’ll find my SQL Queries.

Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin