My Oracle Support Banner

DATABASE VAULT DOES NOT PROTECT PUBLIC SYNONYMS (Doc ID 2659227.1)

Last updated on APRIL 23, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

A database vault command rule, configured to protect against the GRANT statements on all the tables owned by an user, can be bypassed if a public synonym is created for a table. The grant is still allowed even when a command rule is specifically created to secure all the objects owned by PUBLIC.

Due to this Cannot protect the tables and in the same time make use of the public synonyms for the tables. The following test case shows how this behaviour is seen

 


conn dvmanager/xyz
create user test1 identified by xyz;
grant connect to test1;
create user test2 identified by xyz;
grant connect to test2;
create user test3 identified by xyz;
grant connect to test3;
conn / as sysdba
grant resource, create table to test1;
conn test1/xyz
create table tab1(col1 number);
insert into tab1 values (1);
commit;
create table tab2 as select * from tab1;
grant select on tab1 to test2 with grant option;
grant select on tab2 to test2 with grant option;
grant select on tab1 to test3 with grant option;
grant select on tab2 to test3 with grant option;

SQL> conn dvowner/xyz
Connected.
SQL>
SQL>
SQL> BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE(
rule_name => 'RULE_BLOCK_GRANT',
rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''TEST2'''
);
END;
/

PL/SQL procedure successfully completed.

SQL> BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'RULESET_BLOCK_GRANT_STMT',
description => 'Rule Set enabled for grant statements',
enabled => DVSYS.DBMS_MACUTL.G_YES,
eval_options => DBMS_MACUTL.G_RULESET_EVAL_ALL, -- all rules must be
true,
audit_options => DBMS_MACUTL.G_RULESET_AUDIT_OFF, -- no audit
fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,
fail_message => '',
fail_code => NULL,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
handler => NULL
);
END;
/

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'RULESET_BLOCK_GRANT_STMT',
rule_name => 'RULE_BLOCK_GRANT'
);
END;
/

PL/SQL procedure successfully completed.

SQL> BEGIN
DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'GRANT',
rule_set_name => 'RULESET_BLOCK_GRANT_STMT',
object_owner => 'TEST1',
object_name => '%',
enabled => DVSYS.DBMS_MACUTL.G_YES
);
END;
/

PL/SQL procedure successfully completed.

SQL>
SQL> conn test3/xyz
Connected.

--as expected the table is protected when its name is prefixed with the owner
SQL> grant select on test1.tab1 to scott;
grant select on test1.tab1 to scott
*
ERROR at line 1:
ORA-47400: Command Rule violation for GRANT on TEST1.TAB1

SQL> conn / as sysdba
Connected.
SQL> create public synonym tab1 for test1.tab1;

Synonym created.

SQL> create public synonym tab2 for test1.tab2;

Synonym created.

SQL> select owner, object_name, object_type from dba_objects where
object_name in ('TAB1','TAB2');

OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------- -------------------
PUBLIC TAB2 SYNONYM
PUBLIC TAB1 SYNONYM
TEST1 TAB1 TABLE
TEST1 TAB2 TABLE

SQL>
SQL> conn test3/xyz
Connected.
SQL> grant select on tab1 to scott;

Grant succeeded.

SQL> conn scott/xyz
Connected.
SQL> select * from tab1;

COL1
----------
1

SQL> conn dvowner/xyz
Connected.
SQL> BEGIN
DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'GRANT',
rule_set_name => 'RULESET_BLOCK_GRANT_STMT',
object_owner => 'PUBLIC',
object_name => '%',
enabled => DVSYS.DBMS_MACUTL.G_YES
);
END;
/

PL/SQL procedure successfully completed.

SQL> select * from dvsys.DBA_DV_COMMAND_RULE;SQL>

COMMAND RULE_SET_NAME OBJECT_OWNER
OBJECT_NAME ENABLED
PRIVILEGE_SCOPE
------------------------------ ------------------------------
------------------------------ ------------------------------
------------------------------ ---------------
GRANT RULESET_BLOCK_GRANT_STMT TEST1
% Y
GRANT RULESET_BLOCK_GRANT_STMT PUBLIC
% Y

select * from dvsys.DBA_DV_RULE_SET_RULE;
RULE_SET_NAME RULE_NAME
RULE_EXPR ENABLED
RULE_ORDER
------------------------------
--------------------------------------------------
------------------------------------------------------------ --------
----------
RULESET_BLOCK_GRANT_STMT RULE_BLOCK_GRANT
SYS_CONTEXT('USERENV','SESSION_USER') = 'TEST2' Y
1

SQL> conn test3/xyz
Connected.

--the grant succeeds
SQL> grant select on tab1 to scott;

Grant succeeded.

SQL>

SQL> conn dvowner/xyz
Connected.
SQL> BEGIN
DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'GRANT',
rule_set_name => 'RULESET_BLOCK_GRANT_STMT',
object_owner => 'PUBLIC',
object_name => 'TAB1',
enabled => DVSYS.DBMS_MACUTL.G_YES
);
END;
/

PL/SQL procedure successfully completed.

SQL>select * from dvsys.DBA_DV_COMMAND_RULE;

COMMAND RULE_SET_NAME OBJECT_OWNER
OBJECT_NAME ENABLED
PRIVILEGE_SCOPE
------------------------------ ------------------------------
------------------------------ ------------------------------
------------------------------ ---------------
GRANT RULESET_BLOCK_GRANT_STMT TEST1
% Y
GRANT RULESET_BLOCK_GRANT_STMT PUBLIC
% Y
GRANT RULESET_BLOCK_GRANT_STMT PUBLIC
TAB1 Y

SQL> conn test3/xyz
Connected.

--the grant succeeds
SQL> grant select on tab1 to scott;

Grant succeeded.

SQL>
SQL> conn scott/xyz
Connected.
SQL> select * from tab1;

COL1
----------
1

SQL>

 

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.