Oracle用户/角色/权限关系概览

Oracle用户+角色+权限

Oracle用户分为两大类：系统创建用户和手动创建用户；

Oracle角色分为两大类：系统创建角色和手动创建角色；
Oracle权限主要分为系统权限和对象权限，其中对象权限又分为表/视图级别对象权限和列级对象权限；

--系统用户查询所有
select * from dba_users;        --用户列表
select * from dba_roles;        --角色列表（独有）
select * from dba_role_privs;   --所有用户角色权限
select * from dba_sys_privs;    --所有用户和角色被赋于的系统权限
select * from dba_tab_privs;    --所有用户和角色被赋于的表/视图级对象权限
select * from dba_col_privs;    --所有用户和角色被赋于的列级对象权限

--系统角色相关权限
select * from role_tab_privs;   --角色对象权限    
select * from role_sys_privs ;  --角色系统权限
select * from role_role_privs;  --角色与角色权限

--当前登录用户查询用户
select * from user_users ;      --当前用户
select * from user_role_privs;  --当前用户角色权限
select * from user_sys_privs;   --当前用户系统权限
select * from user_tab_privs;   --当前用户表/视图级对象权限
select * from user_col_privs;   --当前用户列级对象权限

Grant用法大全

Grant 表格操作权限

GRANT privilege-type ON { table-Name | view-Name } TO grantees;
其中：
privilege-types
  ALL PRIVILEGES |
  privilege-list
privilege-list
 table-privilege {, table-privilege }*
table-privilege
  DELETE |
  INSERT |
  REFERENCES [column list] |
  SELECT [column list] |
  TRIGGER |
  UPDATE [column list]
column list
  ( column-identifier {, column-identifier}* )
  
 grantees表示被授权用户或角色名称列表,多个以英文逗号隔开,以下类似。
 Example:
 GRANT update(a1) on test.a8 to lis;
 GRANT SELECT ON  test.a8 to PUBLIC;
 GRANT UPDATE ON test.a8 TO PUBLIC,lis ;

Grant 执行权限

GRANT EXECUTE ON  routine-designator TO grantees;
其中：routine-designator表示函数或存储过程名
Example:
GRANT EXECUTE ON p TO george ;

Grant 使用序列权限(11G测试失败)

GRANT USE ON SEQUENCE [ schemaName. ] SQL92Identifier TO grantees;
Example:
GRANT USAGE ON SEQUENCE order_id TO sales_role;

Grant 使用自定义类型权限(11G测试失败)

GRANT USAGE ON TYPE [ schemaName. ] SQL92Identifier TO grantees;
Example:
GRANT USAGE ON TYPE price TO finance_role;

Grant 角色权限

GRANT roleName [ {, roleName }* ] TO grantees;
Example:
GRANT purchases_reader_role TO george,maria;

Revoke用法大全

Revoke 表格操作权限

REVOKE  privilege-type ON { table-Name | view-Name } FROM  grantees;
其中：
privilege-types
  ALL PRIVILEGES |
  privilege-list
privilege-list
 table-privilege {, table-privilege }*
table-privilege
  DELETE |
  INSERT |
  REFERENCES [column list] |
  SELECT [column list] |
  TRIGGER |
  UPDATE [column list]
column list
  ( column-identifier {, column-identifier}* )
  
 grantees表示被授权用户或角色名称列表,多个以英文逗号隔开,以下类似。
 Example:
 REVOKE update(a1) on test.a8 FROM  lis;
 REVOKE SELECT ON  test.a8 FROM PUBLIC;
 REVOKE UPDATE ON test.a8 FROM PUBLIC,lis ;

Revoke 执行权限

REVOKE EXECUTE ON  routine-designator FROM  grantees RESTRICT;
其中：routine-designator表示函数或存储过程名
Example:
REVOKE EXECUTE ON p FROM  george ;

Revoke 使用序列权限(11G测试失败)

REVOKE USAGE ON SEQUENCE [ schemaName. ] SQL92Identifier FROM  grantees RESTRICT;
Example:
REVOKE USAGE ON SEQUENCE order_id FROM  sales_role;

Revoke 使用自定义类型权限(11G测试失败)

REVOKE USAGE ON TYPE [ schemaName. ] SQL92Identifier FROM  grantees RESTRICT;
Example:
REVOKE USAGE ON TYPE price FROM  finance_role;

Revoke 角色权限

REVOKE roleName [ {, roleName }* ] FROM  grantees;
Example:
REVOKE purchases_reader_role FROM  george,maria;