Considerations for Using Application-Based Security

An application security implementation should consider both application and database users and whether to enforce security in the application or in the database.

• Are Application Users Also Database Users?
  Where possible, build applications in which application users are database users, so that you can use the intrinsic security mechanisms of the database.

• Is Security Better Enforced in the Application or in the Database?
  Oracle recommends that applications use the security enforcement mechanisms of the database as much as possible.

Are Application Users Also Database Users?

Where possible, build applications in which application users are database users, so that you can use the intrinsic security mechanisms of the database.

For many commercial packaged applications, application users are not database users. For these applications, multiple users authenticate themselves to the application, and the application then connects to the database as a single, highly-privileged user. This is called the One Big Application User model.

Applications built in this way generally cannot use many of the intrinsic security features of the database, because the identity of the user is not known to the database. However, you can use client identifiers to perform some types of tracking. For example, the OCI_ATTR_CLIENT_IDENTIFIER attribute of the Oracle Call Interface method OCIAttrSet can be used to enable auditing and monitoring of client connections. Client identifiers can be used to gather audit trail data on individual Web users, apply rules that restrict data access by Web users, and monitor and trace applications that each Web user users. The following table describes how the One Big Application User model affects various Oracle Database security features:

Oracle Database Feature	Limitations of One Big Application User Model
Auditing	A basic principle of security is accountability through auditing. If One Big Application User performs all actions in the database, then database auditing cannot hold individual users accountable for their actions. The application must implement its own auditing mechanisms to capture individual user actions.
Oracle strong authentication	Strong forms of authentication (such as client authentication over SSL, tokens, and so on) cannot be used if the client authenticating to the database is the application, rather than an individual user.
Roles	Roles are assigned to database users. Enterprise roles are assigned to enterprise users who, though not created in the database, are known to the database. If application users are not database users, then the usefulness of roles is diminished. Applications must then craft their own mechanisms to distinguish between the privileges which various application users need to access data within the application.
Enterprise user management	The Enterprise user management feature enables an Oracle database to use the Oracle Identity Management Infrastructure by securely storing and managing user information and authorizations in an LDAP-based directory such as Oracle Internet Directory. While enterprise users do not need to be created in the database, they do need to be known to the database. The One Big Application User model cannot take advantage of Oracle Identity Management.

Is Security Better Enforced in the Application or in the Database?

Oracle recommends that applications use the security enforcement mechanisms of the database as much as possible.

Applications, whose users are also database users, can either build security into the application, or rely on intrinsic database security mechanisms such as granular privileges, virtual private databases (fine-grained access control with application context), roles, stored procedures, and auditing (including fine-grained auditing).

When security is enforced in the database itself, rather than in the application, it cannot be bypassed. The main shortcoming of application-based security is that security is bypassed if the user bypasses the application to access data. For example, a user who has SQL*Plus access to the database can run queries without going through the Human Resources application. The user, therefore, bypasses all of the security measures in the application.

Applications that use the One Big Application User model must build security enforcement into the application rather than use database security mechanisms. Because it is the application, and not the database, that recognizes users; the application itself must enforce security measures for each user.

This approach means that each application that accesses data must re-implement security. Security becomes expensive, because organizations must implement the same security policies in multiple applications, and each new application requires an expensive reimplementation.

Related Topics

• Potential Security Problems of Using Ad Hoc Tools

Use of the DB_DEVELOPER_ROLE Role for Application Developers

The DB_DEVELOPER_ROLE role provides most of the system privileges, object privileges, predefined roles, PL/SQL package privileges, and tracing privileges that an application developer needs.

An application developer needs a large number of these privileges to design, develop, and deploy applications. Oracle recommends that you grant the application developer the DB_DEVELOPER_ROLE role, rather than individually granting these privileges or granting the user the DBA role. Granting the application user the DB_DEVELOPER_ROLE role not only adheres to least-privilege principles and ensures greater security for the development environment, it facilitates the management of role grants and revokes for application developers. The DB_DEVELOPER_ROLE role can be used in either the CDB root or the PDB. Do not modify the DB_DEVELOPER_ROLE.

Generating a List of Privileges and Roles Granted by the DB_DEVELOPER_ROLE Role

To generate a full list of the system privileges, object privileges, and roles that are granted by the DB_DEVELOPER_ROLE, run the following statement. Ensure that you include the set serveroutput on format wrapped command, so that the indentation will be shown properly. Note: Be aware that the output will vary, depending on the version or patch release of Oracle Database that you are using.

set serveroutput on format wrapped;
DECLARE
    procedure printRolePrivileges(
      p_role             in varchar2,
      p_spaces_to_indent in number) IS
      v_child_roles   DBMS_SQL.VARCHAR2_TABLE;
      v_system_privs  DBMS_SQL.VARCHAR2_TABLE;
      v_table_privs   DBMS_SQL.VARCHAR2_TABLE;
      v_indent_spaces varchar2(2048);
    BEGIN
      -- Indentation for nested privileges via granted roles.
      for space in
