Hi.
I'm trying to implement fine-grained access control for
hierarchical table.
I have to enable access for ONE branch only of that table ( code 30000
at top level ).
In order to do it I created 2 functions :
- First function ( FUNCTION_A ) accepts subject code,
makes a SELECT ... CONNECT BY PRIOR
from SUBJECT_STUDY table and returns 1 if it is withit correct
branch.
- Second function implements security policy and returns string
"function_a ( subject_code ) = 1".
It is connected to the same SUBJECT_STUDY table.
The problem is that during execution of
"SELECT * FROM SUBJECT_STUDY" Oracle performs security policy function,
calls FUNCTION_A that contains SELECT from the same table and starts
recursive LOOP.
I tried playing with INVOKER RIGHTS feature, but to no avail.
Any ideas??
TIA. Michael.
For any really interested here are definitions.
You need 2 users:
1 - execute all definitions ( I used OPS$ORACLE )
2 - Run SELECT * FROM SUBJECT_STUDY ( M2225283 in my example ).
- Create table of subjects for course
--
CREATE TABLE study_subject (
course NUMBER(5) NOT NULL,
cdfather NUMBER(6) NOT NULL,
cdsubject NUMBER(6) NOT NULL,
cdmark NUMBER(4),
CONSTRAINT study_subject_pk
PRIMARY KEY ( course, cdfather, cdsubject ) );
GRANT SELECT ON study_subject TO PUBLIC;
--
- Insert some data
--
INSERT INTO study_subject (course, cdfather, cdsubject, cdmark )
VALUES (7199,0,30000,'5');
INSERT INTO study_subject (course, cdfather, cdsubject, cdmark )
VALUES (7199,30000, 1,'5');
INSERT INTO study_subject (course, cdfather, cdsubject, cdmark )
VALUES (7199,30000, 21,'5');
INSERT INTO study_subject (course, cdfather, cdsubject, cdmark )
VALUES (7199,30000, 24,'5');
INSERT INTO study_subject (course, cdfather, cdsubject, cdmark )
VALUES (7199,30000, 26,'5');
COMMIT;
--
- Check if subject within correct branch
--
FUNCTION f_in_30000 (course_P IN NUMBER, cdsubject_p IN NUMBER)
RETURN NUMBER IS
t_value NUMBER := 0 ;
status NUMBER;
BEGIN
t_value := 0;
SELECT DISTINCT 1
INTO status
FROM study_subject
WHERE course = course_p AND
cdsubject = cdsubject_p
START WITH
course = course_p AND
cdfather = 30000
CONNECT BY PRIOR cdsubject = cdfather AND course = course_p;
- אם פעילות תחת ענף תחקרי פעיולת אתחל פרמטר בענף באמת
IF status = 1 THEN
t_value := 1;
END IF;
RETURN t_value;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
--
GRANT EXECUTE ON f_in_30000 TO PUBLIC;
--
- Security policy function
--
CREATE OR REPLACE FUNCTION instructor_sec_fnc ( p_schema IN VARCHAR2,
p_object IN VARCHAR2 )
RETURN VARCHAR2 IS
user_id VARCHAR2(40) := NULL;
mi NUMBER := 0;
condition_string VARCHAR2(200):= NULL;
BEGIN
user_id := USER;
IF user_id = 'M2225283' THEN
condition_string := 'course = 7199 ';
IF p_object = 'STUDY_SUBJECT' THEN
condition_string := condition_string ||
' AND OPS$ORACLE.f_in_30000(course,cdsubject) = 1 ';
END IF;
ELSE
condition_string := NULL;
END IF;
RETURN condition_string ;
EXCEPTION
WHEN OTHERS THEN
return '1=2';
END;
--
GRANT EXECUTE ON instructor_sec_fnc TO PUBLIC;
--
- Assign security policy function to table
BEGIN
DBMS_RLS.ADD_POLICY ( object_schema => 'OPS$ORACLE',
object_name => 'STUDY_SUBJECT',
policy_name => 'STUDY_POLICY',
function_schema => 'OPS$ORACLE',
policy_function => 'INSTRUCTOR_SEC_FNC',
statement_types => 'select' );
END;
/
- Connect as user M2225283 and SELECT * FROM STUDY_SUBJECT;
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Dec 15 1999 - 14:43:51 CST
