mysql密码和权限配置

最新推荐文章于 2024-06-14 08:00:00 发布

blog_zxb

最新推荐文章于 2024-06-14 08:00:00 发布

阅读量291

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/blog_zxb/article/details/105597436

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

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 PASSWOR
D '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION
1 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 '*6BB48
37EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION
1 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: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
1 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: *DB8D82DA1344341C0275AED3F8BD6E43A2409FB8
1 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 that
corresponds 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';