之前我写了一篇博客ORACLE如何用一个脚本找出一个用户的授权信息？,有些场景,这个脚本够用了,但是有些场景,例如,你需要将一个账号的权限从开发环境平移到UAT环境时,我想通过一个脚本生成账户(test)的ddl脚本,自己写了一个脚本,后面发现网上有一个脚本更好/更全面,分享于此:

set long 20000
set longchunksize 20000
set pagesize 0
set linesize 1000
set trimspool on
set feedback off
set verify off
column ddl format a1000
--Add a semicolon at the end of each statement
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
--Generate the DDL for User you enter
select dbms_metadata.get_ddl('USER', u.username) AS ddl
from dba_users u
where u.username = upper(trim('&&v_username'))
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from dba_ts_quotas tq
where tq.username = upper(trim('&&v_username')) and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = upper(trim('&&v_username'))
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = upper(trim('&&v_username'))
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = upper(trim('&&v_username'))
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = upper(trim('&&v_username'))
and rp.default_role = 'YES'
and rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from dba_users u
where u.username = upper(trim('&&v_username'))
and u.profile='DEFAULT'
and rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from dba_users u
where u.username = upper(trim('&&v_username'))
and u.profile='DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from dba_users u
where u.username = upper(trim('&&v_username'))
and u.profile='DEFAULT'
and rownum = 1
/

此脚本不是原始脚本,原始脚本请见下文链接. 此脚本做了些许变化, 对输入变量v_username进行了去除空格与转换大写处理,让脚本更健壮一点.

测试如下所示, 生成用户test的ddl脚本,如下所示

SQL> @gen_user_create_script.sql
Enter value for v_username: test
 CREATE USER "TEST" IDENTIFIED BY VALUES 'T:3F0DD3EE56D86868D4C97E562247BFFFD8EC4D8C60BDE2D720D406B46A4BE300C0BCD1BFF90EFD40D8843D872698FCFC62FF64F589E6B6102350CD3C762E22B955F52FF0E0EC64BF96F3B60799FFAE5B'
 DEFAULT TABLESPACE "USERS"
 TEMPORARY TABLESPACE "TEMP";
 GRANT "CONNECT" TO "TEST";
 GRANT CREATE TABLE TO "TEST";
 GRANT CREATE VIEW TO "TEST";
 ALTER USER "TEST" DEFAULT ROLE ALL;
/* Start profile creation script in case they are missing
 ALTER PROFILE "DEFAULT"
 LIMIT
 COMPOSITE_LIMIT UNLIMITED
 SESSIONS_PER_USER UNLIMITED
 CPU_PER_SESSION UNLIMITED
 CPU_PER_CALL UNLIMITED
 LOGICAL_READS_PER_SESSION UNLIMITED
 LOGICAL_READS_PER_CALL UNLIMITED
 IDLE_TIME UNLIMITED
 CONNECT_TIME UNLIMITED
 PRIVATE_SGA UNLIMITED
 FAILED_LOGIN_ATTEMPTS 10
 PASSWORD_LIFE_TIME 15552000/86400
 PASSWORD_REUSE_TIME UNLIMITED
 PASSWORD_REUSE_MAX UNLIMITED
 PASSWORD_VERIFY_FUNCTION NULL
 PASSWORD_LOCK_TIME 86400/86400
 PASSWORD_GRACE_TIME 604800/86400
 INACTIVE_ACCOUNT_TIME UNLIMITED
 PASSWORD_ROLLOVER_TIME -1/86400 ;
End profile creation script */

参考资料

https://smarttechways.com/2021/02/04/generate-ddl-for-the-user-including-grants-in-oracle/