Re: ORA-01031: Create User in Procedure

"Gregory A. Castle" <gcastle_at_email.usps.gov> wrote in message news:c20b19b250725904347d32bf4cbd2817_at_spamfreenews.com...
> [This followup was posted to comp.databases.oracle.server and a copy was
> sent to the cited author.]
>
> I have the following procedure that is executed from an insert trigger
> on an employee table. This procedure works correctly if I insert the
> data logged in as sys and with sysdba rights. With any other user, I
> receive ORA-01031: insufficent privileges. I can execute the statement
> in the procedure directly as another user without any problem. Any
> suggestions as to what privilege I need to have to run this procedure.
>
> create or replace procedure cr8user (cuser IN varchar2, cpass IN
> varchar2) is
> begin
>
> execute immediate 'create user ' || cuser || ' profile default
> identified by ' || cpass || ' default tablespace users temporary
> tablespace temp account unlock';
>
> end cr8user;
>
> cuser and cpass are passed by the insert trigger. I am using Oracle 8i,
> 8.1.5.
>
> Thanks in Advance for any help.
>
> Greg

See:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:245614733 592

For a great answer. (Different situation, but same reason) -- 'AskTom' rocks.

The short answer is roles are not enabled during the execution of the procedure, so when executing it as a user that does not have the create user system privilege granted directly, it won't work.

HTH, -LM Received on Thu Nov 15 2001 - 20:22:41 CST