Re: Problem: Executing a procedure under a not owner (of procedure) user

The way around this one would be to use Oracle7 roles. 1. Create an application schema to contain all the application application schema objects e.g. tables, packages, etc etc

2. Create public synonyms for all your schema objects (including stored packages/procedures) which need to be seen or executed by users

3. Create roles which have execute priviliges on the necessary stored procedures/packages, select, insert, update on the application tables

4. Grant these roles to the appropriate users.

NOTE: If all your data manipulation etc is done via stored procedures you need only grant execute priviliges to the stored procedures and leave out granting select, insert etc on the tables... The stored procedure will have those permissions.

I hope this achieves what you are trying to do. Regards
Lawrence Simela
MAHALINI CONSULTING LIMITED John Vernes wrote in message <68e6r5$csh$1_at_news.worldonline.nl>...
>
>In our database (7.3.3.) we have a user (APPLIC_DBA) under which
>we store all our packages and stored procedures, tables and views for our
>application.
>
>For example the procedure create_classification in the package
>classification.
> The owner of this procedure will be APPLIC_DBA
>
>When executing the procedure under the user APPLIC_DBA it will run under
 the
>user APPLIC_DBA.
>This will ALSO happen when we will connect under a different user, for
>example DIF_USER
>
>Refferences in the procedure to a tablename will be made to the tables
>stored under the user APPLIC_DBA and NOT THE CONNECTED USER DIF_USER.
>
>=========================
>
>Can anyone tell me if it is possible to change this and in what way.
>
>We have an option, but it is a filty one. Namely copying all packages to
 the
>current user and recompiling them there. I believe this is NOT something
 you
>want to do
>because of the difficulties in version-management and multiple storage of
>the same info.
>
>Help would be very much appreciated.
>
>Thanks
>
>John
>
>
>
Received on Thu Jan 01 1998 - 00:00:00 CST