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