MySQL Bugs: #98495: Timestamp is not set to CURRENT_TIMESTAMP in mysql.tables

Bug #98495	Timestamp is not set to CURRENT_TIMESTAMP in mysql.tables_priv
Submitted:	5 Feb 2020 18:53	Modified:	29 Nov 2021 20:43
Reporter:	Ceri Williams	Email Updates:
Status:	Closed	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

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.

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.

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.

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.

Hi Mr. Williams,

I agree with you.

Category is changed .......

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)

```

Hi Mr. Chan,

Yes, this is a bug, but not a serious one .....

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.

Posted by developer:
 
The Timestamp column in the mysql.tables_priv and myql.columns_priv grant
tables was set to a zero timestamp value ("0000-00-00 00:00:00") for GRANT
and REVOKE operations, preventing a logical restore of the grant tables.
As of MySQL 8.0.28, a valid start time value is written to the Timestamp
column. 

If you have existing grant table records with zero
timestamp values that are preventing a logical restore of the grant
tables, a workaround is to update records in the grant tables or in the
dump files, replacing zero timestamp values with CURRENT_TIMESTAMP.

Thanks to Venkatesh Prasad Venugopal for the contribution.