Multiple table qualifiers and security

  I am in the process of designing an Oracle database interface   and would appreciate assistance regarding some requirements...

1. One of the requirements is, that we want to implement multiple tables with the same name, but with different prefixes (e.g foo.tablename, bar.tablename).

     Each user of the 3GL application accessing the database should
     be able to select the prefix he wants to use. This could be
     rather neatly implemented by only coding the tablename
     (select * from tablename) and use synonyms to point to foo.tablename
     or bar.tablename. This way our requirement would be met, since
     the definitions of the synonyms could be performed outside of
     the application.

     However, that leads to requirement number two..

  2. This requirement is about implementing security at the transaction
     level. In other words we do not want to implement security on the
     tables and views only on the actual code which updates an account
     or drops it. In SAA databases this could be solved by using
     packages (one could be created for each source file) and add
     authorites to them.

     In Oracle It could be possible to revoke all table/view authorities
     for common users and create a stored PL/SQL procedure for each
     action. Authorities could be granted to the procedure.

  Each requirement could this way be solved one by one. The problem   that will arise when both of them are applied is, that the   procedure will execute under the authority of its owner.

  That means, that any table reference in the procedure would be to   procowner.tablename instead of the synonym created for the user   who runs the application (foo or bar). Letting the procedure   redefine the synonyms would not be a practical solution.

  Our third requirement is to audit the table access, but since the USER   symbol will contain the user running the application this will work.

  A suggestion on how to combine req 1 and req 2 would be *most* welcome!

Regards

/Johan Hellstrom Received on Tue Sep 07 1993 - 14:09:54 CEST