we are trying to create users and the code below allows us to create a user
FOR s IN (SELECT first_name FROM staff_table WHERE town = 'LONDON ')
EXECUTE IMMEDIATE 'CREATE USER ' || (s.first_name) || ' IDENTIFIED BY LONDON ' ;
dbms_output.put_line (s.first_name || 'IDENTIFIED BY LONDON ');
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);
FOR s IN (SELECT first_name FROM staff_table WHERE town = 'LONDON ' )
EXECUTE IMMEDIATE 'CREATE USER ' || (s.first_name) || ' IDENTIFIED BY LONDON ');
dbms_output.put_line (s.first_name || ' USERNAME IDENTIFIED BY LONDON ');
Any help please?
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.
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.