Query for Oracle User Object Privileges
With the article “Query for Oracle User Object Privileges,” we look at the object privileges assigned to a specific user in the Oracle database.
In the Oracle database, there are two types of privileges: system privileges and object privileges.
System privileges allow performing certain operations in the database, such as accessing the database or creating or deleting objects.
Object privileges allow performing certain Data Manipulation Language (DML) operations (such as INSERT, UPDATE, or DELETE) on a set of objects, even those from other schemas.
Additionally, we select: the owner of the table, the table name, the privilege, and the user to whom the privilege has been granted.
The selected fields are:
OWNER – Object owner
TABLE_NAME – Table name
PRIVILEGE – Object privilege
GRANTEE – Name of the user to whom the privilege is granted.
This information is extracted from the DBA_TAB_PRIVS table.
Finally, we filter the information for the user to whom the privilege has been granted.
Query for Oracle User Object Privileges:
set lines 200
col privilege for a20
col grantee for a20
col owner for a20
select owner OWN_TABLE,table_name,privilege, GRANTEE OWN_PRIVILEGE FROM DBA_TAB_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 the SQL queries.
Stay updated on our activities by joining our community (remember to like our pages, let’s get in touch, and stay informed about all our new guides!).
Facebook
Youtube
Linkedin
For those who haven’t done so yet, we recommend saving this website to your bookmarks.
So that you always have our solutions to the most common database problems, especially Oracle databases, at your fingertips.
0 Comments