LLL的数据库培训-22-第二部分—Oracle基础知识培训—第14讲—权限管理（系统权限+对象权限）

LLL的数据库培训—第二部分—Oracle基础知识培训—第14讲—权限管理（系统权限+对象权限）

一、 权限的分类

权限是用来执行某些特定SQL语句的权力（能力）

1、系统权限:

允许用户执行特定的数据库动作，如创建表、创建索引、连接实例等。

2、对象权限:

允许用户操纵一些特定的对象，如读取视图，可更新某些列、执行存储过程等 。

3、直接权限（第15讲中介绍）

4、角色权限（第15讲中介绍）

二、系统权限

1、DBA的权限

数据库管理员(DBA)是数据库系统中的最高级别的用户，DBA具有数据库系统中的一切系统限并拥有所有的系统资源。

DBA可以把这些权限的一些或全部授予其他的用户，也可以把这些系统资的使用权授予其他的用户。

2、各版本的系统权限种类数量

select count(*) from system_privilege_map;

Oracle 8：80多种系统权限
Oracle 8i：120多种系统权限
Oracle 9i：100多种
Oracle 10g：100多种
Oracle 11g：207种
Oracle 12C：?
Oracle 19C：?

3、常用的系统权限 

（1）有关用户的系统权限如下

CREAT USER：             创建其他的用户(需要具有DBA角色的权限)
ALTER USER：             修改其他用户的设置
DROP USER：              删除其他的用户

（2）有关表的系统权限如下

SELECT ANY TABLE：       查询任何用户的表中的数据和视图中的数据的权力。
UPDATE ANY TABLE：       修改任何用户的表中的数据和视图中的数据的权力
DELETE ANY TABLE：       删除任何用户表中的数据和视图中的数据的权力
CREATE ANY TABLE：       在任何模式中创建表
DROP ANY TABLE：         删除任何模式中所创建的表
ALTER ANY TABLE：        修改任何模式中所创建的表
CREATE TABLE：           在用户自己的模式中创建表。

（3）有关表空间的系统权限如下

CREATE TABLESPACE：      创建表空间的权限
DROP TABLESPACE：        删除表空间的权限
ALTER TABLESPACE:        修改表空间的权限
UNLIMITED TABLESPACE:    使用全部表空间的权限。

（4）有关索引的系统权限如下

CREATE ANY INDEX:        在任何模式中创建索引的权限。
DROP ANY INDEX:          在任何模式中删除索引的权限。
ALTER ANY INDEX:         在任何模式中修改索引的权限

（5）有关会话的系统权限如下

CREATE SESSION:          连接数据库的权限。
ALTER SESSION:           发出 ALTER SESSION语句的权限

（6）其他的系统权限如下

CREATE VIEW:             在用户自己的模式中创建视图的权限
CREATE SEQUENCE:         在用户自己的模式中创建序列号的权限
CREATE PROCEDURE:        在用户自己的模式中创建过程的权限

提示:

在以上的系统权限中,ANY关键字表示在任何模式(用户)中都有所定义的权限

（7）特殊系统权限

另外,在 Oracle数据库系统中有两个特殊的系统权限,它们是 SYSOPER和 SYSDBA系统权限。

SYSOPER系统权限所包括的授权操作如下:

STARTUP                            启动数据库服务
SHUTDOWN                           关闭数据库服务
ALTER DATABASE OPEN MOUNT BACKUP   备份数据库
ARCHIVELOG、RECOVERY               恢复
CREATE SPFILE                      创建参数文件
RESTRICTED SESSION

--SYSDBA系统权限所包括的授权操作如下:

WITH ADMIN OPTION                  --授予其他用户可以将当前角色授予给其他人的权限
CREATE DATABASE                    --创建数据库
ALTER TABLESPACE BEGIN/END BACKUP  --联机备份
RECOVER DATABASE UNTIL.            --恢复数据库

注意:

在这里需要注意的是，只有在系统维护时才使用 SYSOPER或系统权限连接数据库系统，一般的操作都应该使用普通用户登录，连接这样做的目的是:万一操作失误了，不至于发生灭顶之灾。

提示:

在 Oracle 11g和 Oracle 12c中，新增加了 SYSBACKUP、 SYSDGSYSKM和几个系统管理员权限。
这些权限分别负责备份与恢复以及其他的特殊管理工作，以前都是以 SYSDBA权限来完成这些工作的。
将它们从SYSDBA中分离出来，会使 Oracle系统更安全。

4、 授予系统权限语法

grant privilege [, privilege...] to user [, user| role, public...] [with admin option];

选项：

public：            所有用户
with admin option： 使用户同样具有分配权限的权利，可将此权限授予别人授予系统权限
conn /as sysdba;

5、授权系统权限给用户案例

grant create session,create table to llltest;
grant create session to llltest with admin option;
grant execute any procedure to llltest;
grant dba to public                     --不能做，非常危险
revoke dba from public                  --不能做，非常危险

6、 使用系统权限

--使用llltest具有创建会话、创建表

create user llltest identified by llltest;
grant create session,connect to llltest;
grant unlimited tablespace to llltest;
conn llltest/llltest;
create table llltestt1 as select * from user_tables;

7、 显示系统权限

dba_sys_privs                         --针对所有用户被授予的系统权限
user_sys_privs                        --针对当前登陆用户被授予的系统权限

（1）查看所有系统权限：

select * from system_privilege_map;

