0 创建测试用户
create user soctt identified by 11;
grant dba to scott;create user one identified by 11;
1 角色role
-- 查询所有角色, connect/resource/dba比较常见
select * from dba_roles; -- 不存在user_roles和all_roles-- grantee-role: dba
select * from user_role_privs;
select * from dba_role_privs where grantee = 'SCOTT';-- 此时还没有role
select * from dba_role_privs where grantee = 'ONE';
2 权限privilege
- system_privilege和table_privilege
-- [create | alter | drop ..] any [table |view | index | trigger | procedure..]
-- [select | update | detele] and table
select * from system_privilege_map order by name; -- 系统权限共208个-- create alter select update delete execute ...
select * from table_privilege_map; -- 对象权限共26个-- 角色的系统权限和对象权限
select * from role_sys_privileges;
select * from role_tab_privileges;
- 授予、回收系统权限（user_sys_privs）
-- 下面使用scott管理one的权限
-- 报错：user ONE lacks CREATE SESSION privilege; logon denied
sqlplus one/22 -- 没有创建会话的权限grant create session to one; -- sqlplus可登录
revoke create session from one;-- 报错：权限不足
create table t1 ...grant create table to one;-- 报错：对表空间'SYSTEM'无权限
create table t1 ...-- select/update/delete都正常
grant UNLIMITED TABLESPACE to one; -- create table t1 ...正常-- 权限：create session, create table, unlimited tablespace
select * from user_sys_privs;
select * from dba_sys_privs where grantee = 'ONE';select * from dba_sys_privs where grantee = 'DBA' order by privilege;
- 授予、回收对象权限（user_tab_privs）
grant select, update on scott.t1 to one;
revoke update from scott.t1 from one;-- one可在scott.t1执行select/update
select * from scott.t1; -- one-- {grantee, owner, table_name, grantor, privilege}
select * from user_tab_privs; -- scott-- grantor/grantee分别是授予/被授予权限的用户
select * from dba_tab_privs where grantor = 'SCOTT';
select * from all_tab_privs where grantee = 'ONE';
参考：
https://www.cnblogs.com/lichuangblog/p/6892931.html
