user$

以下脚本可以用于收集数据库中用户的角色和表空间等信息(user_role_tbs.sql):

SET pagesize 50  linesize 115
REM
COLUMN username                 format a10 heading User
COLUMN default_tablespace       format a12 heading Default
COLUMN temporary_tablespace     format a12 heading Temporary
COLUMN granted_role             format a25 heading Roles
COLUMN default_role             format a10 heading Default?
COLUMN admin_option             format a7  heading Admin?
COLUMN profile                  format a12 heading Profile
REM
BREAK on username skip 1 on account_status on default_tablespace on temporary_tablespace on profile
REM
SELECT username,
       default_tablespace,
       temporary_tablespace,
       profile,
       granted_role,
       admin_option,
       default_role
  FROM sys.dba_users a, sys.dba_role_privs b
 WHERE a.username = b.grantee
 ORDER BY username,
          default_tablespace,
          temporary_tablespace,
          profile,
          granted_role;
REM
SET termout on flush on feedback on verify on
CLEAR columns
CLEAR breaks

Sample Output:

User       Default      Temporary    Profile      Roles                     Admin?  Default?
---------- ------------ ------------ ------------ ------------------------- ------- ----------
SCOTT      USERS        TEMP         DEFAULT      CONNECT                   NO      YES
                                                  RESOURCE                  NO      YES
SYS        SYSTEM       TEMP         DEFAULT      AQ_ADMINISTRATOR_ROLE     YES     YES
                                                  AQ_USER_ROLE              YES     YES
                                                  CONNECT                   YES     YES
                                                  DBA                       YES     YES
                                                  DELETE_CATALOG_ROLE       YES     YES
                                                  EXECUTE_CATALOG_ROLE      YES     YES
                                                  EXP_FULL_DATABASE         YES     YES
                                                  HS_ADMIN_ROLE             YES     YES
                                                  IMP_FULL_DATABASE         YES     YES
                                                  OEM_MONITOR               YES     YES
                                                  RECOVERY_CATALOG_OWNER    YES     YES
                                                  RESOURCE                  YES     YES
                                                  SELECT_CATALOG_ROLE       YES     YES

SYSTEM     SYSTEM       TEMP         DEFAULT      AQ_ADMINISTRATOR_ROLE     YES     YES
                                                  DBA                       YES     YES

Restoring a user's original password

WARNING
=======

The method described in this article is not officially supported by development,
therefore when you get errors using this procedure support cannot file a bug for it.
Please make no mistakes when setting the HEX value with the VALUES keyword as bad
values may cause internal errors. This undocumented feature exists to support
export / import and retain the original passwords.

Problem Description:
====================

How can you restore a users original password ?

Solution Description:
=====================
With the output of dba_users you can restore a user's old password by
issuing the following:

   alter user <name> identified by values '<original_encrypted_password>';

For example:
============
SQL> create user test identified by test
  2  default tablespace users temporary tablespace temp;

User created.

SQL> select username, password from dba_users where username= 'TEST';

USERNAME   PASSWORD
---------- ------------------------------
TEST       CDC423BEB32B1812

SQL> alter user test identified by test1;
User altered.

SQL> select username, password from dba_users where username= 'TEST';

USERNAME   PASSWORD
---------- ------------------------------
TEST       22F2E341BF4B8764

SQL> alter user test identified by values 'CDC423BEB32B1812';
User altered.

SQL> connect test/test
Connected.

Solution Explanation:
=====================
To do this you would need a spool of dba_users taken before the password was
changed.

Script:收集数据库中用户的角色和表空间等信息

Restoring a user's original password