2 Replies Latest reply: Feb 17, 2014 2:20 AM by onkar.nath RSS

Procedure being created but doesn't work

ccb12718-a8d3-4693-a830-f4ca45fdbe17 Newbie
Currently Being Moderated

Hi!

 

we are trying to create users and the code below allows us to create a user

 

 

DECLARE

BEGIN

     FOR s      IN           (SELECT first_name FROM staff_table WHERE town = 'LONDON ')

    

     LOOP

          EXECUTE IMMEDIATE 'CREATE USER ' || (s.first_name) || ' IDENTIFIED BY LONDON ' ;

          dbms_output.put_line (s.first_name || 'IDENTIFIED BY LONDON ');

 

     END LOOP;

 

END;

/

 

 

but, when we edit the above code to create a procedure it wont allow us to do so,

 

CREATE OR REPLACE PROCEDURE proc_user AS

first_name VARCHAR2 (25);

 

BEGIN

 

FOR s IN (SELECT first_name FROM staff_table WHERE town = 'LONDON ' )

 

LOOP

EXECUTE IMMEDIATE 'CREATE USER ' || (s.first_name) || ' IDENTIFIED BY LONDON ');

dbms_output.put_line (s.first_name || ' USERNAME IDENTIFIED BY LONDON ');

END LOOP;

END;

 

 

Any help please?

 

Thank You

  • 1. Re: Procedure being created but doesn't work
    Tom321 Journeyer
    Currently Being Moderated

    Hi,

     

    oracle handles user privileges differently for stored procedures. You only have directly granted privileges available in stored procedures, not privileges from roles. If your accout has eg. the dba role including the create user privilege, this will allow you to create the user in the psql code snipet but not in the procedure. Grant the create user privilege directly to the owner of the procedure and it should run.

     

    Regards

    Thomas 

  • 2. Re: Procedure being created but doesn't work
    onkar.nath Expert
    Currently Being Moderated

    if you could post the error also, it would be easier to suggest. Alternatively, As Tom said, the owner of the proc (assuming that the owner will always run it) should have create  user privilege or DBA role assigned or if you dont want the owner of the proc to have that privilege then either create this proc as DBA or use AUTHID CURRENT_USER in the proc in which case the proc will take the permission of the one who is executing. So you don't need to grant dba role to any one. Just execute it using a user with DBA role.

     

    Onkar

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points