Skip Headers
Oracle® Database Vault Administrator's Guide
11g Release 1 (11.1)

B31222-21
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

D Oracle Database Vault Security Guidelines

This appendix contains:

Separation of Duty Guidelines

This section contains:

How Oracle Database Vault Handles Separation of Duty

Separation of duty means that you restrict each user's privileges only to the tasks he or she is responsible for, and no more. You should assign specific categories of privileges to specific users, rather than granting many privileges to one user. Simply put, separation of duty creates accountability for each task that your organization requires.

Separation of duty has taken on increased importance over the past 10 years. For many organizations, separation of duty is a new concept that continues to evolve. Database consolidation, regulatory compliance, and outsourcing are just a few of the drivers for increased separation of duty. Oracle Database Vault separation of duty strengthens security by separating security-related administration from day-to-day DBA operations. You can tailor your Database Vault separation of duty implementation to easily adapt to current and future business requirements. Small organizations, in particular, need flexibility as they attempt to increase their security profile with limited resources.

Defining Separate Tasks in an Oracle Database Vault Environment

Oracle Database Vault defines the following main responsibilities:

  • Account management. Account management entails creating, modifying, and dropping user accounts. The DV_ACCTMGR role provides these privileges.

  • Security administration. Security administration covers basic security tasks such as creating realms and command rules, setting security policies for database users' access, and authorizing database users for jobs they are allowed to perform. Security administrators also run security audit reports. The DV_OWNER and DV_ADMIN roles provide these privileges. (For an in-depth look at how the Oracle Database Vault roles provide for separation of duty, see "Oracle Database Vault Roles".)

    Optionally, you can consolidate the account management and security administrative responsibilities.

  • Resource management. Resource management refers to managing the database system but not accessing business data. It includes the following operations:

    • Backup operations require a predefined time to perform the backup using predefined tools.

    • Tuning and monitoring operations require ongoing performance monitoring and analysis.

    • Patching operations require temporary access only during the time the patching takes place

    For resource management, you should create a named account and a backup account for each of these tasks. Add these accounts as owners of the Data Dictionary realm. Use these accounts as the primary resource managers in the database.

You should have separate accounts for database account management, database security administration, and additional named accounts for backup operations. Auditors check for separate database accounts for different responsibilities and being able to track the actions of each account. Less important is the number of users assigned to specific tasks. Remember that Oracle Database Vault audit events are protected and that the Database Vault reports show all attempted violations.

Creating a Separation of Duty Matrix

Before separation of duty can be successful, you must understand who performs basic administration tasks in your environment and what these administration tasks are. Even if a single database administrator is responsible for managing both new database account provisioning and application patching, it is important to document and plan for each of these tasks.Using separate administration accounts for these types of tasks provides increased accountability and reduces associated risks. In midsize to large organizations, database administrators typically must perform common administration tasks but they do not need access to business data managed by the application. Creating a matrix for your separation of duty can help you plan your Database Vault deployment. As needed, you can include additional tasks and associated users to this list. This information should become part of the overall enterprise security documentation for your organization.

Table D-1 shows an example of a separation of duty matrix.

Table D-1 Example Separation of Duty Matrix

User, Process or Application Account Creation Database Administration Security Administrator
SYSDBA Backup Tuning Patching Monitoring

...

JSMITH

X

           

SHARDY

           

X

PKESTNER

   

X

       

RTYLER

       

X

   

SANDERSON

     

X

 

X

 

SYSTEM

       

EBS patching

   

RMAN

 

X

X

       

In some cases, system management tasks may require temporary access to data through specific tools and programs. When this happens, build provisions for this temporary or emergency access into the Oracle Database Vault rules and rule sets.

Identifying and Documenting the Tasks of Users Who Access the Database System

