Oracle用户及角色的权限管理[Oracle基础]
来源:互联网 发布:erp软件系统 编辑:程序博客网 时间:2024/06/03 19:38
1.查看所有用户:
select * from dba_users;
select * from all_users;
select * from user_users;
2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限):
select * from dba_sys_privs;
select * from user_sys_privs;
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;
4.查看用户对象权限:
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
5.查看所有角色:
select * from dba_roles;
6.查看用户或角色所拥有的角色:
select * from dba_role_privs;
select * from user_role_privs;
7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
SQL> select * from dba_role_privs where grantee='CX_ZJ_ROS'; -------------用户所拥有的角色
GRANTEE GRANTED_ROLE ADM DEF
---------- --------------- ----- ----
CX_ZJ_ROS ZHRO NO YES
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='ZHRO'; --------这里查询的是用户和自定义角色所拥有的权限
GRANTEE PRIVILEGE ADM
-------- ------------ -------------------
ZHRO CREATE SEQUENCE NO
ZHRO CREATE SESSION NO
ZHRO CREATE TABLE NO
ZHRO UNLIMITED TABLESPACE NO
-------这里查询的是系统角色所拥有的权限
ROLE PRIVILEGE ADM
--------- ---------------- ----------
CONNECT CREATE SESSION NO
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='ILOG_RTS';
GRANTEE PRIVILEGE ADM
--------- --------------------- -------------
ILOG_RTS UNLIMITED TABLESPACE NO
SQL> select * from dba_role_privs where grantee='ILOG_RTS';
GRANTEE GRANTED_ROLE ADM DEF
---------- ------------- --- ---
ILOG_RTS CONNECT NO YES
ILOG_RTS RESOURCE NO YES
select * from V$PWFILE_USERS
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
DBA_SCHEDULER_JOB_ROLES All scheduler jobs in the database by database role
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
SESSION_ROLES Roles which the user currently has enabled.
TABLE_NAME COMMENTS
------------------- -------------------------------------
DBA_AQ_AGENT_PRIVS
DBA_COL_PRIVS All grants on columns in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_RSRC_CONSUMER_GROUP_PRIVS Switch privileges for consumer groups
DBA_RSRC_MANAGER_SYSTEM_PRIVS system privileges for the resource manager
DBA_SYS_PRIVS System privileges granted to users and roles
DBA_TAB_PRIVS All grants on objects in the database
USER_COL_PRIVS Grants on columns for which the user is the owner, grantor or grantee
USER_COL_PRIVS_MADE All grants on columns of objects owned by the user
USER_COL_PRIVS_RECD Grants on columns for which the user is the grantee
USER_ROLE_PRIVS Roles granted to current user
USER_RSRC_CONSUMER_GROUP_PRIVS Switch privileges for consumer groups for the user
USER_RSRC_MANAGER_SYSTEM_PRIVS system privileges for the resource manager for the user
USER_SYS_PRIVS System privileges granted to current user
USER_TAB_PRIVS Grants on objects for which the user is
the owner, grantor or grantee
USER_TAB_PRIVS_MADE All grants on objects owned by the user
USER_TAB_PRIVS_RECD Grants on objects for which the user is the grantee
ALL_COL_PRIVS Grants on columns for which the user is
the grantor, grantee, owner,or an enabled role or PUBLIC is the grantee
ALL_COL_PRIVS_MADE Grants on columns for which the user is owner or grantor
ALL_COL_PRIVS_RECD Grants on columns for which the user, PUBLIC or enabled role is the grantee
ALL_TAB_PRIVS Grants on objects for which the user is the grantor, grantee,
owner,or an enabled role or PUBLIC is the grantee
ALL_TAB_PRIVS_MADE User's grants and grants on user's objects
ALL_TAB_PRIVS_RECD Grants on objects for which the user, PUBLIC or enabled role is the grantee
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
SESSION_PRIVS Privileges which the user currently hasset
GV$ENABLEDPRIVS Synonym for GV_$ENABLEDPRIVS
V$ENABLEDPRIVS Synonym for V_$ENABLEDPRIVS
set linesize 120
col username for a20
col ACCOUNT_STATUS for a30
col CREATED for a30
set pagesize 600
col DEFAULT_TABLESPACE for a30
select username,ACCOUNT_STATUS,CREATED,DEFAULT_TABLESPACE from dba_users order by CREATED,ACCOUNT_STATUS;
col GRANTEE for a30
col GRANTED_ROLE for a30
col ADMIN_OPTION for a20
col DEFAULT_ROLE for a20
-------------这里查询的是用户角色所拥有的角色
select * from dba_role_privs where grantee in (select username from dba_users where username not in ('SYS','SYSTEM') AND ACCOUNT_STATUS='OPEN') order by GRANTEE,GRANTED_ROLE;
-------------这里查询的是用户和自定义角色所拥有的权限
select distinct GRANTEE,PRIVILEGE,ADMIN_OPTION from (SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE from dba_role_privs where grantee in (select username from dba_users where username not in ('SYS','SYSTEM') AND ACCOUNT_STATUS='OPEN')) union SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE in (select username from dba_users where username not in ('SYS','SYSTEM') AND ACCOUNT_STATUS='OPEN')) order by GRANTEE,PRIVILEGE;
select * from dba_users;
select * from all_users;
select * from user_users;
2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限):
select * from dba_sys_privs;
select * from user_sys_privs;
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;
4.查看用户对象权限:
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
5.查看所有角色:
select * from dba_roles;
6.查看用户或角色所拥有的角色:
select * from dba_role_privs;
select * from user_role_privs;
7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
SQL> select * from dba_role_privs where grantee='CX_ZJ_ROS'; -------------用户所拥有的角色
GRANTEE GRANTED_ROLE ADM DEF
---------- --------------- ----- ----
CX_ZJ_ROS ZHRO NO YES
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='ZHRO'; --------这里查询的是用户和自定义角色所拥有的权限
GRANTEE PRIVILEGE ADM
-------- ------------ -------------------
ZHRO CREATE SEQUENCE NO
ZHRO CREATE SESSION NO
ZHRO CREATE TABLE NO
ZHRO UNLIMITED TABLESPACE NO
5 rows selected.
------------这里的UNLIMITED TABLESPACE权限其实是不能通过角色的方式授予的
SQL> select * from role_sys_privs where role='CONNECT';-------这里查询的是系统角色所拥有的权限
ROLE PRIVILEGE ADM
--------- ---------------- ----------
CONNECT CREATE SESSION NO
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='ILOG_RTS';
GRANTEE PRIVILEGE ADM
--------- --------------------- -------------
ILOG_RTS UNLIMITED TABLESPACE NO
SQL> select * from dba_role_privs where grantee='ILOG_RTS';
GRANTEE GRANTED_ROLE ADM DEF
---------- ------------- --- ---
ILOG_RTS CONNECT NO YES
ILOG_RTS RESOURCE NO YES
select * from V$PWFILE_USERS
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
DBA_SCHEDULER_JOB_ROLES All scheduler jobs in the database by database role
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
SESSION_ROLES Roles which the user currently has enabled.
TABLE_NAME COMMENTS
------------------- -------------------------------------
DBA_AQ_AGENT_PRIVS
DBA_COL_PRIVS All grants on columns in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_RSRC_CONSUMER_GROUP_PRIVS Switch privileges for consumer groups
DBA_RSRC_MANAGER_SYSTEM_PRIVS system privileges for the resource manager
DBA_SYS_PRIVS System privileges granted to users and roles
DBA_TAB_PRIVS All grants on objects in the database
USER_COL_PRIVS Grants on columns for which the user is the owner, grantor or grantee
USER_COL_PRIVS_MADE All grants on columns of objects owned by the user
USER_COL_PRIVS_RECD Grants on columns for which the user is the grantee
USER_ROLE_PRIVS Roles granted to current user
USER_RSRC_CONSUMER_GROUP_PRIVS Switch privileges for consumer groups for the user
USER_RSRC_MANAGER_SYSTEM_PRIVS system privileges for the resource manager for the user
USER_SYS_PRIVS System privileges granted to current user
USER_TAB_PRIVS Grants on objects for which the user is
the owner, grantor or grantee
USER_TAB_PRIVS_MADE All grants on objects owned by the user
USER_TAB_PRIVS_RECD Grants on objects for which the user is the grantee
ALL_COL_PRIVS Grants on columns for which the user is
the grantor, grantee, owner,or an enabled role or PUBLIC is the grantee
ALL_COL_PRIVS_MADE Grants on columns for which the user is owner or grantor
ALL_COL_PRIVS_RECD Grants on columns for which the user, PUBLIC or enabled role is the grantee
ALL_TAB_PRIVS Grants on objects for which the user is the grantor, grantee,
owner,or an enabled role or PUBLIC is the grantee
ALL_TAB_PRIVS_MADE User's grants and grants on user's objects
ALL_TAB_PRIVS_RECD Grants on objects for which the user, PUBLIC or enabled role is the grantee
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
SESSION_PRIVS Privileges which the user currently hasset
GV$ENABLEDPRIVS Synonym for GV_$ENABLEDPRIVS
V$ENABLEDPRIVS Synonym for V_$ENABLEDPRIVS
set linesize 120
col username for a20
col ACCOUNT_STATUS for a30
col CREATED for a30
set pagesize 600
col DEFAULT_TABLESPACE for a30
select username,ACCOUNT_STATUS,CREATED,DEFAULT_TABLESPACE from dba_users order by CREATED,ACCOUNT_STATUS;
col GRANTEE for a30
col GRANTED_ROLE for a30
col ADMIN_OPTION for a20
col DEFAULT_ROLE for a20
-------------这里查询的是用户角色所拥有的角色
select * from dba_role_privs where grantee in (select username from dba_users where username not in ('SYS','SYSTEM') AND ACCOUNT_STATUS='OPEN') order by GRANTEE,GRANTED_ROLE;
-------------这里查询的是用户和自定义角色所拥有的权限
select distinct GRANTEE,PRIVILEGE,ADMIN_OPTION from (SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE from dba_role_privs where grantee in (select username from dba_users where username not in ('SYS','SYSTEM') AND ACCOUNT_STATUS='OPEN')) union SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE in (select username from dba_users where username not in ('SYS','SYSTEM') AND ACCOUNT_STATUS='OPEN')) order by GRANTEE,PRIVILEGE;
0 0
- Oracle用户及角色的权限管理[Oracle基础]
- ORACLE创建用户、角色及权限管理
- Oracle用户、权限及角色管理
- Oracle数据库的权限、用户、角色管理
- Oracle数据库的权限、用户、角色管理
- oracle 中的用户,权限,角色 , 的管理
- Oracle用户角色权限管理
- Oracle用户、权限、角色管理
- Oracle用户角色权限管理
- Oracle用户、权限、角色管理
- Oracle用户、权限、角色管理
- Oracle用户、权限、角色管理
- Oracle用户、权限、角色管理
- Oracle用户、权限、角色管理
- Oracle用户、权限、角色管理
- Oracle用户、角色、权限管理
- Oracle用户、权限、角色管理
- oracle用户、权限、角色管理
- 【求助】一道考验脑细胞的编程题
- java 编写的最大优先级队列
- java架构师—电商 薪资范围:20000-29999元/月
- 汇编:控制台下的几种显示输出方式
- 左值和右值
- Oracle用户及角色的权限管理[Oracle基础]
- 华为OJ_竞赛_单词博弈:最长递增子序列长度
- 赵雅智_ListView
- Struts2中的Action配合jQuery和Ajax操作
- hdu-2586 Y2K Accounting Bug
- ngnix 正向代理配置文件
- 收藏一个架构博客
- OpenCV 自作绘图库函数
- 泡汤