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