【ORACLE】 事务 | 锁 | 约束 | 权限、角色与用户管理

🕺作者： 主页

我的专栏
C语言从0到1
探秘C++
数据结构从0到1
探秘Linux

😘欢迎关注：👍点赞🙌收藏✍️留言

🏇码字不易，你的👍点赞🙌收藏❤️关注对我真的很重要，有问题可在评论区提出，感谢支持！！！

一、事务

（一）什么是事务

事务：
　　事务是指作为单个逻辑工作单元执行的一组相关操作。　　
　　这些操作要求全部完成或者全部不完成。
使用事务的原因：保证数据的安全有效。
事务的四个特点：（ACID）
　　1、原子性（Atomic）：事务中所有数据的修改，要么全部执行，要么全部不执行。
　　2、一致性（Consistence）：事务完成时，要使所有所有的数据都保持一致的状态，换言之：通过事务进行的所有数据修改，必须在所有相关的表中得到反映。
　　3、隔离性（Isolation）：事务应该在另一个事务对数据的修改前或者修改后进行访问。
　　4、持久性（Durability）：保证事务对数据库的修改是持久有效的，即使发生系统故障，也不应该丢失。

（二）相关概念

回滚：RollBack只能对未提交的数据撤销，已经Commit的数据是无法撤销的，因为commit之后已经持久化到数据库中。
脏读（Dirty Read）：事务T1更新了一行数据，还没有提交所做的修改，T2读取更新后的数据，T1回滚，T2读取的数据无效，这种数据称为脏读数据。
不可重复读（UNrepeatable Read）：事务T1读取一行数据，T2修改了T1刚刚读取的记录，T1再次查询，发现与第一次读取的记录不相同，称为不可重复读。
幻读（Phantom Read）：事务T1读取一条带WHERE条件的语句，返回结果集，T2插入一条新纪录，恰好也是T1的WHERE条件，T1再次查询，结果集中又看到T2的记录，新纪录就叫做幻读。

（三）隔离级别

• NO_TRANSACTION　　　　 不支持事务
• READ_UNCOMMITED　　　 允许脏读、不可重复读、幻读
• READ_COMMITED　　　　 允许不可重复读、幻读，不允许脏读
• REPEATABLE　　　　　　　允许幻读，不允许脏读、不可重复读
• SERIALIZABLE　　　　　　 脏读、不可重复读、幻读都不允许

Oracle支持SQL92标准的READ_COMMITED、SERIALIZABLE，自身特有的READ_ONLY

二、锁

