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

set long 20000set longchunksize 20000set pagesize 0set linesize 1000set trimspool onset feedback offset verify offcolumn ddl format a1000--Add a semicolon at the end of each statementexecute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);--Generate the DDL for User you enterselect dbms_metadata.get_ddl('USER', u.username) AS ddlfrom dba_users uwhere u.username = upper(trim('&&v_username'))union allselect dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddlfrom dba_ts_quotas tqwhere tq.username = upper(trim('&&v_username')) and rownum = 1union allselect dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddlfrom dba_role_privs rpwhere rp.grantee = upper(trim('&&v_username'))and rownum = 1union allselect dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddlfrom dba_sys_privs spwhere sp.grantee = upper(trim('&&v_username'))and rownum = 1union allselect dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddlfrom dba_tab_privs tpwhere tp.grantee = upper(trim('&&v_username'))and rownum = 1union allselect dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddlfrom dba_role_privs rpwhere rp.grantee = upper(trim('&&v_username'))and rp.default_role = 'YES'and rownum = 1union allselect to_clob('/* Start profile creation script in case they are missing') AS ddlfrom dba_users uwhere u.username = upper(trim('&&v_username'))and u.profile='DEFAULT'and rownum = 1union allselect dbms_metadata.get_ddl('PROFILE', u.profile) AS ddlfrom dba_users uwhere u.username = upper(trim('&&v_username'))and u.profile='DEFAULT'union allselect to_clob('End profile creation script */') AS ddlfrom dba_users uwhere u.username = upper(trim('&&v_username'))and u.profile='DEFAULT'and rownum = 1/

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

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

SQL> @gen_user_create_script.sqlEnter 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/