Re: Ownership issues, would Trusted Oracle help?

In article <49f2ah$q11_at_ratatosk.uio.no>

           torfridl_at_ulrik.uio.no "Torfrid Leek" writes:                                                 

: OR we could use views, and have an extra column in each table, but again each
: administrative entity would have to have its own set of views. This is a lot of
: overhead. The frontend tool is Powerbuilder, which doesn't exactly improve the
: functionality...

Create a package containing a SQL-callable function that translates the userid into a 'group id'. (Use a package variable so that the code only ever does one call to the database).

Create ONE view for each table along the lines of:

   select .....
   from table_name
   where group_id = my_package.my_function(userid)    with check option;

create a pre-insert trigger on each table which sets the group_id column to my_package.my_function(userid).

Grant execute on the package to all the relevant users (via roles). Grant select on the views to all the relevant users (via roles) Create public synonyms (or lots of private ones) on the views.

If Powerbuilder can update a single table column, you should then be okay. You get the benefit of a single view whose content varies with user; and you don't run the risk of the performance hit of the more traditional approach of declaring the view to include a subquery to a user/group cross reference table.  

The performance overhead is the CPU cost per row of calling the PL/SQL function - which can be quite noticeable if you have to select very large numbers of rows.

Hope this helps: I set it up once for someone about a year ago and don't have the exact details to hand.

-- 
Jonathan Lewis