Gecko [ 122.154.134.11 ]

Name	Size	Permission
charset_collation.test	3.89 KB	-rw-r--r--
disabled.def	485 B	-rw-r--r--
innodb_bitdata.test	434 B	-rw-r--r--
innodb_cursors.test	435 B	-rw-r--r--
innodb_func_view.test	499 B	-rw-r--r--
innodb_storedproc_02.test	218 B	-rw-r--r--
innodb_storedproc_03.test	218 B	-rw-r--r--
innodb_storedproc_06.test	218 B	-rw-r--r--
innodb_storedproc_07.test	218 B	-rw-r--r--
innodb_storedproc_08.test	218 B	-rw-r--r--
innodb_storedproc_10.test	218 B	-rw-r--r--
innodb_trig_0102.test	243 B	-rw-r--r--
innodb_trig_03.test	239 B	-rw-r--r--
innodb_trig_03e.test	849 B	-rw-r--r--
innodb_trig_0407.test	243 B	-rw-r--r--
innodb_trig_08.test	239 B	-rw-r--r--
innodb_trig_09.test	239 B	-rw-r--r--
innodb_trig_1011ext.test	249 B	-rw-r--r--
innodb_trig_frkey.test	239 B	-rw-r--r--
innodb_views.test	540 B	-rw-r--r--
is_basics_mixed.test	19.09 KB	-rw-r--r--
is_character_sets.test	4.47 KB	-rw-r--r--
is_cml_innodb.test	1.01 KB	-rw-r--r--
is_cml_memory.test	944 B	-rw-r--r--
is_cml_myisam.test	1.03 KB	-rw-r--r--
is_cml_ndb.test	1.02 KB	-rw-r--r--
is_coll_char_set_appl.test	4.75 KB	-rw-r--r--
is_collations.test	4.9 KB	-rw-r--r--
is_column_privileges.test	13.12 KB	-rw-r--r--
is_column_privileges_is_mysql_...	2.26 KB	-rw-r--r--
is_columns.test	16.85 KB	-rw-r--r--
is_columns_innodb.test	828 B	-rw-r--r--
is_columns_is.test	828 B	-rw-r--r--
is_columns_is_embedded.test	742 B	-rw-r--r--
is_columns_memory.test	846 B	-rw-r--r--
is_columns_myisam.test	927 B	-rw-r--r--
is_columns_myisam_embedded.tes...	850 B	-rw-r--r--
is_columns_mysql.test	624 B	-rw-r--r--
is_columns_mysql_embedded.test	538 B	-rw-r--r--
is_columns_ndb.test	1.07 KB	-rw-r--r--
is_engines.test	4.61 KB	-rw-r--r--
is_engines_archive.test	425 B	-rw-r--r--
is_engines_blackhole.test	433 B	-rw-r--r--
is_engines_csv.test	409 B	-rw-r--r--
is_engines_federated.test	452 B	-rw-r--r--
is_engines_innodb.test	421 B	-rw-r--r--
is_engines_memory.test	388 B	-rw-r--r--
is_engines_merge.test	392 B	-rw-r--r--
is_engines_myisam.test	387 B	-rw-r--r--
is_engines_ndb.test	416 B	-rw-r--r--
is_events.test	7.53 KB	-rw-r--r--
is_key_column_usage.test	770 B	-rw-r--r--
is_key_column_usage_embedded.t...	819 B	-rw-r--r--
is_routines.test	724 B	-rw-r--r--
is_routines_embedded.test	773 B	-rw-r--r--
is_schema_privileges.test	13.46 KB	-rw-r--r--
is_schema_privileges_is_mysql_...	2.27 KB	-rw-r--r--
is_schemata.test	721 B	-rw-r--r--
is_schemata_embedded.test	770 B	-rw-r--r--
is_schemata_is_mysql_test.test	2.22 KB	-rw-r--r--
is_statistics.test	13.96 KB	-rw-r--r--
is_statistics_is.test	507 B	-rw-r--r--
is_statistics_mysql.test	721 B	-rw-r--r--
is_statistics_mysql_embedded.t...	644 B	-rw-r--r--
is_table_constraints.test	12.65 KB	-rw-r--r--
is_table_constraints_is.test	504 B	-rw-r--r--
is_table_constraints_mysql.tes...	719 B	-rw-r--r--
is_table_constraints_mysql_emb...	647 B	-rw-r--r--
is_table_privileges.test	14.09 KB	-rw-r--r--
is_tables.test	710 B	-rw-r--r--
is_tables_embedded.test	762 B	-rw-r--r--
is_tables_innodb.test	776 B	-rw-r--r--
is_tables_is.test	643 B	-rw-r--r--
is_tables_memory.test	794 B	-rw-r--r--
is_tables_myisam.test	875 B	-rw-r--r--
is_tables_myisam_embedded.test	800 B	-rw-r--r--
is_tables_mysql.test	668 B	-rw-r--r--
is_tables_mysql_embedded.test	591 B	-rw-r--r--
is_tables_ndb.test	764 B	-rw-r--r--
is_triggers.test	723 B	-rw-r--r--
is_triggers_embedded.test	772 B	-rw-r--r--
is_user_privileges.test	12.13 KB	-rw-r--r--
is_views.test	708 B	-rw-r--r--
is_views_embedded.test	757 B	-rw-r--r--
memory_bitdata.test	411 B	-rw-r--r--
memory_cursors.test	416 B	-rw-r--r--
memory_func_view.test	517 B	-rw-r--r--
memory_storedproc_02.test	180 B	-rw-r--r--
memory_storedproc_03.test	180 B	-rw-r--r--
memory_storedproc_06.test	180 B	-rw-r--r--
memory_storedproc_07.test	180 B	-rw-r--r--
memory_storedproc_08.test	180 B	-rw-r--r--
memory_storedproc_10.test	180 B	-rw-r--r--
memory_trig_0102.test	273 B	-rw-r--r--
memory_trig_03.test	220 B	-rw-r--r--
memory_trig_03e.test	769 B	-rw-r--r--
memory_trig_0407.test	273 B	-rw-r--r--
memory_trig_08.test	220 B	-rw-r--r--
memory_trig_09.test	220 B	-rw-r--r--
memory_trig_1011ext.test	279 B	-rw-r--r--
memory_views.test	570 B	-rw-r--r--
myisam_bitdata.test	416 B	-rw-r--r--
myisam_cursors.test	416 B	-rw-r--r--
myisam_func_view.test	517 B	-rw-r--r--
myisam_storedproc_02.test	180 B	-rw-r--r--
myisam_storedproc_03.test	180 B	-rw-r--r--
myisam_storedproc_06.test	180 B	-rw-r--r--
myisam_storedproc_07.test	180 B	-rw-r--r--
myisam_storedproc_08.test	180 B	-rw-r--r--
myisam_storedproc_10.test	180 B	-rw-r--r--
myisam_trig_0102.test	224 B	-rw-r--r--
myisam_trig_03.test	220 B	-rw-r--r--
myisam_trig_03e.test	769 B	-rw-r--r--
myisam_trig_0407.test	224 B	-rw-r--r--
myisam_trig_08.test	220 B	-rw-r--r--
myisam_trig_09.test	220 B	-rw-r--r--
myisam_trig_1011ext.test	230 B	-rw-r--r--
myisam_views-big.test	945 B	-rw-r--r--
ndb_bitdata.test	420 B	-rw-r--r--
ndb_cursors.test	417 B	-rw-r--r--
ndb_func_view.test	490 B	-rw-r--r--
ndb_storedproc_02.test	206 B	-rw-r--r--
ndb_storedproc_03.test	206 B	-rw-r--r--
ndb_storedproc_06.test	206 B	-rw-r--r--
ndb_storedproc_07.test	206 B	-rw-r--r--
ndb_storedproc_08.test	206 B	-rw-r--r--
ndb_storedproc_10.test	206 B	-rw-r--r--
ndb_trig_0102.test	235 B	-rw-r--r--
ndb_trig_03.test	231 B	-rw-r--r--
ndb_trig_03e.test	834 B	-rw-r--r--
ndb_trig_0407.test	235 B	-rw-r--r--
ndb_trig_08.test	231 B	-rw-r--r--
ndb_trig_09.test	231 B	-rw-r--r--
ndb_trig_1011ext.test	241 B	-rw-r--r--
ndb_views.test	519 B	-rw-r--r--
processlist_priv_no_prot.test	1.74 KB	-rw-r--r--
processlist_priv_ps.test	1.68 KB	-rw-r--r--
processlist_val_no_prot.test	1.54 KB	-rw-r--r--
processlist_val_ps.test	1.48 KB	-rw-r--r--
storedproc.test	652.52 KB	-rw-r--r--
suite.opt	38 B	-rw-r--r--