（一）什么是锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
锁是实现数据库[并发控制](https://www.baidu.com/s?wd=%E5%B9%B6%E5%8F%91%E6%8E%A7%E5%88%B6&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1YLujfvP1R3PHTknHb1m1nz0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3Erj03nj6znjn)的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

（二）锁的分类

根据保护的对象不同，Oracle数据库锁可以分为以下几大类：DML锁（data locks，数据锁），用于保护数据的完整性；DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；内部锁和闩（internal locks and latches），保护数据库的内部结构。
DML锁的目的在于保证并发情况下的数据完整性，在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。
当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X 等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。
在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

（三）表级锁

行级排他锁（Row Exclusive，简称RX锁）
当我们进行DML时会自动在被更新的表上添加RX锁，或者也可以通过执行lock命令显式的在表上添加RX锁。在该锁定模式下，允许其他的事务通过DML语句修改相同表里的其他数据行，或通过lock命令对相同表添加RX锁定，但是不允许其他事务对相同的表添加排他锁（X锁）。
行级共享锁（Row Shared，简称RS锁）
通常是通过select … from for update语句添加的，同时该方法也是我们用来手工锁定某些记录的主要方法。比如，当我们在查询某些记录的过程中，不希望其他用户对查询的记录进行更新操作，则可以发出这样的语句。当数据使用完毕以后，直接发出rollback命令将锁定解除。当表上添加了RS锁定以后，不允许其他事务对相同的表添加排他锁，但是允许其他的事务通过DML语句或lock命令锁定相同表里的其他数据行。
共享锁（Share，简称S锁）
通过lock table in share mode命令添加该S锁。在该锁定模式下，不允许任何用户更新表。但是允许其他用户发出select …from for update命令对表添加RS锁。
排他锁（Exclusive，简称X锁）
通过lock table in exclusive mode命令添加X锁。在该锁定模式下，其他用户不能对表进行任何的DML和DDL操作，该表上只能进行查询。
共享行级排他锁（Share Row Exclusive，简称SRX锁）
通过lock table in share row exclusive mode命令添加SRX锁。该锁定模式比行级排他锁和共享锁的级别都要高，这时不能对相同的表进行DML操作，也不能添加共享锁。
这五种模式的TM锁的兼容关系如下表所示（√表示互相兼容的请求；×表示互相不兼容的请求；N/A表示没有锁定请求）：

  从前面的描述中可以看到，我们不仅可以通过发出DML语句的方式，由Oracle自动在表级别上添加TM锁。我们还可以通过发出lock table命令主动地在表级别上添加TM锁，并在该命令中可以指定不同的锁定模式，其命令格式如下所示：

lock table in [row share][row exclusive]
[share][share row exclusive][exclusive] mode;
对Oracle数据库中的各SQL语句所产生的表级锁的情况进行汇总，如下表所示：

对于通过lock table命令主动添加的锁定来说，如果要释放它们，只需要发出rollback命令即可。

三、约束

（一）什么是约束

在Oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。
列级约束：
列级定义是在定义列的同时定义约束；
column [CONSTRAINT constraint_name] constraint_type
表级约束：
表级定义是指在定义了所有列后，再定义约束，这里需要注意，not null约束只能在列级上定义；
column ,…,
[CONSTRAINT constraint_name] constraint_type (column,…)

（二）约束的分类

1 not null（非空）
如果在列上定义了not null，那么当插入数据时，必须为列提供，数据不能为NULL。约束只能在列级定义，不能在表级定义。
2 unique（唯一）
当定义了唯一约束后，该列值是不能重复的，但是可以为null。
3 primary key（主键）
用于唯一的标识表行的数据，当定义主键约束后，该列不但不能重复而且不能为NULL。一张表最多只能有一个主键，但是可以由多个unique约束。
创建主键或唯一约束后，ORACLE会自动创建一个与约束同名的索引（UNIQUENES为UNIQUE唯一索引）。需要注意的是：每个表只能有且有一个主键约束。
4 foreign key（外键）
用于定义主表和从表之间的关系，外键约束要定义在从表上，主要则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为NULL。
用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性. 外键约束是个有争议性的约束，它一方面能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库； 另外一方面它会增加表插入、更新等SQL性能的额外开销，不少系统里面通过业务逻辑控制来取消外键约束。例如在数据仓库中，就推荐禁用外键约束。
5 check
用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000～2000之间，如果不在1000～2000之间就会提示出错。

（三）约束的创建

1 not null（非空）
方法一：
SQL> create table t1(id number,name varchar2(20) constraint nn_t1_id not null);

SQL> select constraint_name,constraint_type,owner from user_constraints;
CONSTRAINT_NAME C OWNER

NN_T1_ID C SCOTT

方法二：
SQL> drop table t1 purge;
SQL> create table t1(id number,name varchar2(20));
SQL> alter table t1 modify id constraint nn_t1_id not null;
SQL> select constraint_name,table_name,owner from user_constraints;
CONSTRAINT_NAME TABLE_NAME OWNER

NN_T1_ID T1 SCOTT

2 unique（唯一）
方法一：
SQL> create table t1(id number,qq number,constraint un_t1_qq unique(qq));
方法二：
SQL> alter table t1 add constraint un_t1_qq unique(qq);

3 primary key（主键）
方法一：
SQL> create table t1(id number,qq number,constraint pk_t1_id primary key(id));
方法二：
SQL> alter table t1 add constraint pk_t1_id primary key(id);
SQL> select constraint_name,table_name,owner from user_constraints;
CONSTRAINT_NAME TABLE_NAME OWNER

PK_T1_ID T1 SCOTT

4 foreign key（外键）
方法一：
SQL> create table t2(id number,cc number,constraint fk_t2_id foreign key(id) references t1(id));
方法二：
SQL> alter table t1 add constraint pk_t2_id foreign key(id) references t1(id);
SQL> select constraint_name,table_name,owner from user_constraints;
CONSTRAINT_NAME TABLE_NAME OWNER

PK_T1_ID T1 SCOTT
PK_T2_ID T1 SCOTT

当定义了外部键约束之后，要求外部键列的数据必须在主表的主键列（或惟一列）中存在，或者为NULL,FOREING KEY约束既可以在列级定义，也可以在表级定义。

关键字说明：
(1) FOREING KEY:该选项用于指定在表级定义外部键约束。当在表级定义外部键约束时必须指定该选项，在列级定义外部键约束不需要指定该选项
(2) REFERENCES:该选项用于指定主表名及其主键列。当定义外部键约束时，该选项必须指定。
(3) ON DELETE CASCAED:该选项用于指定级联删除选项。如果在定义外部键约束时指定了该选项，那么当删除主表数据时会级联删除从表的相关数据。
(4) ON DELECT SET NULL:该选项用于指定转换相关的外部键值为NULL，如果在定义外部键约束时指定了该选项，那么当删除主表数据时会将从表外部键列的数据设置为NULL。
SQL> create table t1(id number,qq number,constraint pk_t1_id primary key(id));
SQL> create table t2(id number,sal number,constraint fk_t2_id foreign key(id) references t1(id));

SQL> delete t1; ----由于主外键约束，无法删除主表
delete t1
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.FK_T2_ID) violated - child record found

