mysql create user grant

1.通过mysql数据库的user表查看用户相关信息

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> desc mysql;
//表结构
mysql> select host,user from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *87F2746835A04895BB77E12AA5054A767******* |
| qxyw      | root |                                           |
| 127.0.0.1 | root |                                           |
| localhost |      |                                           |
| qxyw      |      |                                           |
+-----------+------+-------------------------------------------+
rows in set (0.00 sec)

2.创建数据库

mysql> create database [databasename] default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

3.创建用户

mysql> create user 'dba'@'%' identified by '*******';
Query OK, 0 rows affected (0.00 sec)

user表中host列的值的意义

%               匹配所有主机

localhost    localhost不会被解析成IP地址,直接通过UNIXsocket连接

127.0.0.1   会通过TCP/IP协议连接,并且只能在本机访问;

::1              ::1就是兼容支持ipv6的,表示同ipv4的127.0.0.1

4.对dba用户给予针对指定数据库进行增删改查的权限

#部分权限
mysql> grant select,insert,update,delete,create on databasename.* to username;
Query OK, 0 rows affected (0.00 sec)
#全部权限
mysql> grant all privileges on bee.* to dev;
#drop权限
mysql> grant drop on databasename.* to username;
Query OK, 0 rows affected (0.00 sec)

注意:修改完权限以后 一定要刷新服务,或者重启服务,刷新服务用:

flush privileges; 

5.可以通过show grants命令查看权限,若想要在原来的基础上增加权限则继续执行grant

mysql> show grants for username;
+----------------------------------------------------------------------------------------------------+
| Grants for username@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `[databasename]`.* TO 'username'@'%'              |
+----------------------------------------------------------------------------------------------------+
rows in set (0.00 sec)

6.通过revoke命令可以移除用户的相关权限

mysql> revoke drop on databasename.* from username;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for username;
+----------------------------------------------------------------------------------------------------+
| Grants for username@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `[databasename]`.* TO 'username'@'%'                    |
+----------------------------------------------------------------------------------------------------+
rows in set (0.00 sec)

7.修改密码

mysql> use mysql;
# mysql5.7之前
mysql> update user set password=password('123456') where user='root';
# mysql5.7之后
mysql> update user set authentication_string=password('123456') where user='root';
mysql> flush privileges;
#mysql8.0之后
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NewPassword';
mysql> flush privileges;
#mysql 8.0.11之后
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; 
mysql> flush privileges;

grant:详细参考https://blog.csdn.net/anzhen0429/article/details/78296814

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值