Object Privileges  «Prev  Next»

Lesson 6Listing privileges-columns and Users
ObjectiveList the privileges that have been granted on columns and for users.

Listing privileges columns and Users

You can obtain information about security privileges from the data dictionary views. You can also get information about privileges granted at the column level and query the data dictionary for information about privileges for each user.

Column Privileges

You can grant object privileges for individual columns as well as tables. Just as there are three views that contain information about the privileges on tables, there are three corresponding data dictionary views for column privileges:

USER_COL_PRIVS: Lists all the information about privileges granted and received by the current user
USER_COL_PRIVS_MADE: A subset of the USER_COL_PRIVS, which contains information about all the privileges granted by the user
USER_ COL _PRIVS_RECD: Another subset of the USER_ COL _PRIVS, which contains information about all the privileges granted to the user

User privileges

The earlier discussions on the data dictionary views focused on the privileges that relate to specific database objects. You may also want to know what privileges have been granted to a particular user.
There are two ways to get this information. You can log on as the user in question and query their USER_ privilege tables. You can also query the equivalent ALL_TAB_PRIVS and ALL_COL_PRIVS tables with selection criteria that would limit the results to a particular user, as in:

This would give you a listing of all the privileges granted for all tables to the user named SCOTT.
The next lesson shows how to give a user the ability to grant privileges.

Listing Privileges - Quiz

Click the Quiz link below to test your knowledge of listing privileges.
Listing Privileges - Quiz