mysql select_catalog_role_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