SQL> insert into t2 values(2,2); —由于主外键的约束，无法在外键表插入主键中id列没有的值
insert into t2 values(2,2)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.FK_T2_ID) violated - parent key not found

SQL> delete t2;
1 row deleted.

SQL> rollback;

SQL> drop table t2 purge;
SQL> create table t2(id number,sal number,constraint fk_t2_id foreign key(id) references t1(id) on delete cascade); -----外键表添加级联删除参数

SQL> delete t1; ----删除主键表上的数据，级联删除外键表上的数据
1 row deleted.

SQL> select * from t1;
no rows selected

SQL> select * from t2;
no rows selected

5 check（检查性约束）
方法一：
SQL> create table t3(id number,sal number,constraint ck_t3_sal check(sal between 5000 and 50000));
SQL> select constraint_name,table_name,owner from user_constraints;

CONSTRAINT_NAME TABLE_NAME OWNER

CK_T3_SAL T3 SCOTT
方法二：
SQL> create table t3(id number,sal number);
SQL> alter table t3 add constraint ck_t3_sal check(sal>5000);

（四）约束的维护

1 增加约束
（1） 如果增加UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必须使用ALTER TABLE语句的ADD子句;
（2） 如果增加NOT NULL约束，那么必须使用ALTER TABLE语句的MODIFY子句，如：
ALTER TABLE table_name ADD [CONSTRAINT constraint_name]
constraint_type (column,…)
ALTER TABLE table_name MODIFY column
[CONSTRAINT constraint_name] NOT NULL;

2 修改约束名
在同一个方案中，约束名必须惟一,并且约束名也不能与其他对象同名。当用IMPDP工具或者IMP工具导入其他对象时，如发现有同名的对象，将会出错
语法：
ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name
TO new_constraint_name;
例：
ALTER TABLE emp01 RENAME CONSTRAINT SYS_C005028
TO ck_emp01_salary;

SQL> alter table t1 rename constraint PK_T1_ID to new pk01_t1_id;
alter table t1 rename constraint PK_T1_ID to new pk01_t1_id
*
ERROR at line 1: --------主键无法更改名字
ORA-23290: This operation may not be combined with any other operation

SQL> alter table t2 rename constraint fk_t2_id to fk01_t2_id;
SQL> select constraint_name,table_name from user_constraints where table_name=‘T2’;

CONSTRAINT_NAME TABLE_NAME

FK01_T2_ID T2 -------------外键可以更改名字

SQL> alter table t1 add constraint un_t1_qq unique(qq);

SQL> select constraint_name,table_name from user_constraints where table_name=‘T1’;

CONSTRAINT_NAME TABLE_NAME

PK_T1_ID T1
UN_T1_QQ T1

SQL> alter table t1 rename constraint un_t1_qq to un01_t1_qq;

SQL> select constraint_name,table_name from user_constraints where table_name=‘T1’;

CONSTRAINT_NAME TABLE_NAME

PK_T1_ID T1
UN01_T1_QQ T1

3 禁止约束

禁止约束指使约束临时失效。当禁止了约束之后，约束规则将不再生效。在使用SQL*LOADER或INSERT装载数据之前，为了加快数据装载速度，应该首先禁止约束，然后装载数据。
语法：
ALTER TABLE table_name
DISABLE CONSTRAINT constaint_name [CASCAED];–CASCAED用于指定级联禁止从表的外部键
SQL> insert into t2 values(2,2);
insert into t2 values(2,2)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.FK01_T2_ID) violated - parent key not
found

SQL> alter table t2 disable constraint fk01_t2_id;
Table altered.

