How to block row to be selected in Oracle 9i?
We have table in database, using Oracle 9i(9.2), we must made policy for SELECT query, some group of users can access for exact row and some cannot, standardly it solved by VIEWs:
- making some VIEW table
- changing table name of origin
But there is a problem, we can't change table name, it bureaucracy problems.
Finally, is it some mechanism or triggers(there are no triggers for SELECT but just example) to control row access?
Sorry, if question is dummy,I don't have muc开发者_如何学Ch experience with DBs.
One option would be to create a Virtual Private Database policy for the table in question.
From a presentation I did a couple years ago
Set up the environment
-- The SEMOP user has been granted the following privileges
-- CREATE SESSION
-- CREATE PROCEDURE
-- CREATE ANY CONTEXT
-- UNLIMITED TABLESPACE
-- CREATE TABLE
-- CREATE SEQUENCE
-- EXECUTE ON DBMS_RLS
-- EXECUTE ON DBMS_FGA
-- SELECT ON DBA_FGA_AUDIT_TRAIL
conn oow2009/oow2009;
create table patient (
patient_id number primary key,
patient_first_name varchar2(30),
patient_last_name varchar2(30),
vip_flag char(1)
);
create table service (
service_id number primary key,
service_name varchar2(30)
);
create table doctor (
doctor_id number primary key,
doctor_first_name varchar2(30),
doctor_last_name varchar2(30),
service_id number references service( service_id )
);
create table admission (
admission_id number primary key,
patient_id number references patient( patient_id ),
service_id number references service( service_id ),
primary_doctor_id number references doctor( doctor_id ),
admission_date date,
discharge_date date
);
begin
insert into patient( patient_id, patient_first_name, patient_last_name, vip_flag )
values( 1, 'Barack', 'Obama', 'Y' );
insert into patient( patient_id, patient_first_name, patient_last_name, vip_flag )
values( 2, 'Larry', 'Ellison', 'Y' );
insert into patient( patient_id, patient_first_name, patient_last_name, vip_flag )
values( 3, 'Justin', 'Cave', 'N' );
insert into patient( patient_id, patient_first_name, patient_last_name, vip_flag )
values( 4, 'Jane', 'Doe', 'N' );
insert into service( service_id, service_name )
values( 11, 'Obstetrics' );
insert into service( service_id, service_name )
values( 12, 'Cardiac' );
insert into service( service_id, service_name )
values( 13, 'Opthamology' );
insert into service( service_id, service_name )
values( 14, 'Emergency' );
insert into doctor( doctor_id, doctor_first_name, doctor_last_name, service_id )
values( 21, 'William', 'Mayo', 14 );
insert into doctor( doctor_id, doctor_first_name, doctor_last_name, service_id )
values( 22, 'George', 'Minot', 13 );
insert into doctor( doctor_id, doctor_first_name, doctor_last_name, service_id )
values( 23, 'Richard', 'Morton', 12 );
insert into doctor( doctor_id, doctor_first_name, doctor_last_name, service_id )
values( 24, 'Carl', 'Jung', 11 );
insert into doctor( doctor_id, doctor_first_name, doctor_last_name, service_id )
values( 25, 'Joseph', 'Lister', 12 );
-- Obama has been admitted 3 times, twice for heart tests and once for an eye test
insert into admission( admission_id, patient_id, service_id, primary_doctor_id, admission_date, discharge_date )
values( 31, 1, 12, 23, date '2009-04-01', date '2009-04-01' );
insert into admission( admission_id, patient_id, service_id, primary_doctor_id, admission_date, discharge_date )
values( 32, 1, 12, 25, date '2009-05-03', date '2009-05-03' );
insert into admission( admission_id, patient_id, service_id, primary_doctor_id, admission_date, discharge_date )
values( 33, 1, 13, 22, date '2009-05-03', date '2009-05-03' );
-- Ellison was admitted to the emergency department following a yachting accident
insert into admission( admission_id, patient_id, service_id, primary_doctor_id, admission_date, discharge_date )
values( 34, 2, 14, 21, date '2009-07-01', date '2009-07-03' );
-- Justin was admitted earlier today for an eye exam and hasn't been discharged
insert into admission( admission_id, patient_id, service_id, primary_doctor_id, admission_date, discharge_date )
values( 35, 3, 13, 22, date '2009-09-24', null );
-- Jane was admitted to obstatrics
insert into admission( admission_id, patient_id, service_id, primary_doctor_id, admission_date, discharge_date )
values( 36, 4, 11, 24, date '2009-08-01', date '2009-08-10' );
end;
/
Create a secure context
create or replace context oow2009_ctx
using pkg_secure_context;
create or replace package pkg_secure_context
as
procedure login( p_doctor_first_name IN doctor.doctor_first_name%TYPE,
p_doctor_last_name IN doctor.doctor_last_name%TYPE );
procedure logout;
end;
/
create or replace package body pkg_secure_context
as
procedure login( p_doctor_first_name IN doctor.doctor_first_name%TYPE,
p_doctor_last_name IN doctor.doctor_last_name%TYPE )
as
l_doctor_id doctor.doctor_id%TYPE;
begin
SELECT doctor_id
INTO l_doctor_id
FROM doctor
WHERE doctor_first_name = p_doctor_first_name
AND doctor_last_name = p_doctor_last_name;
dbms_session.set_context( 'oow2009_CTX',
'DOCTOR_ID',
to_char(l_doctor_id) );
end login;
procedure logout
as
begin
dbms_session.clear_context( 'oow2009_CTX' );
end logout;
end;
/
Create a policy function
create or replace function policy_view_own_patients( schema_p IN VARCHAR2,
table_p IN VARCHAR2 )
return VARCHAR2
is
begin
return 'patient_id IN
(SELECT patient_id
FROM admission
WHERE primary_doctor_id =
SYS_CONTEXT( ''oow2009_CTX'', ''DOCTOR_ID'' ))';
end;
/
Create the row-level security policy
begin
dbms_rls.add_policy (
object_schema => 'oow2009',
object_name => 'PATIENT',
policy_name => 'VIEW_OWN_PATIENTS',
policy_function => 'POLICY_VIEW_OWN_PATIENTS'
);
end;
/
This query returns 0 rows now
select patient_first_name || ' ' || patient_last_name patient_name,
service_name,
doctor_last_name,
admission_date,
discharge_date
from patient p,
doctor d,
service s,
admission a
where p.patient_id = a.patient_id
and a.service_id = s.service_id
and a.primary_doctor_id = d.doctor_id
order by patient_last_name;
If you log in as William Mayo, however
exec pkg_secure_context.login( 'William', 'Mayo' );
the same query now returns rows but only those that rows for Mayo's patients. The other rows continue to be filtered out
select patient_first_name || ' ' || patient_last_name patient_name,
service_name,
doctor_last_name,
admission_date,
discharge_date
from patient p,
doctor d,
service s,
admission a
where p.patient_id = a.patient_id
and a.service_id = s.service_id
and a.primary_doctor_id = d.doctor_id
order by patient_last_name;
Your policy function could drive off the currently logged in user or any other piece of information if you didn't want or need the secure context.
精彩评论