当前位置： 移动技术网 > IT编程>数据库>Mysql > MySQL查询用户权限的方法总结

MySQL查询用户权限的方法总结

2020年05月12日  | 移动技术网IT编程  | 我要评论

菲律宾圣安娜,日币对人民币汇率今日,刘谦平

介绍两种查看mysql用户权限的两种方法

1、 使用mysql grants命令

mysql> show grants for username@localhost;

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

| grants for root@localhost |

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

| grant all privileges on *.* to 'root'@'localhost' with grant option |

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

需要注意的是：

● username和ip的组合需要是在mysql.user表中存在的，具体可以通过 select * from mysql.user 命令查看

● ip地址如果是通配符格式需要加引号，例如：show grants for root@'172.%';

2、 使用mysql select命令

mysql> select * from mysql.user where user='root' and host='localhost' \g;
*************************** 1. row ***************************
host: localhost
user: root
password: **********************
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: mysql_native_password
authentication_string:
password_expired: n
1 row in set (0.01 sec)

 

知识点扩展：

我们来创建一个测试账号test，授予表层级的权限

mysql> drop user test;
query ok, 0 rows affected (0.00 sec)
mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
mysql> grant all on mydb.kkk to test@'%' identified by 'test';
query ok, 0 rows affected (0.01 sec)
mysql> 
mysql> show grants for test;
+-----------------------------------------------------------------------------------------------------+
| grants for test@% |
+-----------------------------------------------------------------------------------------------------+
| grant usage on *.* to 'test'@'%' identified by password '*94bdcebe19083ce2a1f959fd02f964c7af4cfc29' |
| grant all privileges on `mydb`.`kkk` to 'test'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from mysql.tables_priv\g;
*************************** 1. row ***************************
host: %
db: mydb
user: test
table_name: kkk
grantor: root@localhost
timestamp: 0000-00-00 00:00:00
table_priv: select,insert,update,delete,create,drop,references,index,alter,create view,show view,trigger
column_priv: 
1 row in set (0.01 sec)
error: 
no query specified
mysql> <br>

我们来创建一个测试账号test，授予列层级的权限

mysql> drop user test;
query ok, 0 rows affected (0.00 sec)
mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
mysql> grant select (id, col1) on mydb.test1 to test@'%' identified by 'test';
query ok, 0 rows affected (0.01 sec)
mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
mysql> 
mysql> select * from mysql.columns_priv;
+------+------+------+------------+-------------+---------------------+-------------+
| host | db | user | table_name | column_name | timestamp | column_priv |
+------+------+------+------------+-------------+---------------------+-------------+
| % | mydb | test | test1 | id | 0000-00-00 00:00:00 | select |
| % | mydb | test | test1 | col1 | 0000-00-00 00:00:00 | select |
+------+------+------+------------+-------------+---------------------+-------------+
2 rows in set (0.00 sec)
mysql> show grants for test;
+-----------------------------------------------------------------------------------------------------+
| grants for test@% |
+-----------------------------------------------------------------------------------------------------+
| grant usage on *.* to 'test'@'%' identified by password '*94bdcebe19083ce2a1f959fd02f964c7af4cfc29' |
| grant select (id, col1) on `mydb`.`test1` to 'test'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> <br>

到此这篇关于mysql查询用户权限的方法总结的文章就介绍到这了,更多相关两种mysql查询用户权限的方法内容请搜索移动技术网以前的文章或继续浏览下面的相关文章希望大家以后多多支持移动技术网！

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网