Hi,
i've a trouble with Oracle Real Application Security.
i've AML db schema.
CREATE TABLE PROVA (OWNER VARCHAR2(30), ID NUMBER, DESCRIZIONE VARCHAR2(100))
CREATE OR REPLACE PROCEDURE ins_prova
IS
BEGIN
INSERT INTO PROVA (
OWNER,
ID,
descrizione
) VALUES (
XS_SYS_CONTEXT('XS$SESSION','USERNAME'),
1,
'PROVA RAS with procedure'
);
END;
I connect with SYS as SYSDBA:
CREATE ROLE db_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON AML.PROVA TO db_role;
GRANT EXECUTE ON AML.INS_PROVA TO db_role;
exec SYS.XS_PRINCIPAL.CREATE_ROLE(NAME => 'app_role', enabled => TRUE);
GRANT db_role TO app_role;
exec SYS.XS_PRINCIPAL.CREATE_USER(NAME => 'USER1', SCHEMA => 'AML');
exec SYS.XS_PRINCIPAL.SET_PASSWORD('USER1', 'pippo123', XS_PRINCIPAL.XS_SHA512);
exec SYS.XS_PRINCIPAL.GRANT_ROLES('USER1', 'app_role');
exec SYS.XS_PRINCIPAL.GRANT_ROLES('USER1', 'XSCONNECT');
-- ACL
DECLARE
aces XS$ace_list := XS$ace_list();
BEGIN
aces.extend(1);
aces(1) := XS$ace_type(privilege_list => XS$name_list('SELECT', 'INSERT', 'UPDATE', 'DELETE'), principal_name => 'app_role');
sys.xs_acl.create_acl(NAME => 'prova_acl', ace_list => aces);
END;
/
-- Realms
DECLARE
realms XS$realm_constraint_list := XS$realm_constraint_list();
cols XS$column_constraint_list := XS$column_constraint_list();
BEGIN
realms.extend(1);
realms(1) := XS$realm_constraint_type(REALM => '1=1', acl_list => XS$name_list('prova_acl'));
sys.xs_data_security.create_policy( NAME => 'prova_ds',
realm_constraint_list => realms,
column_constraint_list => cols
);
END;
/
-- Apply DS
BEGIN
sys.xs_data_security.apply_object_policy(
POLICY => 'prova_ds',
SCHEMA => 'AML',
OBJECT => 'PROVA');
END;
/
BEGIN
IF (sys.xs_diag.validate_workspace()) THEN
dbms_output.put_line('All configurations are correct.');
ELSE
dbms_output.put_line('Some configurations are incorrect.');
END IF;
END;
/
I connect with USER1/pippo123
insert into prova values (XS_SYS_CONTEXT('XS$SESSION','USERNAME'), 2, 'Prova RAS no procedure');
1 row inserted.
Then I try with a call to a procedure:
exec ins_prova;
The result is:
Error starting at line : 7 in command -
BEGIN ins_prova; END;
Error report -
ORA-28115: policy with check option violation
ORA-06512: at "AML.INS_PROVA", line 4
ORA-06512: at line 1
28115. 00000 - "policy with check option violation"
*Cause: Policy predicate was evaluated to FALSE with the updated values.
*Action:
Why did the call with the procedure fail and the explicit insert no?
Regards