CODEWITHSUNDEEP
sqloracle-database
XX_User
XX_User

Reputation: 1

VPD PROBLEM: ORA-28113: policy predicate has error

write a policy function as scott to implement the following policy: Users can access only the data of their own, but sysdba should be able to access any data without restrictions, then attach the policy to the table.?(THE table name is RATING, and one column name is NAME)

SCOTT > CREATE FUNCTION POLICY (
p_schema IN VARCHAR2,
p_object IN VARCHAR2
)
RETURN VARCHAR2
AS
BEGIN
if(SYS_CONTEXT('userenv', 'SESSION_USER') = 'SYS' ) THEN RETURN '';
else 
RETURN 'NAME = SYS_CONTEXT ("USERENV", "SESSION_USER")';
END IF;


SYS AS SYSDBA> EXECUTE DBMS_RLS.ADD_POLICY (object_schema=>'SCOTT', OBJECT_NAME=>'RATING', POLICY_NAME=>'FIRST_POLICY', FUNCTION_SCHEMA=>'SCOTT', POLICY_FUNCTION=>'POLICY', STATEMENT_TYPES=>'SELECT', UPDATE_CHECK=>TRUE);

PL/SQL procedure successfully completed.

ERROR: SCOTT > SELECT * FROM RATING;

SELECT * FROM RATING * ERROR at line 1: ORA-28113: policy predicate has error

Upvotes: 0

Views: 608

Answers (1)

pmdba
pmdba

Reputation: 7043

I see three things:

  1. there is no point in checking for SESSION_USER='SYS', as SYS is exempt from all VPD policies by definition; this will never be true.
  2. double-quotes identify labels, not literal input values. Change your double-quotes to single quotes.
  3. You didn't properly close out your function. Make sure you have an END; statement.

Try this:

CREATE FUNCTION POLICY (
  p_schema IN VARCHAR2,
  p_object IN VARCHAR2
)
RETURN VARCHAR2
AS
BEGIN
  RETURN 'NAME = SYS_CONTEXT (''USERENV'', ''SESSION_USER'')';
END;

Upvotes: 2

Related Questions