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

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

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

一、事务

（一）什么是事务

事务：

　　事务是指作为单个逻辑工作单元执行的一组相关操作。　　

　　这些操作要求全部完成或者全部不完成。

　　这些操作要求全部完成或者全部不完成。

使用事务的原因：保证数据的安全有效。

事务的四个特点：（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