Oracle数据学习笔记四——Oracle数据库用户与角色的应用

最新推荐文章于 2024-09-08 11:03:57 发布

今天博学了吗？

最新推荐文章于 2024-09-08 11:03:57 发布

阅读量398

点赞数

文章标签：数据库 oracle 学习

本文链接：https://blog.csdn.net/weixin_45714402/article/details/125471296

版权

实验四创建和管理用户账号以及权限管理

一、前提步骤

1.启动监听

注：监听实例不启动，接下来的实验是无法完成的

#启动监听
lsnrctl  start

#导入实例
export ORACLE_SID=orcl

#以数据库管理员身份登录
sqlplus / as sysdba

#启动数据库
startup

查看监听状态：lsnrctl status，如下图

orcl监听实例已开启

2.创建新用户

#创建一个新用户sh
CREATE USER sh IDENTIFIED BY sh;

注：用户名严格区分大小写！

3.环境搭建

准备两个窗口，如下图

分别在两个窗口用于登录用户sys和用户sh

窗口1 sys用户登录，用于授权命令

export ORACLE_SID=orcl
sqlplus / as sysdba

窗口2 用户sh登录，用于查询命令

export ORACLE_SID=orcl
sqlplus sh/sh@orcl

注：此时的sh用户并没有得到授权，暂时登录不了

二、用户授权操作

事实上，新的用户创建完成后，不具备任何权利，需要一一的去赋予，实验过程如下：

1.在新窗口，用新用户名sh登录，事实上不具备登录会话的权利

sqlplus sh/sh@orcl

2.于是，在sys用户下授权用户sh创建会话权限

GRANT create session to sh;

3.授权后sh用户登录成功

4.sh用户下建表T1，事实上不具备创建表的权利

create table t1 (id number);

5.sys用户授权创建表权限

GRANT create table to sh;

6.赋予权利后建表成功

7.sh用户往表插入数据，事实上不具备插入数据的权利

insert into t1 values (1);

8.sys用户授权sh使用表空间的配额，即允许sh有插入数据的功能

#允许sh用户有1mb的空间使用
alter user sh quota 1m on users;

9.sh用户再往表插入数据，多次插入数据，直到报错

insert into t1 values (1);
#重复插入
insert into t1 select * from t1;
#运用/ 命令来重复插入，/代表执行最近一次执行过的语句
/

10.sys给sh分配更多的配额

#再分配2mb给sh
alter user sh quota 2m on users;

11.sh用户再往表插入数据，多次插入数据，直到报错

#重复插入
insert into t1 select * from t1;

注：/ 的用法在上个实验讲过，这里不在赘述

实验中，重复的赋予一个用户对表空间的使用大小有点繁琐，所以可直接赋予用户无限使用空间的方法，如下

12.sys用户分配sh无限使用表空间的权限

 #分配无限空间给用户sh
 grant unlimited tablespace to sh;

13.sh用户再往表插入数据，多次插入数据。

#重复插入
insert into t1 select * from t1;

这里不给出结果图，因为可以无限插入数据了

这样后，只要磁盘空间足够大，反复的重复插入数据再也不会像上面会因为sh能使用的空间大小而报错。

实验可知，新创建的用户实际上不会拥有任何权利

三、角色管理

由上面的实验可知，新用户操作时，需要一个一个的授权，显然这样操作比较麻烦。于是oracle提供了角色来简化权限管理

逻辑思路：管理员->角色->用户；即把权限先分配给角色，再把角色分配给用户。

1.创建角色

#创建r1角色
CREATE ROLE r1;
#赋予角色r1 开发者所需要的权限
GRANT create session,create table to r1;

2.新建有个用户，分配r1角色

#赋予角色r1并创建hr用户
CREATE USER hr identified by hr;
GRANT r1 to hr;

3.新建窗口用户hr登录(此时应该有三个会话窗口)，同时建表

sqlplus hr/hr@orcl
create table t1 (name varchar2(20));

跟步骤二对比可以看出，同样是新创建的用户sh和hr，sh没有用角色赋予权限，则需要一条一条的GRANT去赋予，而用户hr继承了角色r1的create session,create table的权限，则不需要一个个GRANT

事实上，在大多数情况下是使用角色的方法，这样更高效，更快捷

四、权限管理和运用

4.1对象权限管理

1.sh用户新建T2表

#开启一个添加的事务
insert into t2 VALUES (1,'oracle1');
insert into t2 VALUES (2,'oracle2');
insert into t2 VALUES (3,'oracle');
#提交事务
commit;

