1

If was ok when i create a function and add a policy in Oracle. Error happen when i want to select a table (OBJECT_NAME). It seems like error happen in the select into variable, but i dont know how to fix it (I make sure that select into 1 variable only and the same type as variable)

CREATE OR REPLACE FUNCTION TC6_NHANVIEN(P_SCHEMA VARCHAR2, P_OBJ VARCHAR2)
RETURN VARCHAR2
AS
    USERCHECK VARCHAR2(100);
    VAITROCHECK VARCHAR(100);
BEGIN
    USERCHECK := SYS_CONTEXT('USERENV', 'SESSION_USER');
    VAITROCHECK :='';
    IF (USER LIKE 'NV%') THEN --i want to check a variable. Error maybe right here
        SELECT VAITRO INTO VAITROCHECK
        FROM SYSTEM.NHANVIEN
        WHERE MANV=USER;
    END IF;
    
    IF (USERCHECK LIKE 'NV%'AND VAITROCHECK!='THANHTRA') THEN
        RETURN 'MANV = '''||USERCHECK||'''';
    ELSE
         RETURN '1=1';
    END IF;
END;

BEGIN
    dbms_rls.add_policy(
        OBJECT_SCHEMA => 'SYSTEM',
        OBJECT_NAME => 'NHANVIEN',
        POLICY_NAME => 'PC_2',
        POLICY_FUNCTION => 'TC6_NHANVIEN',
        STATEMENT_TYPES => 'SELECT,UPDATE',
        UPDATE_CHECK => TRUE
    );
END;

grant select on SYSTEM.NHANVIEN to NV001;
select * from SYSTEM.NHANVIEN --LOG IN AS NV001

0 Answers0