1、数据库字典表
数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等
dictionary表：TABLE_NAME、COMMENTS --全部数据字典表的名称和解释，针对表级别
dict_column表：TABLE_NAME、COLUMN_NAME、COMMENTS --全部数据字典表里字段名称和解释 ，针对表字段级别
2、使用字典表
获取不确定的表名
select * from dictionary where instr(comments,'index')>0; --模糊查询从备注字段获取表名
select * from dictionary where instr(table_name,'USER_')>0; --模糊查询从表名字段获取表名（注意大写）
获取表中字段
select column_name,comments from dict_columns where table_name='USER_USERS'; --获取USER_USERS表中所有字段的描述，注意表名大写
desc USER_USERS; --获取表中字段详细信息指的是类型大小等，并非表字段描述信息，即不可以获取字段用途信息
3、常用的表：用户表
3.1获取相关所有用户表
SQL> select * from dictionary where instr(table_name,'USERS')>0; --注意大写
TABLE_NAME COMMENTS
------------------------------ --------------------------------------------------------------------------------
DBA_USERS Information about all users of the database
USER_USERS Information about the current user
ALL_USERS Information about all users of the database
V$PWFILE_USERS Synonym for V_$PWFILE_USERS
GV$PWFILE_USERS Synonym for GV_$PWFILE_USERS
3.2 USER_USERS --用户的用户信息表
#字段描述
SQL> select table_name,column_name,comments from dict_columns where table_name='USER_USERS';
TABLE_NAME COLUMN_NAME COMMENTS
---------- ------------------------------ --------------------------------------------------------------------------------
USER_USERS USERNAME Name of the user
USER_USERS USER_ID ID number of the user
USER_USERS ACCOUNT_STATUS
USER_USERS LOCK_DATE
USER_USERS EXPIRY_DATE
USER_USERS DEFAULT_TABLESPACE Default tablespace for data
USER_USERS TEMPORARY_TABLESPACE Default tablespace for temporary tables
USER_USERS CREATED User creation date
USER_USERS INITIAL_RSRC_CONSUMER_GROUP User's initial consumer group
USER_USERS EXTERNAL_NAME User external name
10 rows selected
#查询当用户表
SQL> select * from USER_USERS;
USERNAME USER_ID ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED INITIAL_RSRC_CONSUMER_GROUP EXTERNAL_NAME
-------- ------- -------------- --------- ----------- ------------------ -------------------- ----------- --------------------------- -------------
AMS 67 OPEN AMS TEMP 2014/9/17 2 DEFAULT_CONSUMER_GROUP
3.3 DBA_USERS --管理员用户表
SQL> select table_name,column_name,comments from dict_columns where table_name='DBA_USERS';
TABLE_NAME COLUMN_NAME COMMENTS
------------------------------ ------------------------------ --------------------------------------------------------------------------------
DBA_USERS USERNAME Name of the user
DBA_USERS USER_ID ID number of the user
DBA_USERS PASSWORD Encrypted password
DBA_USERS ACCOUNT_STATUS
DBA_USERS LOCK_DATE
DBA_USERS EXPIRY_DATE
DBA_USERS DEFAULT_TABLESPACE Default tablespace for data
DBA_USERS TEMPORARY_TABLESPACE Default tablespace for temporary tables
DBA_USERS CREATED User creation date
DBA_USERS PROFILE User resource profile name
DBA_USERS INITIAL_RSRC_CONSUMER_GROUP User's initial consumer group
DBA_USERS EXTERNAL_NAME User external name
12 rows selected
SQL> select * from DBA_USERS;
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE INITIAL_RSRC_CONSUMER_GROUP
------------------ ------- ----------------- -------------- ------------ ------------ ------------------ -------------------------------- --------- ------------------------
AMS 67 BD821F59270E5F34 OPEN AMS TEMP 2014/9/17 2 DEFAULT DEFAULT_CONSUMER_GROUP
THSH 66 8CE01814DCA8117D OPEN THSH TEMP 2014/9/17 2 DEFAULT DEFAULT_CONSUMER_GROUP
TH 65 D72066A3A758DB12 OPEN THSH TEMP 2014/9/17 2 DEFAULT DEFAULT_CONSUMER_GROUP
ROOT 61 DBE00A0A64628AB1 OPEN WWLROOTUSER TEMP 2014/8/21 1 DEFAULT DEFAULT_CONSUMER_GROUP
MDDATA 50 DF02A496267DEE66 EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 WWLROOTUSER TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
DIP 19 CE4A36B8E06CA59C EXPIRED & LOCKED 2014/7/22 1 WWLROOTUSER TEMP 2007/4/17 3 DEFAULT DEFAULT_CONSUMER_GROUP
TSMSYS 21 3DF26A8B17D0F29F EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 WWLROOTUSER TEMP 2007/4/17 3 DEFAULT DEFAULT_CONSUMER_GROUP
DBSNMP 24 BFB31E6E1671B9F5 OPEN SYSAUX TEMP 2007/4/17 3 MONITO.. DEFAULT_CONSUMER_GROUP
SYSMAN 51 A537AD64831F48A3 OPEN SYSAUX TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
MDSYS 46 72979A94BAD2AF80 EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 SYSAUX TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
ORDSYS 43 7EFA02EC7EA6B86F EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 SYSAUX TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
CTXSYS 36 71E687F036AD56E5 EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 SYSAUX TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
ANONYMOUS 39 anonymous EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 SYSAUX TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
EXFSYS 34 66F4EF5650C20355 EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 SYSAUX TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
DMSYS 35 BFBA5A553FD9E28A EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 SYSAUX TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
WMSYS 25 7C9BA362F8314299 EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 SYSAUX TEMP 2007/4/17 3 DEFAULT DEFAULT_CONSUMER_GROUP
XDB 38 88D8364765FCE6AF EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 SYSAUX TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
ORDPLUGINS 44 88A2B2C183431F00 EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 SYSAUX TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
SI_INFORMTN_SCHEMA 45 84B8CBCA4D477FA3 EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 SYSAUX TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
OLAPSYS 47 3FB8EF9DB538647C EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 SYSAUX TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
MGMT_VIEW 53 E558F30B9EBE5411 OPEN SYSTEM TEMP 2007/4/17 4 DEFAULT DEFAULT_CONSUMER_GROUP
SYS 0 D4C5016086B2DC6A OPEN SYSTEM TEMP 2007/4/17 3 DEFAULT SYS_GROUP
SYSTEM 5 D4DF7931AB130E37 OPEN SYSTEM TEMP 2007/4/17 3 DEFAULT SYS_GROUP
OUTLN 11 4A3BA55E08595C81 EXPIRED & LOCKED 2014/7/22 1 2014/7/22 1 SYSTEM TEMP 2007/4/17 3 DEFAULT DEFAULT_CONSUMER_GROUP
24 rows selected
3.4 ALL_USERS --所有用户表
SQL> select table_name,column_name,comments from dict_columns where table_name='ALL_USERS';
TABLE_NAME COLUMN_NAME COMMENTS
------------------------------ ------------------------------ --------------------------------------------------------------------------------
ALL_USERS USERNAME Name of the user
ALL_USERS USER_ID ID number of the user
ALL_USERS CREATED User creation date
SQL> select * from ALL_USERS;
USERNAME USER_ID CREATED
------------------------------ ---------- -----------
AMS 67 2014/9/17 2
THSH 66 2014/9/17 2
TH 65 2014/9/17 2
ROOT 61 2014/8/21 1
MGMT_VIEW 53 2007/4/17 4
MDDATA 50 2007/4/17 4
SYSMAN 51 2007/4/17 4
MDSYS 46 2007/4/17 4
SI_INFORMTN_SCHEMA 45 2007/4/17 4
ORDPLUGINS 44 2007/4/17 4
ORDSYS 43 2007/4/17 4
OLAPSYS 47 2007/4/17 4
ANONYMOUS 39 2007/4/17 4
XDB 38 2007/4/17 4
CTXSYS 36 2007/4/17 4
EXFSYS 34 2007/4/17 4
WMSYS 25 2007/4/17 3
DBSNMP 24 2007/4/17 3
TSMSYS 21 2007/4/17 3
DMSYS 35 2007/4/17 4
DIP 19 2007/4/17 3
OUTLN 11 2007/4/17 3
SYSTEM 5 2007/4/17 3
SYS 0 2007/4/17 3
24 rows selected
3.5 V$PWFILE_USER和GV$PWFILE_USERS - --当前用户权限表
SQL> select table_name,column_name,comments from dict_columns where table_name='V$PWFILE_USERS'; TABLE_NAME COLUMN_NAME COMMENTS ------------------------------ ------------------------------ -------------------------------------------------------------------------------- V$PWFILE_USERS USERNAME V$PWFILE_USERS SYSDBA V$PWFILE_USERS SYSOPER SQL> select * from V$PWFILE_USERS; USERNAME SYSDBA SYSOPER ------------------------------ ------ ------- SYS TRUE TRUE
--当前用户拥有sysdba和sysoper系统权限
4. 角色权限表
4.1 获取相关角色权限表
SQL> select * from dictionary where instr(table_name,'ROLE')>0; TABLE_NAME COMMENTS ------------------------------ -------------------------------------------------------------------------------- DBA_CONNECT_ROLE_GRANTEES Information regarding which users are granted CONNECT DBA_ROLES All Roles which exist in the database DBA_ROLE_PRIVS Roles granted to users and roles USER_ROLE_PRIVS Roles granted to current user ROLE_ROLE_PRIVS Roles which are granted to roles ROLE_SYS_PRIVS System privileges granted to roles ROLE_TAB_PRIVS Table privileges granted to roles
USER_TAB_PRIVS 用户对象权限 SESSION_ROLES Roles which the user currently has enabled. 8 rows selected
4.2 DBA_ROLES --所有角色
#角色就是具有某种权利的对象，类似于一个用户
#权限授予时，只需将对应role授予指定用户即可
grant connect,resource,dba to ams;
SQL> select table_name,column_name,comments from dict_columns where table_name='DBA_ROLES';
TABLE_NAME COLUMN_NAME COMMENTS
------------------------------ ------------------------------ --------------------------------------------------------------------------------
DBA_ROLES ROLE Role Name
DBA_ROLES PASSWORD_REQUIRED Indicates if the role requires a password to be enabled
SQL> select * from DBA_ROLES; ROLE PASSWORD_REQUIRED ------------------------------ ----------------- CONNECT NO RESOURCE NO DBA NO SELECT_CATALOG_ROLE NO EXECUTE_CATALOG_ROLE NO DELETE_CATALOG_ROLE NO EXP_FULL_DATABASE NO IMP_FULL_DATABASE NO RECOVERY_CATALOG_OWNER NO GATHER_SYSTEM_STATISTICS NO LOGSTDBY_ADMINISTRATOR NO AQ_ADMINISTRATOR_ROLE NO AQ_USER_ROLE NO GLOBAL_AQ_USER_ROLE GLOBAL SCHEDULER_ADMIN NO HS_ADMIN_ROLE NO AUTHENTICATEDUSER NO OEM_ADVISOR NO OEM_MONITOR NO WM_ADMIN_ROLE NO JAVAUSERPRIV NO JAVAIDPRIV NO JAVASYSPRIV NO JAVADEBUGPRIV NO EJBCLIENT NO JAVA_ADMIN NO JAVA_DEPLOY NO CTXAPP NO XDBADMIN NO XDBWEBSERVICES NO OLAP_DBA NO OLAP_USER NO MGMT_USER NO 33 rows selected
4.3 USER_ROLE_PRIVS --当前用户拥有角色权限
SQL> select table_name,column_name,comments from dict_columns where table_name='USER_ROLE_PRIVS'; TABLE_NAME COLUMN_NAME COMMENTS ------------------------------ ------------------------------ -------------------------------------------------------------------------------- USER_ROLE_PRIVS USERNAME User Name or PUBLIC USER_ROLE_PRIVS GRANTED_ROLE Granted role name USER_ROLE_PRIVS ADMIN_OPTION Grant was with the ADMIN option USER_ROLE_PRIVS DEFAULT_ROLE Role is designated as a DEFAULT ROLE for the user USER_ROLE_PRIVS OS_GRANTED Role is granted via the operating system (using OS_ROLES = TRUE) SQL> select * from USER_ROLE_PRIVS; USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED ------------------------------ ------------------------------ ------------ ------------ ---------- AMS CONNECT NO YES NO AMS DBA NO YES NO AMS RESOURCE NO YES NO
4.4 USER_TAB_PRIVS --当前用户对象权限
SQL> select table_name,column_name,comments from dict_columns where table_name='USER_TAB_PRIVS'; TABLE_NAME COLUMN_NAME COMMENTS ------------------------------ ------------------------------ -------------------------------------------------------------------------------- USER_TAB_PRIVS GRANTEE Name of the user to whom access was granted USER_TAB_PRIVS OWNER Owner of the object USER_TAB_PRIVS TABLE_NAME Name of the object USER_TAB_PRIVS GRANTOR Name of the user who performed the grant USER_TAB_PRIVS PRIVILEGE Table Privilege USER_TAB_PRIVS GRANTABLE Privilege is grantable USER_TAB_PRIVS HIERARCHY Privilege is with hierarchy option 7 rows selected
#THSH用户将部分表的查询权限授予用户AMS SQL> select * from USER_TAB_PRIVS; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY ---------------------------- ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- --- AMS THSH MK_MAN THSH UPDATE NO NO AMS THSH MK_MAN THSH INSERT NO NO AMS THSH MK_MAN THSH DELETE NO NO AMS THSH MK_MAN THSH SELECT NO NO AMS THSH ORG_MK THSH SELECT NO NO AMS THSH AUTH_APP_ADMIN THSH SELECT NO NO AMS THSH AUTH_APP_INFO THSH SELECT NO NO AMS THSH AUTH_AREA THSH SELECT NO NO AMS THSH AUTH_FUNCTION THSH SELECT NO NO AMS THSH AUTH_GRP_ADMIN THSH SELECT NO NO AMS THSH AUTH_GRP_INFO THSH SELECT NO NO AMS THSH AUTH_GRP_USER THSH SELECT NO NO AMS THSH AUTH_ONLINE THSH SELECT NO NO AMS THSH AUTH_ORG_INFO THSH SELECT NO NO AMS THSH AUTH_PART_ORG THSH SELECT NO NO AMS THSH AUTH_PCI THSH SELECT NO NO AMS THSH AUTH_POST THSH SELECT NO NO AMS THSH AUTH_ROLE THSH SELECT NO NO AMS THSH AUTH_ROLE_FUNCTION THSH SELECT NO NO AMS THSH AUTH_SCOPE THSH SELECT NO NO AMS THSH AUTH_USERGRP THSH SELECT NO NO AMS THSH AUTH_USER_INFO THSH SELECT NO NO AMS THSH AUTH_USER_ROLE THSH SELECT NO NO 23 rows selected
总结：
角色不是用户，角色代表一种权限
dba是具有某种权限的角色，而SYS拥有了角色dba的权限即SYS用户就是管理员
dba_users与all_users不同：前者是具体的用户状态信息（是否可用、登录密码等），后者是用户名称及创建时间
常用命令：
当前用户级别命令：
select * from user_tables; --查询当前用户所有表
select * from user_users; --当前用户信息状态
select * from V$PWFILE_USERS;--查看是否拥有sysdba或sysoper系统权限
select * from user_role_privs;或者 select * from session_privs;--查询当前用户具有的角色权限
select * from user_tab_privs; --查询当前用户对象权限
select * from user_sys_privs; --查询用户系统权限
系统管理员：
grant dba,resource,connect to UserName; --将dba,resource,connect角色权限授予指定用户
select * from user_role_privs;或者 select * from session_privs;--查询当前用户具有的角色权限
select * from dba_tab_privs where grantee='USERNAME'; --查询指定用户对象权限
select * from dba_role_privs where grantee='USERNAME'; --查询指定用户角色权限
select * from dba_sys_privs where grantee='USERNAME'; --从系统权限表中获取指定用户权限
select * from all_users; --所有用户信息
select * from dba_privs;--获取数据库所有角色权限
注意：字典表针对的不是用户自己创建的表
1958
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
