MySQL优化之――权限管理 - 数据库编程

在mysql 数据库中，有mysql_install_db脚本初始化权限表，存储权限的表有：

1、user表

2、db表

3、host表

4、table_priv表

5、columns_priv表

6、proc_priv表

MySQL存取控制包含2个阶段：

阶段1：服务器检查你是否允许连接。 阶段2：假定你能连接，服务器检查你发出的每个请求。看你是否有足够的权限实施它。例如，如果你从数据库中一个表精选(select)行或从数据库抛弃一个表，服务器确定你对表有 select权限或对数据库有 drop权限。

服务器在存取控制的两个阶段使用在mysql的数据库中的user、db和host表，在这些授权表中字段如下：

表名称	user	db	host
范围字段	Host	Host	Host
?	User	Db	Db
?	Password	User	?
权限字段	Select_priv	Select_priv	Select_priv
?	Insert_priv	Insert_priv	Insert_priv
?	Update_priv	Update_priv	Update_priv
?	Delete_priv	Delete_priv	Delete_priv
?	Index_priv	Index_priv	Index_priv
?	Alter_priv	Alter_priv	Alter_priv
?	Create_priv	Create_priv	Create_priv
?	Drop_priv	Drop_priv	Drop_priv
?	Grant_priv	Grant_priv	Grant_priv
?	Reload_priv	?	?
?	Shutdown_priv	?	?
?	Process_priv	?	?
?	File_priv	?	?

对存取控制的第二阶段(请求证实)，如果请求涉及表，服务器可以另外参考tables_priv和columns_priv表。这些表的字段如下：

表名称	tables_priv	columns_priv
范围字段	Host	Host
?	Db	Db
?	User	User
?	Table_name	Table_name
?	?	Column_name
权限字段	Table_priv	Column_priv
?	Column_priv	?
其他字段	Timestamp	Timestamp
?	Grantor	?

每个授权表包含范围字段和权限字段。

user表主要分为：用户列、权限列、安全列、资源控制列

host表主要分为：用户列、权限列

这里美中不足的是mysql.user 没有一个列是保存用户创建时间的

有时候排查用户问题的时候，比如某个客户在某个时间说连接不上数据库，我们在user表里只能查到是否存在那个用户

但是不知道这个用户的创建时间，也就是说客户说的那个时间究竟用户是否已经创建我们是不知道的

帐户管理

MYSQL提供许多语句用来管理用户帐号，这些语句可以用来包括登录和退出MYSQL服务器、创建用户、删除用户、密码管理、权限管理

MYSQL数据库的安全性，需要通过帐户管理来保证

登录和退出MYSQL

mysql命令的常用参数

-h：主机名或ip，默认是localhost，最好指定-h参数

-u：用户名

-p：密码，注意：该参数后面的字符串和-p不能有空格

-P：端口号，默认为3306

数据库名：可以在命令最后指定数据库名

-e：执行SQL语句，如果指定该参数，将在登录后执行-e后面的命令或sql语句并退出

命令执行完之后返回book表的结构，查询返回之后会自动退出MYSQL

用户

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']]

新建普通用户

CREATE USER 'jeffrey'@'localhost' identified BY 'mypass';

用户名部分为“jeffrey”，主机名默认为“%”（即对所有主机开放权限）

如果指定用户登录不需要密码，则可以省略identified BY部分

对于使用插件认证连接的用户，服务器调用指定名称的插件，客户端需要提供验证方法所需要的凭据。

如果创建用户时或者连接服务器时，服务器找不到对应的插件，将返回一个错误

identified with语法

CREATE user 'jeffrey'@'localhost' identified with my_auth_plugin;

identified with只能在MYSQL5.5.7及以上版本使用。

identified with和identified by是互斥的，所以对一个帐户来说只能使用一个验证方法。

CREATE USER语句的操作会被记录到服务器日志文件或者操作历史文件中

例如 ~/.mysql_history。这意味着对这些文件有读取权限的人，都可以读取到新添加用户的明文密码

一个办法就是新建用户的时候使用password关键字

CREATE user 'tom'@'localhost' identified BY password'*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
SELECT password('mypass');
SELECT * FROM `mysql`.`user` WHERE `User` ='tom';

先查出你的密码的哈希值，然后在新建用户的时候输入哈希值

那么在日志里面就只能看到哈希值

使用GRANT语句创建新用户

GRANT USER语句可以用来创建帐户，通过该语句可以在user表中添加一条新记录

比起CREATE USER语句创建的新用户，还需要使用GRANT语句赋予用户权限

使用GRANT语句创建新用户时必须有GRANT权限。

语法

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER 'cipher' [AND]]
        [ISSUER 'issuer' [AND]]
        [SUBJECT 'subject']]
    [WITH with_option [with_option] ...]

使用GRANT语句创建一个新用户testUser，密码为testpwd，并授予用户对所有数据表的SELECT和UPDATE权限

GRANT SELECT ,UPDATE ON *.* TO 'testUser'@'localhost

MySQL优化之――权限管理(一)