mysql创建一个用户并配置权限

前提条件：登录到mysql

格式：grant <privileges> on <database>.<table> to <user>@<host> identified by <password> [with grant option]

参数解释：

privileges：表示要给这个用户分配什么样的权限，使用all表示所有权限

database：表示要给用户分配的权限是在哪个数据库上，使用*表示所有数据库

table：表示要给用户分配的权限是在哪个数据库上的那个表，使用*表示所有表

user：表示要给那个用户分配权限

host：表示用户可以从个ip来登录到mysql修改，使用%表示可以从任何ip登录，使用localhost表示只能从本机登录

password：表示用户的登录密码

with grant option：可选参数，表示这个新建的用户添加grant权限，即新建用户可以给别的账号配置权限

例子：创建一个zxb用户，有全部数据库全部表的全部权限，以及grant权限

# 添加用户zxb并配置全部权限mysql> grant all on *.* to 'zxb'@'%' identified by '123456' with grant option;Query OK, 0 rows affected (0.00 sec)# 刷新权限mysql> flush privileges;# 查看用户zxb的权限mysql> show grants for 'zxb'@'%'\G*************************** 1. row ***************************Grants for zxb@%: GRANT ALL PRIVILEGES ON *.* TO 'zxb'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION1 row in set (0.00 sec)

删除权限

例子：删除zxb用户的全部数据库全部表的全部权限

# 删除用户zxb的全部权限mysql> revoke all on *.* from 'zxb'@'%';# 查看权限，发现只有usage权限了mysql> show grants for 'zxb'@'%'\G*************************** 1. row ***************************Grants for zxb@%: GRANT USAGE ON *.* TO 'zxb'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION1 row in set (0.00 sec)

mysql修改密码

目标：修改用户zxb这个账号的密码为zxb123

1、登录到mysql(旧版mysql)

格式：mysql> set password for <user>@<host> = password(<password>);

mysql> select password from mysql.user where user='zxb' and host='%'\G*************************** 1. row ***************************password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD91 row in set (0.00 sec)mysql> set password for 'zxb'@'%'=password('zxb123');Query OK, 0 rows affected (0.00 sec)# 查看结果mysql> select password from mysql.user where user='zxb' and host='%'\G*************************** 1. row ***************************password: *DB8D82DA1344341C0275AED3F8BD6E43A2409FB81 row in set (0.00 sec)

2、修改mysql.user表的数据(旧版mysql)

格式：mysql> update mysql.user set password=password(<password>) where user=<user> and host=<host> ;

mysql> update mysql.user set password=password('123456') where user='zxb' and host='%';Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0

3、新版数据库修改密码

格式：mysql> alter user <user>@<host> identified by <password>;

# 新版5.7之后，修改密码的方式，当前用的版本是5.5.39，所以会报错mysql> alter user 'zxb'@'%' identified by 'zxb123';ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'user'zxb'@'%' identified by 'zxb123'' at line 1# 查看数据库版本mysql> select version();+-----------+| version() |+-----------+| 5.5.39    |+-----------+1 row in set (0.00 sec)

ps： mysql的user表使用user和host两个字段作为主键：PRIMARY KEY (`Host`,`User`)

下面是mysql的user表的表结构

CREATE TABLE `user` (  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',  `ssl_cipher` blob NOT NULL,  `x509_issuer` blob NOT NULL,  `x509_subject` blob NOT NULL,  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',  `plugin` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',  `authentication_string` text COLLATE utf8_bin NOT NULL,  PRIMARY KEY (`Host`,`User`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';

转载地址：http://usyvb.baihongyu.com/

你可能感兴趣的文章

两种HDR格式(HLG, HDR10)的理解

查看>>

视频主观质量对比工具(Visual comparision tool based on ffplay)