Listing privileges per Table (that have been granted)

Listing Table Privileges in Oracle 12c

Oracle 12c provides robust security features, including a fine-grained system of privileges to control access to data objects. As an Oracle DBA, understanding and managing these privileges is crucial. To list the privileges that have been granted on a specific table, you can query the Oracle Data Dictionary Views. Here's a step-by-step approach:

Access the Oracle Database: Before you can list the privileges, ensure you have access to the Oracle database and are equipped with sufficient privileges to query the system views.
```
CONNECT your_username/your_password@your_service_name;
```
Replace `your_username`, `your_password`, and `your_service_name` with the appropriate credentials and connection string.
Query the `DBA_TAB_PRIVS` View: The `DBA_TAB_PRIVS` view provides comprehensive information on all object grants in the database. To list privileges granted on a specific table, say `MY_TABLE`, execute:
```
SELECT GRANTEE, PRIVILEGE 
FROM DBA_TAB_PRIVS 
WHERE TABLE_NAME = 'MY_TABLE' 
ORDER BY GRANTEE, PRIVILEGE;
```
For More Detailed Information: If you want a detailed report that includes the grantor, the type of the object, and the schema where the table resides, you can extend the query:
```
SELECT GRANTOR, GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE, GRANTABLE
FROM DBA_TAB_PRIVS 
WHERE TABLE_NAME = 'MY_TABLE' 
ORDER BY GRANTEE, PRIVILEGE;
```
Here, `GRANTABLE` will indicate if the grantee can further grant these privileges to other users.
For Specific Schema:
If you are interested in privileges granted on a table within a specific schema, you can add the `TABLE_SCHEMA` condition:
```
SELECT GRANTEE, PRIVILEGE 
FROM DBA_TAB_PRIVS 
WHERE TABLE_NAME = 'MY_TABLE' AND TABLE_SCHEMA = 'MY_SCHEMA' 
ORDER BY GRANTEE, PRIVILEGE;
```
Replace `MY_SCHEMA` with the desired schema name.
Other Relevant Views:
1. ALL_TAB_PRIVS: This view displays the object grants for which the current user is the object owner, grantor, or grantee.
2. USER_TAB_PRIVS: It lists object grants for which the current user is the grantor or grantee.
Utilizing these views based on your access level and specific needs can further help in monitoring and managing table privileges.
Audit and Review: Regularly review table privileges to ensure data security and that principles of least privilege are adhered to. Revoke any unnecessary or outdated privileges to maintain a secure database environment.

In Oracle 12c, effectively monitoring and managing table privileges is a cornerstone of database security and integrity. By querying Oracle's Data Dictionary Views, DBAs can achieve a comprehensive view of object grants, allowing for informed privilege management and bolstered database security.

You have just seen how to use Security Manager to display security privileges. The information displayed came from some of Oracle's data dictionary views.

Data dictionary views for privileges

There are three views that contain information about the privileges relating to a table:

USER_TAB_PRIVS:

Lists all the information about privileges granted and received by the current user

USER_TAB_PRIVS_MADE:

A subset of the USER_TAB_PRIVS that contains information about all the privileges that have been granted by the current user

USER_TAB_PRIVS_RECD:

Another subset of the USER_TAB_PRIVS that contains information about all the privileges that have been granted to the current user

Columns in views

Because all three of these views are related to each other, they share a set of columns, which include:

TABLE_NAME:

The name of the table

OWNER:

The owner of the object, which is only relevant for the USER_TAB_PRIVS and USER_TAB_PRIVS_RECD views, as the USER_TAB_PRIVS_MADE only applies to objects owned by the current user

GRANTOR:

The user who granted the privilege

GRANTEE:

The user who received the privilege

GRANTABLE:

Whether the GRANTEE was given permission to re-grant the privilege

These data dictionary views only contain information about privileges that have been explicitly granted for database objects. The owner of an object is automatically granted all privileges for that object, but these privileges are not shown in the data dictionary tables.

SQL Command Example

The following SQL command returns a list of all privileges associated with the table named "COIN".

SELECT * FROM USER_TABLE_PRIVS 
WHERE TABLE_NAME = ' COIN ';

This is one scenario where you might want to use the ALL_TAB_PRIVS view, with selection conditions on the OWNER or TABLE_NAME. The syntax for the command would be nearly the same, except that you would probably have to supply a value for the extra column TABLE_SCHEMA. The next lesson demonstrates how to list the privileges granted to a particular user.

Lesson 5	Listing privileges-per Table
Objective	List the Privileges that have been granted on a Table