用户设置

添加用户并赋权

1.添加至表

在 MySQL 数据库中的 user 表添加新用户,授权用户可进行 SELECT, INSERT 和 UPDATE操作权限

mysql> INSERT INTO user
    -> (host, user, password, 
    -> select_priv, insert_priv, update_priv) 
    -> VALUES ('localhost', 'user1',
    -> PASSWORD('123456'), 'Y', 'Y', 'Y');
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

执行后报错  ERROR 1054(42S22) Unknown column 'password' in ‘field list’
错误的原因是 5.7版本下的mysql数据库下已经没有password这个字段了,password字段改成了authentication_string

mysql> INSERT INTO user 
    -> (host, user, authentication_string, 
    -> select_priv, insert_priv, update_priv) 
    -> VALUES ('localhost', 'user1', 
    -> PASSWORD('123456'), 'Y', 'Y', 'Y');
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

出现错误的原因是mysql默认配置严格模式,该模式禁止通过insert的方式直接修改mysql库中的user表进行添加新用户。

MySQL默认是禁止这种方法来创建用户是为了数据库的安全,所以禁止写入有它的道理

2. 创建用户后再赋予权限

mysql> CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE USER 'cat@%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

localhost -- 只能在本地登录
% -- 任何电脑远程登陆

赋权:

GRANT privileges ON databasename.tablename TO 'username'@'host'
  • privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等,全部表示为ALL
  • 赋予全部数据库或表则用表示,赋予对整个数据库的操作权限用.* 表示
mysql> GRANT SELECT, INSERT, UPDATE ON mq.* TO 'dog'@'localhost';
Query OK, 0 rows affected (0.02 sec)

3.创建用户同时授权

mysql> grant all privileges on *.* to fish@localhost identified by '123456';
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

验证

[root@diudiu ~]# mysql -u fish -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

privileges权限表

更改用户密码

  1. SET PASSWORD

    mysql> set password for fish@localhost = password('123123');
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
  2. mysqladmin
    注意-p 不要和后面的密码分开写
[root@diudiu ~]# mysqladmin -ufish -p121212 password 111222
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
  1. 编辑user表
    好像不能直接编辑,pass掉

撤销用户权限