Error when create policy after create view. What is the problem? [message #649768] |
Tue, 05 April 2016 13:46 |
|
quytrieu
Messages: 1 Registered: April 2016 Location: Viet Nam
|
Junior Member |
|
|
when i create view TO user NHANVIEN . Select, insert, update is still ok.
CREATE VIEW V_NHANVIEN
AS
SELECT MADA,TENDA,PHONGCHUTRI,TRUONGDA FROM DUAN_1312508_1312474;
GRANT SELECT ON V_NHANVIEN TO ROLE_NHANVIEN;
But i create policy to different user 'TRUONGDA', then user 'NHANVIEN' select, insert, update is ERROR;
CREATE OR REPLACE FUNCTION TEST_TRUONGDA(P_SCHEMA VARCHAR2, P_OBJ VARCHAR2)
RETURN VARCHAR2
AS
USER VARCHAR2(100);
BEGIN
IF (SYS_CONTEXT('USERENV', 'ISDBA') = 'TRUE') THEN
RETURN '';
ELSE
USER:=SYS_CONTEXT('USERENV', 'SESSION_USER');
RETURN 'DUAN IN(SELECT MADA FROM DUAN_1312508_1312474 WHERE TRUONGDA = '||USER||');';
END IF;
END;
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'HQTCSDL_1312474_1312508',
object_name => 'CHITIEU_1312508_1312474',
policy_name => 'p_CHITIEU',
function_schema => 'HQTCSDL_1312474_1312508',
policy_function => 'TEST_TRUONGDA',
statement_types => 'SELECT,UPDATE, INSERT',
update_check => TRUE);
END;
what is the problem?
What is relative between view and policy on a table? or my code have a problem?
thanks everyone saw.
[Updated on: Tue, 05 April 2016 13:52] Report message to a moderator
|
|
|
|
|
Re: Error when create policy after create view. What is the problem? [message #650278 is a reply to message #649768] |
Thu, 21 April 2016 07:31 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Problem is in:
RETURN 'DUAN IN(SELECT MADA FROM DUAN_1312508_1312474 WHERE TRUONGDA = '||USER||');';
It has missing quotes and extra semi-colon. It should be either
RETURN 'DUAN IN(SELECT MADA FROM DUAN_1312508_1312474 WHERE TRUONGDA = '''||USER||''')';
Or better:
RETURN 'DUAN IN(SELECT MADA FROM DUAN_1312508_1312474 WHERE TRUONGDA = SYS_CONTEXT(''USERENV'', ''SESSION_USER''))';
Also USER is name of built-in function, so I'd stay away of naming PL/SQL variable USER. It will not work as expected if you reference it in a SQL statement.
SY.
[Updated on: Thu, 21 April 2016 07:40] Report message to a moderator
|
|
|