| Problem using Invoker Rights stored procedures [message #2704] |
Wed, 07 August 2002 07:17  |
Mark Grimshaw
Messages: 73
Registered: June 2002
|
Member |
|
|
Hello,
Yet another cry for help. I am having problems executing a stored procedure in one schema that has been defined with invoker rights. I create the procedure, assign it to a role and assign the user to the role. I also use ALTER USER DEFAULT ROLE ALL to place the role on the user slist of default roles so I don't need to use SET ROLE.
The problem is that when I start up sql plus (I have also granted the user the CREATE SESSION privilege) I have to issue the command ALTER SESSIOn SET CURRENT_SCHEMA = SYSTEM to get **a procedure that calls the stored procedure assigned to the role** to recognise the procedure first created.
Any takers ?
TYIA
|
|
|
|
| Re: Problem using Invoker Rights stored procedures [message #2714 is a reply to message #2704] |
Wed, 07 August 2002 11:56  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Mark, it would really help if you could give the actual schema names and show the steps that you have done using standard SQL*Plus format (with the current user identified), like:
SYSTEM>create procedure system_proc ...; (this can be the shorthand showing the proc created in SYSTEM)
SYSTEM>grant execute on system_proc to some_role;
SYSTEM>grant some_role to JIM;
JIM>something_jim_is_trying_to_do
This sounds the synonym issue again but I can't tell for sure from your description.
|
|
|
|
| Re: Problem using Invoker Rights stored procedures [message #2726 is a reply to message #2704] |
Thu, 08 August 2002 05:53 |
Mark Grimshaw
Messages: 73
Registered: June 2002
|
Member |
|
|
Ok, here goes.
If logged on as SYSTEM from Sql*Plus I do this: -
(confirmation messages removed for clarity)
SYSTEM> CREATE USER Joe IDENTIFIED BY letmein;
SYSTEM> GRANT EXECUTE ON TestPackage TO DataManager;
SYSTEM> GRANT DataManager TO Joe;
SYSTEM> GRANT CREATE SESSION TO Joe;
SYSTEM> CREATE PUBLIC SYNONYM TestPackage FOR TestPackage;
TestPackage in SYSTEM schema has a procedure called TestProcedure1: -
PROCEDURE TestProcedure1(TestTableID IN NUMBER,
MyCursor IN OUT REF CURSOR)
SqlStatement VARCHAR2(100);
BEGIN
SqlSatement := 'SELECT COLUMN2 FROM TEST_TABLE
WHERE TEST_TABLE_id = :ID';
OPEN MyCursor FOR SqlStatement USING TestTableID;
END;
Now I log in as Joe.
Joe>SET ROLE DataManager;
Joe>ALTER SESSION SET CURRENT_SCHEMA = SYSTEM;
Now I think I should be able to execute a procedure/script that calls TestProcedure1.
The salient part of the script is this:-
DECLARE
aCursor REf CURSOR;
BEGIN
TestPackage.TestProcedure1(1000, aCursor);
...
...
END;
I can execute the script from SYSTEM ok. The specific and actual error that is output is as follows:-
DECLARE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYSTEM.TESTPACKAGE", line 17
ORA-06512: at line 7
Line 7 of the script is:
TestPackage.TestProcedure1(1000, aCursor);
I hope this helps. Also I have a question that relates to the other thread which you have helped me on where you mention that an applications data should not be created in the SYSTEM schema but should be stored separately. I was reading the documentation and was wondering whether you meant that I should ideally create an application user say AppUser with a default tablespace that points to one that I have manually set up. If I don't specify a tablespace then the system one is used anyway.
Mark
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
