Create User in a Stored Procedure

[fmsousa]

Hi,

In a form, i have a button that would create a new user. this button has got the following code:

create_user user_name, :user_password, :user_database);
...
...

in this procedure i put the following code to create the new user:

CREATE OR REPLACE PROCEDURE CREATE_USER (user_name IN VARCHAR2, user_pass IN VARCHAR2, user_db IN VARCHAR2)
IS

V_CURSORID INTEGER;
V_NUMROWS INTEGER;
V_TRIGSTRING VARCHAR2(100);

BEGIN

V_CURSORID := DBMS_SQL.OPEN_CURSOR;
V_TRIGSTRING := 'CREATE USER' || user_name || 'IDENTIFIED BY ' || user_pass || ';';

DBMS_SQL.PARSE(V_CURSORID, V_TRIGSTRING, DBMS_SQL.NATIVE);
--DBMS_SQL.PARSE(V_CURSORID, V_TRIGSTRING, DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR(V_CURSORID);
END;


But i haven´t got any results!
It brings to me this error:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "create_user", line 16
ORA-06512: at line 1

How can i do this?
Thanks in advance.

 

[lewisp]

Procedures will run using the privileges of the original creator. You need to make sure the procedure is created by a user who has 'create user' privilege.

 

[sem]

In fact procedures are NORMALLY executed under the priviledges of their creator (so called early-binding). This is not the case for dynamic sql, because the priviledges are checked while parsing the statement. The user who calls your procedure must have explicit right to create user.

 

[SyedAqif]

Try using this ...maybe it'll help. I dont remember the exact code but it goes somting like that

Forms_ddl('Create User :TxtUN identified by :txtPw')

passing ddl statement on the form could be alternate approach

Cheers!
Aqif

 

[sem]

You can not use bind variables within quotes, they will be treated as string literals. FORMS_DDL really executes ddl commands, so if you do not plan to use your procedure somewhere else, it's the best choice.