mysql用户主要分为_MySQL用户和权限管理

一. 创建用户

命令:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

说明：

username：你将创建的用户名

host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%

password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

例子：

CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';

CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';

CREATE USER 'pig'@'%' IDENTIFIED BY '123456';

CREATE USER 'pig'@'%' IDENTIFIED BY '';

CREATE USER 'pig'@'%';

二. 权限说明:

MySQL 的权限表在数据库启动的时候就载入内存，当用户通过身份认证后，就在内存中进行相应权限的存取，这样，此用户就可以在数据库中做权限范围内的各种操作了。

mysql 的权限体系大致分为5个层级:

全局层级:全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。

数据库层级:数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。

表层级:表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。

列层级:列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。

子程序层级:CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。 这些权限信息存储在下面的系统表中: mysql.user mysql.db mysql.host mysql.table_priv mysql.column_priv 当用户连接进来，mysqld会通过上面的这些表对用户权限进行验证！

注意:权限表的存取

在权限存取的两个过程中，系统会用到 “mysql” 数据库(安装 MySQL 时被创建，数据库名称叫“mysql”) 中 user、host 和 db这3个最重要的权限表。在这 3 个表中，最重要的是 user 表，其次 db 表，host 表在大多数情况下并不使用。

user 中的列主要分为 4 个部分：用户列、权限列、安全列和资源控制列。

通常用的最多的是用户列和权限列，其中权限列又分为普通权限和管理权限。普通权限用于数据库的操作，比如 select_priv、super_priv 等。

当用户进行连接时，权限表的存取过程有以下两个过程：先从 user 表中的 host、user 和 password 这 3 个字段中判断连接的 IP、用户名、和密码是否存在于表中，如果存在，则通过身份验证，否则拒绝连接。如果通过身份验证、则按照以下权限表的顺序得到数据库权限：user -> db -> tables_priv -> columns_priv。

三.授权

命令:

GRANT privileges ON databasename.tablename TO 'username'@'host'

说明:

privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL

databasename：数据库名

tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

例子:

GRANT SELECT, INSERT ON test.user TO 'pig'@'%';

GRANT ALL ON *.* TO 'pig'@'%';

GRANT ALL ON maindataplus.* TO 'pig'@'%';

注意:

用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

注:添加用户或者权限，使用mysql> flush privileges; 刷新权限

四.设置与更改用户密码

命令:

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

如果是当前登陆用户用:

SET PASSWORD = PASSWORD("newpassword");

例子:

SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");

五. 撤销用户权限

命令:

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

说明:

privilege, databasename, tablename：同授权部分

例子:

REVOKE SELECT ON *.* FROM 'pig'@'%';

注意:

假如你在给用户'pig'@'%'授权的时候是这样的(或类似的)：GRANT SELECT ON test.user TO 'pig'@'%'，则在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT操作。相反，如果授权使用的是GRANT SELECT ON *.* TO 'pig'@'%';则REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select权限。

具体信息可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看。

六.删除用户

命令:

DROP USER 'username'@'host';

注意:

usage 权限不能被回收，也就是说，revoke 用户并不能删除用户。

要彻底的删除账号，还是得使用 drop user

七.账号资源限制

创建 MySQL 账号时，还有一类选项称为账号资源限制，这类选项的作用是限制每个账号实际具有的资源限制，这里的“资源”主要包括：

max_queries_per_hour count : 单个账号每小时执行的查询次数

max_upodates_per_hour count : 单个账号每小时执行的更新次数

max_connections_per_hour count : 单个账号每小时连接服务器的次数

max_user_connections count : 单个账号并发连接服务器的次数

当某个用户的max_user_connections非0时，则忽略全局系统参数对应的配置，反之则使用全局参数。

命令：

mysql> create user temp@'%' identified by 'temp' WITH MAX_QUERIES_PER_HOUR 20

MAX_UPDATES_PER_HOUR 10  MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2;

mysql> SELECT u.Host,u.User,u.max_questions,u.max_updates,u.max_connections,

u.max_user_connections FROM mysql.user u WHERE u.user='temp';

mysql> show variables like '%max_user_connections%';

取消某项资源限制既把原先的值改成0,0表示不限制资源。

mysql> alter user temp@'%' identified by 'temp' WITH MAX_QUERIES_PER_HOUR 0

MAX_UPDATES_PER_HOUR 0  MAX_CONNECTIONS_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

mysql> SELECT u.Host,u.User,u.max_questions,u.max_updates,u.max_connections,

u.max_user_connections FROM mysql.user u WHERE u.user='temp';

总结：账号的资源限制除非是做精细化管理，否则在测试生产环境上的功能都比较鸡肋。创建账号使用默认设置即可。