|
Re: Create & drop user thru PL/SQL [message #38872 is a reply to message #38871] |
Thu, 23 May 2002 06:45 |
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 |
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 |
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.
|
|
|