Privileges

When a user performs any action in or on the database, the database will verify that the user has the rights to successfully execute the action. These rights or permissions are known as privileges.

The access control mechanisms in the database determine access based on user (schema) privileges. The privileges are the permissions or the actual rights needed to perform any and all specific database actions—queries, updates, table creations, establish database connections, and so on.

Note 

Privileges are the permissions or the actual rights needed to perform an action.

Database privileges occur at three levels: system privileges, object privileges, and intra-object privileges. System and object privileges are relevant for an understanding of database roles. Intra-object privileges will be discussed in Part IV, “Fine-Grained Access Controls.”

System Privileges

The database supports a number of very useful and convenient system privileges. I like to decompose system privileges into two categories. The first types are the privileges that are applicable for all objects across the entire database. That is, these privileges are not specific to any one object in the database, but rather to all objects of a specific type. I call these the “ANY” privileges, of which there are over 100.

Consider the EXECUTE ANY PROCEDURE system privilege. This allows the authorized user to successfully execute any procedure defined in any (non-SYS) schema in the database. It also allows them to view the source code for any (non-SYS) procedure in the database. Note that the ANY privileges don’t apply to certain SYS objects that are protected when the O7_DICTIONARY_ACCESSIBILITY initialization parameter is set to FALSE; this fact also expresses why the parameter should be set to false.

The ANY privileges are very powerful, and they are applicable not only to existing database objects but also to any objects created in the future. Be cautious and cognizant of this. It’s easy to assume that system privileges are appropriate when there is only one application running in the database. The security flaw occurs when a newly installed application is added or consolidated into the existing database and the administrator of the first application now has privileges on the new application, too.

The other class of system privileges either affects the state of the database or allows processes to occur in or on the database. For example, the ability to issue ALTER DATABASE, ALTER SYSTEM, and ALTER USER are examples of other system privileges. Even the ability to log in to the database, the CREATE SESSION privilege, is a system privilege.

System privileges are important because they are both powerful in what they can do and pervasive in the number of things they can do it to.

Viewing System Privileges

The SYSTEM_PRIVILEGE_MAP view lists many of the data system privileges and can be helpful when you forget the exact name of the privilege. For example, you know there is a privilege to manage application contexts but you can’t remember what the privilege is, so you could issue the following query:

SELECT NAME   FROM system_privilege_map  WHERE NAME LIKE '%CONTEXT%';

This view lists the privileges but doesn’t tell you who has the privileges or what the privileges allow the user to do. To determine what a privilege does requires research into the Oracle product documentation (start with the Database Security Guide). To determine who has a system privilege, you need to query DBA_SYS_PRIVS. For example, if you want to see who has the SELECT ANY TABLE privilege, you would issue the following query:

SELECT grantee     FROM dba_sys_privs    WHERE PRIVILEGE = 'SELECT ANY TABLE';

The results from this query will show both the users and the database roles that have the privilege. As you will read in the “roles act as containers of privileges that can ease the security administration duties. Granting a role to a user effectively grants the user all the privileges that were granted to that role. Unfortunately, there is no easy way to see all the privileges that a user has because the privileges can be granted both directly to the user and indirectly through roles.

There are several solutions to this. In the following example, a view is constructed that will show a user all their system privileges, regardless of whether the privileges were granted directly, granted to PUBLIC, or inherited from a role. The view is based on one created by Thomas Kyte for the “ask Tom” website. The view displays all roles for the current user. This view is a helper object to another view that will display all the users’ system privileges. You’ll also build one that displays all the users’ object privileges.

You’ll first need access to some very sensitive tables that reside in the SYS schema. You will have to grant the SELECT privilege with the GRANT OPTION because you’ll create views based on these tables and you want the views to be accessible to others. The following privileges were granted in the setup of security manager schema in Appendix A:

GRANT SELECT ON sysauth$ TO sec_mgr WITH GRANT OPTION; GRANT SELECT ON user$ TO sec_mgr WITH GRANT OPTION; GRANT SELECT ON dba_tab_privs TO sec_mgr WITH GRANT OPTION; GRANT SELECT ON dba_sys_privs TO sec_mgr WITH GRANT OPTION;

