MySQL的用户管理

1.创建用户：

mysql> create user mysqluser identified by 'mysqluser';
Query OK, 0 rows affected (0.00 sec)

账户信息：

mysql> select host,user,authentication_string from  mysql.user where user='mysqluser';
+------+-----------+-------------------------------------------+
| host | user      | authentication_string                     |
+------+-----------+-------------------------------------------+
| %    | mysqluser | *3A307C6C3EB8C91C40676C2428752F3616A5BF63 |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

2.授权：

命令格式：grant privileges Code on dbName.tableName to username@host identified by "password";

grant all privileges on mydb.* to mysqluser@'%' identified by 'mysqluser';

flush privileges;

在mysql.db表里可以查看到新增数据库权限的信息：

mysql> select host,db,user from mysql.db where user='mysqluser';
+------+------+-----------+
| host | db   | user      |
+------+------+-----------+
| %    | mydb | mysqluser |
+------+------+-----------+
1 row in set (0.00 sec)

可以通过show grants 来查看权限：

mysql> show grants for mysqluser;
+-----------------------------------------------------+
| Grants for mysqluser@%                              |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'mysqluser'@'%'               |
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'mysqluser'@'%' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

3.删除用户：

drop user mysqluser@'%';

drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。

4.验证：

[mysql@master ~]$ mysql -u mysqluser -p
Enter password: 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
+--------------------+
2 rows in set (0.00 sec)

information_schema数据库是MySQL系统自带的数据库，它提供了数据库元数据的访问方式。感觉information_schema就像是MySQL实例的一个百科全书，记录了数据库当中大部分我们需要了结的信息，比如字符集，权限相关，数据库实体对象信息，外检约束，分区，压缩表，表信息，索引信息，参数，优化，锁和事物等等。通过information_schema我们可以窥透整个MySQL实例的运行情况，可以了结MySQL实例的基本信息，甚至优化调优，维护数据库等