SQL> insert into t2 values(2,2);
1 row created.

4 激活约束

语法：
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
例：
SQL> alter table t2 enable constraint fk01_t2_id;
alter table t2 enable constraint fk01_t2_id
*
ERROR at line 1: —外键激活失败，原因是在外键表中含有主键表中没有的数据
ORA-02298: cannot validate (SYS.FK01_T2_ID) - parent keys not found

SQL> delete t2 where id=2; -------删除数据

SQL> alter table t2 enable constraint fk01_t2_id; ----激活成功

5 删除约束

当删除特定表的主键约束时，如果该表具有相关的从表，那么在删除主键约束时必须带有CASCAED选项
语法：
ALTER TABLE table_name DROP
CONSTRAINT constraint_name |PRIMARY KEY
例一：（删除唯一性约束）
SQL> select constraint_name,table_name from user_constraints where table_name=‘T1’;

CONSTRAINT_NAME TABLE_NAME

PK_T1_ID T1
UN01_T1_QQ T1

SQL> alter table t1 drop constraint un01_t1_qq;

Table altered.

SQL> select constraint_name,table_name from user_constraints where table_name=‘T1’;

CONSTRAINT_NAME TABLE_NAME

PK_T1_ID T1

例二：（删除主键约束，级联删除外键约束）
SQL> alter table t1 drop primary key cascade;

Table altered.

SQL> select constraint_name,table_name from user_constraints where table_name=‘T2’;

no rows selected

SQL> select constraint_name,table_name from user_constraints where table_name=‘T1’;

no rows selected

6 显示信息

1.USER_CONSTRAINTS
2.USER_CONS_COLUMNS

SQL> select constraint_name,table_name,column_name from user_cons_columns where table_name=‘T1’;

CONSTRAINT_NAME TABLE_NAME COLUM

UN_T1_ID T1 ID

四、权限、角色与用户管理

（一）概述

ORACLE 数据库系统预先定义了 CONNECT 、RESOURCE、 DBA、 EXP_FULL_DATABASE、 IMP_FULL_DATABASE 五个角色。
CONNECT 具有创建表、视图、序列等特权；(alter session create cluster )
RESOURCE 具有创建过程、触发器、表、序列等特权、
DBA 具有全部系统特权；
EXP_FULL_DATABASE、 IMP_FULL_DATABASE 具有卸出与装入数据库的特权。

（二）权限分类

系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言)。
实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。

（三）系统权限管理

DBA: 拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构。
RESOURCE:拥有 Resource 权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有 Connect 权限的用户只可以登录 Oracle，不可以创建实体，不可以创建数据库结构。
对于普通用户：授予 connect, resource 权限。
对于 DBA 管理用户：授予 connect，resource, dba 权限。

[系统权限只能由 DBA 用户授出：sys, system(最开始只能是这两个用户)]
授权命令：SQL> grant connect, resource, dba to 用户名 1 [,用户名 2]…;
[普通用户通过授权可以具有与 system 相同的用户权限，但永远不能达到与 sys 用户相同的权限，system 用户的权限也可以被回收。]
例： SQL> connect system/manager
SQL> Create user user50 identified by user50;
SQL> grant connect, resource to user50;
查询用户拥有哪些权限：
SQL> select * from dba_role_privs;
SQL> select * from dba_sys_privs;
SQL> select * from role_sys_privs;
删除用户：SQL> drop user 用户名 cascade; //加上 cascade 则将用户连同其创建的东西全部删除

（四）实体权限管理

1、实体权限分类：select, update, insert, alter, index, delete, all //all 包括所有权限
execute //执行存储过程权限
user01:
SQL> grant select, update, insert on product to user02;
SQL> grant all on product to user02;
user02:
SQL> select * from user01.product;
// 此时 user02 查 user_tables，不包括 user01.product 这个表，但如果查 all_tables 则可以查到，因为他可以访问。
2. 将表的操作权限授予全体用户：
SQL> grant all on product to public; // public 表示是所有的用户，这里的 all 权限不包括 drop。
[实体权限数据字典]:
SQL> select owner, table_name from all_tables; // 用户可以查询的表
SQL> select table_name from user_tables; // 用户创建的表
SQL> select grantor, table_schema, table_name, privilege from all_tab_privs; // 获权可以存取的表（被授权的）
SQL> select grantee, owner, table_name, privilege from user_tab_privs; // 授出权限的表(授出的权限)
查看用户拥有哪些系统权利
Sql> select grantee，privilege from dba_sys_privs where grantee=‘SCOTT’;
查看用户拥有哪些对象权利
Sql> select grantee，privilege，owner，table_name from dba_tab_privs where grantee=‘SCOTT’;
3. DBA 用户可以操作全体用户的任意基表(无需授权，包括删除)：
DBA 用户：
SQL> Create table stud02.product(
id number(10),
name varchar2(20));
SQL> drop table stud02.emp;
SQL> create table stud02.employee
as
select * from scott.emp;
4. 实体权限传递(with grant option)：
user01:
SQL> grant select, update on product to user02 with grant option; // user02 得到权限，并可以传递。
5. 实体权限回收：
user01:
SQL>Revoke select, update on product from user02; //传递的权限将全部丢失。
说明
1）如果取消某个用户的对象权限，那么对于这个用户使用 WITH GRANT OPTION 授予权限的
用户来说，同样还会取消这些用户的相同权限，也就是说取消授权时级联的。

