Bug #98495 Timestamp is not set to CURRENT_TIMESTAMP in mysql.tables_priv
Submitted: 5 Feb 2020 18:53 Modified: 11 Feb 2020 13:15
Reporter: Ceri Williams Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:8.0.18, 5.7.28 OS:Linux
Assigned to: CPU Architecture:Any
Tags: tables_priv current_timestamp

[5 Feb 2020 18:53] Ceri Williams
Description:
The mysql.tables_priv structure specifies the Timestamp field as:

NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

https://dev.mysql.com/doc/refman/8.0/en/grant-tables.html#grant-tables-tables-priv-columns...

The Timestamp and Grantor columns are set to the current timestamp and the CURRENT_USER value, respectively, but are otherwise unused.

Neither of these are true. The table does not get the timestamp set and furthermore there is no error even when the sql_mode is set to contain:

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE

This applies when the engine is InnoDB (8.0) as well as MyISAM (5.7).

Interestingly, there is one user that always seems to have this field populated and that user is mysql.sys.

select user, timestamp from mysql.tables_priv;
mysql.session   0000-00-00 00:00:00
mysql.sys       2020-02-05 18:23:10

How to repeat:
# Using dbdeployer

cd $SANDBOX_HOME

dbdeployer downloads get-unpack --prefix glibc mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
dbdeployer deploy single glibc8.0.18 --gtid --binary-version 8.0.18

cat <<SQL | ./msb_glibc8.0.18/use -uroot -Bs
select @@global.sql_mode;
select current_timestamp;
show fields from mysql.tables_priv like 'timestamp';
select user, timestamp from mysql.tables_priv;
create user if not exists ceri@'localhost' identified by '';
grant select on mysql.slave_master_info to ceri@'localhost';
select timestamp from mysql.tables_priv where user = 'ceri';
SQL

dbdeployer downloads get-unpack --prefix glibc mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
dbdeployer deploy single glibc5.7.28 --gtid --binary-version 5.7.28

cat <<SQL | ./msb_glibc5.7.28/use -uroot -Bs
select @@global.sql_mode;
select current_timestamp;
show fields from mysql.tables_priv like 'timestamp';
select user, timestamp from mysql.tables_priv;
create user if not exists ceri@'localhost' identified by '';
grant select on mysql.slave_master_info to ceri@'localhost';
select timestamp from mysql.tables_priv where user = 'ceri';
SQL

# Using default MySQL binary install

cat <<SQL | mysql -uroot -Bs
select @@global.sql_mode;
select current_timestamp;
show fields from mysql.tables_priv like 'timestamp';
select user, timestamp from mysql.tables_priv;
create user if not exists ceri@'localhost' identified by '';
grant select on mysql.slave_master_info to ceri@'localhost';
select timestamp from mysql.tables_priv where user = 'ceri';
SQL

Suggested fix:
Ensure that the timestamp field is always auto populated and updated and error when the SQL mode should not allow a zero date.

If for any reason this is in someway intentional, fix the docs.
[6 Feb 2020 9:59] Ceri Williams
To clarify why this is an issue for a production environment:

mysqldump --compact --replace --skip-add-locks --skip-lock-tables --no-create-info --skip-triggers --set-gtid-purged=OFF mysql tables_priv | \
mysql --database=mysql

ERROR 1292 (22007) at line 1: Incorrect datetime value: '0000-00-00 00:00:00' for column 'Timestamp' at row 1

You cannot perform a logical restore without changing the sql_mode of the server.
[6 Feb 2020 14:01] MySQL Verification Team
Hello Mr. Williams,

Thank you for your bug report.

I happen to be the one who was the author of the entire privilege system, so I know what you are asking and what are the causes of this design.

Two columns that you are writing about were meant to be used in future extensions of the system, which never materialised. Timestamp column was there for the security information, to know when the last change occurred.

`Grantor` column is there in order to know which user granted those privileges. This could have lead to the possibility of reviewing the entire hierarchy of who gave privileges to whom and in what order.

However, this idea was not implemented, most likely because nobody asked for that feature.

`mysql.sys` user is a special case of a system user, that deals with our internal schemas.

However, I agree with you that it should be documented.

Verified as a documentation bug.
[11 Feb 2020 13:15] Ceri Williams
Thanks for the notes Sinisa.

However, you appear to have missed the part where this prevents a logical restore with the default sql_mode due to zero dates, so it is a bug not just a case of updating the documentation.
[11 Feb 2020 13:37] MySQL Verification Team
Hi Mr. Williams,

I agree with you.

Category is changed .......
[20 May 9:06] fander chan
I dont agree with you.
This feature works in older versions!
It's ok in 5.5.62 (the last verion of 5.5),But it is not ok in any sub version of 5.6、5.7、8.0.

So I think it's also a bug, though not serious.

Here is my test code:
```
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.5.62    |
+-----------+
1 row in set (0.00 sec)

mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+--------------------------------------------------+
| @@sql_mode                                       |
+--------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database fanderchan;
Query OK, 1 row affected (0.00 sec)

mysql> use fanderchan;
Database changed
mysql> create table fanderchan(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> create user fanderchan identified by 'MySQL@2021';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on fanderchan.fanderchan to fanderchan;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.tables_priv;
+------+------------+------------+------------+----------------+---------------------+------------+-------------+
| Host | Db         | User       | Table_name | Grantor        | Timestamp           | Table_priv | Column_priv |
+------+------------+------------+------------+----------------+---------------------+------------+-------------+
| %    | fander     | fander     | fander     | root@localhost | 2021-05-20 16:20:38 | Select     |             |
| %    | fander     | fander     | fander2    | root@localhost | 2021-05-20 16:35:26 | Select     |             |
| %    | fanderchan | fanderchan | fanderchan | root@localhost | 2021-05-20 17:04:29 | Select     |             |
+------+------------+------------+------------+----------------+---------------------+------------+-------------+
3 rows in set (0.00 sec)

```
[20 May 13:52] MySQL Verification Team
Hi Mr. Chan,

Yes, this is a bug, but not a serious one .....
[14 Jul 14:03] OCA Admin
Contribution submitted via Github - Bug #98495: Timestamp is not set to CURRENT_TIMESTAMP in mysql.tables… 
(*) Contribution by Venkatesh Prasad Venugopal (Github venkatesh-prasad-v, mysql-server/pull/348#issuecomment-879671385): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_689667510.txt (text/plain), 24.12 KiB.