Global Roles Are Not Correctly Taken Into Account By Database Vault (Doc ID 1229004.1)

Last updated on APRIL 28, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.5 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

An EUS user being granted a global role is not able to create a table in a schema protected by a Database Vault realm although the global role was added in the authorizations list.



SQL> conn dvo/DVTEST_1
Connected

SQL> begin
dvsys dbms_macadm ADD_AUTH_TO_REALM( realm_name => 'Protect the HR schema',grantee => 'TESTROLE' ,auth_options => 0);
commit;
end;
/

SQL> conn testroleoid/DVTEST_1
Connected
SQL> show user
USER is "DBA_SHARED"


SQL> show user
USER is "DBA_SHARED"
SQL> SELECT sys_context('userenv','external_name') FROM dual;

SYS_CONTEXT('USERENV','EXTERNAL_NAME')
------------------------------------------------------------------------------
--
cn=testroleoid,cn=Users,dc=oracle,dc=com

SQL> select * from session_roles;

ROLE
------------------------------
DV_PUBLIC
GLOBAL_ROLE <<<<---- Here is the Global Role
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA
RESOURCE

SQL> CREATE TABLE HR.TEST(X NUMBER) TABLESPACE USERS;
CREATE TABLE HR.TEST(X NUMBER) TABLESPACE USERS
*
ERROR at line 1:
ORA-604: error occurred at recursive SQL level 1
ORA-47401: Realm violation for create table on HR.TEST
ORA-6512: at "DVSYS AUTHORIZE_EVENT", line 55
ORA-6512: at line 31




Even the dbms_macadm user_has_role_varchar is not able to determine correctly whether a global role was granted to an user or not:


SQL> conn testeroleoid/DVTEST_1
Connected

SQL> select DVSYS DBMS_MACUTL USER_HAS_ROLE_VARCHAR('GLOBAL_ROLE')
from dual;

DVSYS DBMS_MACUTL USER_HAS_ROLE_VARCHAR('GLOBAL_ROLE')
------------------------------------------------------------------------------
--
N



The database roles are working OK:



SQL> conn / as sysdba
Connected
SQL> create role testrole;

Role created

SQL> conn dvamgr/DVTEST_1
Connected
SQL> create user testuser identified by testuser;

User created


SQL> conn / as sysdba
Connected
SQL> grant create session, dba to testuser;

Grant succeeded

SQL> conn dvo/DVTEST_1
Connected

SQL> begin
dvsys dbms_macadm ADD_AUTH_TO_REALM( realm_name => 'Protect the HR schema',grantee => 'TESTROLE' ,auth_options => 0);
commit;
end;
/
2 3 4 5
PL/SQL procedure successfully completed


SQL> conn / as sysdba
Connected
SQL> grant testrole to testuser;
Grant succeeded

SQL> conn testuser/testuser
Connected

SQL>
SQL> create table hr.teste(col1 number) tablespace users;
Table created



Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms