Mysql用户和权限管理

3qwe

用户：
MySQL的用户和操作系统没有关系
用户公开的，密码加密的   加密方式：password函数

用户帐号：
    用户名@主机
        用户名：16字符以内
        主机：
            主机名：www.benet.com, mysql
            IP: 192.168.1.1
            网络地址:
                192.168.1.0/255.255.255.0

            通配符：%，_
                192.168.%.%
                %.benet.com

创建用户的两种方法：
1.使用命令创建。最基本权限，一般建立完成之后会自动读取权限列表
语法：
CREATE USER username@host [IDENTIFIED BY 'password']
例子：创建用户为test，主机为通配符，密码为test
mysql> CREATE USER test@'%' IDENTIFIED BY 'test';

2.直接在MySQL.user表中插入用户信息
INSERT INTO mysql.user
插入数据不会重新读取权限，需要手动刷新
mysql> FLUSH PRIVILEGES;

删除用户
DROP USER 'username'@'localhost';
重命名用户
RENAME USER 旧用户名 TO 新用户名;

权限
设置用户权限（用户不存在时，则新建用户）
GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [ IDENTIFIED BY ‘密码’ ]
查看用户的权限
SHOW GRANTS FOR 用户名@域名或IP
撤销用户的权限
REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@域名或IP

操作：
查看用户权限
mysql> SHOW GRANTS FOR test@'%';

1 2 3 4 5

+-----------------------------------------------------------------------------------------------------+ | Grants for test@%                                                                                   | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' | +-----------------------------------------------------------------------------------------------------+

使用test用户登录数据库
查看数据库只能看到一个数据库
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+-------------------+
创建一个数据库，失败
mysql> CREATE DATABASE aa;
ERROR 1044 (42000): Access denied for user 'test'@'%' to database 'aa'

允许test用户创建数据库
mysql> GRANT CREATE ON testdb.* TO 'test'@'%';


使用test用户创建数据库没有报错
mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
进入testdb数据库创建表成功
mysql> USE testdb;
Database changed
mysql> CREATE TABLE testtb (ID INT UNSIGNED AUTO_INCREMENT NOT NULL, Name CHAR(20), PRIMARY KEY(ID));
Query OK, 0 rows affected (0.21 sec)

往testtb表中插入数据报错
mysql> INSERT INTO testdb (Name) VALUES ('zhangsan');
ERROR 1142 (42000): INSERT command denied to user 'test'@'localhost' for table 'testdb'

授予test用户往testdb.*中所有表插入数据的权限
mysql> GRANT INSERT ON testdb.* TO 'test'@'%';  

test用户重新连接数据库，（必须重新连接数据库）插入数据成功
mysql> INSERT INTO testtb (Name) VALUES ('zhangsan');
Query OK, 1 row affected (0.05 sec)

允许test用户添加字段
mysql> GRANT ALTER ON testdb.* TO 'test'@'%';      
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

使用test用户给testtb表插入一个Age字段
mysql> ALTER TABLE testtb ADD Age TINYINT UNSIGNED;


设置test用户只能更改年龄字段
mysql> SELECT * FROM testtb;
+----+----------+------+
| ID | Name     | Age  |
+----+----------+------+
|  1 | zhangsan | NULL |
+----+----------+------+
授权test可以更改年龄字段
mysql> GRANT UPDATE(Age) ON testdb.testtb TO 'test'@'%';

验证test更改用户年龄成功，更改姓名报错
mysql> UPDATE testtb SET Age=30 WHERE ID=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE testtb SET Name=lisi WHERE ID=1;        
ERROR 1143 (42000): UPDATE command denied to user 'test'@'localhost' for column 'Name' in table 'testtb'

查看自己的权限
字段级别权限单独存储
mysql> SHOW GRANTS FOR 'test'@'%';

1 2 3 4 5 6 7

+-----------------------------------------------------------------------------------------------------+ | Grants for test@%                                                                                   | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' | | GRANT SELECT, INSERT, CREATE, ALTER ON `testdb`.* TO 'test'@'%'                                     | | GRANT UPDATE (Age) ON `testdb`.`testtb` TO 'test'@'%'                                               | +-----------------------------------------------------------------------------------------------------+

收回用户的SELECT权限
mysql> REVOKE SELECT ON testdb.* FROM 'test'@'%';