The first view displays all possible paths a user can receive a privilege. It lists all the roles, the user’s name, and the user group PUBLIC because a grant to any of these will enable the privilege for the user:

sec_mgr@KNOX10g> CREATE OR REPLACE VIEW all_user_priv_path     2  AS     3    SELECT DISTINCT usr.NAME granted_role     4               FROM (SELECT     *   5                           FROM SYS.sysauth$   6                     CONNECT BY PRIOR privilege# = grantee#   7                START WITH grantee# = UID OR grantee# = 1) sauth,   8                    SYS.user$ usr     9              WHERE usr.user# = sauth.privilege#  10    UNION ALL   11    SELECT USER  12      FROM DUAL    13    UNION ALL    14    SELECT 'PUBLIC'  15      FROM DUAL    16  /     View created. sec_mgr@KNOX10g> GRANT SELECT ON all_user_priv_path TO PUBLIC; Grant succeeded. sec_mgr@KNOX10g> CREATE PUBLIC SYNONYM all_user_priv_path     2    FOR all_user_priv_path; Synonym created.

Note that you can set the values of USER and UID (in bold above) to a specific user to see the privilege path for that user. The USER_ID column of the ALL_USERS view will provide you with the UID for your user.

Logging in as SYSTEM, you can test this view. The first query shows what might typically be issued when checking a user’s roles. Unfortunately, this only shows direct role grants. The subsequent query uses the view created above to show all roles that have been granted either directly or indirectly:

 system@KNOX10g> -- Show all directly granted roles for user   system@KNOX10g> SELECT granted_role     2    FROM dba_role_privs     3   WHERE grantee = USER; GRANTED_ROLE   ------------------------------   DBA   MGMT_USER   AQ_ADMINISTRATOR_ROLE     system@KNOX10g> -- Show all directly granted roles, system@KNOX10g> -- indirectly granted roles, system@KNOX10g> -- USER and PUBLIC. This is the system@KNOX10g> -- sum of all ways a user can receive a privilege   system@KNOX10g> SELECT * FROM all_user_priv_path; GRANTED_ROLE   ------------------------------   AQ_ADMINISTRATOR_ROLE   DBA   DELETE_CATALOG_ROLE   EXECUTE_CATALOG_ROLE   EXP_FULL_DATABASE   GATHER_SYSTEM_STATISTICS   HS_ADMIN_ROLE   IMP_FULL_DATABASE   JAVA_ADMIN   JAVA_DEPLOY   MGMT_USER   OLAP_DBA   SCHEDULER_ADMIN   SELECT_CATALOG_ROLE   WM_ADMIN_ROLE   XDBADMIN   SYSTEM   PUBLIC     18 rows selected.

By including the static strings for the USER (which resolves to SYSTEM in the preceding example) and PUBLIC, you are sure to include privileges granted directly to the user as well as privileges granted to PUBLIC. You can now create the view that displays all system privileges for a user:

sec_mgr@KNOX10g> CREATE OR REPLACE VIEW user_system_privs     2  AS     3    SELECT DISTINCT PRIVILEGE     4               FROM dba_sys_privs     5              WHERE grantee IN (SELECT *   6                                  FROM all_user_priv_path)   7  /    View created. sec_mgr@KNOX10g> GRANT SELECT ON user_system_privs TO PUBLIC; Grant succeeded. sec_mgr@KNOX10g> CREATE PUBLIC SYNONYM user_system_privs     2    FOR user_system_privs; Synonym created. sec_mgr@KNOX10g> -- Check system privileges for CTXSYS   sec_mgr@KNOX10g> conn ctxsys Enter password: Connected. ctxsys@KNOX10g> select * FROM user_system_privs; PRIVILEGE   ----------------------------------------   ALTER SESSION   CREATE CLUSTER   CREATE DATABASE LINK   CREATE INDEXTYPE   CREATE OPERATOR   CREATE PROCEDURE   CREATE PUBLIC SYNONYM   CREATE SEQUENCE   CREATE SESSION   CREATE SYNONYM   CREATE TABLE   CREATE TRIGGER   CREATE TYPE   CREATE VIEW   DROP PUBLIC SYNONYM   UNLIMITED TABLESPACE     16 rows selected.

Note 

All system privileges should be considered extremely powerful and should be guarded strongly and granted only when absolutely necessary.

Object Privileges

While system privileges are general, object privileges, as the name suggests, are relevant to a specific object. For example, a user requires the SELECT privilege on the SCOTT.EMP table to successfully issue queries against the table (assuming they don’t have the SELECT ANY TABLE system privilege). As with system privileges, the object privileges can be granted several ways.

Viewing Object Privileges

You can create the analogous view for displaying a user’s object privileges based on the ALL_USER_PRIV_PATH view:

sec_mgr@KNOX10g> CREATE OR REPLACE VIEW user_object_privs   2  AS   3    SELECT DISTINCT PRIVILEGE,   4                    owner,   5                    table_name object,   6                    grantee   7               FROM dba_tab_privs   8              WHERE grantee IN (   9                             SELECT *  10                               FROM all_user_priv_path)  11  / View created. sec_mgr@KNOX10g> GRANT SELECT ON user_object_privs TO PUBLIC; Grant succeeded. sec_mgr@KNOX10g> CREATE PUBLIC SYNONYM user_object_privs   2    FOR user_object_privs; Synonym created. sec_mgr@KNOX10g> SELECT COUNT (*)   2    FROM user_object_privs;   COUNT(*) ----------      20811

There are too many actual object privileges to list due mainly to the number of grants that have been made to the user group PUBLIC. The USER_OBJECT_PRIVS view is helpful in determining if the user has privileges on objects within a specific schema. The following query shows all the object privileges the SEC_MGR user has on SCOTT’s objects:

sec_mgr@KNOX10g> -- Show all object privileges user has sec_mgr@KNOX10g> -- for any of SCOTT's objects sec_mgr@KNOX10g> SELECT privilege, object   2    FROM user_object_privs   3   WHERE owner = 'SCOTT'; PRIVILEGE            OBJECT -------------------- ------------------------------ SELECT               DEPT SELECT               EMP

Both of these views will be referenced below to illustrate other points regarding privileges and roles.

I find it useful to have a script that will allow an administrator to check the object privileges granted to other users. The following script, saved in the file “obj_privs.sql,” allows you to check the object privileges granted to a user on objects contained in a specific schema. This differs from the view above because you don’t have to be logged on as the user to check the privileges. This script also shows how the above views can be adapted to meet other “customized” security queries. The important part is that this shows not only what privileges are available but also how the privileges were received. This will help you in ensuring the least privilege principle is being followed.

/*** File obj_privs.sql ***/ SET verify off COL privilege format a20 COL "Object Name" format a20 COL "Privilege Granted To" format a20 PROMPT Checking object privileges: PROMPT The following lists the privileges a user has on an owner's objects ACCEPT USERNAME prompt  '> Enter user''s name         : ' ACCEPT OWNERNAME prompt '> Enter object owner''s name : ' SELECT PRIVILEGE,        table_name "Object Name",        grantee "Privilege Granted To"   FROM (SELECT DISTINCT PRIVILEGE,                         owner,                         table_name,                         grantee                    FROM dba_tab_privs                   WHERE grantee IN (         SELECT DISTINCT usr.NAME                           granted_role                    FROM (SELECT     *                                FROM SYS.sysauth$                          CONNECT BY PRIOR privilege# =                                       grantee#                          START WITH grantee# =                                       (SELECT user#                                          FROM SYS.user$                                         WHERE NAME =                                                 UPPER                                                   ('&username'))                                  OR grantee# =                                       1) sauth,                         SYS.user$ usr                   WHERE usr.user# =                           sauth.privilege#         UNION ALL         SELECT UPPER                     ('&username')           FROM DUAL         UNION ALL         SELECT 'PUBLIC'           FROM DUAL))  WHERE owner = UPPER ('&ownername') order by 2; 

Running the script on my database, I can check the object privileges SCOTT has on the SEC_MGR schema:

sec_mgr@KNOX10g> @obj_privs Checking object privileges: The following lists the privileges a user has on an owner's objects > Enter user's name         : scott > Enter object owner's name : sec_mgr PRIVILEGE            Object Name          Privilege Granted To -------------------- -------------------- -------------------- SELECT               ALL_USER_PRIV_PATH   PUBLIC EXECUTE              DEBUG_POLICY         SCOTT SELECT               ENV                  PUBLIC EXECUTE              GET_TIMESTAMP        PUBLIC SELECT               GET_TRACE_FILENAME   PUBLIC EXECUTE              PEOPLE_CTX_MGR       SCOTT SELECT               USER_OBJECT_PRIVS    PUBLIC SELECT               USER_SYSTEM_PRIVS    PUBLIC 8 rows selected.

Synonyms

The database allows you to create synonyms for your objects. Synonyms allow you to provide convenient and user-friendly names for objects. It’s a good method for obscuring the owner and real name of the object. For example, a table called SHARED can be created and a publicly available synonym can be defined to reference the table.

sec_mgr@KNOX10g> -- Create a table sec_mgr@KNOX10g> CREATE TABLE SHARED (x NUMBER); Table created. sec_mgr@KNOX10g> -- Create a public synonym on table. sec_mgr@KNOX10g> -- Change table name. sec_mgr@KNOX10g> CREATE PUBLIC SYNONYM the_table FOR SHARED; Synonym created.

Authorized and privileged users will be able to access the SHARED table by direct reference to SEC_MGR.SHARED or by synonym reference with the simple name “THE_TABLE.” From a security perspective, the public synonym does not mask the identity of the underlying object—the synonym is merely an alias for the real object. Notice that SCOTT will know the synonym is referencing the SEC_MGR.SHARED object even though he doesn’t have access to the table:

scott@KNOX10g> -- Describe synonym THE_TABLE scott@KNOX10g> DESC the_table ERROR ORA-04043: object "SEC_MGR"."SHARED" does not exist

Privileges and Synonyms

It’s important to understand how privileges are used with synonyms. Privileges granted to synonyms are actually privilege grants to the base objects. It would be logical to assume that grants given by way of a synonym would be revoked when the synonym was dropped. That is not what happens. Dropping the synonym does not remove the privileges that were granted by way of that synonym.

An example proving this also illustrates an important point: mixing object and privilege grants may add confusion to the security management and administration process. First, create a table and populate it with some data. You’ll create a public synonym and then grant SELECT privileges on the synonym:

sec_mgr@KNOX10g> CREATE  TABLE t AS SELECT * FROM DUAL; Table created. sec_mgr@KNOX10g> CREATE PUBLIC SYNONYM t_synonym FOR sec_mgr.t; Synonym created. sec_mgr@KNOX10g> -- privilege to access T given to synonym sec_mgr@KNOX10g> GRANT  SELECT ON t_synonym TO scott; Grant succeeded.

Connect as SCOTT. Note that SCOTT can reference the base table directly.

scott@KNOX10g> SELECT * FROM sec_mgr.t; D - X

Next, drop the synonym.

sec_mgr@KNOX10g> DROP PUBLIC SYNONYM t_synonym; Synonym dropped.

This didn’t revoke the privileges on the object. SCOTT can still access the table.

scott@KNOX10g> SELECT * FROM SEC_MGR.t; D - X

The point to this exercise is that haphazard or random assignment of privileges, via synonyms in some cases and direct objects in others, can lead to confusion.

System and Object Privileges Together

One challenging area associated with access control is determining what privileges a user has and how they received them. Often the privilege can be derived multiple ways. This is convenient but it may also be a security risk, because oversight of this fact could allow an administrator to make a mistake, leaving access to something in the database. For example, the privilege to query the table T just created could occur by direct grant or by possessing the SELECT ANY TABLE system privilege.

The following example shows this. SCOTT gets the system privilege to query any table:

system@KNOX10g> GRANT SELECT ANY TABLE TO scott; Grant succeeded.

As the table owner, you decide that you no longer want SCOTT to access your table. You could have also revoked the privilege from a role to which SCOTT belonged. Unfortunately, you may be unaware that SCOTT has been granted the system privilege.

sec_mgr@KNOX10g> REVOKE SELECT ON t FROM scott; Revoke succeeded. sec_mgr@KNOX10g> -- Ensure no one has table privs sec_mgr@KNOX10g> SELECT grantee   2    FROM dba_tab_privs   3   WHERE owner = 'SEC_MGR' AND table_name = 'T'; no rows selected

Revoking one privilege doesn’t affect the other. The system privilege is still available to SCOTT. You also should have checked the SELECT ANY TABLE privilege. Consequently, revoking only the object privilege doesn’t prevent SCOTT from querying the table.

scott@KNOX10g> SELECT * FROM SEC_MGR.t; D - X

The solution to this problem is knowledge. Understanding the database privileges and how they can be issued and received is absolutely necessary for ensuring a secure database. Also, there are two system privileges that are extremely powerful: GRANT ANY PRIVILEGE and GRANT ANY OBJECT PRIVILEGE allow the recipient to grant any system privilege and any object privilege to any schema. This is great if the grantor is a security administrator but very bad if the grantor is a hacker.

Privilege Persistence

Another important concept to understanding privileges is in how they persist. If you grant a privilege to a user with administrative rights, then that user can grant the privilege to other users. If the user that had the administrative rights is dropped, all object privileges they granted to other users are also dropped. However, none of the system privileges are dropped.

The following example illustrates this subtle but important point. You’ll create three users. The first is the data schema that will hold your objects:

system@KNOX10g> -- Create a data schema system@KNOX10g> CREATE USER data_owner   2    IDENTIFIED BY VALUES 'no pwd'   3    DEFAULT TABLESPACE users   4    TEMPORARY TABLESPACE temp; User created. system@KNOX10g> -- Grant quota privileges to data schema system@KNOX10g> ALTER USER data_owner   2  QUOTA 10 m ON "USERS"; User altered. system@KNOX10g> -- Create a table in the data schema. system@KNOX10g> -- To create a table in another schema, you  system@KNOX10g> -- need the CREATE ANY TABLE system priv. system@KNOX10g> CREATE TABLE data_owner.t AS SELECT * FROM DUAL; Table created. system@KNOX10g> -- Create a procedure in the data schema system@KNOX10g> -- To create a procedure in another schema, you  system@KNOX10g> -- need the CREATE ANY PROCEDURE system priv. system@KNOX10g> CREATE PROCEDURE data_owner.foo   2  AS   3  BEGIN   4    NULL;   5  END;   6  / Procedure created.

Next create a “security officer” schema. This schema will control privileges on the DATA_OWNER schema’s objects. The security officer will subsequently require object privileges on DATA_OWNER’s objects and a few system privileges.

system@KNOX10g> -- create a junior security officer system@KNOX10g> CREATE USER sec_mgr_jr IDENTIFIED BY sec_mgr_jr; User created. system@KNOX10g> GRANT CREATE ROLE TO sec_mgr_jr; Grant succeeded. system@KNOX10g> -- delegate privs to the junior security officer system@KNOX10g> -- object privs system@KNOX10g> GRANT ALL ON data_owner.t TO sec_mgr_jr   2    WITH GRANT OPTION; Grant succeeded. system@KNOX10g> GRANT EXECUTE ON data_owner.foo TO sec_mgr_jr   2    WITH GRANT OPTION; Grant succeeded. system@KNOX10g> -- system privs system@KNOX10g> GRANT ALTER SESSION TO sec_mgr_jr WITH ADMIN OPTION; Grant succeeded. system@KNOX10g> GRANT CREATE SESSION TO sec_mgr_jr WITH ADMIN OPTION; Grant succeeded. system@KNOX10g> GRANT CREATE SYNONYM TO sec_mgr_jr WITH ADMIN OPTION; Grant succeeded. system@KNOX10g> GRANT CREATE SEQUENCE TO sec_mgr_jr WITH ADMIN OPTION; Grant succeeded.

Next, create a database user, SOME_USER, that represents the user who will be accessing the DATA_OWNER objects. Then log in as the security officer. The security officer will create a role and assign some object and system privileges to the role. The officer will grant the role to the user. Additional object and system privileges are granted directly to the user.

system@KNOX10g> -- create a user for this example system@KNOX10g> CREATE USER some_user IDENTIFIED BY some_user; User created. system@KNOX10g> -- junior security officer grants privs to some_user system@KNOX10g> CONNECT sec_mgr_jr/sec_mgr_jr Connected. sec_mgr_jr@KNOX10g> CREATE ROLE data_owner_access; Role created. sec_mgr_jr@KNOX10g> -- grant direct object privs sec_mgr_jr@KNOX10g> GRANT SELECT ON data_owner.t TO some_user; Grant succeeded. sec_mgr_jr@KNOX10g> GRANT EXECUTE ON data_owner.foo TO some_user; Grant succeeded. sec_mgr_jr@KNOX10g> -- grant role object privs sec_mgr_jr@KNOX10g> GRANT INSERT ON data_owner.t TO data_owner_access; Grant succeeded. sec_mgr_jr@KNOX10g> GRANT data_owner_access TO some_user; Grant succeeded. sec_mgr_jr@KNOX10g> -- grant direct system privs sec_mgr_jr@KNOX10g> GRANT ALTER SESSION TO some_user; Grant succeeded. sec_mgr_jr@KNOX10g> GRANT CREATE SESSION TO some_user; Grant succeeded. sec_mgr_jr@KNOX10g> -- grant role system privs sec_mgr_jr@KNOX10g> GRANT CREATE SYNONYM TO data_owner_access; Grant succeeded. sec_mgr_jr@KNOX10g> GRANT CREATE SEQUENCE TO data_owner_access; Grant succeeded.

By connecting as the user, you can verify the user has the role, the object, and the system privileges.

sec_mgr_jr@KNOX10g> -- show some user can connect and query table sec_mgr_jr@KNOX10g> CONNECT some_user/some_user Connected. some_user@KNOX10g> SELECT *   2    FROM session_roles; ROLE ------------------------------ DATA_OWNER_ACCESS some_user@KNOX10g> COL PRIVILEGE FORMAT A15 some_user@KNOX10g> COL OWNER FORMAT A15 some_user@KNOX10g> COL TABLE_NAME FORMAT A15 some_user@KNOX10g> SELECT *   2    FROM user_object_privs   3   WHERE owner = 'DATA_OWNER'; PRIVILEGE       OWNER           TABLE_NAME --------------- --------------- --------------- EXECUTE         DATA_OWNER      FOO INSERT          DATA_OWNER      T SELECT          DATA_OWNER      T some_user@KNOX10g> SELECT *   2    FROM user_system_privs; PRIVILEGE --------------- ALTER SESSION CREATE SEQUENCE CREATE SESSION CREATE SYNONYM some_user@KNOX10g> DESC data_owner.t  Name                                        Null?    Type  ------------------------------------------- -------- ------------  DUMMY                                                VARCHAR2(1)

This is where it may get confusing. Now drop the security manager.

system@KNOX10g> -- drop junior security officer system@KNOX10g> -- this drops the object privilege system@KNOX10g> -- but does not drop the system privilege system@KNOX10g> DROP USER sec_mgr_jr CASCADE; User dropped.

Note 

You didn’t drop the data objects—they still exist. When you reconnect as the database user, you’ll notice that all access to the data objects (the object privileges) is gone, yet all of the system privileges remain. The role still exists as well, but object privileges given to the role have also been dropped.

some_user@KNOX10g> SELECT *   2    FROM session_roles; ROLE ------------------------------ DATA_OWNER_ACCESS some_user@KNOX10g> DESC data_owner.t ERROR: ORA-04043: object data_owner.t does not exist some_user@KNOX10g> SELECT *   2    FROM user_object_privs   3   WHERE owner = 'DATA_OWNER'; no rows selected some_user@KNOX10g> SELECT *   2    FROM user_system_privs; PRIVILEGE --------------- ALTER SESSION CREATE SEQUENCE CREATE SESSION CREATE SYNONYM

Effective security management requires us to understand these Oracle privilege principles.