（五）角色管理

我在前面的篇幅中说明权限和用户。慢慢的在使用中你会发现一个问题：如果有一组人，他们的所需的权限是一样的，当对他们的权限进行管理的 时候会很不方便。因为你要对这组中的每个用户的权限都进行管理。
有一个很好的解决办法就是：角色。角色是一组权限的集合，将角色赋给一个用户，这个用户就拥有了这个角色中的所有权限。那么上述问题就
很 好处理了，只要第一次将角色赋给这一组用户，接下来就只要针对角色进行管理就可以了。
以上是角色的一个典型用途。其实，只要明白：角色就是一组权限的集合。

1.建一个角色
sql>create role role1;
2.授权给角色
sql>grant create any table,create procedure to role1;
Sql>grant create session，create table to role1;
Sql>revoke create session，create table from role1;

3.授予角色给用户
sql>grant role1 to user1;
Sql>grant role1 to user1 with admin option;

revoke role1 from user1;

查看系统中所有的角色
Sql>select * from dba_roles;

查看用户被授予了哪些角色
Sql>select grantee，granted_role from dba_role_privs where grantee=‘SCOTT’;
查看角色中包含了哪些系统权限
Sql>select role，privilege from role_sys_privs where role=‘ROLE1’;
查看角色中包含了哪些对象权限
Sql>select role，privilege，from role_tab_privs where role=‘ROLE1’;
将角色授予角色
Sql>grant role1 to role2;

4.查看角色所包含的权限
sql>select * from role_sys_privs;
5.创建带有口令以角色(在生效带有口令的角色时必须提供口令)
sql>create role role1 identified by password1;
6.修改角色：是否需要口令 sql>alter role role1 not identified;
sql>alter role role1 identified by password1;
7.设置当前用户要生效的角色
(注：角色的生效是一个什么概念呢？假设用户 a 有 b1,b2,b3 三个角色，那么如果 b1 未生效，则 b1 所包含的权限对于 a 来讲是不拥有的，只有角色生效了，角色内的权限才作用于用户，最大可生效角色数由参数 MAX_ENABLED_ROLES 设定；在用户登录后，oracle 将所有直接赋给用户的权限和用户默认角色中的权限赋给用户。）
sql>set role role1;//使 role1 生效
sql>set role role,role2;//使 role1,role2 生效
sql>set role role1 identified by password1;//使用带有口令的 role1 生效
sql>set role all;//使用该用户的所有角色生效
sql>set role none;//设置所有角色失效
sql>set role all except role1;//除 role1 外的该用户的所有其它角色生效。
sql>select * from SESSION_ROLES;//查看当前用户的生效的角色。
8.修改指定用户，设置其默认角色
sql>alter user user1 default role role1;
sql>alter user user1 default role all except role1;
详见 oracle 参考文档
9.删除角色
sql>drop role role1;
角色删除后，原来拥用该角色的用户就不再拥有该角色了，相应的权限也就没有了。
说明:
1)无法使用 WITH GRANT OPTION 为角色授予对象权限
2)可以使用 WITH ADMIN OPTION 为角色授予系统权限,取消时不是级联
查看角色中还包含哪些角色
Sql>select role，granted_role from role_role_privs where role=‘DBA’；
备注：授予用户 DBA、RESOURCE 这俩个角色后系统会自动再授予用户 unlimited tablespace文章来源地址https://www.toymoban.com/news/detail-814822.html

到了这里，关于【ORACLE】 事务 | 锁 | 约束 | 权限、角色与用户管理的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！