CREATE USER ?!

[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.

 

[carp]

Who are you logged in as (apparently not SYS!), and how did you get your privileges on DBMS_SQL? I believe you have to have the privileges granted explicitly to your account, not to a role that you have been granted.

Log in as SYS and grant execute on DBMS_SQL to the account you are using. You may also have to explicitly GRANT CREATE USER to your account. Alternatively, compile the code as SYS.

 

[rtefft]

Normal Oracle installation gives you access to DBMS_SQL, so I suspect the problem is with the CREATE USER itself. Stand-alone procedures like yours execute using the privs of the user executing it, so you would have to give CREATE USER privs to everyone who might use it. :-(

Consider a package...
By default, packages (and the procedures in them) are executed using the rights of the package owner, not the person running it. This means that an account with DBA privs like SYS, SYSTEM, etc could own your package, but users can execute it (and whatever it does) as long as they have EXECUTE privs on the package. I wrote a package this way, and let the developers change user's passwords and kill sessions in the test database. By using a package owned by SYS, I only had to give them execute access to the package (not ALTER USER or ALTER SYSTEM as I normally would). I included safeguards in the procedure to prevent them from messing with non-user accounts as an added safeguard. I think an approach like this would work for you.

You can create a package and have it executed as the person running it, by specifying AUTHID CURRENT_USER when you create it (see SQL manual).

Hope this helps,
Rich Tefft ____________________________
Rich Tefft
PL/SQL Programmer