Procedures

[ocp2b]

Assume I have a user "A". User "A" has tables and procedures in his schema. Now assume I have user "B". User "B" has been granted access to a stored procedure in user "A"'s schema. The stored procedure creates a table. I know that the procedure will create a table in user "A" 's schema if user "A" executes it, but what happens if user "B" executes it? In whose schema does the table get created? Assume the procedure does not have any schema qualifier such as "create table a.newtable ...".

[kgb]

Table will be created in the schema A.

[ocp2b]

Thanks. That is what I though. Is there a way to get the procedure to create the table in the schema of the user that is executing it rather than in the schema of the owner? Example: table would be created in "B", not "A".

[svk]

Get the userid of the connected session and qualify the table name in the create table command with it. It works.

[kgb]

Yes you can, you have to use dynamic sql and function SYS_CONTEXT which will return you username 'B', than you can use it.

SELECT SYS_CONTEXT ('USERENV','CURRENT_USER') into usr FROM DUAL;
'create table '|| usr ||'.table ( ... )';
...
and so on ...

Originally posted by ocp2b
Thanks. That is what I though. Is there a way to get the procedure to create the table in the schema of the user that is executing it rather than in the schema of the owner? Example: table would be created in "B", not "A".

[jmodic]

If you are on 8i, then definitely the best way to deal with your problem is to use "invokers rights" for that procedure. That way you dont have to worry about in whose schema the table will be created - it will allways be created in a schema of the user that is currently executing the procedure. All you have to do is to specify "AUTHID CURRENT_USER" when you create a procedure:

CREATE OR REPLACE PROCEDURE my_procedure AUTHID CURRENT_USER
AS ......