Re: Creating users via PL/SQL

dstrait_at_omni.voicenet.com ("Darin Strait") wrote:

You must use dynamic sql (you didn't specify a database version, but you will need 7.1 or above to do this). Pl/sql doesn't support DDL directly.

Create a small procedure:

procedure execute_immediate( stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

    when others then

          dbms_output.put_line( sqlcode );
          dbms_output.put_line( sqlerrm );
          dbms_output.put_line( substr( stmt, 1, 200 ) );
      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

And then you will be able to:

> PROCEDURE CreateSomeUser(iUserName VARCHAR2) IS
> BEGIN

                execute_immediate( 


>			'CREATE USER iUserName identified by tiger ' ||

>				'default tablespace TS_TABLES ' ||

>				'temporary tablespace TS_TEMP ' ||

>				'QUOTA UNLIMITED on TS_TABLES ' ||

>				'QUOTA UNLIMITED on TS_INDEXES ' ||

>				'QUOTA 2M on TS_TEMP' );

 


>			execute_immediate( 'GRANT some_role TO iUserName' );

 


>		END CreateSomeUser ;

 


>	END fn_users ;

NOTE:
the create user privelege must be GRANTED DIRECTLY TO THE PERSON compiling CreateSomeUser. You cannot have inherited this privelege from a role (eg: DBA is a role with this privelege).

Don't grant execute on EXECUTE_IMMEDIATE to anyone or else they will be able to execute any statement under your account with your priveleges.

>I am trying to write a small package to create a user. While I can get this
>to work through a script, PL/SQL doesn't seem to know how to deal with the
>keywords CREATE or GRANT. I've looked through the PL/SLQL User's guide and
>grepped through the scripts that Oracle installs, but I can't find anything
>about creating users in a package. Can someone point me in the correct
>direction?
 

>Some stripped-down example code follows:
 

>CREATE OR REPLACE PACKAGE fn_users AS
 

> PROCEDURE CreateSomeUser(iUserName VARCHAR2);
 

> END fn_users ;
>/
 

>CREATE OR REPLACE PACKAGE BODY fn_users AS
 

>--# CreateSomeUser() allows for easy creation of users.
> PROCEDURE CreateSomeUser(iUserName VARCHAR2) IS
> BEGIN
> CREATE USER iUserName identified by tiger
> default tablespace TS_TABLES
> temporary tablespace TS_TEMP
> QUOTA UNLIMITED on TS_TABLES
> QUOTA UNLIMITED on TS_INDEXES
> QUOTA 2M on TS_TEMP;
 

> GRANT some_role TO iUserName ;
 

> END CreateSomeUser ;
 

> END fn_users ;
>/
>--# /*** End of file ***/

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Tue Oct 24 1995 - 00:00:00 CET