有付权限的权利,用有DBA角色,又或者要有相关权限。create or replace procedure name_pro
as
sql1 varchar2(50);
sql2 varchar2(50);
begin
sql1:='create user.....';
sql2:='grant ...to user_name';
execute immediate sql1;
execute immediate sql2;
end name_pro;
/
随笔写,自已检查吧.
as
sql1 varchar2(50);
sql2 varchar2(50);
begin
sql1:='create user.....';
sql2:='grant ...to user_name';
execute immediate sql1;
execute immediate sql2;
end name_pro;
/
随笔写,自已检查吧.
(userid In varchar2,
passwd In varchar2,
errnumber out number,
errdesc out varchar2) is
user_count Number := 0;
Begin If passwd IS NULL Then
errnumber := 6000;
errdesc := 'Password could not be null!';
Return;
End If; If userid IS NULL Then
errnumber := 6000;
errdesc := 'Username could not be null!';
return;
Else
If upper(userid) = 'ISA' then
errnumber := 6000;
errdesc := 'Could not create system user: ISA!';
return;
Else
SELECT COUNT(*) INTO user_count FROM DBA_USERS WHERE username = upper(userid);
If user_count > 0 Then
errnumber := 6000;
errdesc := 'User[' || userid || '] has already exist';
return;
End If;
dbms_repcat_admin.do_sql('Create user "' || userid || '" Identified by "' || passwd ||
'" DEFAULT TABLESPACE "TEST_DATA" TEMPORARY TABLESPACE "ISATMP_DATA" PROFILE DEFAULT',TRUE);
dbms_repcat_admin.do_sql('GRANT "CONNECT" TO "' || userid || '"',TRUE);
dbms_repcat_admin.do_sql('GRANT "MIS" TO "' || userid || '"',TRUE);
dbms_repcat_admin.do_sql('ALTER USER "' || userid || '" DEFAULT ROLE ALL',TRUE);
End if;
End If;
EXCEPTION WHEN others THEN
RAISE;
End;