MySQL用户管理

 MySQL是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定允许的权限。MySQL用户可以分普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。用户管理包括管理用户账户、权限等。

    一、权限表

    1、user表

    user表是MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息，里面的权限是全局级的。例如：一个用户在user表重被授予了DELETE权限，则该用户可以删除MySQL服务器上所有数据库中的任何记录。

    （1）用户列

    user表的用户列包括Host、User、Password，分别表示主机名、用户名和密码。其中User和Host为User表的联合主键。

    （2）权限列

    权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。包括查询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等高级权限。普通权限用于操作数据库；高级权限用于数据库管理。

    user表中对应的权限是针对所有用户数据库的。这些字段值得类型为ENUM，可以取的值只能为Y和N，Y表示该用户有对应的权限；N表示用户没有对应的权限。

    （3）安全列

    安全列只有6个字段，其中两个是ssl相关的（加密），2个是x509相关的（标示用户），另外2个是授权插件相关的。

    （4）资源控制列

    资源控制列的字段用来限制用户使用的资源，包含4个字段，分别为：

    max_question——用户每小时允许执行的查询操作次数

    max_updates——用户每小时允许执行的更新操作次数

    max_connections——用户每小时允许执行的连接操作次数

    max_user_connections——用户允许同时建立的连接次数

    

    2、db表和host表

    db表中存储了用户对某个数据库的操作权限，决定用户能够从哪个主机存取哪个数据库。host表中存储了某个主机对数据库的操作权限，配合db权限表对给定主机上的数据库级操作权限做更细致的控制。

    

    3、    tables_priv表和columns_priv表

    tables_priv表用来对表设置操作权限，columns_priv表用来对表的某一列设置权限。

    tables_priv表有8个字段，分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和Column_priv，各个字段说明如下：

    Host、Db、User和Table_name4个字段分别表示主机名、数据库名、用户名和表名。

    Grantor表示修改该记录的用户。

    Timestamp字段表示修改该记录的时间。

    Table_priv表示对表的操作权限包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index和Alter。

    Columns_priv字段表示对表中的列的操作权限，包括Select、Insert、Update和References。

 

    4、procs_priv表

    procs_priv表可以对存储过程和存储函数设置操作权限。

    procs_priv表包含8个字段，分别是Host、Db、User、Routine_name、Routine_type、Grantor、Proc_priv和Timestamp。

    Host、Db和User表字段分别表示主机名、数据库名和用户名。Routine_name表示存储过程或函数的名称。

    Routine_type表示存储过程或函数的类型

    Routine_type字段有两个值，分别是FUNCTION和PROCEDURE。前者表示函数；后者表示存储过程。

    Grantor是插入或修改该记录的用户。

    Proc_priv表示拥有的权限，包括Execute、Alter Routine、Grant3种。

    Timestamp表示记录更新时间。

 

二、新建普通用户

    创建新用户，必须有相应的权限来执行创建操作。在MySQL数据库中，有两种方式创建新用户：一种是使用CREATE USER或GRANT语句；另一种是直接操作MySQL授权表。

    eg：CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

 

    GRANT privileges ON db.table TO user@host [IDENTIFIED BY 'password'] [, user [IDENTIFIED BY 'password']] [WITH GRANT OPTION]

    privileges表示赋予用户的权限类型；db.table表示用户的权限所作用的数据库中的表；IDENTIFITY BY关键字用来设置密码；'password'表示用户密码；WITH GRANT OPOTION为可选参数，表示对新建立的用户赋予GRANT权限，即该用户可以对其他用户赋予权限。

 

三、删除普通用户

    DROP USER user[,user];

    eg：

    DROP USER 'user'@'localhost';

    DROP USER;

 

四、root用户修改密码

    （1）使用mysqladmin命令在命令行指定新密码

    mysqladmin -u username -h localhost -p passwprd "newpwd"

    （2）修改mysql数据库的use表

    UPDATE mysql.user set Password=PASSWORD("rootpwd") WHERE User="root"  and Host="localhost";

 

五、普通用户修改密码

    root用户修改普通用户的密码

    SET PASSWORD FOR 'user'@'host' = PASSWORD('somepasswprd');

    普通用户修改自己的密码

    SET PASSWORD = PASSWORD('somepassword')；

    修改密码完成后，必须使用FLUSH PRIVILEGES语句加载权限表。

 

六、权限管理

    GRANT和REVOKE语句所涉及的权限的名称：

　

   

 

七、数据备份

    （1）使用mysqldump命令备份

    mysqldump -u user -h host -p password dbname[tbname,[tbname]] > filename.sql

    备份单个数据库中的所有表

    eg:

    mysqldump -u root -p booksdb > C:\backup\booksdb_20180316.sql;

    备份数据库中的某个表

    mysqldump -u user -h host -p dbname [tbname, [tbname...]] >  filename.sql   

    eg：

    mysqldump -u root -p booksDB books > C:\backup\booksdb_20180316.sql;

    备份多个数据库

    mysqldump -u user -h host -p --database [dbname,[dbname...]] >  filename.sql

    eg：

    mysqldump -u root -p --database booksDB test > C:\backup\booksdb_20180316.sql;

    备份所有数据库

    mysqldump -u user -h host -p --all-database > filename.sql;

    

八、数据还原

    mysql -u user -p [dbname] < filename.sql