Code Editor : is_basics_mixed.test

# suite/funcs_1/t/is_basics_mixed.test
#
# Checks of some basic properties of the INFORMATION_SCHEMA which are not
# related to a certain INFORMATION_SCHEMA table.
#
# This test should not check properties related to storage engines.
#
# Author:
# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
#                           testsuite funcs_1
#                   Create this script based on older scripts and new code.
#

# This test is strict adjusted to the behaviour of the non embedded server.
# Example of common differences between both servers:
#    USE information_schema; CREATE VIEW tables AS SELECT 'garbage';
#    non embedded server:
#        - errname ER_DBACCESS_DENIED_ERROR
#        - ERROR 42000: Access denied for user 'root'@'localhost' to
#          database 'information_schema'
#    embedded server:
#        - errno 1 
#        - Can't create/write to file
#          '.../var/master-data/information_schema/tables.frm~
--source include/not_embedded.inc

--source suite/funcs_1/datadict/datadict.pre

# $engine_type must point to storage engine which is all time available.
# The fastest engine should be preferred.
let $engine_type = MEMORY;

# The INFORMATION_SCHEMA database must exist.
SHOW DATABASES LIKE 'information_schema';

--echo #######################################################################
--echo # Testcase 3.2.1.20: USE INFORMATION_SCHEMA is supported
--echo #######################################################################
# Ensure that USE INFORMATION_SCHEMA allows the user to switch to the
# INFORMATION_SCHEMA database, for query purposes only.
#
# Note: The "for query purposes only" is checked in other tests.
# High privileged user (root)
--echo # Switch to connection default
connection default;
USE test;
SELECT DATABASE();
USE information_schema;
SELECT DATABASE();
#
--error 0,ER_CANNOT_USER
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
# Low privileged user
--echo # Establish connection testuser1 (user=testuser1)
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (testuser1, localhost, testuser1, , test);
SELECT DATABASE();
USE information_schema;
SELECT DATABASE();
#
--echo # Switch to connection default and close connection testuser1
connection default;
disconnect testuser1;
DROP   USER 'testuser1'@'localhost';

