mysql用户及权限管理
发布时间：2021-07-14 18:23:30 作者：admin 阅读：1048次
root@bananapi /home/pi # cat /etc/mysql/my.cnf | grep bind
#bind-address = 127.0.0.1
1、mysql创建账户
mysql> select *from user where 1 \G;
mysql> select Host,User,Password from user;
mysql> create user 'test'@'localhost' identified by 'test'
mysql> insert into user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) values('localhost','test3',PASSWORD('123456'),'','','');
mysql> grant select on *.* to 'test4'@'localhost' identified by '123456';
2、mysql删除账户
mysql> drop user 'test4'@'localhost';
mysql> delete from user where Host='localhost' AND User='test3';
3、修改密码
root@test:~# mysqladmin -uroot -p password '123456'
Enter password:
root@test:~# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@test:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.13-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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> 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> select Host,User,Password from user;
+----------------------+-----------------+-------------------------------------------+
| Host | User | Password |
+----------------------+-----------------+-------------------------------------------+
| localhost | root | *7D49C188B3440558036967373D6EE374A29326EC |
| test-Aspire-E1-571G | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | backupuser | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 10.10.20.136 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 211.151.107.178 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| % | test1@localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | test2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----------------------+-----------------+-------------------------------------------+
10 rows in set (0.00 sec)
mysql> update mysql.user set Password=PASSWORD('123456')
-> where user='test2'
-> and Host='localhost'
-> ;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select Host,User,Password from user;
+----------------------+-----------------+-------------------------------------------+
| Host | User | Password |
+----------------------+-----------------+-------------------------------------------+
| localhost | root | *7D49C188B3440558036967373D6EE374A29326EC |
| test-Aspire-E1-571G | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | backupuser | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 10.10.20.136 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 211.151.107.178 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| % | test1@localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | test2 | *7D49C188B3440558036967373D6EE374A29326EC |
+----------------------+-----------------+-------------------------------------------+
10 rows in set (0.00 sec)
mysql> update mysql.user set Password=PASSWORD('123456') where user='test2' and Host='localhost';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> set password=password('123456');
Query OK, 0 rows affected (0.00 sec)
mysql> select Host,User,Password from user;
+----------------------+-----------------+-------------------------------------------+
| Host | User | Password |
+----------------------+-----------------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| test-Aspire-E1-571G | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | backupuser | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 10.10.20.136 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 211.151.107.178 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| % | test1@localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | test2 | *7D49C188B3440558036967373D6EE374A29326EC |
+----------------------+-----------------+-------------------------------------------+
10 rows in set (0.00 sec)
mysql> set password for 'localhost'@'test2'=password('123456');
mysql> set password for 'test2'@'localhost'=password('123456');
Query OK, 0 rows affected (0.00 sec)
mysql> select Host,User,Password from user;
+----------------------+-----------------+-------------------------------------------+
| Host | User | Password |
+----------------------+-----------------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| test-Aspire-E1-571G | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | backupuser | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 10.10.20.136 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 211.151.107.178 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| % | test1@localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | test2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----------------------+-----------------+-------------------------------------------+
10 rows in set (0.00 sec)
mysql> update mysql.user set Password=password('123456') where User='test2' and Host='localhost';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select Host,User,Password from user;
+----------------------+-----------------+-------------------------------------------+
| Host | User | Password |
+----------------------+-----------------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| test-Aspire-E1-571G | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | backupuser | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 10.10.20.136 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 211.151.107.178 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| % | test1@localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | test2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----------------------+-----------------+-------------------------------------------+
10 rows in set (0.00 sec)
mysql> grant select on *.* to 'test2'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select Host,User,Password from user;
+----------------------+-----------------+-------------------------------------------+
| Host | User | Password |
+----------------------+-----------------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| test-Aspire-E1-571G | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | backupuser | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 10.10.20.136 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 211.151.107.178 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| % | test1@localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | test2 | *7D49C188B3440558036967373D6EE374A29326EC |
+----------------------+-----------------+-------------------------------------------+
10 rows in set (0.00 sec)
mysql>
普通用户自己修改自己的密码
mysql>set password=password('123456');
3、root密码忘记
root@test:~# netstat -ntlp|grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 14871/mysqld
root@test:~# killall mysqld
root@test:~# killall mysqld
root@test:~# killall mysqld131202 10:18:49 mysqld_safe mysqld from pid file /usr/local/mysql/data/test.pid ended
[1]+ 完成 /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql (工作目录：/usr/local/mysql/data)
(当前工作目录：~)
root@test:~# killall mysqld
mysqld：没有发现操作
root@test:~# ps aux|grep mysql
root 19086 0.0 0.0 5824 816 pts/0 S+ 10:18 0:00 grep --color=auto mysql
root@test:~# /usr/local/mysql/bin//mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --user=mysql &
[1] 19093
root@test:~# 131202 10:19:26 mysqld_safe Logging to '/usr/local/mysql/data/error.log'.
131202 10:19:26 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
^C
root@test:~# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.13-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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> select user();
+--------+
| user() |
+--------+
| root@ |
+--------+
1 row in set (0.00 sec)
mysql> exit
Bye
root@test:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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> select user();
+--------+
| user() |
+--------+
| root@ |
+--------+
1 row in set (0.00 sec)
mysql> set password=password('123456');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
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> update mysql.user set password=password('123456') where user='root' and host='localhost';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)
mysql> exit
Bye
root@test:~# killall mysqld
root@test:~# killall mysqld
root@test:~# 131202 10:21:02 mysqld_safe mysqld from pid file /usr/local/mysql/data/test.pid ended
killall mysqld
mysqld：没有发现操作
[1]+ 完成 /usr/local/mysql/bin//mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --user=mysql
root@test:~# /usr/local/mysql/bin//mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
[1] 19601
root@test:~# 131202 10:21:09 mysqld_safe Logging to '/usr/local/mysql/data/error.log'.
131202 10:21:09 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
^C
root@test:~# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@test:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
权限
mysql> select *, user,password,host from user where user='test' \G;
*************************** 1. row ***************************
Host: localhost
User: test
Password: *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
user: test
password: *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29
host: localhost
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> grant select,update on *.* to 'test'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select *, user,password,host from user where user='test' \G;
*************************** 1. row ***************************
Host: localhost
User: test
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: Y
Insert_priv: N
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
user: test
password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
host: localhost
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> revoke select on *.* from 'user'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'user' on host 'localhost'
mysql> revoke select on *.* from 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select *, user,password,host from user where user='test' \G;
*************************** 1. row ***************************
Host: localhost
User: test
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: N
Insert_priv: N
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
user: test
password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
host: localhost
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> grant select,update on *.* to 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select *, user,password,host from user where user='test' \G;
*************************** 1. row ***************************
Host: localhost
User: test
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: Y
Insert_priv: N
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
user: test
password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
host: localhost
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> grant select,update on *.* to 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select *, user,password,host from user where user='test' \G;
*************************** 1. row ***************************
Host: localhost
User: test
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: Y
Insert_priv: N
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
user: test
password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
host: localhost
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> grant create on *.* to 'test'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> select *, user,password,host from user where user='test' \G;
*************************** 1. row ***************************
Host: localhost
User: test
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: Y
Insert_priv: N
Update_priv: Y
Delete_priv: N
Create_priv: Y
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: Y
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
user: test
password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
host: localhost
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select *, user,password,host from user where user='test2' \G;
*************************** 1. row ***************************
Host: localhost
User: test2
Password: *7D49C188B3440558036967373D6EE374A29326EC
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
user: test2
password: *7D49C188B3440558036967373D6EE374A29326EC
host: localhost
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> grant all privileges on *.* to 'test2'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> select *, user,password,host from user where user='test2' \G;
*************************** 1. row ***************************
Host: localhost
User: test2
Password: *7D49C188B3440558036967373D6EE374A29326EC
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: NULL
user: test2
password: *7D49C188B3440558036967373D6EE374A29326EC
host: localhost
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> revoke all privileges from 'test2'@'localhost' ;
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 'from 'test2'@'localhost'' at line 1
mysql> revoke all privileges,grant option from 'test2'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)
mysql> select *, user,password,host from user where user='test2' \G;
*************************** 1. row ***************************
Host: localhost
User: test2
Password: *7D49C188B3440558036967373D6EE374A29326EC
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
user: test2
password: *7D49C188B3440558036967373D6EE374A29326EC
host: localhost
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show grants for 'test'@'localhost';
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, CREATE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
如有问题，可以QQ搜索群1028468525加入群聊，欢迎一起研究技术
有疑问联系站长，请联系QQ：
上一篇：mysql默认引擎以及编码
下一篇：mysqlhotcopy
转载请注明：mysql用户及权限管理 出自老鄢博客 | 欢迎分享