Home » SQL & PL/SQL » SQL & PL/SQL » Create & drop user thru PL/SQL
Create & drop user thru PL/SQL [message #38871] Thu, 23 May 2002 06:07 Go to next message
bharath
Messages: 30
Registered: November 2000
Member
can any body help me in
creating or deleting a user using Pl/Sql
thanks in advance
bharath
Re: Create & drop user thru PL/SQL [message #38872 is a reply to message #38871] Thu, 23 May 2002 06:45 Go to previous messageGo to next message
oxkar
Messages: 9
Registered: May 2002
Junior Member
CREATE OR REPLACE PROCEDURE CREATE_USER
(USERNAME IN VARCHAR2,
PASSWORD IN VARCHAR2)
IS

vString VARCHAR2(500);
source_cursor integer;
rows_processed integer;

BEGIN
vString := 'CREATE USER ' || USERNAME || ' IDENTIFIED BY ' || PASSWORD;
vString := vString || ' DEFAULT TABLESPACE TS_DATA';
vString := vString || ' TEMPORARY TABLESPACE TS_TEMP';
vString := vString || ' QUOTA 200M ON TS_DATA';
vString := vString || ' QUOTA 50M ON TS_TEMP';

source_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(source_cursor, vString, DBMS_SQL.NATIVE);
rows_processed := DBMS_SQL.EXECUTE(source_cursor);
DBMS_SQL.CLOSE_CURSOR(source_cursor);

/* Now the connection privilege */
vString := 'GRANT CONNECT TO ' || USERNAME;

source_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(source_cursor, vString, DBMS_SQL.NATIVE);
rows_processed := DBMS_SQL.EXECUTE(source_cursor);
DBMS_SQL.CLOSE_CURSOR(source_cursor);
END;
/

Replace the create part with the appropriate sentence for dropping a user and it'll be done.
You could add another parameter to the procedure in order to indicate if you want create or drop and of course modify the procedure to act in every case.
Re: Create & drop user thru PL/SQL [message #38912 is a reply to message #38871] Mon, 27 May 2002 04:55 Go to previous messageGo to next message
Godwin
Messages: 37
Registered: January 2002
Member
I ran the procedure but got this error

ERROR at line 1:
ORA-01932: ADMIN option not granted for role 'CONNECT'
ORA-06512: at "SYS.DBMS_SYS_SQL", line 487
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "AB.CREATE_USER", line 25
ORA-06512: at line 1
What is the solution then?
Re: Create & drop user thru PL/SQL [message #38918 is a reply to message #38871] Mon, 27 May 2002 13:47 Go to previous message
Oskar
Messages: 26
Registered: May 2002
Junior Member
You must give explicit permissions to the user executing the procedure. It's not enough with having the DBA role. I can't remember which ones were the minumum to achieve the correct execution but if you give all of them to the user explicitly it'll work.
Previous Topic: help me...please
Next Topic: Re: Access to other user's object from procedure
Goto Forum:
  


Current Time: Thu Mar 28 08:41:48 CDT 2024