1..p_spaces_to_indent LOOP
        v_indent_spaces := v_indent_spaces || ' ';
      end LOOP;
      -- Get the system privileges granted to p_role
      select PRIVILEGE bulk collect into v_system_privs
      from DBA_SYS_PRIVS
      where GRANTEE = p_role
      order by PRIVILEGE;

      -- Print the system privileges granted to p_role
      for privind in 1..v_system_privs.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
          v_indent_spaces || 'System priv: ' || v_system_privs(privind));
      END LOOP;

      -- Get the object privileges granted to p_role
      select PRIVILEGE || ' ' || OWNER || '.' || TABLE_NAME
        bulk collect into v_table_privs
      from DBA_TAB_PRIVS
      where GRANTEE = p_role
      order by TABLE_NAME asc;

      -- Print the object privileges granted to p_role
      for tabprivind in 1..v_table_privs.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
          v_indent_spaces || 'Object priv: ' || v_table_privs(tabprivind));
      END LOOP;

      -- get all roles granted to p_role
      select GRANTED_ROLE bulk collect into v_child_roles
      from DBA_ROLE_PRIVS
      where GRANTEE = p_role
      order by GRANTED_ROLE asc;

      -- Print all roles granted to p_role and handle child roles recursively.
      for roleind in 1..v_child_roles.COUNT LOOP
        -- Print child role
        DBMS_OUTPUT.PUT_LINE(
         v_indent_spaces || 'Role priv: ' || v_child_roles(roleind));

        -- Print privileges for the child role recursively. Pass 2 additional
        -- spaces to illustrate these privileges belong to a child role.
        printRolePrivileges(v_child_roles(roleind), p_spaces_to_indent + 2);
      END LOOP;

      EXCEPTION
        when OTHERS then
          DBMS_OUTPUT.PUT_LINE('Got exception: ' || SQLERRM );

    END printRolePrivileges;

BEGIN
    printRolePrivileges('DB_DEVELOPER_ROLE', 0);
END;
/

Output similar to the following appears:

System priv: CREATE ANALYTIC VIEW
System priv: CREATE ATTRIBUTE DIMENSION
System priv: CREATE CUBE
System priv: CREATE CUBE BUILD PROCESS
System priv: CREATE CUBE DIMENSION
System priv: CREATE DIMENSION
System priv: CREATE DOMAIN
System priv: CREATE HIERARCHY
System priv: CREATE JOB
System priv: CREATE MATERIALIZED VIEW
System priv: CREATE MINING MODEL
System priv: CREATE MLE
System priv: CREATE PROCEDURE
System priv: CREATE SEQUENCE
System priv: CREATE SESSION
System priv: CREATE SYNONYM
System priv: CREATE TABLE
System priv: CREATE TRIGGER
System priv: CREATE TYPE
System priv: CREATE VIEW
System priv: DEBUG CONNECT SESSION
System priv: EXECUTE DYNAMIC MLE
System priv: FORCE TRANSACTION
System priv: ON COMMIT REFRESH
Object priv: SELECT SYS.DBA_PENDING_TRANSACTIONS
Object priv: EXECUTE SYS.JAVASCRIPT
Object priv: READ SYS.V_$PARAMETER
Object priv: READ SYS.V_$STATNAME
Role priv: CTXAPP
  System priv: CREATE SEQUENCE
  Object priv: EXECUTE CTXSYS.CTX_ANL
  Object priv: EXECUTE CTXSYS.CTX_DDL
  Object priv: EXECUTE CTXSYS.CTX_ENTITY
  Object priv: EXECUTE CTXSYS.CTX_OUTPUT
  Object priv: EXECUTE CTXSYS.CTX_THES
  Object priv: EXECUTE CTXSYS.CTX_ULEXER
  Object priv: INSERT CTXSYS.DR$DICTIONARY
  Object priv: DELETE CTXSYS.DR$DICTIONARY
  Object priv: SELECT CTXSYS.DR$DICTIONARY
  Object priv: UPDATE CTXSYS.DR$DICTIONARY
  Object priv: INSERT CTXSYS.DR$THS
  Object priv: INSERT CTXSYS.DR$THS_BT
  Object priv: INSERT CTXSYS.DR$THS_FPHRASE
  Object priv: UPDATE CTXSYS.DR$THS_PHRASE
  Object priv: INSERT CTXSYS.DR$THS_PHRASE
  Object priv: EXECUTE CTXSYS.DRIENTL
  Object priv: EXECUTE CTXSYS.DRITHSL
Role priv: SODA_APP
  Object priv: EXECUTE XDB.DBMS_SODA_ADMIN
  Object priv: EXECUTE XDB.DBMS_SODA_USER_ADMIN
  Object priv: READ XDB.JSON$USER_COLLECTION_METADATA

Performing Grants and Revokes of the DB_DEVELOPER_ROLE Role

To grant the DB_DEVELOPER_ROLE to another user or role, use the GRANT statement, as you would with any role grant. For example:

GRANT DB_DEVELOPER_ROLE TO pfitch;

To check the grant:

SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='pfitch';

Revoking the DB_DEVELOPER_ROLE is similar:

REVOKE DB_DEVELOPER_ROLE FROM pfitch;