--echo #######################################################################
--echo # Testcase TBD1: The INFORMATION_SCHEMA cannot be dropped.
--echo #######################################################################
--error ER_DBACCESS_DENIED_ERROR
DROP DATABASE information_schema;

--echo #######################################################################
--echo # Testcase TBD2: There cannot be a second database INFORMATION_SCHEMA.
--echo #######################################################################
--error ER_DBACCESS_DENIED_ERROR
CREATE DATABASE information_schema;

--echo ##################################################################################
--echo # Testcase 3.2.1.6+3.2.1.7: No user may create an INFORMATION_SCHEMA table or view
--echo ##################################################################################
# 3.2.1.6 Ensure that no user may create an INFORMATION_SCHEMA base table.
# 3.2.1.7 Ensure that no user may create an INFORMATION_SCHEMA view
#

# 1. High privileged user (root)
--echo # Switch to connection default (user=root)
connection default;
--source suite/funcs_1/datadict/basics_mixed1.inc

# 2. High privileged user (testuser1)
--error 0,ER_CANNOT_USER
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
GRANT ALL ON *.* TO testuser1@localhost;
SHOW GRANTS FOR testuser1@localhost;
--echo # Establish connection testuser1 (user=testuser1)
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (testuser1, localhost, testuser1, , test);
--source suite/funcs_1/datadict/basics_mixed1.inc

