Re: Stored Procedure Use vs. Role Use
Date: 1997/09/18
Message-ID: <342174b9.31862235_at_newshost>#1/1
On Wed, 17 Sep 1997 14:58:21 -0500, Larry Jones <lljo_at_chevron.com> wrote:
>I have a big problem. I have a major bit of PL/SQL, I want to put into a
>stored procedure. It works fine, without being in a stored procedure
>(just executing from SQL*PLUS command line). But when I put it into a
>stored procedure for actual use, it bombs. It has to do with grants on
>some of the tables I am accessing.
>
>Evidently who ever executes the stored procedure, has to be granted
>access to the tables directly, not through roles. It's not the owner of
>the stored procedure, but the person executing it.
>
No, thats absolutely wrong. the OWNER of the procedure must be granted direct access to the object. End users who just need to run the procedure just need to be granted EXECUTE on the procedure, either directly or via a role.
You cannot create a stored object (procedures/views/packages/etc) that access an object via privs inherited from a role. You must have direct access to the object to create the stored object BUT once you have created the stored object (procedure for example) no one else needs to have access to the underlying tables. Consider this example. I create three users, a, b, c. A & B have connect and resource. C has only create session and test_role. A creates a table. A grants all on table to B. B creates a procedure that access A.table. B grants execute on the procedure via a role to C. C cannot even see A's table, let alone access it but C can run the procedure B wrote that does access it. Thats what procedures are all about:
SQL> create role test_role;
Role created.
SQL> create user A identified by A;
User created.
SQL> grant connect, resource, test_role to A; Grant succeeded.
SQL> create user B identified by B;
User created.
SQL> grant connect, resource, test_role to B; Grant succeeded.
SQL> create user C identified by C;
User created.
SQL> grant create session, test_role to C; Grant succeeded.
SQL>
SQL> connect a/a
Connected.
SQL> create table test as select * from all_users;
Table created.
SQL> grant all on test to b;
Grant succeeded.
SQL>
SQL> connect b/b
Connected.
SQL> create procedure test_proc
2 as
3 begin
4 for x in ( select count(*) cnt from a.test ) loop 5 dbms_output.put_line( x.cnt ); 6 end loop;
7 end;
8 /
Procedure created.
SQL> grant execute on test_proc to test_role; Grant succeeded.
SQL> connect c/c
Connected.
SQL> desc a.test
ERROR:
ORA-04043: object a.test does not exist
SQL> desc b.test_proc
PROCEDURE b.test_proc
SQL> set serveroutput on
SQL> exec b.test_proc
1328
PL/SQL procedure successfully completed.
>This will totally blow away our role/security structure and is not going
>to happen here.
>
>Is there anyway around this, that anyone knows of? I can't believe
>something like this is causing such a problem. Roles are a great way to
>handle access, and for Oracle not to handle them properly, is a joke.
>
>BTW... I will be calling the procedure from PowerBuilder, to an Oracle
>7.3.3 database.
>
>Thanks
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 18 1997 - 00:00:00 CEST