Listing privileges per Table(that have been granted)

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

Listing privileges per Database table

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.