Re: Question on Grant Execute

Sally,
  Did you remove the package/procedure/tables from the other users? Oracles pecking order when resolving object references. Current user, private synonym, public synonym.
Synonyms will need to be created if you don't prefix the call to the package

The package will operate only in it's tables in it's schema. You package procedure has insert into a_table ? Unless you specify other users table names in the package (e.g insert into userb.a_table). To use the central procedure with the procedure's calling end user table would involve dynamic SQL to change where the data is inserted and Grant insert to all users table to central user controlling the package. Or grant insert any table. kind of dangerous. Sounds kinda messy but possible.

Another Design idea. if data doesn't have to be kept separate for each user. Why not keep the data central and have a new column (owner) identifying the user that inserted it to segregate each users data. You could make that part of the key structure or not depending on the data chance for pk violation. Index on the column. This owner column can be populated with an insert trigger or in the insert statement by adding value of USER for owner column. A View can be created showing data for only for the user creating it. create view a_table_user_v as select data1,data2 from a_table where owner =user.

This gives users their data and lets you manage it in one schema keeping all the data together. Grant execute to package, grant select to view(s), create public synonym for package/view(s). Grant object access to the role. Grant the role to the users.

hth
Dave Fowler

"Sally Madeira" <sallym1_at_home.com> wrote in message news:HWnE6.13862$Ce4.1338919_at_news1.rdc1.ct.home.com...
> I am having some problems with grants and I am sure it is what I am doing.
> I have 5 users. Each user has the same tables (we just need to process
 data
> seperately). I currentlty have the same packages in all 5 users and
 finding
> that it has become a maintenance nightmare. This is what I tried to do.
 The
> Division User is the Main user. I have all the packages and procedures in
> that user and want the other 4 users to access the procedures/packages in
> Division. I created a role called R_PROCS_USER and granted Execute rights
> on the packages and procedures in the roll and gave the users the role.
> This actually ran when I executed several procedures in one package in
> Division but when I went to see if the tables where populated in the
 calling
> user they were not (It said the procedure from FRF User ran successfully).
> When I tried calling another packege it did not work.
>
> Is there anything special I need to do in order to execute procedure that
> are located in one USER that are called from another.
>
> Any help would be appreciated
> Thanks
>
>
>
Received on Sat Apr 28 2001 - 09:22:31 CDT