SQL编程：权限和服务器锁

MySQL权限管理

权限管理主要是对登录到MySQL的用户进行权限验证，所有用户的权限都存储在MySQL的权限表中，不合理的权限规划会给MySQL服务器带来安全隐患。数据库管理员要对所有用户的权限进行合理规划管理。MySQL权限系统的主要功能时证实连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT/INSERT/UPDATE和DELETE权限。

MySQL各种权限

账户权限信息被存储在MySQL数据库的几张权限表中，在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。其中GRANT和REVOKE语句所涉及的常用权限大致如下这些：CREATE、DROP、SELECT、INSERT、UPDATE、DELETE、INDEX、ALTER、CREATE ROUTINE、GRANT、FILE等。

MySQL用户授权

授权就是为某个用户授予权限，合理的授权可以保证数据库的安全，MySQL中可以使用GRANT语句为用户授予权限。授权可以分为多个层次：

全局层级：全局权限适用于一个给定服务器中的所有数据库，这些权限存储在mysql.user表中。

数据库层级：数据库权限适用于一个给定数据库中的所有目标，这些权限存储在mysql.db表中。

表层级：表权限适用于一个给定表中的所有列，这些权限存储在mysql.tables_priv表中。

列层级：列权限使用于一个给定表中的单一列，这些权限存储在mysql.columns_priv表中。

子程序层级：CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予子程序层级，并存储在mysql.procs_priv表中。

PS：MySQL中必须拥有GRANT权限的用户才可以执行GRANT语句。

用户权限管理

GRANT赋予用户权限

# 定义对已经存在的用户可以操作此库下的所有表及所有权限;
mysql> GRANT ALL PRIVILEGES ON db_name.* TO ‘username’@’host’;
  
# 创建tom用户并赋予select权限对此库下的所有表;
mysql> GRANT SELECT ON db_name.* TO 'tom'@'localhost' identified by '1234';

# 定义tom用户赋予insert权限对db库下的xsb表; 
mysql> GRANT INSERT ON db.xsb TO 'tom'@'localhost';
 
# 定义tom用户赋予update权限对db库下的xsb表;
mysql> GRANT UPDATE ON db.xsb TO 'tom'@'localhost';

# 定义tom用于赋予update权限对db库下的xsb表中的AGE字段;
mysql> GRANT UPDATE(AGE) ON db.xsb TO 'tom'@'localhost';

# 定义tom用于赋予super权限在*.*上(super权限可以对全局变量更改);
mysql> GRANT SUPER ON *.* TO 'tom'@'%';

# 定义对已经存在的用户可以操作此库下的所有表及所有权限;

mysql> GRANT ALL PRIVILEGES ON db_name.* TO ‘username’@’host’;

# 创建tom用户并赋予select权限对此库下的所有表;

mysql> GRANT SELECT ON db_name.* TO 'tom'@'localhost' identified by '1234';

# 定义tom用户赋予insert权限对db库下的xsb表;

mysql> GRANT INSERT ON db.xsb TO 'tom'@'localhost';

# 定义tom用户赋予update权限对db库下的xsb表;

mysql> GRANT UPDATE ON db.xsb TO 'tom'@'localhost';

# 定义tom用于赋予update权限对db库下的xsb表中的AGE字段;

mysql> GRANT UPDATE(AGE) ON db.xsb TO 'tom'@'localhost';

# 定义tom用于赋予super权限在*.*上(super权限可以对全局变量更改);

mysql> GRANT SUPER ON *.* TO 'tom'@'%';

# 刷新授权表；
mysql> FLUSH PRIVILEGES;

1 2	# 刷新授权表； mysql> FLUSH PRIVILEGES;

REVOKE移除用户权限

# 移除tom用户对于db.xsb的所有操作权限;
mysql> REVOKE ALL ON db.xsb FROM 'tom'@'localhost';

# 移除tom用户对db库下xsb表的INSERT权限;
mysql> REVOKE INSERT ON db.xsb TO 'tom'@'localhost';

# 移除tom用户对于db.xsb的所有操作权限;

mysql> REVOKE ALL ON db.xsb FROM 'tom'@'localhost';

# 移除tom用户对db库下xsb表的INSERT权限;

mysql> REVOKE INSERT ON db.xsb TO 'tom'@'localhost';

SHOW GRANTS查看用户权限

# 查询此用户的权限;
mysql> SHOW GRANTS FOR 'USERNAME'@'HOST';

1 2	# 查询此用户的权限; mysql> SHOW GRANTS FOR 'USERNAME'@'HOST';

PS：使用REVOKE收回权限之后，用户帐户的记录将从db、host、tables_priv、columns_priv表中删除，但是用户帐号记录依然在user表中保存。

服务器级别的锁

# 针对表的读锁，即此表可以有读操作，但不能有写操作，也叫共享锁;
mysql> LOCK TABLE table_name READ;

# 针对表的写锁，即此表不可以读也不可以写，也叫排他锁;
mysql> LOCK TABLE table_name WRITE;

# 全局读锁定，执行这个命令之后所有库所有表都被锁定只读，写操作将被阻塞;
mysql> FLUSH TABLES WITH READ LOCK;

# 可以解除指定表的锁定和全局读锁定;
mysql> UNLOCK TABLES;