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种对象权限
对象权限 | 表 | 视图 | 序列 | 过程 |
修改(alter) | √ | √ | ||
删除(delete) | √ | √ | ||
执行(execute) | √ | |||
索引(index) | √ | |||
插入(insert) | √ | √ | ||
关联(references) | √ | √ | ||
选择(select) | √ | √ | √ | |
更新(update) | √ | √ |
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'; --查看角色拥有的对象权限