|
mysql.user : It is a view into mysql.global_priv created for compatibility with older applications and monitoring scripts.
Any specific reason for SELECT ….. INTO OUTFILE to cause it to complain like below about a "view".
|
MariaDB [(none)]> SELECT CONCAT('SHOW GRANTS FOR ',user,'@',host,';' ) FROM mysql.user INTO OUTFILE '/tmp/Grants.txt';
|
ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
While its working fine without INTO OUTFILE
|
MariaDB [(none)]> SELECT CONCAT('SHOW GRANTS FOR ',user,'@',host,';' ) FROM mysql.user;
|
+-----------------------------------------------+
|
| CONCAT('SHOW GRANTS FOR ',user,'@',host,';' ) |
|
+-----------------------------------------------+
|
| SHOW GRANTS FOR maxuser@%; |
|
| SHOW GRANTS FOR repuser@%; |
|
| SHOW GRANTS FOR sysbench@%; |
|
| SHOW GRANTS FOR mariadb.sys@localhost; |
|
| SHOW GRANTS FOR mysql@localhost; |
|
| SHOW GRANTS FOR root@localhost; |
|
| SHOW GRANTS FOR sstuser@localhost; |
|
+-----------------------------------------------+
|
7 rows in set (0.001 sec)
|
|
Its working fine even if we consider table as *mysql.global_priv *
|
MariaDB [(none)]> SELECT CONCAT('SHOW GRANTS FOR ',user,'@',host,';' ) FROM mysql.global_priv INTO OUTFILE '/tmp/Grants.txt';
|
Query OK, 7 rows affected, 1 warning (0.000 sec)
|
|
MariaDB [(none)]> show warnings;
|
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Warning | 1287 | '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead |
|
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
[root@mariadbtest tmp]# cat Grants.txt
|
SHOW GRANTS FOR maxuser@%;
|
SHOW GRANTS FOR repuser@%;
|
SHOW GRANTS FOR sysbench@%;
|
SHOW GRANTS FOR mariadb.sys@localhost;
|
SHOW GRANTS FOR mysql@localhost;
|
SHOW GRANTS FOR root@localhost;
|
SHOW GRANTS FOR sstuser@localhost;
|
|
|