Re:create a new oracle user
dha...@my-deja.com skrev i artiklen <88bskt$i3...@nnrp1.deja.com>...
Quote
> hi all,
> i have this program that needs to create a new user once the
> administrator clicks on the 'create new user', could someone out there
> help me.
I would suggest you write a PL/SQL procedure that does the job, and calls
this procedure with parameters username , userpassword etc
The procedure could look something like this on Oracle 8.0 (not tested)!
create or replace procedure CreateUser(USERNAME IN varchar2, USERPWD IN
varchar2) as
cid: integer;
ddl: varchar2(2000);
begin
-- procedure creates a new user and sets default tablespaces and then
grants a role to the new user.
-- DDL statements are executed by the parse call, which performs the
implied commit
ddl := 'create user '||USERNAME|| ' identified by '||USERPWD||
' DEFAULT TABLESPACE tablespace '||
'TEMPORARY TABLESPACE temptablespace '||
'QUOTA UNLIMITED ON tablespace';
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, ddl, DBMS_SQL.Native);
ddl := 'GRANT userrole TO '||USERNAME;
DBMS_SQL.PARSE(cursor_name, ddl, DBMS_SQL.Native);
DBMS_SQL.CLOSE_CURSOR(cid);
end;
Peter Laursen