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.