You should document the following areas of the tasks your organization needs:

  • The responsibilities of each administrative user

  • The kind of access users need. For example, application owners should have data access and developers need access to development instances only.

  • Who must manage the system without accessing business data (for example, users who perform backup, patching, tuning, and monitoring operations).

  • The duties of each category of tasks (for example, the files that must be backed up, the applications that require patching, what exactly is monitored). Include the alternate user accounts for each of these tasks.

  • The databases and applications that must be protected. This includes Oracle applications, partner applications, and custom applications.

  • Who must be authorized to access business data, including the following:

    • Application owners through middle tier processes

    • Business users through an application interface

  • Emergency "what if" scenarios, such as how to handle a security breach

  • Reporting in a production environment, which should include the following:

    • Who runs the reports

    • Which reports must be run

    • The frequency with which each report is run

    • The users who must receive a copy of each report

  • In addition to a separation of duty matrix, the creation of the following matrices:

    • An Oracle Database Vault-specific matrix, which can cover the names and tasks of users who have been granted Database Vault roles

    • An application protection matrix, which can cover the applications to be protected and the types of protections you have put in place.

    Table D-2 shows an example of protections Oracle created for PeopleSoft Applications. You can download the scripts to create these security policies from the following URL:

    http://www.oracle.com/technology/software/products/database_vault/index.html

    Table D-2 Example Application Protection Maxtrix

    Protection Type Authorized with Rule Set
    SYSADM PSFTDBA SYSTEM DBA

    PeopleSoft Realm

    Owner

    Owner

    No Access

    No Access

    SELECT Command Rule

    Not Restricted

    Limit PSFTDB Rule Set

    No Access

    No Access

    CONNECT Command Rule

    PeopleSoftAccess Rule Set

    Not Restricted

    Not Restricted

    Not Restricted

    DROP TABLESPACE Command Rule

    Disabled Rule Set

    Disabled Rule Set

    Disabled Rule Set

    Disabled Rule Set


Managing Oracle Database Administrative Accounts

This section contains:

Using the SYSTEM User Account for General Administrative Uses

If you use the SYSTEM account for general database administrative purposes, create named database administrative accounts for your database administrators. Doing so increases accountability for administrative actions in the database.

Using the SYSTEM Schema for Application Tables

If your site holds application tables in the SYSTEM schema, then you should add the SYSTEM account to your realm authorizations for these tables so that these applications can continue to work normally. You can place restrictions on the SYSTEM account to increase or fine-tune security for these applications. For example, you can create a Database Vault rule set to restrict the SYSTEM user's access to specific IP addresses.

Limiting the SYSDBA Privilege

Limit the SYSDBA privilege only to users who must connect using this privilege when absolutely necessary and for applications that still require SYSDBA access, such as Oracle Recovery Manager (RMAN) and mandatory patching processes. For all other cases, create named database accounts to perform daily database administration.

Managing Root and Operating System Access

