Re: Can you trigger an automatic WHERE-clause on any statement on a table?

The suggestions about RLS and views are both in the right ballpark, but full RLS is a bit like overkill for the requirement described.

The optimum strategy is probably:

    Create a context to hold the reference userid for a user

        (e.g. create context security_context ...)

    create a logon trigger to set an attribute of the context

        dbms_session.set_context (security_context, {users ref id});

    create a single view on each table as

        select * from table where col_value = sys_context('security_context','userid');

    Create a post insert trigger on each table to:

        :new.col_name := sys_context('security_context',userid);

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Jacob wrote in message <0y4E5.18745$Ly1.252129_at_news5.giganews.com>...


>Hi all

>

>We are currently in the process of migrating a previous single-user

>application to a multi-user application running on the web using Forms

>Server 6i on Oracle8i 8.1.6 on Windows NT. The multi-user application is

>supposed to be based on user rights specified in a user table, which

>contains information about, which modules of the application, the user is

>allowed to use.

>

>The existing database schema will be changed to include a "user_id" tuple

 in


>each table, which contains user data. This tuple will reference the user

>table's primary key "user_id". This means, that either we have to change a

>lot of SQL statement throughout the program to include "WHERE <user

>data-table>.user_id = users.user_id" (the "users.user_id" is at login

>compared with "SELECT USER FROM DUAL", and if no rows returned, the user is

>denied application login).

>

>Does anyone know, if it is possible to create a trigger or some other

>database mechanism, which you could use on the user data tables, which

>automatically appends the mentioned WHERE-clause, or do we have to re-code

>all the SQL statements in the application?

>

>Any advice on this subject would be greatly appreciated!

>

>Thanks in advance

>Jacob

>

>jacmads_at_hotmail.com

>

>

>

>