Object Privileges  «Prev  Next»

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

Retrieving Column-Level Privileges and User Privileges in Oracle

Oracle's Data Dictionary is an invaluable resource for DBAs, housing detailed metadata about the database. If you need to ascertain privileges granted at the column level and gather data on privileges assigned to each user, follow this structured approach:
  1. Connect to the Oracle Database: Initiate a connection to your Oracle database using a user account with the requisite permissions to query the system views.
    CONNECT your_username/your_password@your_service_name;
    

    Ensure you replace `your_username`, `your_password`, and `your_service_name` with the appropriate credentials and service details.
  2. Retrieve Column-Level Privileges: To identify column-level privileges, you should query the `DBA_COL_PRIVS` view, which provides details on all column grants in the database.
    To list column-level privileges for a specific table, for instance, `MY_TABLE`, execute the following:
    SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE 
    FROM DBA_COL_PRIVS 
    WHERE TABLE_NAME = 'MY_TABLE' 
    ORDER BY GRANTEE, COLUMN_NAME, PRIVILEGE;
    

    This will list the users (`GRANTEE`) who have specific column-level privileges on the designated table.
  3. Query User-specific Privileges: To understand the privileges granted to a specific user, there are several views to consider:
    • DBA_SYS_PRIVS: This view lists system privileges granted to users and roles.
      SELECT PRIVILEGE
      FROM DBA_SYS_PRIVS
      WHERE GRANTEE = 'SPECIFIC_USER';
      

      Replace `SPECIFIC_USER` with the desired username.
    • DBA_TAB_PRIVS: Use this to see privileges granted to users on specific tables or views.
      SELECT TABLE_NAME, PRIVILEGE
      FROM DBA_TAB_PRIVS
      WHERE GRANTEE = 'SPECIFIC_USER';
      
    • DBA_ROLE_PRIVS: For information about roles granted to specific users, turn to this view.
      SELECT GRANTED_ROLE
      FROM DBA_ROLE_PRIVS
      WHERE GRANTEE = 'SPECIFIC_USER';
      
  4. Delve Deeper with Join Operations: For a more comprehensive view, you might consider joining some of these views. For instance, to determine which system privileges are associated with a specific role granted to a user:
    SELECT r.GRANTED_ROLE, p.PRIVILEGE
    FROM DBA_ROLE_PRIVS r
    JOIN DBA_SYS_PRIVS p ON r.GRANTED_ROLE = p.GRANTEE
    WHERE r.GRANTEE = 'SPECIFIC_USER';
    
  5. Regularly Audit and Monitor: As best practice, periodically review and audit the privileges. This not only ensures data security but also helps in compliance with the principle of least privilege, ensuring users have the bare minimum permissions necessary for their roles.
In Oracle, the Data Dictionary provides DBAs with powerful tools to introspect and manage the myriad of privileges distributed across users. By leveraging the views provided, DBAs can ensure a robust, secure, and streamlined privilege management system, maintaining the equilibrium between data access and data security.

Obtain Information about Security Privilege

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:
SELECT * FROM ALL_TAB_PRIVS 
WHERE GRANTEE = 'SCOTT';

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