Oracle Database Vault prevents highly privileged database users from accessing sensitive data. In addition, if you are using Oracle Database itself, you can use transparent data encryption to prevent the most highly privileged operating system users from accessing sensitive data. Transparent data encryption enables you to hide individual table columns. (See Oracle Database Advanced Security Administrator's Guide for more information about transparent data encryption.) As a best practice, always carefully review and restrict direct access to the operating system.

You should have personalized accounts access the operating system. These personalized accounts should, in the Linux or UNIX environments, login using sudo to the oracle software owner when needed. With sudo, you can control which specific command each personalized user can execute. Be sure to prevent the use of the make, relink, gdb, or other commands that could potentially harm the database, for these users. However, if an administrative user must install a patch or perform some other emergency operation, you can enable the make and relink commands for a limited time, and audit their actions during this period.

Accounts and Roles Trusted by Oracle Database Vault

Oracle Database Vault restricts access to application data from many privileged users and roles in the database. However, in some cases, Oracle Database Vaults trusts certain roles and privileges.

Table D-3 lists the trusted roles and privileges that are created when you install Oracle Database Vault.

Table D-3 Trusted Oracle Database Vault Roles and Privileges

Role or Privilege        Status         Description

DV_ACCTMGR role

Open

Role created during installation and used for creating new database accounts

DV_OWNER role

Open

Role created during installation and used for managing realms, factors and command rules. This user cannot add himself or herself to realm authorizations, nor can users who have the DV_ACCTMGR role alter this user.

SYSDBA privilege

Enabled

Privilege created during Oracle Database installation. Required by some Oracle features. See "Managing SYSDBA Access" for guidelines on managing SYSDBA.

SYSOPER privilege

Enabled

Privilege created during Oracle Database installation. Database startup and shutdown. Granted to SYS only by default. See "Managing SYSOPER Access" for guidelines on managing SYSOPER.


Accounts and Roles That Should be Limited to Trusted Individuals

Several accounts and roles have very powerful privileges in a default Oracle Database installation. You should limit these accounts and roles only to trusted individuals.

Managing Users with Root Access to the Operating System

Users who have root user access have full control over the system, including the following activities:

  • Reading unencrypted files

  • Moving and deleting any files

  • Starting or stopping any program on the system

  • Logging in as any user, including the user who owns the Oracle Database installation

Oracle Database Vault does not provide protection against the operating system root access. Ensure that you grant root user privileges only to the appropriate people with the appropriate responsibility.

Managing the Oracle Software Owner

Users who have access to a system as the Oracle software owner have control over the Oracle software, including the following activities:

  • Disabling Oracle Database Vault in the given system

  • Reading unencrypted database files

  • Moving and deleting database files

  • Starting or stopping Oracle programs in the system

Oracle Database Vault does not provide protection against the operating system access of the Oracle software owner. Ensure that you grant Oracle software owner access only to the appropriate people with the appropriate responsibility.

Managing SYSDBA Access

The SYSDBA privilege is a trusted privilege in Oracle Database Vault. Grant this privilege to trusted users only.

Managing SYSOPER Access

By default, Oracle Database limits SYSOPER access to operating system users in the SYSOPER group and the user SYS. It prevents SYSOPER from modifying the Oracle data dictionary directly. The SYSOPER privilege has limited privileges within the database, but individuals with this role can start and shut down the Oracle database. Only grant the SYSOPER privilege to trusted individuals.

Guidelines for Using Oracle Database Vault in a Production Environment

Follow these guidelines when running Oracle Database Vault in a production environment:

  • Run a full test of your applications to ensure that the Database Vault policies you have created are working as expected

  • Monitor the performance of your applications, and if necessary, tune your rule expressions

  • Assign responsibilities to the appropriate production support and security groups, as follows:

    • Assign security responsibilities to the database security administrator.

    • Assign account management to the database account manager.

    • Assign resource management tasks to database administrators.

  • Back up your Database Vault API scripts to a secure server.

Secure Configuration Guidelines

Follow these configuration and security guidelines:

Note:

  • Installing patches and new applications might re-grant some of the privileges that Oracle recommends that you revoke in this section. Check these privileges after you install patches and new applications to verify that they are still revoked.

  • When you revoke EXECUTE privileges on packages, ensure that you grant EXECUTE on the packages to the owner, check the package dependencies, and recompile any invalid packages after the revoke.

    To find users who have access to the package, log in to SQL*Plus as SYSTEM and issue the following query.

    SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME = package_name;
    

    package_name is the name of the package you are looking for.

    To find the users, packages, procedures, and functions that are dependent on the package, issue this query:

    SELECT OWNER, NAME, TYPE  FROM ALL_DEPENDENCIES 
    WHERE REFERENCED_NAME = package_name;
    

    Note that these two queries do not identify references to packages made through dynamic SQL.

Security Considerations for the UTL_FILE and DBMS_FILE_TRANSFER Packages

The UTL_FILE package is owned by SYS and granted to PUBLIC. However, a user must have access to the directory object to manipulate the files in that operating system directory. You can configure the UTL_FILE package securely; see Oracle Database PL/SQL Packages and Types Reference for more information.

The DBMS_FILE_TRANSFER package is owned by SYS and granted to the EXECUTE_CATALOG_ROLE. Users with EXECUTE access on this package can move files from one location to another on the same file system. They also can move files between database instances, including databases on remote systems.

To secure the DBMS_FILE_TRANSFER package, do the following:

  • Revoke the EXECUTE privilege from the DBMS_FILE_TRANSFER package and grant the EXECUTE privilege only to trusted users who need it.

  • Create command rules to control the CREATE DATABASE LINK and CREATE DIRECTORY SQL statements. See "Creating and Editing a Command Rule" for information on creating command rules by using Oracle Database Vault Administrator.

Alternatively, Example D-1 and Example D-2 show how you can use the Oracle Database Vault DBMS_MACADM package to create command rules that limit and enable access to the CREATE DATABASE LINK statement, which is used to establish connections to remote databases. To use this method, log in to SQL*Plus using the Oracle Database Vault Owner account.

Example D-1 Creating a Command Rule to Deny Access to CREATE DATABASE LINK

BEGIN 
 DBMS_MACADM.CREATE_COMMAND_RULE (
  command       => 'CREATE DATABASE LINK', 
  rule_set_name => 'Disabled', 
  object_owner  => '%', 
  object_name   => '%', 
  enabled       => DBMS_MACUTL.G_YES); 
  END; 
  / 
COMMIT;

When a valid user must use the CREATE DATABASE LINK statement, the Oracle Database Vault owner can reenable it from Oracle Database Vault Administrator or issue the following commands in SQL*Plus.

Example D-2 Creating a Command Rule to Enable Access to CREATE DATABASE LINK

BEGIN 
 DBMS_MACADM.UPDATE_COMMAND_RULE (
  command       => 'CREATE DATABASE LINK', 
  rule_set_name => 'Enabled', 
  object_owner  => '%', 
  object_name   => '%', 
  enabled       => DBMS_MACUTL.G_YES); 
 END; 
 /  
COMMIT;

Similarly, Example D-3 shows command rules that disable and enable access to CREATE DIRECTORY.

Example D-3 Command Rules to Disable and Enable Access to CREATE DIRECTORY

-- Disable access to CREATE DIRECTORY
BEGIN
 DBMS_MACADM.CREATE_COMMAND_RULE (
  command       => 'CREATE DIRECTORY', 
  rule_set_name => 'Disabled', 
  object_owner  => '%', 
  object_name   => '%', 
  enabled       => dbms_macutl.g_yes); 
 END; 
  / 
COMMIT;

-- Enable access to CREATE DIRECTORY
BEGIN 
 dbms_macadm.update_command_rule (
  command       => 'CREATE DIRECTORY', 
  rule_set_name => 'Enabled', 
  object_owner  => '%', 
  object_name   => '%', 
  enabled       => dbms_macutl.g_yes); 
 END; 
 /  
COMMIT;

Security Considerations for the Recycle Bin

In this release of Oracle Database Vault, the recycle bin feature has been enabled. The ALTER SYSTEM command rule prevents it from being enabled or disabled. To disable the recycle bin, log in to SQL*Plus, disable the command rule ALTER SYSTEM, and then disable the recycle bin.

When the recycle bin feature is enabled, any realm-protected object that is dropped goes into the recycle bin. Once there, it is no longer protected by the realm. In SQL*Plus, you can check the contents of the recycle bin as follows:

SELECT * FROM RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;

To purge the contents of the recycle bin, use the PURGE SQL statement:

PURGE RECYCLEBIN;
PURGE USER_RECYCLEBIN;

To disable the ALTER SYSTEM command rule and disable the recycle bin:

  1. As the DVOWNER or DV_ADMIN account, disable the ALTER SYSTEM command rule.

    BEGIN
     DBMS_MACADM.UPDATE_COMMAND_RULE(
      command         => 'ALTER SYSTEM', 
      rule_set_name   => 'Allow System Parameters', 
      object_owner    => '%', 
      object_name     => '%', 
      enabled         => 'N');
    END;
    /
    
  2. Connect as SYS using the SYSDBA privilege and then disable the RECYCLE BIN.

    CONNECT SYS AS SYSDBA
    Enter password: password
    Connected
    
    ALTER SYSTEM SET RECYCLEBIN=OFF SCOPE=SPFILE;
    
  3. Connect as DVOWNER or DV_ADMIN and then reenable the ALTER SYSTEM command rule.

    BEGIN
     DBMS_MACADM.UPDATE_COMMAND_RULE(
      command         => 'ALTER SYSTEM', 
      rule_set_name   => 'Allow System Parameters', 
      object_owner    => '%', 
      object_name     => '%', 
      enabled         => 'Y');
    END;
    /  
    

Security Considerations for the CREATE ANY JOB and CREATE JOB Privileges

In this release of Oracle Database Vault, the CREATE JOB privilege has been revoked from the DBA and the SCHEDULER_ADMIN roles. Ensure that this change does not affect your applications.

Security Considerations for the CREATE EXTERNAL JOB Privilege

The CREATE EXTERNAL JOB privilege was introduced in Oracle Database 10g Release 2 (10.2). It is required for database users who want to execute jobs that run on the operating system outside the database. By default, this privilege is granted to all users who have been granted the CREATE JOB privilege. For greater security, revoke this privilege from users who do not need it and then grant it only to those users who do need it.

Security Considerations for the LogMiner Packages

In this release of Oracle Database Vault, the role EXECUTE_CATALOG_ROLE no longer has the EXECUTE privilege granted by default on the following LogMiner packages:

  • DBMS_LOGMNR

  • DBMS_LOGMNR_D

  • DBMS_LOGMNR_LOGREP_DICT

  • DBMS_LOGMNR_SESSION

Ensure that this change does not affect your applications.

Security Considerations for the ALTER SYSTEM and ALTER SESSION Privileges

Be aware that trace and debug commands have the potential to show Oracle database memory information. Oracle Database Vault does not protect against these commands. To help secure the Oracle database memory information, Oracle recommends that you strictly control access to the ALTER SYSTEM and ALTER SESSION privileges. These privileges can be granted by the user SYS when connected as SYSDBA and by any user granted the DBA role.

Oracle also recommends that you add rules to the existing command rule for ALTER SYSTEM statement. You can use Oracle Database Vault Administrator to create a rule and add it to a rule set. You should grant the ALTER SESSION privilege only to trusted users. (For example, the ALTER SESSION statement can enable tracing.)

Example D-4 shows how you can create this type of rule. This rule prevent users with ALTER SYSTEM privilege from issuing the command ALTER SYSTEM DUMP. Log in to SQL*Plus as the Oracle Database Vault Owner when you create this command rule.

Example D-4 Adding Rules to the Existing ALTER SYSTEM Command Rule

CONNECT amalcolm_dvacctmgr
Enter password: password

BEGIN
 DBMS_MACADM.CREATE_RULE('NO_SYSTEM_DUMP',
 '(INSTR(UPPER(DVSYS.DV_SQL_TEXT),''DUMP'') = 0)');
 END;
/
EXEC DBMS_MACADM.ADD_RULE_TO_RULE_SET
  ('Allow System Parameters','NO_SYSTEM_DUMP');

EXEC DBMS_MACADM.UPDATE_COMMAND_RULE
   ('ALTER SYSTEM', 'Allow System Parameters', '%', '%', 'Y');
COMMIT;

Alternatively, you can use Oracle Database Vault Administrator to create and add this rule to the rule set. See "Creating a Rule to Add to a Rule Set" for more information.

Security Considerations for Java Stored Procedures and Oracle Database Vault

A definer's rights stored procedure relies on the privileges of the owner of the stored procedure to access objects referenced within the stored procedure. Invoker's rights stored procedures rely on the privileges of the executor of the stored procedure to access objects referenced within the stored procedure. The default for Java stored procedures is invoker's rights.

Oracle Database Vault security works by intercepting calls made within the Oracle Database.

For Java stored procedures with definer's rights, the execution of the stored procedure is not blocked and realm protection is not enforced. However, underlying objects accessed by the Java stored procedure can be protected by Oracle Database Vault command rules.

For Java stored procedures with invoker's rights, the execution of the stored procedure is not blocked. However, underlying objects accessed by the Java stored procedure are protected by both Oracle Database Vault realms and command rules.

Limiting Access to Java Stored Procedures

By default, the EXECUTE ANY PROCEDURE privilege is granted to the DBA, EXPORT_FULL_DATABASE, and IMPORT_FULL_DATABASE roles. You can limit access to Java stored procedures by revoking the EXECUTE ANY PROCEDURE from users and roles who do not require it, and then by selectively assigning them read privileges. Note also that revoking the EXECUTE ANY PROCEDURE from users further secures the database by limiting access to SYS-owned packages.

Step 1: Identify the Java Stored Procedures Created with Definer's Rights

Identify the Java stored procedures that were created with definers rights by running the query in Example D-5. This query returns only Java stored procedures that connect to the database, and then it spools the results to the file java_dr.lst.

Example D-5 Query to Identify Java Stored Procedures with Definers Rights

COLUMN plsql_owner FORMAT a8
COLUMN plsql FORMAT a30
COLUMN java_owner FORMAT a8
COLUMN java FORMAT a30
SPOOL java_dr
select distinct plu.name plsql_owner, plo.name plsql,
                ju.name java_owner, jo.name java
 from obj$ plo, user$ plu, user$ ju, obj$ jo, procedurejava$ j
 where jo.name=j.classname and ju.user#=jo.owner# and ju.name=j.ownername
   and jo.type#=29 and bitand(jo.flags, 8)=0
   and plo.owner#=plu.user#
   and j.obj#=plo.obj# and bitand(plo.flags, 8)=0
   and ju.name not in ('SYS', 'ORDSYS') 
   and jo.obj# in
   (select d_obj# from dependency$ connect by d_obj#=prior p_obj#
   start with p_obj#=(select obj# from obj$ where name='java/sql/Connection'
      and owner#=0));
SPOOL off

Step 2: Find the Java Stored Procedures That Access Realm-Protected Objects

Analyze the Java stored procedures you queried in Step 1 and determine whether any of them access Realm protected objects. You can find a list of the realm-secured objects in the current database instance by using the DBA_DV_REALM_OBJECT view, which is described in "DBA_DV_REALM_OBJECT View".

Step 3: Create a Package to Wrap Procedures Accessing Realm-Protected Objects

For Java stored procedures that do access realm-protected objects, create a PL/SQL package to wrap the Java stored procedure. Due to PL/SQL optimizations, the PL/SQL package wrapper must have a dummy variable defined in the package header. Adding the dummy variable enables Oracle Database Vault to intercept and block execution of Java stored procedures. Bear in mind that while this method does secure the execution of the Java stored procedure, it does not provide protection against calls to other Java stored procedures that may be embedded.

Example D-6 shows the PL/SQL package mypackage being created to wrap the Java class emp_count.

Example D-6 Creating a PL/SQL Wrapper

CREATE OR REPLACE PACKAGE SCOTT.MYPACKAGE AS
 tmp varchar2(200) := 'TEST';  -- dummy variable
 FUNCTION empcount RETURN VARCHAR2;
END;
/
 
CREATE OR REPLACE PACKAGE BODY SCOTT.MYPACKAGE AS
   FUNCTION empcount RETURN VARCHAR2 AS LANGUAGE JAVA
   NAME 'emp_count.count() return java.lang.String';
END;
/

Step 4: Identify the Java Stored Procedures Created with Invoker's Rights

Next, you are ready to identify the Java stored procedures that were created with invoker's rights. Do so by running the query in Example D-7. This query only returns Java stored procedures that connect to the database, and then it spools the results to the file java_dr.lst.

Example D-7 Identifying Java Stored Procedures with Invoker's Rights

COLUMN plsql_owner FORMAT a8
COLUMN plsql FORMAT a30
COLUMN java_owner FORMAT a8
COLUMN java FORMAT a30
spool java_ir
 
select distinct plu.name plsql_owner, plo.name plsql,
                ju.name java_owner, jo.name java
 from obj$ plo, user$ plu, user$ ju, obj$ jo, procedurejava$ j
 where jo.name=j.classname and ju.user#=jo.owner# and ju.name=j.ownername
   and jo.type#=29 and bitand(jo.flags, 8)=8
   and plo.owner#=plu.user#
   and j.obj#=plo.obj# and bitand(plo.flags, 8)=0
   and ju.name not in ('SYS', 'ORDSYS')
   and jo.obj# in
   (select d_obj# from dependency$ connect by d_obj#=prior p_obj#
   start with p_obj#=(select obj# from obj$ where name='java/sql/Connection'
      and owner#=0));
 
spool off

Step 5: Block the Execution of Java Stored Procedures

Oracle Database Vault realm and command rules are enforced for invoker's rights stored procedures. However, it can be useful to even block execution on Java stored procedures. You can do this by following Step 3: Create a Package to Wrap Procedures Accessing Realm-Protected Objects.

Step 6: Verify Oracle Database Vault Protection for Java Stored Procedures

Verify that Oracle Database Vault is protecting your Java stored procedures. Example D-8 show how you can test Oracle Database Vault security. Log in to a tool such as SQL*Plus. Then try to access a realm-protected object directly and execute a Java stored procedure to access a realm protected object.

Example D-8 Testing Oracle Database Vault Protection for Java Stored Procedures

SQL> CONNECT u1
Enter password: password

SQL> SELECT * FROM SESSION_PRIVS;
 
PRIVILEGE
----------------------------------------
CREATE SESSION
SELECT ANY TABLE
CREATE PROCEDURE
EXECUTE ANY PROCEDURE
 
Protecting access on direct SQL access 
 
SQL> SELECT COUNT(*) FROM SCOTT.EMP;

ERROR at line 1:
ORA-01031: insufficient privileges
 
--Now show protecting access through Java
 
SQL> SELECT SCOTT.MYPACKAGE.EMPCOUNT FROM DUAL;

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.MYPACKAGE", line 2

Step 7: Secure the Invoker's Rights for New Java Stored Procedures

If you are writing new Java stored procedures, ensure that Java classes execute with invoker's rights and define them in a PL/SQL package specification. Remember, it is important to include a dummy PL/SQL variable in the package header. Adding the dummy variable enables Oracle Database Vault to intercept and block execution of Java stored procedures.

Security Considerations for External C Callouts and Oracle Database Vault

For external C callouts with definer's rights, the execution of the callout is not blocked and realm protection is not enforced. However, underlying objects accessed by the external C callout are protected by Oracle Database Vault command rules. The default for external C callouts is invoker's rights.

For external C callouts with invoker's rights, the execution of the external C callout is not blocked. However, underlying objects accessed by the external C callouts are protected by both Oracle Database Vault realms and command rules.

Oracle Database Vault security works by intercepting calls made within the Oracle Database.

Securing EXECUTE ANY PROCEDURE by Limiting Access to External C Callouts

By default the EXECUTE ANY PROCEDURE privilege is granted to the DBA, EXPORT_FULL_DATABASE, and IMPORT_FULL_DATABASE roles. You can limit access to external C callouts by revoking the EXECUTE ANY PROCEDURE from users and roles who do not require it. Note also that revoking the EXECUTE ANY PROCEDURE from users further secures the database by limiting access to SYS-owned packages.

Step 1: Identifying the External C Callouts Created with Definer's Rights

Identify the external C callouts that were created with definer's rights by running the query in Example D-9. This query spools the results to the file external_wrap.lst.

Example D-9 Identifying External C Callouts That Are Wrapped by PL/SQL Packages

spool external_wrap
select u.name OWNER, o.name object, o.type#, o.flags from
  sys.obj$ o, sys.user$ u
where o.owner# = u.user# and
u.name not in ('MDSYS', 'ORDSYS', 'SYS') and o.obj# in (
select d_obj# from dependency$ connect by d_obj#=prior p_obj#
   start with p_obj# in (select obj# from library$ where property = 0))
order by owner, object;
spool off

Step 2: Finding the External C Callouts That Access Realm-Protected Objects

Analyze the external C callouts and determine whether any of them access realm-protected objects. You can find a list of the realm-secured objects in the current database instance by using the DBA_DV_REALM_OBJECT view, which is described in "DBA_DV_REALM_OBJECT View".

Step 3: Creating a Package to Wrap C Callouts That Access Realm-Protected Objects

For external C callouts that do access realm-protected objects, create a PL/SQL package to wrap the external C callout. Due to PL/SQL optimizations, the PL/SQL package wrapper must have a dummy variable defined in the package header. Adding the dummy variable enables Oracle Database Vault to intercept and block execution of external C callout stored procedures. Bear in mind that while this method does secure the execution of the external C callout, it does not provide protection against calls to other external C callouts that may be embedded.

Example D-10 Creating a PL/SQL Wrapper

create or replace package scott.mytestpkg1 as
tmp integer;  /* create a dummy plsql variable */
function test return binary_integer;
end;
/
 
create or replace package body scott.mytestpkg1 as
function test return binary_integer as language C library
c_utils name "test" with context parameters(context,
            return indicator short,
          return int);
end;
/

Step 4: Identifying the External C Callouts Created with Invoker's Rights

Identify the external C callouts that were created with invoker's rights by running the query in Example D-11. This query spool the results to the file external_standalone.lst.

Example D-11 Identifying External C Callouts That Are Wrapped by PL/SQL Packages

spool external_standalone
select u.name OWNER, o.name object, o.type#, o.flags from
  sys.obj$ o, sys.user$ u
where o.owner# = u.user# and
u.name not in ('MDSYS', 'ORDSYS', 'SYS') and
o.type# in (7,8) and o.obj# in (
select d_obj# from dependency$ connect by d_obj#=prior p_obj#
   start with p_obj# in (select obj# from library$ where property = 0))
order by owner, object;
spool off

Step 5: Blocking Execution of External C Callouts

Oracle Database Vault realm and command rules are enforced for external C callouts. However, it can be useful to even block execution on external C callouts. You can accomplish this by following Step 3: Creating a Package to Wrap C Callouts That Access Realm-Protected Objects.

Step 6: Verifying Oracle Database Vault Protection for External C Callouts

Verify Oracle Database Vault protection for external C callouts. Example D-12 shows how you can test Oracle Database Vault security by logging into a tool such as SQL*Plus and attempting to execute an external C callout.

Example D-12 Testing Oracle Database Security for an External C Callout

SQL> CONNECT u1
Enter password: password

SQL> SELECT * FROM SESSION_PRIVS;
 
PRIVILEGE
----------------------------------------
CREATE SESSION
SELECT ANY TABLE
CREATE PROCEDURE
EXECUTE ANY PROCEDURE
 
SQL> SELECT COUNT(*) FROM SCOTT.EMP;

ERROR at line 1:
ORA-01031: insufficient privileges
 
SQL> SELECT TEST FROM DUAL;

TEST
-------------------------------------------------------------------------------
14
 
SQL> SELECT SCOTT.MYPACKAGE1.TEST FROM DUAL;

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.MYPACKAGE1", line 2

Step 7: Securing Invoker's Rights for New External C Callouts

If you are writing new external C callouts, ensure they are wrapped in an invoker's rights PL/SQL package specification. Remember, it is important to include a dummy PL/SQL variable in the package header. Adding the dummy variable enables Oracle Database Vault to intercept and block execution of external C callouts.