mysql查询用户权限 - 关于Trigger
开发的时候，因为在执行Trigger相关语句的时候报错。根据提示，大概是用户权限的问题，由此想到了要不查下用户的Trigger权限信息先。
因此我去查询相关的资料，找到如下方法
使用该用户来登陆mysql，然后执行以下命令：
show triggers;如果没有显示，再查看改用户的所有权限：
show grants for [email protected]'localhost';根据查看返回结果中是否有TRIGGER ON关键词判断。如果有则说明具有Trigger的权限
然后在执行最后一句的时候出现错误：（以下用户名部分全部用[username]代替）
MySQL [lantuplatform]> show grants for [username]@localhost;
ERROR 1141 (42000): There is no such grant defined for user '[username]' on host 'localhost'由此想到是否是[username]@localhost这块填写错误。
好的，查下当前用户
select current_user();
结果：
+----------------+
| current_user() |
+----------------+
| [username]@% |
+----------------+
那么，可不可以查询以下mysql中当前user的权限信息
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| innodb |
| [table1] |
| [table2] |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
MySQL [(none)]> use mysql;
Database changed
MySQL [mysql]> show tables;
+-------------------------------+
| Tables_in_mysql |
+-------------------------------+
| columns_priv |
| db |
.............此处省略............
| time_zone_transition |
| time_zone_transition_type |
| user |
+-------------------------------+
41 rows in set (0.00 sec)
MySQL [mysql]> select * from user;
+-----------+----................---------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+----..........................--------------+
| localhost | rdsadmin | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password |..省略... NULL | N |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password |..省略... NULL | Y |
| % | tera_admin | Y | Y | Y | Y | Y | Y | Y | N | Y | N | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y[注意此处是Trigger_priv信息]| N | | | | | 0 | 0 | 0 | 0 | mysql_native_password |..省略... NULL | N |
+-----------+--............--------------+
3 rows in set (0.00 sec)
好的，不仅找到了[username]的用户信息，也直接找到了[username]的Trigger_priv信息。不过为了继续完成上面的命令，还是又执行了一遍。调整后的sql
//host与user查询结果中[username]的Host信息值一致。注意host部分要用单引号
show grants for [username]@'%';查询结果如下：
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [username]@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO '[username]'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
在结果信息中看到了TRIGGER ON ，由此说明，我的用户账号是具有Trigger的权限的。很开心，感觉好像没啥问题，是不是sql写错了，再执行一遍看看提示。
----------------------------------------------------------------------------------------------------------------------------------------------------这里是快乐截至的分界线--------------------------------------------------------------------------------------------------------------------------------------------------------------------
然后继续执行Trigger语句，emmm... 还是报错
ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
此刻的我突然醒悟，麻蛋，这个不是Trigger的权限问题叭。找啊找，找对了对应的权限关键词 —— Super_priv
唉..........
这个，想来又不会给我们root权限，再看提示 you *might* want to use the less safe log_bin_trust_function_creators variable， 查查
show variables like 'log_bin_trust_function_creators';
执行结果：
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.01 sec)
那么，改一下？
set global log_bin_trust_function_creators=1;
执行结果：
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operationemm....
还是要去申请权限？不想相信的我，继续查
尝试了这个
update user set Super_priv='Y' where User='[username]';
flush privileges;
结果是这样的
MySQL [mysql]> update user set Super_priv='Y' where User='tera_admin';ERROR 1054 (42S22): Unknown column 'ERROR (RDS): SUPER PRIVILEGE CANNOT BE GRANTED OR MAINTAINED' in 'field list'找运营方申请权限去了。。。
上一篇： python pip提权
下一篇： Hive 建表详解