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

以下脚本可以用于收集数据库中用户的角色和表空间等信息(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