Hello,
I'm working with Oracle 12.2.0.1.0 on Linux, and trying to créate a command rule that check if the user has granted a role to allow do a SELECT operation, is not working.
Here is my steps.
1. Create a RULE set.
set echo on
exec DBMS_MACADM.DELETE_RULE_SET(rule_set_name => 'HAS_APP_ROLE');
BEGIN
DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'HAS_APP_ROLE',
description => 'Filtro para ver si tienes una ROLE de control',
enabled => DBMS_MACUTL.G_YES,
eval_options => DBMS_MACUTL.G_RULESET_EVAL_ALL,
audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL + DBMS_MACUTL.G_RULESET_AUDIT_SUCCESS,
fail_options => '',
fail_message => 'ERROR ACCEDIENDO AL real HR por Rule Set De ROLE...',
fail_code => 20101,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
is_static => FALSE,
handler => '');
END;
/
2. Create a rule
exec DBMS_MACADM.DELETE_RULE('RULE2')
begin
DBMS_MACADM.CREATE_RULE(
rule_name => 'RULE2',
rule_expr => 'DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR(''APP_ROLE'') = ''Y''',
scope => DBMS_MACUTL.G_SCOPE_LOCAL);
END;
/
3. Asign the rule to the rule set.
BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'HAS_APP_ROLE',
rule_name => 'RULE2');
END;
/
4. Create the command RULE.
exec DBMS_MACADM.DELETE_COMMAND_RULE('SELECT','OE','%');
BEGIN
DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'SELECT',
object_owner => 'OE',
object_name => '%',
rule_set_name => 'HAS_APP_ROLE',
enabled => DBMS_MACUTL.G_YES);
END;
/
Then I connect with the user ahunold
[oracle@server1 scripts]$ sqlplus ahunold/oracle
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 28 18:18:19 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sat Jul 28 2018 18:07:33 +02:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------------------------------------------------------
CONNECT
DBA
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
CAPTURE_ADMIN
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
OPTIMIZER_PROCESSING_RATE
EM_EXPRESS_BASIC
EM_EXPRESS_ALL
SCHEDULER_ADMIN
HS_ADMIN_SELECT_ROLE
HS_ADMIN_EXECUTE_ROLE
XDBADMIN
XDB_SET_INVOKER
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
OLAP_XS_ADMIN
OLAP_DBA
OE_DBA
APP_ROLE ---> The role used to control
25 rows selected.
SQL>
And I I try Access to a HR table.
SQL> select * from oe.emp;
select * from oe.emp
*
ERROR at line 1:
ORA-47306: 20101: ERROR ACCEDIENDO AL real HR por Rule Set De ROLE...
If I delete the command Rule, the user has Access to the table because has the DBA role.
Any know why is not working ?
Many thanks
Arturo