--echo # Switch to connection default (user=root) and close connection testuser1
connection default;
disconnect testuser1;
DROP   USER 'testuser1'@'localhost';

--echo ###############################################################################
--echo # Testcase 3.2.1.1+3.2.1.2: INFORMATION_SCHEMA tables can be queried via SELECT
--echo ###############################################################################
# 3.2.1.1 Ensure that every INFORMATION_SCHEMA table can be queried with
#         a SELECT statement, just as if it were an ordinary user-defined table.
# 3.2.1.2 Ensure that queries on an INFORMATION_SCHEMA table can accept all
#         SELECT statement options and are always correctly evaluated.
#
# Some notes(mleich):
# - Currently here only a subset of select statement options is checked, it's
#   still not possible to check here all possible options
# - The content of many INFORMATION_SCHEMA tables is checked in other tests.
# - We work here only with a subset of the columns of information_schema.tables
#   because we want have a stable base (all time existing table, stable layout).
--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
--replace_result $engine_type <some_engine>
eval
CREATE TABLE db_datadict.t1_first (f1 BIGINT UNIQUE, f2 BIGINT)
ENGINE = $engine_type;
--replace_result $engine_type <some_engine>
eval
CREATE TABLE db_datadict.t1_second (f1 BIGINT UNIQUE, f2 BIGINT)
ENGINE = $engine_type;

# SELECT *
--echo # Attention: The protocolling of the next result set is disabled.
--disable_result_log
SELECT * FROM information_schema.tables;
--enable_result_log
#
# SELECT <some columns> + WHERE
--sorted_result
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'db_datadict';
#
# SELECT string_function(<some column>) + ORDER BY
SELECT LENGTH(table_name) FROM information_schema.tables
WHERE table_schema = 'db_datadict' ORDER BY table_name;
#
# SELECT aggregate_function(<some column>) + WHERE with LIKE
SELECT count(table_name) FROM information_schema.tables
WHERE table_schema LIKE 'db_datadic%';
#
# SELECT with addition in column list
--sorted_result
SELECT CAST((LENGTH(table_schema) + LENGTH(table_name)) AS DECIMAL(15,1))
FROM information_schema.tables
WHERE table_schema = 'db_datadict';
#
# WHERE with IN + LIMIT
SELECT table_name FROM information_schema.tables
WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1;
SELECT table_name FROM information_schema.tables
WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1,1;
#
# WHERE with AND
SELECT table_name,table_schema AS my_col FROM information_schema.tables
WHERE table_name = 't1_first' AND table_schema = 'db_datadict';
#
# SELECT HIGH_PRIORITY + WHERE with OR
--sorted_result
SELECT HIGH_PRIORITY table_name AS my_col FROM information_schema.tables
WHERE table_name = 't1_first' OR table_name = 't1_second';
#
# Empty result set
SELECT 1 AS my_col FROM information_schema.tables
WHERE table_name = 't1_third';
#
# SELECT INTO USER VARIABLE
SELECT table_name,table_schema INTO @table_name,@table_schema
FROM information_schema.tables
WHERE table_schema = 'db_datadict' ORDER BY table_name LIMIT 1;
SELECT @table_name,@table_schema;
#
# SELECT INTO OUTFILE
let $OUTFILE = $MYSQLTEST_VARDIR/tmp/datadict.out;
--error 0,1
remove_file $OUTFILE;
--replace_result $OUTFILE <OUTFILE>
eval SELECT table_name,table_schema
INTO OUTFILE '$OUTFILE'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM information_schema.tables
WHERE table_schema = 'db_datadict' ORDER BY table_name;
cat_file $OUTFILE;
remove_file $OUTFILE;
#
# UNION
--sorted_result
SELECT table_name FROM information_schema.tables
WHERE table_name = 't1_first'
UNION ALL
SELECT table_name FROM information_schema.tables
WHERE table_name = 't1_second';
#
# DISTINCT + SUBQUERY
SELECT DISTINCT table_schema FROM information_schema.tables
WHERE table_name IN (SELECT table_name FROM information_schema.tables
                     WHERE table_schema = 'db_datadict')
