Re: [Q] Conditional Grant ?

In article <55umhc$83j_at_netnews.nctu.edu.tw>, Lin FuXiong <gis81566_at_cis.nctu.edu.tw> writes
>Dear netters:
>
> Is it possible for ORACLE to do "run time grant" ( row level grant ?) ?
>
> For example:
> table : tb1
> related column: id
> I want let "user1" manipulate those data in tb1 with tb1.id='1'.
>
> I guess the statement would look like:
>
> grant select, insert, update, delete on tb1 to user1 WHERE id='1';
>
> How can I do it ? ( I don't have ORACLE SQL SYNTAX REFERENCE at hand.)
> Any response to answer the question is appreciated.
>---
>Fu-Xiong Lin
>

We implemented something like this.

In our cases, each user was a meber of one or more groups, and the group determined which rows they could access. We added a group column to a table (this would be equivalent to tb1.id in your cass). We create a lookup table of userids and group names.

create table target_table

( tt_name varchar...,
  tt_desc varchar,,,,
  TT_owner varchar...);

create table user_groups

  ug_user_id
  ug_group_id
  ug_primary_ind)

We then created the following view

create view target_view
as select * (or all columns except TT_owner)

   from target_table
   where tt_owner in (select ug_group_id

                        from user_groups
                        where ug_user_id in USER)

This uses the pseudocolumn USER to restrict the view to only those rows owned by a group to which the uer belongs. If the rows are owned by individual users then the where clause is even simpler "where tt_ouner=USER". The view is updateable because it uses a subquery rather than a join.

In order to ensure that newly created records have a valid group attached you need a trigger

(excuse the syntax)
create or replace trigger tt_pre_insert
on target_table
before insert
for each row

begin
 grp varchar;

 select ug_group_id into grp where ug_user_id=USER  and ug_primary_ind is not null;

 :new.tt_owner := grp;

end;

This inserts the user's primary group into the table.

We added this scheme to an existing application and it seriously screwed up some query plans, but in general there was no noticable performance hit. If you start a new application using this you shouldn't have any problems.

-- 
Jim Smith