（2）显示用户所具有的系统权限：

select * from dba_sys_privs;

（3）显示当前用户所具有的系统权限：

select * from user_sys_privs;

（4）显示当前会话所具有的系统权限：

select * from session_privs;

（5）在sys用户下查llltest用户的权限

--单个用户查询

select * from dba_sys_privs where grantee='LLLTEST' ;

--多个用户查询

select * from dba_sys_privs where grantee in ('LLLTEST','SYSTEM') ;
select * from dba_sys_privs where grantee in ('LLLTEST','SYSTEM') order by grantee ;

8、收回系统权限

revoke create table from llltest

注意：

对于使用with admin option 为某个用户授予系统权限，那么对于被这个用户授予相同权限的所有用户来说，取消该用户的系统权限并不会级联取消这些用户的相同权限

三、对象权限

不同的对象具有不同的对象权限

对象的拥有者拥有所有对象权限

对象的拥有者可以向外分配对象权限

1、oracle一共有8种对象权限

2、对象授权

（1）语法

grant object_priv｜ all [(columns)]  on object to {user|role|public}  [with grant option];

all：               所有对象权限
public：            授给所有的用户
with grant option： 允许用户再次给其它用户授权

（2）授权案例：

conn /as sysdba;
grant select on scott.emp to test3;
grant update(sal,mgr) on scott.emp to test3 with grant option;

（3）测试：

conn test3/123
select * from scott.emp;
update scott.emp set sal=sal+100;

3、查询权限分配情况

user_tab_privs_made  ---查询授出去的对象权限(通常是属主自己查）
user_tab_privs_recd  ---用户拥有的对象权限

user_col_privs_made  ---用户分配出去的列的对象权限
user_col_privs_recd  ---用户拥有的关于列的对象权限

user_sys_privs       ---用户拥有的系统权限
user_tab_privs       ---用户拥有的对象权限
user_role_priv       ---用户拥有的角色

DBA_SYS_PRIVS:       ---查询某个用户所拥有的系统权限
USER_SYS_PRIVS:      ---当前用户所拥有的系统权限
SESSION_PRIVS:       ---当前用户所拥有的全部权限
ROLE_SYS_PRIVS:      ---某个角色所拥有的系统权限

注意: 要以SYS用户登陆查询这个视图,否则返回空.
ROLE_ROLE_PRIVS:     ---当前角色被赋予的角色
SESSION_ROLES:       ---当前用户被激活的角色
USER_ROLE_PRIVS:     ---当前用户被授予的角色

另外还有针对表的访问权限的视图:
TABLE_PRIVILEGES
ALL_TAB_PRIVS   
ROLE_TAB_PRIVS:     某个角色被赋予的相关表的权限

4、收回对象权限

（1）语法

-- 使用revoke 语句收回权限
-- 使用with grant option 子句所分配的权限同样被收回

-- revoke {privilege [, privilege...]|all}
-- on object
-- from {user[, user...]|role|public}
-- [cascade constraints];

cascade constraints       --为处理引用完整性时需要

（2）举例

SQL> conn /as sysdba;
Connected.
SQL>revoke select on scott.emp from llltest;
Revoke succeeded.
SQL> revoke update(sal,mgr) on scott.emp from llltest;
revoke update(sal,mgr) on scott.emp from llltest
*
ERROR at line 1:
ORA-01750: UPDATE/REFERENCES may only be REVOKEd from the whole table, not by column
SQL> revoke update on scott.emp from llltest;
Revoke succeeded.

（3）其它

--检查dba权限的用户

select * from dba_role_privs where granted_role='dba';

-- 查看用户具有的系统权限：

select * from session_privs;

四、系统权限和对象权限的差异

1、语法差异

（1）授予对象权限时需要指定关键字on，

从而能够确定权限所应用的对象, 对于表和视图可以指定特定的列来授权。

SQL>grant select on scott.emp to LLL;

（2）授予系统权限时，直接to user，没有on这个关键字

SQL>grant create session,create table to llltest; 

2、with admin option和with grant option区别

（1）with admin option—系统权限

①用于系统权限授权
②使得该用户具有将自身获得的系统权限授予其它用户的功能。
③但收回系统权限时，不会从其它帐户级联取消曾被授予的相同权限
sys 把unlimited tablespace 系统权限通过with admin option 给了test2;
test2 拥有unlimited tablespace 同时把此权限给其他用户，比如test3;
revoke unlimited tablespace from test2;
test3 还有 unlimited tablespace

（2）with grant option—对象权限

①用于对象权限授权
②使得该用户具有将自身获得的对象权限授予其它用户的功能
③但收回对象权限时，会从其它帐户级联取消曾被授予的相同权限
sys 把scott.emp的select对象权限通过with grant option 给了test2;
test2 拥有scott.emp的select对象权限 同时可以把此权限给其他用户，比如test3;
revoke select on scott.emp from test2;
test3 同时失去select scott.emp的权限。

3、查看系统/对象权限

（1）查看用户或角色拥有的系统权限

同时关注with admin option;

select * from dba_sys_privs;

（2）查看用户或角色拥有的对象权限

select  * from dba_tab_prvis;

（3）查看角色拥有的系统权限

select * from role_sys_privs where role='LLL_ROLE';  --查看角色拥有的系统权限

（4）查看角色拥有的对象权限

select * from role_tab_privs where role='LLL_ROLE';  --查看角色拥有的对象权限