F Oracle Database Vault Security Guidelines

F.3.7.1 Securing EXECUTE ANY PROCEDURE by 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. Note also that revoking the EXECUTE ANY PROCEDURE from users further secures the database by limiting access to SYS-owned packages.

F.3.7.2 The Difference Between Invoker's and Definer's Rights in Java Stored Procedures

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.

F.3.7.3 Securing Java Stored Procedures

Oracle recommends that you analyze your Java stored procedures when using Oracle Database Vault to maximize security. You can do so by following these steps:

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

• Step 2: Finding Java Stored Procedures That Access Realm-Protected Objects

• Step 3: Creating a Package to Wrap Procedures That Access Realm-Protected Objects

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

• Step 5: Blocking Execution of Java Stored Procedures

• Step 6: Verifying Oracle Database Vault Protection for Java Stored Procedures

• Step 7: Securing Invoker's Rights for New Java Stored Procedures

F.3.7.4 Step 1: Identifying 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 F-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.

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

F.3.7.5 Step 2: Finding 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 Table C-3, "Oracle Database Vault Database Views".

F.3.7.6 Step 3: Creating a Package to Wrap Procedures That Access 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 F-6 shows the PL/SQL package mypackage being created to wrap the Java class emp_count.

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

F.3.7.7 Step 4: Identifying 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 F-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.

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

F.3.7.8 Step 5: Blocking 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: Creating a Package to Wrap Procedures That Access Realm-Protected Objects.

F.3.7.9 Step 6: Verifying Oracle Database Vault Protection for Java Stored Procedures

Verify that Oracle Database Vault is protecting your Java stored procedures. Example F-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.

SQL> connect u1
Enter password: password
Connected.
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;
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;
select scott.mypackage.empcount from dual
       *
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.MYPACKAGE", line 2

F.3.7.10 Step 7: Securing 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.

F.3.8.1 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.

F.3.8.2 The Difference Between Invoker's and Definer's Rights in External C Callouts

For definer rights external C callouts, 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 invoker rights external C callouts, 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.

F.3.8.3 Securing External C Callouts

Oracle recommends that you analyze your external C callouts to maximize security when using Oracle Database Vault. You can do so by following these steps:

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

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

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

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

• Step 5: Blocking Execution of Java Stored Procedures

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

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

F.3.8.4 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 F-9. This query spools the results to the file external_wrap.lst.

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

F.3.8.5 Step 2: Finding the External C 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 Table C-3, "Oracle Database Vault Database Views".

F.3.8.6 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.

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;
/

F.3.8.7 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 F-11. This query spool the results to the file external_standalone.lst.

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

F.3.8.8 Step 5: Blocking Execution of Java Stored Procedures

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.

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

Verify Oracle Database Vault protection for external C callouts. Example F-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.

SQL> connect u1
Enter password: password
Connected.
SQL>
SQL> select * from session_privs;
 
PRIVILEGE
----------------------------------------
CREATE SESSION
SELECT ANY TABLE
CREATE PROCEDURE
EXECUTE ANY PROCEDURE
 
SQL>
SQL> select count(*) from scott.emp;
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-01031: insufficient privileges
 
SQL> select test from dual;
TEST
-------------------------------------------------------------------------------
14
 
SQL>
SQL> select scott.mypackage1.test from dual;
select scott.mypackage1.test from dual
       *
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.MYPACKAGE1", line 2

F.3.8.10 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.