Mysql如何对用户授权

Mysql用户授权

一，创建用户

1),在终端命令行输入 mysqladmin -u root -p password "新密码" 回车 ，Enter password: 【输入原来的旧密码】

2),登录mysql系统修改， mysql -uroot -p 回车 Enter password: 【输入原来的密码】

mysql> use mysql;

mysql>UPDATE user SET authentication_string=PASSWORD("Gm02_prd8!") WHERE user='root';

PS:【密码注意大小写】

mysql> flush privileges;

mysql> exit;   

二、修改用户名

mysql> use mysql;

mysql> update user set user ='TrustPrd' where user = 'Trust_Prd';

mysql> flush privileges;

用户授权命令：

实例//设置用户testuser，只能访问数据库test_db，其他数据库均不能访问 ；

Grant all privileges on test_db.* to testuser@localhost identified by “123456” ;

制章

grant all privileges on  seal_make_sm2.* to Make_Prd@localhost identified by 'GmMake18_Prd7!' ;

信任支撑

grant all privileges on  certificate_manager.* to TrustPrd@localhost identified by 'GmTrust20_Prd9!' ;

管理

grant all privileges on  seal_manager_sm2.* to Make_Prd@localhost identified by 'GmMake18_Prd7!' ;

发布

grant all privileges on  seal_publish_sm2.* to Publish_Prd@localhost identified by 'GmPublish19_Prd8!' ;

  3. 回收普通用户全局权限

    revoke all privileges on *.* from 'Make_Prd'@'%'; 

    revoke all privileges on *.* from 'Publish_Prd'@'%';

    revoke all privileges on *.* from 'TrustPrd'@'%';   

  4. 刷新权限操作

    flush privileges;

1. 登陆验证

sudo docker exec -it 容器ID mysql -u Make_Prd -pGmMake18_Prd7!   

    sudo docker exec -it 容器ID mysql -u Publish_Prd -pGmPublish19_Prd8!

    sudo docker exec -it 容器ID mysql -u TrustPrd -pGmTrust20_Prd9!     

  6.回收部分数据库权限可参考:    

revoke all privileges on 数据库名.* from '数据库账户'@'%';

1. 用户授权实例效果图

授权

Last login: Wed Jul 10 14:33:44 2019 from 192.168.105.200

[gomain@yinzhang ~]$ sudo su

[sudo] password for gomain:

[root@yinzhang gomain]# docker ps -a

CONTAINER ID   IMAGE                                COMMAND          CREATED       STATUS      PORTS  NAMES

cb64bcded2be   gmprd.baiwang-inner.com/lhl-mysql:5.7.24  "docker-entrypoint..."  2 months ago   Up 2 months         mysql5.7.24

813885bc2041   gmprd.baiwang-inner.com/redis:4.0.10     "redis-server /opt..."   4 months ago   Up 3 months          gmprd-redis0001

73729cbdcfe6   gmprd.baiwang-inner.com/redis:4.0.10      "redis-sentinel /e..."   4 months ago   Up 3 months         gmprd-sentinel0001

[root@yinzhang gomain]# docker exec -ti mysql5.7.24 mysql -u root -pGmRmake10_Prd1!

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ceshi              |

| mysql              |

| performance_schema |

| seal_make_sm2      |

| seal_manager_sm2   |

| sys                |

+--------------------+

7 rows in set (0.00 sec)

mysql> grant all privileges on  seal_make_sm2.* to Make_Prd@localhost identified by 'GmMake18_Prd7!' ;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on  seal_manager_sm2.* to Make_Prd@localhost identified by 'GmMake18_Prd7!' ;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@yinzhang gomain]# docker exec -ti mysql5.7.24 mysql -u Make_Prd -pGmMake18_Prd7!

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| seal_make_sm2      |

| seal_manager_sm2   |

+--------------------+

3 rows in set (0.00 sec)

mysql>exit