Object Privileges «Prev  Next»

Lesson 5Listing privileges-per Table
ObjectiveList 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.