ORDER BY table_name;
#
# JOIN
SELECT table_name FROM information_schema.tables t1
LEFT JOIN information_schema.tables t2 USING(table_name,table_schema)
WHERE t2.table_schema = 'db_datadict'
ORDER BY table_name;
#
# No schema assigned in SELECT + we are in SCHEMA test
#    --> The table tables does not exist
USE test;
--error ER_NO_SUCH_TABLE
SELECT * FROM tables;

--echo #########################################################################
--echo # Testcase 3.2.1.17+3.2.1.18
--echo #########################################################################
# 3.2.1.17: Ensure that the SELECT privilege is granted TO PUBLIC WITH GRANT
#           OPTION on every INFORMATION_SCHEMA table.
#
# 3.2.1.18: Ensure that the CREATE VIEW privilege on an INFORMATION_SCHEMA table
#           may be granted to any user.
#
# Note (mleich): The requirements are to some extend outdated.
#                Every user is allowed to SELECT on the INFORMATION_SCHEMA.
#                But the result sets depend on the privileges of the user.
#
--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
--replace_result $engine_type <some_engine>
eval
CREATE TABLE db_datadict.t1 (f1 BIGINT UNIQUE, f2 BIGINT)
ENGINE = $engine_type;
SELECT * FROM db_datadict.t1;

--error 0,ER_CANNOT_USER
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
--error 0,ER_CANNOT_USER
DROP   USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
GRANT CREATE VIEW,SELECT ON db_datadict.* TO testuser1@localhost
WITH GRANT OPTION;
GRANT USAGE ON db_datadict.* TO testuser2@localhost;
FLUSH PRIVILEGES;

# Check 0: Reveal that GRANT <some privilege> ON INFORMATION_SCHEMA is no
#          longer allowed.
--error ER_DBACCESS_DENIED_ERROR
GRANT SELECT on information_schema.* TO testuser1@localhost;
--error ER_DBACCESS_DENIED_ERROR
GRANT CREATE VIEW ON information_schema.* TO 'u_6_401018'@'localhost';

# Check 1: Show that a "simple" user (<> root) has the permission to SELECT
#          on some INFORMATION_SCHEMA table.
--echo # Establish connection testuser1 (user=testuser1)
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (testuser1, localhost, testuser1, , db_datadict);
SELECT table_schema,table_name FROM information_schema.tables
WHERE table_schema = 'information_schema' AND table_name = 'tables';

# Check 2: Show the privileges of the user on some INFORMATION_SCHEMA tables.
SELECT * FROM information_schema.table_privileges
WHERE table_schema = 'information_schema';
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'information_schema';

# Check 3: Show the following
#          1. If a simple user (testuser1) has the privilege to create a VIEW
#             than this VIEW could use a SELECT on an INFORMATION_SCHEMA table.
#          2. This user (testuser1) is also able to GRANT the SELECT privilege
#             on this VIEW to another user (testuser2).
#          3. The other user (testuser2) must be able to SELECT on this VIEW
#             but gets a different result set than testuser1.
CREATE VIEW db_datadict.v2 AS
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
FROM information_schema.tables WHERE table_schema = 'db_datadict';
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
FROM db_datadict.v2;
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
FROM information_schema.tables WHERE table_schema = 'db_datadict';
GRANT SELECT ON db_datadict.v2 to testuser2@localhost;
#
--echo # Establish connection testuser2 (user=testuser2)
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (testuser2, localhost, testuser2, , db_datadict);
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
FROM db_datadict.v2;
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
FROM information_schema.tables WHERE table_schema = 'db_datadict';

