Oracle管理中常用的SQL语句(用户信息)

用户信息

SELECT username
    , user_id
    , password
    , account_status
    , default_tablespace
    , temporary_tablespace
    , created
FROM dba_users

col username form a20
col default_tablespace form a20
col temporary_tablespace form a20
col password form a20
col account_status form a30

SELECT username "User(s) with Default Password!"
FROM dba_users
WHERE password IN (
  'E066D214D5421CCC', -- dbsnmp
  '24ABAB8B06281B4C', -- ctxsys
  '72979A94BAD2AF80', -- mdsys
  'C252E8FA117AF049', -- odm
  'A7A32CD03D3CE8D5', -- odm_mtr
  '88A2B2C183431F00', -- ordplugins
  '7EFA02EC7EA6B86F', -- ordsys
  '4A3BA55E08595C81', -- outln
  'F894844C34402B67', -- scott
  '3F9FBD883D787341', -- wk_proxy
  '79DF7A1BD138CF11', -- wk_sys
  '7C9BA362F8314299', -- wmsys
  '88D8364765FCE6AF', -- xdb
  'F9DA8977092B7B81', -- tracesvr
  '9300C0977D7DC75E', -- oas_public
  'A97282CE3D94E29E', -- websys
  'AC9700FD3F1410EB', -- lbacsys
  'E7B5D92911C831E1', -- rman
  'AC98877DE1297365', -- perfstat
  '66F4EF5650C20355', -- exfsys
  '84B8CBCA4D477FA3', -- si_informtn_schema
  'D4C5016086B2DC6A', -- sys
  'D4DF7931AB130E37'  -- system
)

确认用户角色

SELECT granted_role
    , admin_option
    , default_role
FROM dba_role_privs
WHERE grantee='SCOTT'

确认用户的系统权限

SELECT privilege
    , admin_option
FROM dba_sys_privs
WHERE grantee='SCOTT'

SELECT privilege
    , admin_option
FROM dba_sys_privs
WHERE grantee='SYS'
UNION
SELECT DECODE(sysdba, 'TRUE', 'SYSDBA', NULL)
    , 'YES'
FROM v$pwfile_users
WHERE username='SYS'
  AND sysdba='TRUE'
UNION
SELECT DECODE(sysoper, 'TRUE', 'SYSOPER', NULL)
    , 'YES'
FROM v$pwfile_users
WHERE username='SYS'
  AND sysoper='TRUE'

确认用户的对象权限

SELECT privilege priv
    , owner||'.'||table_name table_name
	 , grantee
	 , grantable
FROM dba_tab_privs
WHERE owner='SCOTT'