Re: [Q] Conditional Grant ?

Lin FuXiong wrote:
>
> 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
Good evening,

Create a view :

create view tbl_user1 as select * from tbl where id='1' with check option constraint tbl_user1_ck ;

Grant select, insert, update, delete on tb1_user1 ;

create synonym user1.tbl for tbl_user1 ;

Probably the view will be more complicated but avoid join into. ( The view cannot be updated if there is a join in some Oracle Version).

Eventually use IN or EXISTS Clause. Be carefull for performance trouble.

This can be updated.
create view tbl_user1 as select * from tbl where id IN ( select id from user_can_access_the_id username = user ) ....

This cannot in all Oracle Version.
 create view tbl_user1 as select tbl.* , user_can_access_the_id ucai where ucai.username = user and tbl.id = uaci.id .....

Regards Francois. Received on Sat Nov 09 1996 - 00:00:00 CET