# Cleanup
--echo # Switch to connection default and close connections testuser1 and testuser2
connection default;
disconnect testuser1;
disconnect testuser2;
DROP USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
DROP DATABASE db_datadict;

--echo #########################################################################
--echo # Testcase 3.2.1.19
--echo #########################################################################
# Ensure that no other privilege on an INFORMATION_SCHEMA table is granted, or
# may be granted, to any user.
#
--error 0,ER_CANNOT_USER
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';

# Initial privileges on the INFORMATION_SCHEMA tables (empty result sets)
let $my_select1 = SELECT 'empty result set was expected' AS my_col
FROM information_schema.schema_privileges
WHERE table_schema = 'information_schema';
let $my_select2 = SELECT 'empty result set was expected' AS my_col
FROM information_schema.table_privileges
WHERE table_schema = 'information_schema';
let $my_select3 = SELECT 'empty result set was expected' AS my_col
FROM information_schema.column_privileges
WHERE table_schema = 'information_schema';
eval $my_select1;
eval $my_select2;
eval $my_select3;

#FIXME: check GRANT on IS
--error ER_DBACCESS_DENIED_ERROR
GRANT ALTER ON information_schema.*
TO 'testuser1'@'localhost';

#FIXME: check GRANT on IS
--error ER_DBACCESS_DENIED_ERROR
GRANT ALTER ROUTINE ON information_schema.*
TO 'testuser1'@'localhost';

#FIXME: check GRANT on IS
--error ER_DBACCESS_DENIED_ERROR
GRANT CREATE ON information_schema.*
TO 'testuser1'@'localhost';

#FIXME: check GRANT on IS
--error ER_DBACCESS_DENIED_ERROR
GRANT CREATE ROUTINE ON information_schema.*
TO 'testuser1'@'localhost';

#FIXME: check GRANT on IS
--error ER_DBACCESS_DENIED_ERROR
GRANT CREATE TEMPORARY TABLES ON information_schema.*
TO 'testuser1'@'localhost';

#FIXME: check GRANT on IS
--error ER_DBACCESS_DENIED_ERROR
GRANT DELETE ON information_schema.*
TO 'testuser1'@'localhost';

#FIXME: check GRANT on IS
--error ER_DBACCESS_DENIED_ERROR
GRANT DROP ON information_schema.*
TO 'testuser1'@'localhost';

#FIXME: check GRANT on IS
--error ER_DBACCESS_DENIED_ERROR
GRANT EXECUTE ON information_schema.*
TO 'testuser1'@'localhost';

#FIXME: check GRANT on IS
--error ER_DBACCESS_DENIED_ERROR
GRANT INDEX ON information_schema.*
TO 'testuser1'@'localhost';

#FIXME: check GRANT on IS
--error ER_DBACCESS_DENIED_ERROR
GRANT INSERT ON information_schema.*
TO 'testuser1'@'localhost';

#FIXME: check GRANT on IS
--error ER_DBACCESS_DENIED_ERROR
GRANT LOCK TABLES ON information_schema.*
TO 'testuser1'@'localhost';

#FIXME: check GRANT on IS
--error ER_DBACCESS_DENIED_ERROR
GRANT UPDATE ON information_schema.*
TO 'testuser1'@'localhost';

# Has something accidently changed?
eval $my_select1;
eval $my_select2;
eval $my_select3;

# Cleanup
DROP USER 'testuser1'@'localhost';

--echo #########################################################################
--echo # Testcase 3.2.1.16
--echo #########################################################################
# Ensure that no user may use any INFORMATION_SCHEMA table to determine any
# information on a database and/or its structure unless authorized to get that
# information.
# Note: The plan is to create a new database and objects within it so that
#       any INFORMATION_SCHEMA table gets additional rows if possible.
#       A user having no rights on the new database and no rights on objects
#       must nowhere see tha name of the new database.
--source suite/funcs_1/datadict/basics_mixed3.inc

