关系数据库原理

实验7:数据库用户帐户管理与并发控制


返回主页』『实验1』 『实验2』『实验3』『实验4』 『实验5』『实验6』『实验7』 『实验8』『大作业

MySQL用户帐户管理是MySQL服务器安全性控制的重要组成部分。而数据库的并发控制,也是 数据库管理的重要机制。本次实验课需要两位同学为一组通力合作完成。所以下面SQL文件中lab*_owner.sql 由其中一位同学执行其中一个会话,lab7*_partner.sql部分则由另一位同学完成另一个会话。

附加:MySQL函数及安全管理


一、实验目的

  1. 掌握数据库角色和权限的概念。
  2. 学习如何创建数据库角色,改变数据库用户的权限等级等。
  3. 掌握表级锁和行级锁的概念、联系与区别。
  4. 了解数据库隔离级别的概念。


二、数据库权限管理

    实验环境
    【硬件环境】
    【操作系统】Redhat CentOS 6.5
    【数据库环境】MySQL 5.7

    数据库权限系统

    数据库权限系统的功能是验证特定用户连接,并为用户与特定数据库元素 的操作权限如SELECT/INSERT/UPDATE/DELETE等建立关联。记住,你不能显式的为数据库建立“拒绝” 用户列表;你可以授予某用户在某个数据库中添加和删除表的权限,但无法授予用户添加和删除某数 据库本身的权限;某特定用户的密码是全局性的,你不能为同一用户不同的权限指定不同的密码。

    作为数据库用户,当你在连接 数据库时,你所在的主机(或者IP)和用户名是确定的,注意数据库的用户名和操作系统的用户帐号是 独立的。


  1. 用SQL命令grantrevoke创建和删除数据库用户

    grant命令的语法是:

    mysql> GRANT [SELECT|INSERT|UPDATE|DELETE|ALL PRIVILEGES] ON database_name.table_name TO username@host IDENTIFIED BY 'password' [WITH GRANT OPTION]; mysql> FLUSH PRIVILEGES;

    如果需要为数据库用户加入多种操作权限,多种权限之间用逗号连接;如果指定数据库名、 表名、用户名、主机名时采用“*”通配符,则表示“任意”数据库/表/用户/主机,“IDENTIFIED BY”后面 显式指定用户密码。“WITH GRANT OPTION”则表示赋予该用户修改权限的权限。

    revoke命令的用法和grant类似:


    mysql> REVOKE priv_types [(column list)] ON [object_tpe] priv_level FROM user [, user] ...;
  2. 采用数据库查询等操作添加和删除用户

    数据库服务器将所有用户的权限信息存储在mysql数据库中,其中用户信息存储在 表user中,而权限则存储在表db中:

    mysql> CREATE USER user IDENTIFIED BY 'password'; mysql> DROP USER user; mysql> SET PASSWORD [FOR user] = { PASSWORD('password') OLD PASSWORD('old_password') mysql> USE mysql; mysql> SELECT User, authentication_string FROM user WHERE User='root'; mysql> UPDATE user set authentication_string=PASSWORD('password') WHERE User='user';

    需要说明的是:SQL函数PASSWORD()采用了mysql特有的一种字符串加密方式。
    本节任务

    • 为你的同伴添加一个用户,控制其权限,使其能访问你的部分数据库;
    • 尝试使用mysql连接你的同伴的数据库,看是否能够连接,如果不能,分析原因并解决。


    数据库并发控制

    数据库并发控制的目的是在多个用户同时访问或者更新行时,保护数据库完整性的各种技术。 并发机制不正确可能导致脏读、幻读和不可重复读等问题。所以,并发控制机制是衡量一个 DBMS性能的重要标志之一。并发控制事实上就是对并发操作进行正确调度,以保证ACID中的I (隔离性),进而保证数据库的一致性。

    我们知道,在关系数据库标准中有四个事务隔离级别:

    • 未提交读(Read Uncommitted):事务隔离的最低级别。解决第一类丢失更新的问 题,但是会出现脏读、不可重复读、第二类丢失更新的问题。
    • 提交读(Read Committed):只能读取到已经提交的数据。解决第一类丢失更新和脏 读的问题,但会出现不可重复读、第二类丢失更新的问题、幻读(PHANTOM READ)的问 题。
    • 可重复读(Repeated Read):可重复读。在同一个事务内的查询都是事务开始时 刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还 存在幻读(phantom read),但在MySQL中不会。
    • 串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁, 读写相互都会阻塞
    查看InnoDB存储引擎默认的系统级别的事务隔离级别:
    mysql> SELECT @@global.tx_isolation;
    +-----------------------+
    | @@global.tx_isolation |
    +-----------------------+
    | REPEATABLE-READ       |
    +-----------------------+
    1 row in set (0.00 sec)
    

    查看InnoDB会话级别的事务隔离级别:

    mysql> SELECT @@tx_isolation;
    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set (0.00 sec)
    

    修改事务隔离级别:

    mysql> set global transaction isolation level read committed;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set session transaction isolation level read committed;
    Query OK, 0 rows affected (0.00 sec)
    
  3. 准备工作

    执行脚本文件movie.sqlmovie2000.sql以及lab7.sql。为你的 数据库服务器添加产生数据库movie及表movie和movie2000,以及为该数据库添加特权用户。

    将autocommit设为0:

    mysql> set autocommit = 0;
  4. 上机任务

    在本节开始之前,必须清楚认识每次会话开始用begin开始,以commit或rollback结束。InnoDB默认的隔离级别是repeatable read,这里我们将改变隔离级别,观察不同的结果,并解释原因。

    1. commit和rollback:打开lab7ab.sql并执行其中脚本,观察commit和rollback的作用。
    2. 隔离级别与隐式的数据锁:将数据库owner和同伴的数据库隔离级别均设为read committed或者将同伴的隔离级别改变为serializable, 如同脚本文件lab7c_owner.sqllab7c_partner.sql。前后两种 情况有什么变化?
    3. 显式的数据锁:仍然将owner和同伴的数据库隔离级别设为read committed,观察在提交前后同伴查询结果的差别。MySQL还可通过for update 来执行行级锁,行级锁与表级锁有什么不同?如脚本文件lab7d_owner.sqllab7d_partner.sql
    4. 不可重复读和幻读:这里采用lock table显式的为表加锁,加锁后必须用unlock tables释放表级锁, 否则即使提交(commit)也无法释放锁。如脚本文件lab7e_owner.sqllab7e_partner.sql

  5. 如何显式添加表级锁
  6. MySQL表级锁分为读锁和写锁。其中MyISAM和InnoDB都支持表级锁,创建表级锁的 开销小,不会出现死锁,由于锁定的是整张表,所以并发度低。当需要频繁对大部分数据 进行GROUP BY操作,或者需要频繁扫描整张表时,推荐使用表级锁。

    • LOCK TABLE table_name [AS alias_name] READ
      • 成功申请读锁的前提是当前没有线程对该表使用写锁,否则语句阻塞;
      • 申请读锁成功后,其他线程可以对该表进行读操作,但不允许有线程对其进行写操作,包括当前线程;
      • 锁住A表后,只能对A表进行读操作,而其他表的读操作会出现错误。
    • LOCK TABLE table_name [AS alias_name] [ LOW_PRIORITY ] WRITE
      • 当多个线程同时申请多种锁(LOW_PRIORITY,READ,WRITE)时,LOW_PRIORITY是优先级最低的锁
      • 申请写锁成功的前提是什么?
  7. 如何利用SELECT加行级锁

    行级锁都是基于索引的,如果SQL语句中没有用到索引,是不会使用行级锁的,会 使用表级锁。行级锁的缺点是:需要请求大量的锁资源,因此速度慢,内存消耗大。

    • SELECT ...FROM

      是一个持续读,读取数据库的快照并且设置不锁定,除非事务隔离级别被设为 SERIALIZABLE。对于SERIALIZABLE级别,这个设置对它遇到的索引记录设置共享的锁定。

    • SELECT ... FROM ... LOCK IN SHARE MODE

      对读遇到的所有索引记录设置共享锁,对查询结果中的所有行添加共享锁,当没有其 他线程对查询结果中的任何一行使用排他锁,可以成功申请共享锁,否则阻塞;其他线程 也可以读取使用了共享锁的表,而且读取的是同一个版本的数据。

    • SELECT ... FROM ... FOR UPDATE

      对读遇到的所有索引记录设置排他锁,当没有其他线程对查询结果中的任何 一行使用排他锁时,可以成功申请排他锁,否则阻塞。

三、实验报告

完成以上练习,并将实验报告以rar附件的形式发送到ricket.woo AT gmail.com,文件名为5091409XXXlab7.rar。实验报告的内容 应包括实验环境,实验内容和结果讨论。