From cfb9365d7ac9547ee697b9542cbc18b649f45ea2 Mon Sep 17 00:00:00 2001 From: Venkatesh Prasad Venugopal Date: Wed, 14 Jul 2021 12:59:16 +0530 Subject: [PATCH] Bug #98495: Timestamp is not set to CURRENT_TIMESTAMP in mysql.tables_priv Problem ------- All the grant tables have a column called Timestamp to display when the last change occurred for a user. However, it was not fully implemented and caused the Timestamp field of the mysql.*_priv table to be set to NULL timestamp("0000-00-00 00:00:00") for all GRANT/REVOKE queries and was preventing the logical restore of grant tables to zero date. Fix --- We now write the the query start time as the timestamp for the GRANT/REVOKE queries. NOTE ---- For older datadirs, even after the upgrade, the existing entries can still have zero timestamps. In such cases, for performing a logical backup, users are expected to 1. Run GRANT/REVOKE query and then take backup. OR 2. Update the timestamp of the mysql.*_priv tables. Eg: UPDATE mysql.tables_priv SET Timestamp = CURRENT_TIMESTAMP WHERE User = 'u1'; OR 3. Take backup and then edit the dump file and replace zero timestamp in the with CURRENT_TIMESTAMP. For example: change the contents REPLACE INTO `tables_priv` VALUES ('%','test','u1','t1','root@localhost','0000-00-00 00:00:00','','Select'); to REPLACE INTO `tables_priv` VALUES ('%','test','u1','t1','root@localhost',CURRENT_TIMESTAMP,'','Select'); --- mysql-test/r/hostname_length.result | 6 +- mysql-test/r/mysqlpump_long_hostname.result | 8 +- mysql-test/r/timestamps_in_priv_tables.result | 47 +++++++ mysql-test/t/hostname_length.test | 4 + mysql-test/t/mysqlpump_long_hostname.test | 5 + mysql-test/t/timestamps_in_priv_tables.test | 122 ++++++++++++++++++ sql/auth/sql_auth_cache.cc | 3 + sql/auth/sql_user_table.cc | 14 ++ 8 files changed, 202 insertions(+), 7 deletions(-) create mode 100644 mysql-test/r/timestamps_in_priv_tables.result create mode 100644 mysql-test/t/timestamps_in_priv_tables.test diff --git a/mysql-test/r/hostname_length.result b/mysql-test/r/hostname_length.result index 653024764ae0..cf1ba5f7e08a 100644 --- a/mysql-test/r/hostname_length.result +++ b/mysql-test/r/hostname_length.result @@ -233,10 +233,10 @@ GRANT PROXY ON root@localhost TO some_user_name@host_1234567890abcdefghij1234567 GRANT PROXY ON some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 TO root@localhost; SELECT * FROM mysql.proxies_priv WHERE user ='some_user_name'; Host User Proxied_host Proxied_user With_grant Grantor Timestamp -host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 some_user_name localhost root 0 root@localhost 0000-00-00 00:00:00 +host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 some_user_name localhost root 0 root@localhost ####-##-##-##:##:## SELECT * FROM mysql.proxies_priv WHERE Proxied_user ='some_user_name'; Host User Proxied_host Proxied_user With_grant Grantor Timestamp -localhost root host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 some_user_name 0 root@localhost 0000-00-00 00:00:00 +localhost root host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 some_user_name 0 root@localhost ####-##-##-##:##:## REVOKE PROXY ON root@localhost FROM some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890; REVOKE PROXY ON some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 FROM root@localhost; # @@ -285,7 +285,7 @@ SELECT * FROM mysql.proxies_priv WHERE Proxied_user ='u1' -------------- Host User Proxied_host Proxied_user With_grant Grantor Timestamp -localhost u2 host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 u1 0 u1@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 0000-00-00 00:00:00 +localhost u2 host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 u1 0 u1@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 DATEVALUE -------------- REVOKE PROXY ON u1@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 FROM u2@localhost -------------- diff --git a/mysql-test/r/mysqlpump_long_hostname.result b/mysql-test/r/mysqlpump_long_hostname.result index b34eb47e9054..f176b3b25846 100644 --- a/mysql-test/r/mysqlpump_long_hostname.result +++ b/mysql-test/r/mysqlpump_long_hostname.result @@ -81,10 +81,10 @@ GRANT PROXY ON root@localhost TO some_user_name@host_1234567890abcdefghij1234567 GRANT PROXY ON some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 TO root@localhost; SELECT * FROM mysql.proxies_priv WHERE user ='some_user_name'; Host User Proxied_host Proxied_user With_grant Grantor Timestamp -host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 some_user_name localhost root 0 root@localhost 0000-00-00 00:00:00 +host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 some_user_name localhost root 0 root@localhost ####-##-##-##:##:## SELECT * FROM mysql.proxies_priv WHERE Proxied_user ='some_user_name'; Host User Proxied_host Proxied_user With_grant Grantor Timestamp -localhost root host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 some_user_name 0 root@localhost 0000-00-00 00:00:00 +localhost root host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 some_user_name 0 root@localhost ####-##-##-##:##:## DROP EVENT e1; DROP PROCEDURE p1; DROP FUNCTION f1; @@ -157,10 +157,10 @@ some_user_name host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij # Grant PROXY. SELECT * FROM mysql.proxies_priv WHERE user ='some_user_name'; Host User Proxied_host Proxied_user With_grant Grantor Timestamp -host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 some_user_name localhost root 0 root@localhost 0000-00-00 00:00:00 +host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 some_user_name localhost root 0 root@localhost ####-##-##-##:##:## SELECT * FROM mysql.proxies_priv WHERE Proxied_user ='some_user_name'; Host User Proxied_host Proxied_user With_grant Grantor Timestamp -localhost root host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 some_user_name 0 root@localhost 0000-00-00 00:00:00 +localhost root host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 some_user_name 0 root@localhost ####-##-##-##:##:## # Cleanup REVOKE ALL PRIVILEGES, GRANT OPTION FROM some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890; REVOKE ALL ON *.* FROM some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890; diff --git a/mysql-test/r/timestamps_in_priv_tables.result b/mysql-test/r/timestamps_in_priv_tables.result new file mode 100644 index 000000000000..cb2b48bca4a7 --- /dev/null +++ b/mysql-test/r/timestamps_in_priv_tables.result @@ -0,0 +1,47 @@ +CREATE TABLE t1(i int, j int); +CREATE USER test_user; +CREATE PROCEDURE timestamp_test() SELECT 1; +################## +# TEST FOR GRANT # +################## +# 1. Testing of mysql.columns_priv and mysql.tables_priv tables. +GRANT SELECT(i, j) ON t1 TO test_user; +# 2. Testing of mysql.procs_priv table. +GRANT EXECUTE ON PROCEDURE timestamp_test TO test_user; +# 3. Testing of mysql.proxies_priv table. +GRANT PROXY ON root TO test_user; +include/assert.inc [User has correct timestamp for mysql.tables_priv table.] +include/assert.inc [User has correct timestamp for mysql.columns_priv table.] +include/assert.inc [User has correct timestamp for mysql.procs_priv table.] +include/assert.inc [User has correct timestamp for mysql.proxies_priv table.] +# Verify that Server allows creation of tables with same table definition as of +# the grant tables when table has some entries in it. +CREATE TABLE tables_bkp AS SELECT USER,TIMESTAMP FROM mysql.tables_priv; +CREATE TABLE columns_bkp AS SELECT USER,TIMESTAMP FROM mysql.columns_priv; +CREATE TABLE procs_bkp AS SELECT USER,TIMESTAMP FROM mysql.procs_priv; +CREATE TABLE proxies_bkp AS SELECT USER,TIMESTAMP FROM mysql.proxies_priv; +################### +# TEST FOR REVOKE # +################### +# 1. Testing of mysql.columns_priv and mysql.tables_priv tables. +REVOKE SELECT(i) ON t1 FROM test_user; +# 2. Testing of mysql.procs_priv table. +REVOKE EXECUTE ON PROCEDURE timestamp_test FROM test_user; +# 3. Testing of mysql.proxies_priv table. +REVOKE PROXY ON root FROM test_user; +include/assert.inc [User has correct timestamp for mysql.tables_priv table.] +####################### +# TEST WITH MYSQLDUMP # +####################### +CREATE DATABASE dump; +CREATE TABLE dump.tables_priv AS SELECT * FROM mysql.tables_priv; +TRUNCATE TABLE dump.tables_priv; +include/diff_tables.inc [mysql.tables_priv, dump.tables_priv] +DROP DATABASE dump; +DROP TABLE procs_bkp; +DROP TABLE columns_bkp; +DROP TABLE tables_bkp; +DROP TABLE proxies_bkp; +DROP PROCEDURE timestamp_test; +DROP USER test_user; +DROP TABLE t1; diff --git a/mysql-test/t/hostname_length.test b/mysql-test/t/hostname_length.test index 372eef962ac3..7bbca215c61e 100644 --- a/mysql-test/t/hostname_length.test +++ b/mysql-test/t/hostname_length.test @@ -260,7 +260,10 @@ DROP PROCEDURE p1; GRANT PROXY ON root@localhost TO some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890; GRANT PROXY ON some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 TO root@localhost; + +--replace_column 7 ####-##-##-##:##:## SELECT * FROM mysql.proxies_priv WHERE user ='some_user_name'; +--replace_column 7 ####-##-##-##:##:## SELECT * FROM mysql.proxies_priv WHERE Proxied_user ='some_user_name'; REVOKE PROXY ON root@localhost FROM some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890; REVOKE PROXY ON some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 FROM root@localhost; @@ -275,6 +278,7 @@ GRANT ALL ON *.* TO u1@host_1234567890abcdefghij1234567890abcdefghij1234567890ab SET GLOBAL DEBUG='+d,vio_peer_addr_fake_hostname1'; --echo # Execute test with long hostname in GRANTOR. +--replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DATEVALUE/ --exec $MYSQL --user=u1 --ssl-mode=DISABLED --host=host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 -#d,vio_client_use_localhost -v test < $MYSQL_TEST_DIR/std_data/hostname_in_grantor.sql SET GLOBAL DEBUG='-d,vio_peer_addr_fake_hostname1'; diff --git a/mysql-test/t/mysqlpump_long_hostname.test b/mysql-test/t/mysqlpump_long_hostname.test index 9c47d3df9b02..28c917b4486e 100644 --- a/mysql-test/t/mysqlpump_long_hostname.test +++ b/mysql-test/t/mysqlpump_long_hostname.test @@ -85,7 +85,10 @@ SELECT User, Host FROM mysql.procs_priv WHERE User LIKE 'some_user_name%'; --echo # Grant PROXY. GRANT PROXY ON root@localhost TO some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890; GRANT PROXY ON some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890 TO root@localhost; + +--replace_column 7 ####-##-##-##:##:## SELECT * FROM mysql.proxies_priv WHERE user ='some_user_name'; +--replace_column 7 ####-##-##-##:##:## SELECT * FROM mysql.proxies_priv WHERE Proxied_user ='some_user_name'; # Take dump @@ -146,7 +149,9 @@ SELECT User, Host FROM mysql.password_history WHERE User='pass_hist_user'; SELECT User, Host FROM mysql.procs_priv WHERE User LIKE 'some_user_name%'; --echo # Grant PROXY. +--replace_column 7 ####-##-##-##:##:## SELECT * FROM mysql.proxies_priv WHERE user ='some_user_name'; +--replace_column 7 ####-##-##-##:##:## SELECT * FROM mysql.proxies_priv WHERE Proxied_user ='some_user_name'; --echo # Cleanup diff --git a/mysql-test/t/timestamps_in_priv_tables.test b/mysql-test/t/timestamps_in_priv_tables.test new file mode 100644 index 000000000000..22e572be2514 --- /dev/null +++ b/mysql-test/t/timestamps_in_priv_tables.test @@ -0,0 +1,122 @@ +# === Purpose === +# +# This test verifies that correct timestamp is generated for GRANT system +# tables (mysql.*_priv tables). +# +# === References === +# +# Bug #98495: Timestamp is not set to CURRENT_TIMESTAMP in mysql.tables_priv +# PS-7617: Timestamp is not set to CURRENT_TIMESTAMP in grant tables + +# Test Setup. +CREATE TABLE t1(i int, j int); +CREATE USER test_user; +CREATE PROCEDURE timestamp_test() SELECT 1; + +--echo ################## +--echo # TEST FOR GRANT # +--echo ################## + +# Record the timestamp before the test. +--let $test_start_time = `SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP)` + +--echo # 1. Testing of mysql.columns_priv and mysql.tables_priv tables. +GRANT SELECT(i, j) ON t1 TO test_user; + +--echo # 2. Testing of mysql.procs_priv table. +GRANT EXECUTE ON PROCEDURE timestamp_test TO test_user; + +--echo # 3. Testing of mysql.proxies_priv table. +GRANT PROXY ON root TO test_user; + +# Record the timestamp after the test. +--let $test_end_time = `SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP)` + +# Validation of timestamp column in mysql.tables_priv +--let $user_creation_time = `SELECT UNIX_TIMESTAMP(Timestamp) FROM mysql.tables_priv WHERE User = 'test_user'` +--let $assert_text = User has correct timestamp for mysql.tables_priv table. +--let $assert_cond = [SELECT $user_creation_time BETWEEN $test_start_time AND $test_end_time] = 1 +--source include/assert.inc + +# Validation of timestamp column in mysql.columns_priv +--let $user_creation_time = `SELECT UNIX_TIMESTAMP(Timestamp) FROM mysql.columns_priv WHERE User = 'test_user'` +--let $assert_text = User has correct timestamp for mysql.columns_priv table. +--let $assert_cond = [SELECT $user_creation_time BETWEEN $test_start_time AND $test_end_time] = 1 +--source include/assert.inc + +# Validation of timestamp column in mysql.procs_priv +--let $user_creation_time = `SELECT UNIX_TIMESTAMP(Timestamp) FROM mysql.procs_priv WHERE User = 'test_user'` +--let $assert_text = User has correct timestamp for mysql.procs_priv table. +--let $assert_cond = [SELECT $user_creation_time BETWEEN $test_start_time AND $test_end_time] = 1 +--source include/assert.inc + +# Validation of timestamp column in mysql.proxies_priv +--let $user_creation_time = `SELECT UNIX_TIMESTAMP(Timestamp) FROM mysql.proxies_priv WHERE User = 'test_user'` +--let $assert_text = User has correct timestamp for mysql.proxies_priv table. +--let $assert_cond = [SELECT $user_creation_time BETWEEN $test_start_time AND $test_end_time] = 1 +--source include/assert.inc + +--echo # Verify that Server allows creation of tables with same table definition as of +--echo # the grant tables when table has some entries in it. +CREATE TABLE tables_bkp AS SELECT USER,TIMESTAMP FROM mysql.tables_priv; +CREATE TABLE columns_bkp AS SELECT USER,TIMESTAMP FROM mysql.columns_priv; +CREATE TABLE procs_bkp AS SELECT USER,TIMESTAMP FROM mysql.procs_priv; +CREATE TABLE proxies_bkp AS SELECT USER,TIMESTAMP FROM mysql.proxies_priv; + +--echo ################### +--echo # TEST FOR REVOKE # +--echo ################### +# Record the timestamp before the test. +--let $test_start_time = `SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP)` + +--echo # 1. Testing of mysql.columns_priv and mysql.tables_priv tables. +REVOKE SELECT(i) ON t1 FROM test_user; + +--echo # 2. Testing of mysql.procs_priv table. +REVOKE EXECUTE ON PROCEDURE timestamp_test FROM test_user; + +--echo # 3. Testing of mysql.proxies_priv table. +REVOKE PROXY ON root FROM test_user; + +# Record the timestamp after the test. +--let $test_end_time = `SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP)` + +# +# We only validate for mysql.tables_priv as the entries are deleted in case of +# other REVOKE queries. +# + +# Validation of timestamp column in mysql.tables_priv +--let $user_creation_time = `SELECT UNIX_TIMESTAMP(Timestamp) FROM mysql.tables_priv WHERE User = 'test_user'` +--let $assert_text = User has correct timestamp for mysql.tables_priv table. +--let $assert_cond = [SELECT $user_creation_time BETWEEN $test_start_time AND $test_end_time] = 1 +--source include/assert.inc + +--echo ####################### +--echo # TEST WITH MYSQLDUMP # +--echo ####################### +--let $mysqldumpfile = $MYSQLTEST_VARDIR/tmp/mysqldumpfile.sql +--exec $MYSQL_DUMP --compact --replace --skip-add-locks --skip-lock-tables --no-create-info --skip-triggers --set-gtid-purged=OFF mysql tables_priv --skip-tz-utc > $mysqldumpfile + +# We use CTAS for creating a table with exact schema, as DD doesn't allow +# CREATE TABLE LIKE query on mysql db tables. +CREATE DATABASE dump; +CREATE TABLE dump.tables_priv AS SELECT * FROM mysql.tables_priv; +TRUNCATE TABLE dump.tables_priv; +--exec $MYSQL dump < $mysqldumpfile + +--let $diff_tables= mysql.tables_priv, dump.tables_priv +--source include/diff_tables.inc + + +# Cleanup +--remove_file $mysqldumpfile +DROP DATABASE dump; +DROP TABLE procs_bkp; +DROP TABLE columns_bkp; +DROP TABLE tables_bkp; +DROP TABLE proxies_bkp; + +DROP PROCEDURE timestamp_test; +DROP USER test_user; +DROP TABLE t1; diff --git a/sql/auth/sql_auth_cache.cc b/sql/auth/sql_auth_cache.cc index b1aad85cefad..1bcbed23ce7b 100644 --- a/sql/auth/sql_auth_cache.cc +++ b/sql/auth/sql_auth_cache.cc @@ -645,6 +645,9 @@ int ACL_PROXY_USER::store_data_record(TABLE *table, const LEX_CSTRING &hostname, system_charset_info)) return true; + timeval tm = table->in_use->query_start_timeval_trunc(0); + table->field[MYSQL_PROXIES_PRIV_TIMESTAMP]->store_timestamp(&tm); + return false; } diff --git a/sql/auth/sql_user_table.cc b/sql/auth/sql_user_table.cc index 112d255a6c84..17d0c82c0103 100644 --- a/sql/auth/sql_user_table.cc +++ b/sql/auth/sql_user_table.cc @@ -1213,6 +1213,10 @@ int replace_column_table(THD *thd, GRANT_TABLE *g_t, TABLE *table, store_record(table, record[1]); // copy original row } + timeval tm; + tm = thd->query_start_timeval_trunc(0); + table->field[5]->store_timestamp(&tm); + table->field[6]->store((longlong)get_rights_for_column(privileges), true); if (old_row_exists) { @@ -1473,6 +1477,11 @@ int replace_table_table(THD *thd, GRANT_TABLE *grant_table, } table->field[4]->store(grantor, strlen(grantor), system_charset_info); + + timeval tm; + tm = thd->query_start_timeval_trunc(0); + table->field[5]->store_timestamp(&tm); + table->field[6]->store((longlong)store_table_rights, true); table->field[7]->store((longlong)store_col_rights, true); rights = fix_rights_for_table(store_table_rights); @@ -1633,6 +1642,11 @@ int replace_routine_table(THD *thd, GRANT_NAME *grant_name, TABLE *table, table->field[5]->store(grantor, strlen(grantor), &my_charset_latin1); table->field[6]->store((longlong)store_proc_rights, true); + + timeval tm; + tm = thd->query_start_timeval_trunc(0); + table->field[7]->store_timestamp(&tm); + rights = fix_rights_for_procedure(store_proc_rights); if (old_row_exists) {