批量查询已经创建的用户，密码和授权

今天一个网友问我能不能实现批量的查询已经创建出来的用户和权限的脚本；偶测试了下，希望对他有点帮助。

SQL> select * from v$version;BANNER----------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE    11.2.0.3.0      ProductionTNS for 32-bit Windows: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionSQL> show userUSER 为 "SYS"

 select 'create user ' || tt.username || '  identified by "' ||
    (select password from sys.user$ mm where tt.username = mm.name) || '" ' ||
       chr(10) || ' default tablespace  ' || tt.default_tablespace ||
       chr(10) || ' temporary tablespace ' || tt.temporary_tablespace ||
       chr(10) || ' profile ' || tt.profile || ';'
  from dba_users tt 
union all
 select 'grant unlimited tablespace to  ' || tt.username || ';'
  from dba_users tt   ;

SQL>  select 'create user ' || tt.username || '  identified by "' ||  2      (select password from sys.user$ mm where tt.username = mm.name) || '" ' ||  3         chr(10) || ' default tablespace  ' || tt.default_tablespace ||  4         chr(10) || ' temporary tablespace ' || tt.temporary_tablespace ||  5         chr(10) || ' profile ' || tt.profile || ';'  6    from dba_users tt  7  union all  8   select 'grant unlimited tablespace to  ' || tt.username || ';'  9    from dba_users tt   ;'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET--------------------------------------------------------------------------------create user MGMT_VIEW  identified by "8610026342614DEE" default tablespace  SYSTEM temporary tablespace TEMP profile DEFAULT;create user SYS  identified by "8A8F025737A9097A" default tablespace  SYSTEM temporary tablespace TEMP profile DEFAULT;create user SYSTEM  identified by "2D594E86F93B17A1"'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET-------------------------------------------------------------------------------- default tablespace  SYSTEM temporary tablespace TEMP profile DEFAULT;create user DBSNMP  identified by "FFF45BB2C0C327EC" default tablespace  SYSAUX temporary tablespace TEMP profile MONITORING_PROFILE;create user SYSMAN  identified by "2CA614501F09FCCC" default tablespace  SYSAUX'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET-------------------------------------------------------------------------------- temporary tablespace TEMP profile DEFAULT;create user A  identified by "AFCC9478DFBF9029" default tablespace  USERS temporary tablespace TEMP profile DEFAULT;create user YYGL_DZ  identified by "247EDFCF5DBF02CF" default tablespace  USERS temporary tablespace TEMP'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET-------------------------------------------------------------------------------- profile DEFAULT;create user SCOTT  identified by "CDC57F9E62A38D03" default tablespace  USERS temporary tablespace TEMP profile DEFAULT;create user HR  identified by "4C6D73C3E8B0F0DA" default tablespace  USERS temporary tablespace TEMP profile DEFAULT;'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET--------------------------------------------------------------------------------create user AA  identified by "467E90852FA381CC" default tablespace  USERS temporary tablespace TEMP profile DEFAULT;create user OUTLN  identified by "4A3BA55E08595C81" default tablespace  SYSTEM temporary tablespace TEMP profile DEFAULT;'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET--------------------------------------------------------------------------------create user FLOWS_FILES  identified by "22886B8194105AC5" default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;create user MDSYS  identified by "72979A94BAD2AF80" default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;create user ORDSYS  identified by "7EFA02EC7EA6B86F"'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET-------------------------------------------------------------------------------- default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;create user EXFSYS  identified by "33C758A8E388DEE5" default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;create user WMSYS  identified by "7C9BA362F8314299" default tablespace  SYSAUX'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET-------------------------------------------------------------------------------- temporary tablespace TEMP profile DEFAULT;create user APPQOSSYS  identified by "519D632B7EE7F63A" default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;create user APEX_030200  identified by "F4A3EC8D7050726A" default tablespace  SYSAUX temporary tablespace TEMP'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET-------------------------------------------------------------------------------- profile DEFAULT;create user OWBSYS_AUDIT  identified by "FD8C3D14F6B60015" default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;create user ORDDATA  identified by "A93EC937FCD1DC2A" default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET--------------------------------------------------------------------------------create user CTXSYS  identified by "D1D21CA56994CAB6" default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;create user ANONYMOUS  identified by "anonymous" default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET--------------------------------------------------------------------------------create user XDB  identified by "88D8364765FCE6AF" default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;create user ORDPLUGINS  identified by "88A2B2C183431F00" default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;create user OWBSYS  identified by "610A3C38F301776F"'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET-------------------------------------------------------------------------------- default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;create user SI_INFORMTN_SCHEMA  identified by "84B8CBCA4D477FA3" default tablespace  SYSAUX temporary tablespace TEMP profile DEFAULT;create user OLAPSYS  identified by "4AC23CC3B15E2208" default tablespace  SYSAUX'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET-------------------------------------------------------------------------------- temporary tablespace TEMP profile DEFAULT;create user ORACLE_OCM  identified by "5A2E026A9157958C" default tablespace  USERS temporary tablespace TEMP profile DEFAULT;create user XS$NULL  identified by "DC4FCC8CB69A6733" default tablespace  USERS temporary tablespace TEMP'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET-------------------------------------------------------------------------------- profile DEFAULT;create user MDDATA  identified by "DF02A496267DEE66" default tablespace  USERS temporary tablespace TEMP profile DEFAULT;create user DIP  identified by "CE4A36B8E06CA59C" default tablespace  USERS temporary tablespace TEMP profile DEFAULT;'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET--------------------------------------------------------------------------------create user APEX_PUBLIC_USER  identified by "1CBEF9C095705DBF" default tablespace  USERS temporary tablespace TEMP profile DEFAULT;create user SPATIAL_CSW_ADMIN_USR  identified by "1B290858DD14107E" default tablespace  USERS temporary tablespace TEMP profile DEFAULT;'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET--------------------------------------------------------------------------------create user SPATIAL_WFS_ADMIN_USR  identified by "7117215D6BEE6E82" default tablespace  USERS temporary tablespace TEMP profile DEFAULT;grant unlimited tablespace to  MGMT_VIEW;grant unlimited tablespace to  SYS;grant unlimited tablespace to  SYSTEM;grant unlimited tablespace to  DBSNMP;grant unlimited tablespace to  SYSMAN;grant unlimited tablespace to  A;'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET--------------------------------------------------------------------------------grant unlimited tablespace to  YYGL_DZ;grant unlimited tablespace to  SCOTT;grant unlimited tablespace to  HR;grant unlimited tablespace to  AA;grant unlimited tablespace to  OUTLN;grant unlimited tablespace to  FLOWS_FILES;grant unlimited tablespace to  MDSYS;grant unlimited tablespace to  ORDSYS;grant unlimited tablespace to  EXFSYS;grant unlimited tablespace to  WMSYS;grant unlimited tablespace to  APPQOSSYS;'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET--------------------------------------------------------------------------------grant unlimited tablespace to  APEX_030200;grant unlimited tablespace to  OWBSYS_AUDIT;grant unlimited tablespace to  ORDDATA;grant unlimited tablespace to  CTXSYS;grant unlimited tablespace to  ANONYMOUS;grant unlimited tablespace to  XDB;grant unlimited tablespace to  ORDPLUGINS;grant unlimited tablespace to  OWBSYS;grant unlimited tablespace to  SI_INFORMTN_SCHEMA;grant unlimited tablespace to  OLAPSYS;grant unlimited tablespace to  ORACLE_OCM;'CREATEUSER'||TT.USERNAME||'IDENTIFIEDBY"'||(SELECTPASSWORDFROMSYS.USER$MMWHERET--------------------------------------------------------------------------------grant unlimited tablespace to  XS$NULL;grant unlimited tablespace to  MDDATA;grant unlimited tablespace to  DIP;grant unlimited tablespace to  APEX_PUBLIC_USER;grant unlimited tablespace to  SPATIAL_CSW_ADMIN_USR;grant unlimited tablespace to  SPATIAL_WFS_ADMIN_USR;已选择68行。

查看授予哪些权限，例如查看授予DBA权限的批量处理语句

select ' grant ' || d.privilege || ' on ' || owner || '.' || table_name ||
        ' to ' || grantee || case
       when grantable = 'YES' then
           ' with grant option;'
          else
           ';'
       end
  from dba_tab_privs d
where grantee = 'DBA';

'GRANT'||D.PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE||CASEWHENGRANT-------------------------------------------------------------------------------- grant UPDATE on XDB.XDB$CHECKOUTS to DBA; grant SELECT on XDB.XDB$CHECKOUTS to DBA; grant INSERT on XDB.XDB$CHECKOUTS to DBA; grant DELETE on XDB.XDB$CHECKOUTS to DBA; grant ALTER on XDB.XDB$CHECKOUTS to DBA; grant FLASHBACK on XDB.XDB$CONFIG to DBA; grant DEBUG on XDB.XDB$CONFIG to DBA; grant QUERY REWRITE on XDB.XDB$CONFIG to DBA; grant ON COMMIT REFRESH on XDB.XDB$CONFIG to DBA; grant UPDATE on XDB.XDB$CONFIG to DBA; grant SELECT on XDB.XDB$CONFIG to DBA;'GRANT'||D.PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE||CASEWHENGRANT-------------------------------------------------------------------------------- grant INSERT on XDB.XDB$CONFIG to DBA; grant DELETE on XDB.XDB$CONFIG to DBA; grant ALTER on XDB.XDB$CONFIG to DBA; grant FLASHBACK on XDB.XDB$D_LINK to DBA; grant DEBUG on XDB.XDB$D_LINK to DBA; grant QUERY REWRITE on XDB.XDB$D_LINK to DBA; grant ON COMMIT REFRESH on XDB.XDB$D_LINK to DBA; grant UPDATE on XDB.XDB$D_LINK to DBA; grant SELECT on XDB.XDB$D_LINK to DBA; grant INSERT on XDB.XDB$D_LINK to DBA; grant DELETE on XDB.XDB$D_LINK to DBA;'GRANT'||D.PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE||CASEWHENGRANT-------------------------------------------------------------------------------- grant ALTER on XDB.XDB$D_LINK to DBA; grant FLASHBACK on XDB.XS$DATA_SECURITY to DBA; grant DEBUG on XDB.XS$DATA_SECURITY to DBA; grant QUERY REWRITE on XDB.XS$DATA_SECURITY to DBA; grant ON COMMIT REFRESH on XDB.XS$DATA_SECURITY to DBA; grant UPDATE on XDB.XS$DATA_SECURITY to DBA; grant SELECT on XDB.XS$DATA_SECURITY to DBA; grant INSERT on XDB.XS$DATA_SECURITY to DBA; grant DELETE on XDB.XS$DATA_SECURITY to DBA; grant ALTER on XDB.XS$DATA_SECURITY to DBA; grant FLASHBACK on XDB.XS$PRINCIPALS to DBA;'GRANT'||D.PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE||CASEWHENGRANT-------------------------------------------------------------------------------- grant DEBUG on XDB.XS$PRINCIPALS to DBA; grant QUERY REWRITE on XDB.XS$PRINCIPALS to DBA; grant ON COMMIT REFRESH on XDB.XS$PRINCIPALS to DBA; grant UPDATE on XDB.XS$PRINCIPALS to DBA; grant SELECT on XDB.XS$PRINCIPALS to DBA; grant INSERT on XDB.XS$PRINCIPALS to DBA; grant DELETE on XDB.XS$PRINCIPALS to DBA; grant ALTER on XDB.XS$PRINCIPALS to DBA; grant FLASHBACK on XDB.XS$ROLESETS to DBA; grant DEBUG on XDB.XS$ROLESETS to DBA; grant QUERY REWRITE on XDB.XS$ROLESETS to DBA;'GRANT'||D.PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE||CASEWHENGRANT-------------------------------------------------------------------------------- grant ON COMMIT REFRESH on XDB.XS$ROLESETS to DBA; grant UPDATE on XDB.XS$ROLESETS to DBA; grant SELECT on XDB.XS$ROLESETS to DBA; grant INSERT on XDB.XS$ROLESETS to DBA; grant DELETE on XDB.XS$ROLESETS to DBA; grant ALTER on XDB.XS$ROLESETS to DBA; grant FLASHBACK on XDB.XS$SECURITYCLASS to DBA; grant DEBUG on XDB.XS$SECURITYCLASS to DBA; grant QUERY REWRITE on XDB.XS$SECURITYCLASS to DBA; grant ON COMMIT REFRESH on XDB.XS$SECURITYCLASS to DBA; grant UPDATE on XDB.XS$SECURITYCLASS to DBA;'GRANT'||D.PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE||CASEWHENGRANT-------------------------------------------------------------------------------- grant SELECT on XDB.XS$SECURITYCLASS to DBA; grant INSERT on XDB.XS$SECURITYCLASS to DBA; grant DELETE on XDB.XS$SECURITYCLASS to DBA; grant ALTER on XDB.XS$SECURITYCLASS to DBA;已选择290行。SQL>

这个时候我们看到的密码是一个加密的状态（MD5加密算法）