Oracle 创建用户并配置随机密码

事例

SYS@TEST 2024-06-03 10:46:58>@cu.sql
 
Function created.
 
Enter value for nuser: BBBB
old   3: Nuser VARCHAR2(32767) :='&Nuser';
new   3: Nuser VARCHAR2(32767) :='BBBB';
Enter value for nspace: TEST
old   4: Nspace VARCHAR2(32767) :='&Nspace';
new   4: Nspace VARCHAR2(32767) :='TEST';
用户:BBBB 密码:ESWgRSfGgEMHlTCS
 
PL/SQL procedure successfully completed.
 
SYS@TEST 2024-06-03 10:47:11>
SYS@TEST 2024-06-03 10:47:48>@cu.sql
 
Function created.
 
Enter value for nuser: 456
old   3: Nuser VARCHAR2(32767) :='&Nuser';
new   3: Nuser VARCHAR2(32767) :='456';
Enter value for nspace: TEST
old   4: Nspace VARCHAR2(32767) :='&Nspace';
new   4: Nspace VARCHAR2(32767) :='TEST';
用户:456 密码:zRQFMkWeTttrUrKF
 
PL/SQL procedure successfully completed.
 
 
   USER_ID|USERNAME                 |ACCOUNT_STATUS      |DEFAULT_TABLESPACE            |TEMPORARY_TABLESPACE          |PROFILE             |CREATED
----------|-------------------------|--------------------|------------------------------|------------------------------|--------------------|--------------------------------------------------
        62|TEST                     |OPEN                |TEST                          |TEMP                          |DEFAULT             |2024-04-18 15:21:03
        86|AAAA                     |OPEN                |TEST                          |TEMP                          |DEFAULT             |2024-05-21 11:44:40
        87|123                      |OPEN                |TEST                          |TEMP                          |DEFAULT             |2024-05-30 15:18:39
        88|BBBB                     |OPEN                |TEST                          |TEMP                          |DEFAULT             |2024-06-03 10:47:11
        89|456                      |OPEN                |TEST                          |TEMP                          |DEFAULT             |2024-06-03 10:47:58

如果执行完成没有显示用户密码，请修改 $ORACLE_HOME/sqlplus/admin/glogin.sql 文件

define _editor=vi
set lin 2222 pages 9999 long 99999 serveroutput on size 1000000 trimspool on termout off verify off
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
set sqlprompt '&_user.@&_connect_identifier. _DATE>'
set termout on

CreateUser.sql

CREATE OR REPLACE FUNCTION random_string(p_len IN NUMBER) RETURN VARCHAR2 IS
  l_random_string VARCHAR2(32767);
BEGIN
  SELECT REPLACE(SUBSTR(DBMS_RANDOM.STRING('A', p_len), 1, p_len), 'A', 'a')
  INTO l_random_string
  FROM dual;
 
  RETURN l_random_string;
END random_string;
/
declare
Npass VARCHAR2(32767);
Nuser VARCHAR2(32767) :=UPPER('&Nuser'）;
Nspace VARCHAR2(32767) :=UPPER('&Nspace'）;
begin
select random_string(16) into Npass from dual;
EXECUTE IMMEDIATE 'CREATE USER "'||Nuser||'" IDENTIFIED BY "'||Npass||'" DEFAULT TABLESPACE "'||Nspace||'" TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK';
EXECUTE IMMEDIATE 'grant create session,unlimited tablespace,connect,resource to "'||Nuser||'"';
dbms_output.put_line('用户:'||Nuser||' 密码:'||Npass);
end;
/