2.hr用户访问sh下的T2表

select * from sh.t2;

3.sh用户授权hr select权限

GRANT select on t2 to hr;

4.随后，hr用户访问sh下的T2表即可成功

5.如果hr想修改sh下T2表的数据，则需要授权

#赋予更新，删除，插入的权限
GRANT update,delete,insert on t2 to hr;

6.hr用户进行下面操作

#开始一些事务
insert into SH.T2 values (4,'oracle4');
update sh.t2 set name='oracle3' where id=3;
delete sh.t2 where id=1;
#提交事务
commit;

注：授权操作和修改操作的顺序不能反，否则会因为没有权限而报错

4.2对象权限跨级授权

授权顺序：sh -> hr -> oe

关键字：GRANT

常用with grant option 来授予被授予的用户有授予其他用户的权限

1.创建oe用户，分配r1角色，故oe有r1角色的权限(此时应该有四个会话窗口)

#赋予角色r1并创建oe用户
GRANT r1 to oe identified by oe;
#登录oe用户
sqlplus oe/oe@orcl

2.sh用户把T2表的update权限给hr，同时允许hr把该权限授权给其它用户

#越级授予
GRANT update on t2 to hr with grant option;

3.hr用户把sh.T2表的update权限给oe

#授权
GRANT update on sh.t2 to oe;

注：2和3的步骤不能反且必须要加上with grant option

4.oe用户访问sh.t2表，由于只授予了update权限，所以这里访问是失败的

update sh.T2 set name='oracle5' where id=4;

错误原因：没有select权限

5.sh授权oe在t2 select的权限

#授权
GRANT select on t2 to oe;

6、oe用户访问sh.t2表成功

#重新开启事务
update sh.T2 set name='oracle5' where id=4;
#提交
commit;

4.3 回收权限

遵循一个原则:谁授权的谁revoke!

权限回收:revoke关键字

遵循一个原则:谁授权的谁revoke!，这里举出一个反例:

从4.2的步骤来看：授权顺序：sh -> hr -> oe 所以遵循原则的话，oe的权限是不能从sh那里回收的

于是

1.sh用户把update权限从oe回收

#回收权限
revoke update on t2 from oe;

由图可知:回收失败，只能由授权者回收。故遵循谁授予谁revoke的原则

2.sh用户把delete权限回收

 revoke delete on t2 from hr;

符合授权顺序：sh -> hr -> oe，故回收成功

3.sh把hr权限回收的同时，会把hr授权给其它用户的权限一起回收

revoke update on t2 from hr;

符合授权顺序：sh -> hr -> oe，故回收成功

4.检查：由于授权顺序：sh -> hr -> oe 且上一步回收了hr的权限，oe的权限一并被回收

 update sh.T2 set name='oracle5' where id=4;

说明越级回收成功，第3小步正确

4.4 系统权限的授予

4.4.1 sys授权给oe(sys-oe)

#1.授权oe可以查看所有普通用户的表

GRANT select any table to OE;

#2.授权oe可以更改所有普通用户的表

GRANT update any table to oe;

#3.SYS->OE允许授权的用户管理授权的权限

GRANT select any table to OE WITH ADMIN OPTION;

注：系统权限的传递用WITH ADMIN OPTION;

4.4.2 OE授权给HR；HR授权给SH(OE ->HR -> SH）

#系统授权
GRANT select any table to HR WITH ADMIN OPTION;#在oe窗口下执行
GRANT select any table to SH WITH ADMIN OPTION;#在hr窗口下执行

4.4.3在oe窗口下，查看当前授权的系统权限

#查看oe用户的权利
SELECT * FROM SESSION_PRIVS;

4.5 系统权限的回收

这里主要讨论回收系统权限是否和4.3小节一样遵循**谁授权的谁revoke!**的原则

由于权限都被回收，根据4.4小节赋予了oe系统权利，故授权顺序变为:SYS ->OE ->HR -> SH

注：在sh窗口下完成

1.sh 能不能回收 hr分配的权限

REVOKE SELECT ANY TABLE FROM HR;

2.sh能不能回收 oe分配的权限？

REVOKE SELECT ANY TABLE FROM oe;

]

3.sh能不能回收 sys分配的权限？不成功

REVOKE SELECT ANY TABLE FROM sys;

4.sh能不能回收自己的权限？成功

REVOKE SELECT ANY TABLE FROM sh;

顺序变为:SYS ->OE ->HR -> SH