--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
--replace_result $engine_type <some_engine>
eval
CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT NOT NULL, f3 BIGINT,
PRIMARY KEY(f1))
ENGINE = $engine_type;
CREATE UNIQUE INDEX UIDX ON db_datadict.t1(f3);
CREATE PROCEDURE db_datadict.sproc1()      SELECT 'db_datadict';
CREATE FUNCTION db_datadict.func1() RETURNS INT RETURN 0;
CREATE TRIGGER db_datadict.trig1 BEFORE INSERT ON db_datadict.t1
FOR EACH ROW SET @aux = 1;
CREATE VIEW db_datadict.v1 AS SELECT * FROM db_datadict.t1;
CREATE VIEW db_datadict.v2 AS SELECT * FROM information_schema.tables;

--source suite/funcs_1/datadict/basics_mixed3.inc

--error 0,ER_CANNOT_USER
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
GRANT ALL ON test.* TO 'testuser1'@'localhost';

--echo # Establish connection testuser1 (user=testuser1)
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (testuser1, localhost, testuser1, , test);
--source suite/funcs_1/datadict/basics_mixed3.inc

# Cleanup
--echo # Switch to connection default and close connections testuser1 and testuser2
connection default;
disconnect testuser1;
DROP   USER 'testuser1'@'localhost';
DROP DATABASE db_datadict;

--echo ########################################################################
--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
--echo #           DDL on INFORMATION_SCHEMA tables are not supported
--echo ########################################################################
# Thorough tests checking the requirements above per every INFORMATION_SCHEMA
# table are within other scripts.
# We check here only that the requirement is fulfilled even when using a
# STORED PROCEDURE.
--disable_warnings
DROP PROCEDURE IF EXISTS test.p1;
--enable_warnings
CREATE PROCEDURE test.p1()
INSERT INTO information_schema.tables
SELECT * FROM information_schema.tables LIMIT 1;
--error ER_DBACCESS_DENIED_ERROR
CALL test.p1();

DROP PROCEDURE test.p1;
CREATE PROCEDURE test.p1()
UPDATE information_schema.columns SET table_schema = 'garbage';
--error ER_DBACCESS_DENIED_ERROR
CALL test.p1();

DROP PROCEDURE test.p1;
CREATE PROCEDURE test.p1()
DELETE FROM information_schema.schemata;
--error ER_DBACCESS_DENIED_ERROR
CALL test.p1();

DROP PROCEDURE test.p1;

--echo #########################################################################
--echo # Testcase 3.2.17.1+3.2.17.2: To be implemented outside of this script
--echo #########################################################################
# 3.2.17.1 Ensure that every INFORMATION_SCHEMA table shows all the correct
#          information, and no incorrect information, for a database to which
#          100 different users, each of which has a randomly issued set of
#          privileges and access to a randomly chosen set of database objects,
#          have access.
#          The database should contain a mixture of all types of database
#          objects (i.e. tables, views, stored procedures, triggers).
# 3.2.17.2 Ensure that every INFORMATION_SCHEMA table shows all the correct
#          information, and no incorrect information, for 10 different
#          databases to which 50 different users, each of which has a randomly
#          issued set of privileges and access to a randomly chosen set of
#          database objects in two or more of the databases, have access.
#          The databases should each contain a mixture of all types of database
#          objects (i.e. tables, views, stored procedures, triggers).
#
# Note(mleich): These requirements are kept here so that they do not get lost.
#          The tests are not yet implemented.
#          If they are ever developed than they should be stored in other
#          scripts. They will have most probably a long runtime because
#          the current INFORMATION_SCHEMA implementation has some performance
#          issues if a lot of users, privileges and objects are involved.
#

${this.title}

Code Editor : is_basics_mixed.test