procedure privilege

[athom]

hy,

i have an user "toto" and he compile a procedure with a "select" inside on a table "toto_tab" who is in his schema.

i have an other user "toto2" and i would like toto2 can execute this procedure but for him the "select" on "toto_tab" is a view of toto.toto_tab.

the problem is, when i give the privilege "grant execute on procedure to toto2", toto2 don't need privilege on toto.toto_tab.

i try with the synonyms (whith the same name so "toto_tab&quot, with the view (whith the same name ).

Please help me !!

thanks, and scuse my bad english.

athom

 

[spenglerr]

Hi athom,

actually this is exactly the way Oracle works normally. The procedure is the procedure of toto, so when it is executed it is always working with the privileges of the user toto and not with the privileges of the user who has execute rights on it.

Actually if you intend to use toto2.toto_tab instead of toto.toto_tab there are two ways.
- dynamically request the schema of the user and use it in the select inside the procedure.

- compile the procedure also for the user toto2.

The second is the easiest one.

For the first you will have to use dynamic-SQL. To build your select-statement in that way that it uses: &quot;...FROM <scheme>.toto_tab...&quot; instead of &quot;...FROM toto_tab...&quot;.

 

[spenglerr]

I saw that you posted your message a second time, jees hint is the best.