Edit File: grant4.result
drop database if exists mysqltest_db1; create database mysqltest_db1; use mysqltest_db1; create table t_column_priv_only (a int, b int); create table t_select_priv like t_column_priv_only; create table t_no_priv like t_column_priv_only; grant all privileges on test.* to mysqltest_u1@localhost; grant insert (a) on mysqltest_db1.t_column_priv_only to mysqltest_u1@localhost; grant select on mysqltest_db1.t_select_priv to mysqltest_u1@localhost; ** Connect as restricted user mysqltest_u1. ** Test column level privileges only. No SELECT privileges on the table. ** INSERT INTO ... VALUES ... ** Attempting to insert values to a table with only column privileges ** should work. insert into mysqltest_db1.t_column_priv_only (a) VALUES (1); ** SHOW COLUMNS ** Should succeed because we have privileges (any) on at least one of the columns. select column_name as 'Field',column_type as 'Type',is_nullable as 'Null',column_key as 'Key',column_default as 'Default',extra as 'Extra' from information_schema.columns where table_schema='mysqltest_db1' and table_name='t_column_priv_only'; Field Type Null Key Default Extra a int(11) YES NULL show columns from mysqltest_db1.t_column_priv_only; Field Type Null Key Default Extra a int(11) YES NULL ** SHOW COLUMNS ** Should fail because there are no privileges on any column combination. show columns from mysqltest_db1.t_no_priv; ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't_no_priv' ** However, select from I_S.COLUMNS will succeed but not show anything: select column_name as 'Field',column_type as 'Type',is_nullable as 'Null',column_key as 'Key',column_default as 'Default',extra as 'Extra' from information_schema.columns where table_schema='mysqltest_db1' and table_name='t_no_priv'; Field Type Null Key Default Extra ** CREATE TABLE ... LIKE ... require SELECT privleges and will fail. create table test.t_no_priv like mysqltest_db1.column_priv_only; ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'column_priv_only' ** Just to be sure... SELECT also fails. select * from mysqltest_db1.t_column_priv_only; ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't_column_priv_only' ** SHOW CREATE TABLE ... require any privileges on all columns (the entire table). ** First we try and fail on a table with only one column privilege. show create table mysqltest_db1.t_column_priv_only; ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't_column_priv_only' ** Now we do the same on a table with SELECT privileges. ** SHOW COLUMNS ** Success because we got some privileges on the table (SELECT_ACL) show columns from mysqltest_db1.t_select_priv; Field Type Null Key Default Extra a int(11) YES NULL b int(11) YES NULL ** CREATE TABLE ... LIKE ... require SELECT privleges and will SUCCEED. drop table if exists test.t_duplicated; create table test.t_duplicated like mysqltest_db1.t_select_priv; drop table test.t_duplicated; ** SHOW CREATE TABLE will succeed because we have a privilege on all columns in the table (table-level privilege). show create table mysqltest_db1.t_select_priv; Table Create Table t_select_priv CREATE TABLE `t_select_priv` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ** SHOW CREATE TABLE will fail if there is no grants at all: show create table mysqltest_db1.t_no_priv; ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't_no_priv' use mysqltest_db1; CREATE TABLE t5 (s1 INT); CREATE INDEX i ON t5 (s1); CREATE TABLE t6 (s1 INT, s2 INT); CREATE VIEW v5 AS SELECT * FROM t5; CREATE VIEW v6 AS SELECT * FROM t6; CREATE VIEW v2 AS SELECT * FROM t_select_priv; CREATE VIEW v3 AS SELECT * FROM t_select_priv; CREATE INDEX i ON t6 (s1); GRANT UPDATE (s2) ON t6 to mysqltest_u1@localhost; GRANT UPDATE (s2) ON v6 to mysqltest_u1@localhost; GRANT SHOW VIEW ON v2 to mysqltest_u1@localhost; GRANT SHOW VIEW, SELECT ON v3 to mysqltest_u1@localhost; use mysqltest_db1; ** Connect as restricted user mysqltest_u1. ** SELECT FROM INFORMATION_SCHEMA.STATISTICS will succeed because any privileges will do (authentication is enough). SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='t5'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT def mysqltest_db1 t5 1 mysqltest_db1 i 1 s1 A NULL NULL NULL YES BTREE ** SHOW INDEX FROM t5 will fail because we don't have any privileges on any column combination. SHOW INDEX FROM t5; ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't5' ** SHOW INDEX FROM t6 will succeed because there exist a privilege on a column combination on t6. SHOW INDEX FROM t6; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t6 1 i 1 s1 A NULL NULL NULL YES BTREE ** CHECK TABLE requires any privilege on any column combination and should succeed for t6: CHECK TABLE t6; Table Op Msg_type Msg_text mysqltest_db1.t6 check status OK ** With no privileges access is naturally denied: CHECK TABLE t5; ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't5' ** CHECKSUM TABLE requires SELECT privileges on the table. The following should fail: CHECKSUM TABLE t6; ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't6' ** And this should work: CHECKSUM TABLE t_select_priv; Table Checksum mysqltest_db1.t_select_priv 0 SHOW CREATE VIEW v5; ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v5' SHOW CREATE VIEW v6; ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v6' SHOW CREATE VIEW v2; ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v2' SHOW CREATE VIEW v3; View Create View character_set_client collation_connection v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t_select_priv`.`a` AS `a`,`t_select_priv`.`b` AS `b` from `t_select_priv` latin1 latin1_swedish_ci drop database mysqltest_db1; drop user mysqltest_u1@localhost;
Back to File Manager