Re: A trigger for queries

"-" <name_at_company.com> wrote:

>I`ve got a problem I can´t solve. What I want is to handle all the queries
>(SELECT) before ORACLE prccesses them. The purpose is to control the access
>to data but in a "smart" way (that´s, with really complex rules). So I´ve
>dessign a program that analyzes the code of the SELECT SQL sentence and
>checks if the user has access to the data requested. I´d thought to run it
>just before would proccess the sentence.
>
>I´ve tried with TRIGGERS but, unfortunately, i´ve could see they only work
>with INSERT, DELETE and UPDATE sentences, not with SELECT.
>
>i really have no idea what to do. If anyone can help me I´ll thank very
>much.
>

How about using roles and views? When used in conjunction with properly set indexes it would be much faster

Given table PAYROLL

EMPLOYEE	      VARCHAR2(30)
DEPT_ID           NUMBER
SALARY            NUMBER(15,2)
MANGER          NUMBER
OVERTIME       NUMBER(4,2)

set up a couple of views

create view emp_mgr as
select * from payroll where dept_id in
  (select dept_id from where employee_id = payroll.manager)

create view limited as
  select employee, salary from payroll

     where salary < 1000.00

then grant access to the views to specify roles and assign the roles to the correct users. The complexity of the where clause(s) is only limited by the programmer's ability and the efficiency should be much faster than any programatic method. Received on Sun Feb 08 1998 - 00:00:00 CST