CODEWITHSUNDEEP
sqldatabaseoracle-databasevpd
user20137254
user20137254

Reputation: 1

Get wrong result when using SYS_CONTEXT('SYS_SESSION_ROLES', 'DBA') in function of Oracle VPD

I am using Oracle 19 and Navicat.

When I execute this, it returns TRUE.

SELECT SYS_CONTEXT('SYS_SESSION_ROLES', 'DBA') FROM DUAL;

However, when I implement a policy to a table, using a function to control permission, in which SYS_CONTEXT('SYS_SESSION_ROLES', 'DBA') returns FALSE.

But, when I try to use SYS_CONTEXT( 'USERENV', 'SESSION_USER' ), it returns the same user in SQL and function.

Here only the last SYS_CONTEXT(''USERENV'', ''SESSION_USER'' ) works well.

CREATE OR REPLACE
FUNCTION show_own_salary(v_schema IN VARCHAR2, v_obj IN VARCHAR2)
RETURN VARCHAR2 AS condition VARCHAR2 (200);
BEGIN
IF SYS_CONTEXT('SYS_SESSION_ROLES', 'DBA') = 'TRUE' THEN
        RETURN '';
END IF;
IF SYS_CONTEXT( 'SYS_SESSION_ROLES', 'ALL_ACCOUNTANT' ) = 'TRUE' THEN
    RETURN '';
END IF;
RETURN 'SID = (SYS_CONTEXT(''USERENV'', ''SESSION_USER'' ))';
END show_own_salary;

I executed SELECT * FROM session_roles and it shows below: roles

USER_SYS_PRIVS USER_ROLE_PRIVS

Upvotes: 0

Views: 258

Answers (0)

Related Questions