| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Question on Grant Execute
Sally,
here is an example for your setup. Run it as a dba. It will switch to the just created users.
SQL> create role R_PROCS_USER;
Role created.
SQL>
SQL> create user division identified by division
2 default tablespace users
3 temporary tablespace temp
4 /
User created.
SQL>
SQL> create user usr_a identified by usr_a
2 default tablespace users
3 temporary tablespace temp
4 /
User created.
SQL>
SQL> create user usr_b identified by usr_b
2 default tablespace users
3 temporary tablespace temp
4 /
User created.
SQL>
SQL> grant connect, resource to division;
Grant succeeded.
SQL> grant connect, resource to usr_a;
Grant succeeded.
SQL> grant connect, resource to usr_b;
Grant succeeded.
SQL> grant r_procs_user to usr_a;
Grant succeeded.
SQL> grant r_procs_user to usr_b;
Grant succeeded.
SQL>
SQL> connect division/division
Connected.
SQL>
SQL> -- We *must* have the template pop_tab for the owner of the procedure
populate
SQL> -- because otherwise we get a compile time error. SQL> -- If we would not want to use this template we have to use dynamic sql. SQL> create table pop_tab ( 2 n number
Table created.
SQL>
SQL> create or replace procedure populate (
2 i_n in number
3 ) authid current_user is
4 begin
5 insert into pop_tab (n) values (i_n);
6 end populate;
7 /
Procedure created.
SQL>
SQL> grant execute on populate to r_procs_user;
Grant succeeded.
SQL>
SQL> connect usr_a/usr_a
Connected.
SQL>
SQL> create table pop_tab (
2 n number
3 )
4 /
Table created.
SQL>
SQL> execute division.populate (55)
PL/SQL procedure successfully completed.
SQL>
SQL> select * from usr_a.pop_tab;
N
----------
55
SQL>
SQL> connect usr_b/usr_b
Connected.
SQL>
SQL> create table pop_tab (
2 n number
3 )
4 /
Table created.
SQL>
SQL> execute division.populate (33)
PL/SQL procedure successfully completed.
SQL>
SQL> select * from usr_b.pop_tab;
N
----------
33
SQL> Hope this explains it.
Martin
Sally Madeira wrote:
>
> Martin,
>
> Thanks for the feed back. I have never had the opportunity to use cller
> feedback thus I am a little new to that terminology. What is the syntax for
> caller indentification and do I have to use it with in each procedure with
> the package owned by procown or just the calling procedure.
>
> thanks
>
> "Martin Haltmayer" <Martin.Haltmayer_at_0800-einwahl.de> wrote in message
> news:3AE21256.3B2C642F_at_0800-einwahl.de...
> > Try using caller identification (in Oracle 8.1.x) because if you have a
> > procedure procabc that is owned by procown and accesses a table proctab,
it will
> > always access that table that is defined by name resolution of the
procown. So
> > without caller identification you have to use dynamic sql incorporating
the
> > calling user to access the correct table.
> >
> > Martin
> >
Received on Mon Apr 23 2001 - 16:30:17 CDT
 |
 |