oracle修改MySQL授权协议,MySQL修改用户密码与授权

MySQL修改用户密码与授权

通过grant授权方式

此方法8.0版本不支持

grant 权限 on 库名.表名 to '用户名'@'IP' identified by '密码';

mysql> grant all privileges on *.* to 'root'@'localhost' identified by '000000';

Query OK, 0 rows affected (0.03 sec)

mysql> grant all privileges on *.* to 'root'@'%' identified by '000000';

Query OK, 0 rows affected (0.00 sec)

通过set password

Syntax:

SET PASSWORD [FOR user] = password_option

password_option: {

PASSWORD('auth_string')

| OLD_PASSWORD('auth_string')

| 'hash_string'

}

mysql> set password for root@'localhost' =password('000000');

Query OK, 0 rows affected (0.04 sec)

忘记密码情况

[root@server ~]# systemctl stop mysqld

[root@server ~]# mysqld_safe --skip-grant-tables --skip-networking

190801 04:33:20 mysqld_safe Logging to '/var/log/mysqld.log'.

190801 04:33:20 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@server ~]# mysql#可直接登陆

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.61 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> update mysql.user set password=password('123456') where host='localhost' and user='root';

Query OK, 1 row affected (0.11 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@server ~]# systemctl restart mysqld

[root@server ~]# mysql -uroot -p123456

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.61 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

查看授权信息

MySQL是通过IP地址和用户名联合进行确认的。MySQL的权限表在数据库中启动时就载入内存，当用户通过身份认证后，就在内存中进行相应的存取，这样用户就可以在数据库中做权限范围内的各种操作。

当用户进行连接时，权限表的存取过程有以下两个阶段

先从user表中的host、user和password这三个字段中判读连接的IP、用户名和密码是否存在与表中，如果存在，则通过身份验证，否则拒绝连接。

如果通过身份验证，则按照以下权限表的顺序得到数据库权限：

user–>db–>tables_priv–>columns_priv

mysql> select * from mysql.user where user='root' and host='localhost'\G

*************************** 1. row ***************************

Host: localhost

User: root

Password: *032197AE5731D4664921A6CCAC7CFCE6A0698693

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin:

authentication_string:

1 row in set (0.00 sec)

#查看账号权限

show grants for user@host;

host可以不写。默认是%

mysql> show grants for root\G

*************************** 1. row ***************************

Grants for root@%: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693'

1 row in set (0.00 sec)

mysql> show grants for root@localhost\G

*************************** 1. row ***************************

Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' WITH GRANT OPTION

*************************** 2. row ***************************

Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

2 rows in set (0.00 sec)

回收权限

Syntax:

REVOKE

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

FROM user [, user] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION

FROM user [, user] ...

REVOKE PROXY ON user

FROM user [, user] ...

mysql> grant select,insert on *.* to 'test'@'localhost' identified by '000000';

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test'@'localhost'\G

*************************** 1. row ***************************

Grants for test@localhost: GRANT SELECT, INSERT ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693'

1 row in set (0.00 sec)

#收回select权限

mysql> revoke select on *.* from 'test'@'localhost'

-> ;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test'@'localhost'\G

*************************** 1. row ***************************

Grants for test@localhost: GRANT INSERT ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693'

1 row in set (0.00 sec)