mysql查询_MySQL查看用户权限的两种方法

MySQL查看用户权限命令的两方法:

一. 使用MySQL grantsmysql> show grants for username@localhost;实例:mysql> show grants for root@localhost;

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

| Grants for root@localhost |

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

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

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

1 row in set (0.01 sec)

二. 直接通过mysql select查询语句:mysql> select * from mysql.user where user='test' and host='127.0.0.1' \G;

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

Host: 127.0.0.1

User: test

Password: *EB3C643405D7F53BD4BF7FBA98DCF5641E228833

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: mysql_native_password

authentication_string:

password_expired: N

1 row in set (0.00 sec)

可以看到Select_priv,Insert_priv,Update_priv...等为N表示没有权限,该用户权限一目了然.这时可以使用命令

给用户加权限:grant all privileges on *.* to 'test'@'127.0.0.1' identified by 'passwd';

flush privileges;另外:show可以看到很多东西:show databases;

show tables;

show create database dbname; // 这个可以看到创建数据库时用到的一些参数。

show create table tablename; // 可以看到创建表时用到的一些参数