Description:
1. We have created a table 't1' , a user 'r1' and granted 'select' privilege on column 'f1' to a user 'r1'.
2. When we execute 'alter table 'statment on table 't1' and change the column from 'f1' to 'f100',it is executed successfully.
3. When we reconnect with user credentials 'r1' to mysql database.And execute 'select' statement on 'column_privileges' table in 'information_schema' database.
4. Table 'COLUMN_PRIVILEGES' do not dispaly the altered information .
How to repeat:
1. Set the delimiter ie execute the following SQL statement:
delimiter //
2. Change the database to 'test1' ie execute the following SQL statement:
use test1//
3. Create a table 't1' ie execute the following SQL statement:
create table t1(f1 int,f2 int)//
4. Create a user 'r1' ie execute the following SQL statement:
Create user r1//
5. Grant 'Select' privileges to user 'r1' on 'test1' database.
grant select(f1) on test1.t1 to r1//
6. Execute 'flush' statement.
flush privileges//
7. Execute 'alter table' statement :
alter table t1 change f1 f100 int//
8. Reconnect to mysql with loggin credentails of user 'r1'.
reconnect to mysql with user r1.
9. Change the database to'information_schema' ie execute the following SQL statement:
use information_schema//
10. Execute 'select' statement:
select * from column_privileges//
Observed : Step 10 ,displays the following output:
+----------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------+---------------+--------------+------------+-------------+----------------+--------------+
| 'r1'@'%' | NULL | test1 | t1 | f1 | SELECT | NO |
+----------+---------------+--------------+------------+-------------+----------------+--------------+
1 row in set (0.00 sec)
Expected Result: Step 10 should display the altered information as following .
+----------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------+---------------+--------------+------------+-------------+----------------+--------------+
| 'r1'@'%' | NULL | test1 | t1 | f100 | SELECT | NO |
+----------+---------------+--------------+------------+-------------+----------------+--------------+
1 row in set (0.00 sec)
Actual Result: Step 10 do not displays altered information.
+----------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------+---------------+--------------+------------+-------------+----------------+--------------+
| 'r1'@'%' | NULL | test1 | t1 | f1 | SELECT | NO |
+----------+---------------+--------------+------------+-------------+----------------+--------------+
1 row in set (0.00 sec)
Description: 1. We have created a table 't1' , a user 'r1' and granted 'select' privilege on column 'f1' to a user 'r1'. 2. When we execute 'alter table 'statment on table 't1' and change the column from 'f1' to 'f100',it is executed successfully. 3. When we reconnect with user credentials 'r1' to mysql database.And execute 'select' statement on 'column_privileges' table in 'information_schema' database. 4. Table 'COLUMN_PRIVILEGES' do not dispaly the altered information . How to repeat: 1. Set the delimiter ie execute the following SQL statement: delimiter // 2. Change the database to 'test1' ie execute the following SQL statement: use test1// 3. Create a table 't1' ie execute the following SQL statement: create table t1(f1 int,f2 int)// 4. Create a user 'r1' ie execute the following SQL statement: Create user r1// 5. Grant 'Select' privileges to user 'r1' on 'test1' database. grant select(f1) on test1.t1 to r1// 6. Execute 'flush' statement. flush privileges// 7. Execute 'alter table' statement : alter table t1 change f1 f100 int// 8. Reconnect to mysql with loggin credentails of user 'r1'. reconnect to mysql with user r1. 9. Change the database to'information_schema' ie execute the following SQL statement: use information_schema// 10. Execute 'select' statement: select * from column_privileges// Observed : Step 10 ,displays the following output: +----------+---------------+--------------+------------+-------------+----------------+--------------+ | GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE | +----------+---------------+--------------+------------+-------------+----------------+--------------+ | 'r1'@'%' | NULL | test1 | t1 | f1 | SELECT | NO | +----------+---------------+--------------+------------+-------------+----------------+--------------+ 1 row in set (0.00 sec) Expected Result: Step 10 should display the altered information as following . +----------+---------------+--------------+------------+-------------+----------------+--------------+ | GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE | +----------+---------------+--------------+------------+-------------+----------------+--------------+ | 'r1'@'%' | NULL | test1 | t1 | f100 | SELECT | NO | +----------+---------------+--------------+------------+-------------+----------------+--------------+ 1 row in set (0.00 sec) Actual Result: Step 10 do not displays altered information. +----------+---------------+--------------+------------+-------------+----------------+--------------+ | GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE | +----------+---------------+--------------+------------+-------------+----------------+--------------+ | 'r1'@'%' | NULL | test1 | t1 | f1 | SELECT | NO | +----------+---------------+--------------+------------+-------------+----------------+--------------+ 1 row in set (0.00 sec)