Procedures
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Procedures

  1. #1
    Join Date
    Sep 2001
    Posts
    163
    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 ...".


  2. #2
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    Table will be created in the schema A.
    Best wishes!
    Dmitri

  3. #3
    Join Date
    Sep 2001
    Posts
    163
    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".

  4. #4
    Join Date
    Jul 2000
    Posts
    521
    Get the userid of the connected session and qualify the table name in the create table command with it. It works.
    svk

  5. #5
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    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".
    Best wishes!
    Dmitri

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 ......
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width