Tag: mysql.user

mysql> select user from mysql.user;

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

mysql> SELECT CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)) FROM mysql.global_priv where user in (‘root’,’mariadb.sys’)\G;
* 1. row *
CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)): mariadb.sys@localhost => {
“access”: 0,
“plugin”: “mysql_native_password”,
“authentication_string”: “”,
“account_locked”: true,
“password_last_changed”: 0
}
* 2. row *
CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)): root@localhost => {
“access”: 1073741823,
“plugin”: “mysql_native_password”,
“authentication_string”: “XXXXXXXX”,
“password_last_changed”: 1633918260
}
* 3. row *
CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)): root@127.0.0.1 => {
“access”: 1073741823,
“plugin”: “mysql_native_password”,
“authentication_string”: “XXXXXXXXX”,
“password_last_changed”: 1633918260
}
* 4. row *
CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)): root@::1 => {
“access”: 1073741823,
“plugin”: “mysql_native_password”,
“authentication_string”: “*XXXXXX”,
“password_last_changed”: 1633918260
}

mysql > select * from mysql.tables_priv where User=’mariadb.sys’;
Empty set (0.001 sec)

mysql > INSERT INTO tables_priv (Host, Db, User, Table_name, Grantor, Timestamp, Table_priv, Column_priv) VALUES (‘localhost’,’mysql’,’mariadb.sys’,’global_priv’, ‘root@localhost’,’0000-00-00 00:00:00′,’Select,Update,Delete’,”);
ERROR 1292 (22007): Incorrect datetime value: ‘0000-00-00 00:00:00’ for column mysql.tables_priv.Timestamp at row 1

mysql > select now();
+———————+
| now() |
+———————+
| 2022-02-07 01:09:07 |
+———————+
1 row in set (0.001 sec)

mysql > INSERT INTO tables_priv (Host, Db, User, Table_name, Grantor, ,Column_priv`) VALUES (‘localhost’,’mysql’,’mariadb.sys’,’global_priv’,’root@localhost’,current_timestamp(),’Select,Up
Query OK, 1 row affected (0.017 sec)

mysql > select * from user;
ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s) or function(s) or definer/invoker of view

mysql -P 3306 -e “SET global innodb_fast_shutdown = 1;”

Stop MySQL

Start MySQL

mysql > select user from mysql.user where user=’root’;
+——+
| User |
+——+
| root |
+——+
1 rows in set (0.001 sec)