Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32500

Information schema leaks table names and structure to unauthorized users

Details

    Description

      InnoDB is used to get a foreign key, otherwise it is not specific to InnoDB.

      --source include/have_innodb.inc
      		 
       
      		 
      create database db;
      		 
      create table db.t1 (x int, key(x)) engine=InnoDB;
      		 
      create table db.t2 (a int, b int, c int, unique(b), check(c>b), foreign key(c) references db.t1(x)) engine=InnoDB;
      		 
       
      		 
      create user u@localhost;
      		 
      grant select (a) on db.t2 to u@localhost;
      		 
       
      		 
      --connect (con1,localhost,u,,db)
      		 
      --echo #
      		 
      --echo # This is OK:
      		 
      select table_name, column_name from information_schema.columns where table_name in ('t1','t2');
      		 
      --echo #
      		 
      --echo # This is not:
      		 
      select table_name, column_name from information_schema.key_column_usage where table_name in ('t1','t2');
      		 
      select table_name, unique_constraint_name, referenced_table_name from information_schema.referential_constraints where table_name in ('t1','t2');
      		 
      --echo #
      		 
      --echo # This is possibly not OK either:
      		 
      select table_name, constraint_name from information_schema.table_constraints where table_name in ('t1','t2');
      		 
      select table_name, constraint_name, constraint_type from information_schema.table_constraints where table_name in ('t1','t2');
      		 
      show index in t2;
      		 
       
      		 
      # Cleanup
      		 
      --disconnect con1
      		 
      --connection default
      		 
      drop user u@localhost;
      		 
      drop database db;

      Actual result, 10.4 b1c8ea83

      		 
      # This is OK:
      		 
      select table_name, column_name from information_schema.columns where table_name in ('t1','t2');
      		 
      table_name	column_name
      		 
      t2	a
      		 
      #
      		 
      # This is not:
      		 
      select table_name, column_name from information_schema.key_column_usage where table_name in ('t1','t2');
      		 
      table_name	column_name
      		 
      t2	b
      		 
      t2	c
      		 
      select table_name, unique_constraint_name, referenced_table_name from information_schema.referential_constraints where table_name in ('t1','t2');
      		 
      table_name	unique_constraint_name	referenced_table_name
      		 
      t2	x	t1
      		 
      #
      		 
      # This is possibly not OK either:
      		 
      select table_name, constraint_name from information_schema.table_constraints where table_name in ('t1','t2');
      		 
      table_name	constraint_name
      		 
      t2	b
      		 
      t2	CONSTRAINT_1
      		 
      t2	t2_ibfk_1
      		 
      select table_name, constraint_name, constraint_type from information_schema.table_constraints where table_name in ('t1','t2');
      		 
      table_name	constraint_name	constraint_type
      		 
      t2	b	UNIQUE
      		 
      t2	CONSTRAINT_1	CHECK
      		 
      t2	t2_ibfk_1	FOREIGN KEY
      		 
      show index in t2;
      		 
      Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
      		 
      t2	0	b	1	b	A	0	NULL	NULL	YES	BTREE		
      t2	1	c	1	c	A	0	NULL	NULL	YES	BTREE

      The expected result is probably only to return one row to the first query and nothing else, but maybe it's trickier than that.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34838 SELECT Grant restricts visibility to referential_constraints table

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33647 SELECT FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS empty set when specific privileges on table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32501 INFORMATION_SCHEMA.KEY_PERIOD_USAGE reveals information to unprivileged user

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Description _InnoDB is used to get a foreign key, otherwise it is not specific to InnoDB._

            {code:sql}
            --source include/have_innodb.inc

            create database db;
            create table db.t1 (x int, key(x)) engine=InnoDB;
            create table db.t2 (a int, b int, c int, unique(b), check(c>b), foreign key(c) references db.t1(x)) engine=InnoDB;

            create user u@localhost;
            grant select (a) on db.t2 to u@localhost;

            --connect (con1,localhost,u,,db)
            --echo #
            --echo # This is OK:
            select table_name, column_name from information_schema.columns where table_name in ('t1','t2');
            --echo #
            --echo # This is not:
            select table_name, column_name from information_schema.key_column_usage where table_name in ('t1','t2');
            select table_name, unique_constraint_name, referenced_table_name from information_schema.referential_constraints where table_name in ('t1','t2');
            --echo #
            --echo # This is probably not OK either:
            select table_name, constraint_name from information_schema.table_constraints where table_name in ('t1','t2');
            select table_name, constraint_name, constraint_type from information_schema.table_constraints where table_name in ('t1','t2');

            # Cleanup
            --disconnect con1
            --connection default
            drop user u@localhost;
            drop database db;
            {code}

            {code:sql|title=Actual result, 10.4 b1c8ea83}
            # This is OK:
            select table_name, column_name from information_schema.columns where table_name in ('t1','t2');
            table_name column_name
            t2 a
            #
            # This is not:
            select table_name, column_name from information_schema.key_column_usage where table_name in ('t1','t2');
            table_name column_name
            t2 b
            t2 c
            select table_name, unique_constraint_name, referenced_table_name from information_schema.referential_constraints where table_name in ('t1','t2');
            table_name unique_constraint_name referenced_table_name
            t2 x t1
            #
            # This is probably not OK either:
            select table_name, constraint_name from information_schema.table_constraints where table_name in ('t1','t2');
            table_name constraint_name
            t2 b
            t2 CONSTRAINT_1
            t2 t2_ibfk_1
            select table_name, constraint_name, constraint_type from information_schema.table_constraints where table_name in ('t1','t2');
            table_name constraint_name constraint_type
            t2 b UNIQUE
            t2 CONSTRAINT_1 CHECK
            t2 t2_ibfk_1 FOREIGN KEY
            {code}

            The expected result is probably only to return one row to the first query and nothing else, but maybe it's trickier than that.             		_InnoDB is used to get a foreign key, otherwise it is not specific to InnoDB._

            {code:sql}
            --source include/have_innodb.inc

            create database db;
            create table db.t1 (x int, key(x)) engine=InnoDB;
            create table db.t2 (a int, b int, c int, unique(b), check(c>b), foreign key(c) references db.t1(x)) engine=InnoDB;

            create user u@localhost;
            grant select (a) on db.t2 to u@localhost;

            --connect (con1,localhost,u,,db)
            --echo #
            --echo # This is OK:
            select table_name, column_name from information_schema.columns where table_name in ('t1','t2');
            --echo #
            --echo # This is not:
            select table_name, column_name from information_schema.key_column_usage where table_name in ('t1','t2');
            select table_name, unique_constraint_name, referenced_table_name from information_schema.referential_constraints where table_name in ('t1','t2');
            --echo #
            --echo # This is possibly not OK either:
            select table_name, constraint_name from information_schema.table_constraints where table_name in ('t1','t2');
            select table_name, constraint_name, constraint_type from information_schema.table_constraints where table_name in ('t1','t2');
            show index in t2;

            # Cleanup
            --disconnect con1
            --connection default
            drop user u@localhost;
            drop database db;
            {code}

            {code:sql|title=Actual result, 10.4 b1c8ea83}
            # This is OK:
            select table_name, column_name from information_schema.columns where table_name in ('t1','t2');
            table_name column_name
            t2 a
            #
            # This is not:
            select table_name, column_name from information_schema.key_column_usage where table_name in ('t1','t2');
            table_name column_name
            t2 b
            t2 c
            select table_name, unique_constraint_name, referenced_table_name from information_schema.referential_constraints where table_name in ('t1','t2');
            table_name unique_constraint_name referenced_table_name
            t2 x t1
            #
            # This is possibly not OK either:
            select table_name, constraint_name from information_schema.table_constraints where table_name in ('t1','t2');
            table_name constraint_name
            t2 b
            t2 CONSTRAINT_1
            t2 t2_ibfk_1
            select table_name, constraint_name, constraint_type from information_schema.table_constraints where table_name in ('t1','t2');
            table_name constraint_name constraint_type
            t2 b UNIQUE
            t2 CONSTRAINT_1 CHECK
            t2 t2_ibfk_1 FOREIGN KEY
            show index in t2;
            Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
            t2 0 b 1 b A 0 NULL NULL YES BTREE
            t2 1 c 1 c A 0 NULL NULL YES BTREE
            {code}

            The expected result is probably only to return one row to the first query and nothing else, but maybe it's trickier than that.
            elenst Elena Stepanova made changes -
            serg Sergei Golubchik added a comment -
            • second case (key_column_usage) is not OK, one needs to have some privileges on all columns that are part of the key
            • third case (table_constraints) in not OK but barely, one needs any non-select privilege on a table or on any columns of the table, so having update (a) on db.t2 would be enough
            • fourth case (show index) is non-standard, we can do whatever we want. may be doing what key_column_usage does would be reasonable?
            serg Sergei Golubchik added a comment - second case ( key_column_usage ) is not OK, one needs to have some privileges on all columns that are part of the key third case ( table_constraints ) in not OK but barely, one needs any non-select privilege on a table or on any columns of the table, so having update (a) on db.t2 would be enough fourth case ( show index ) is non-standard, we can do whatever we want. may be doing what key_column_usage does would be reasonable?
            elenst Elena Stepanova added a comment -

            fourth case (show index) is non-standard, we can do whatever we want. may be doing what key_column_usage does would be reasonable?

            Yes, I would expect SHOW commands behave consistently with I_S views with similar semantics (key_column_usage seems a good candidate at the first glance), but since I_S itself is all over the place, I couldn't even formulate it this way.

            Note that there is also case 2a (not to break your numeration completely), referential_constraints. Its specifics is that shows the referenced table, to which the user has no privileges at all.

            elenst Elena Stepanova added a comment - fourth case (show index) is non-standard, we can do whatever we want. may be doing what key_column_usage does would be reasonable? Yes, I would expect SHOW commands behave consistently with I_S views with similar semantics (key_column_usage seems a good candidate at the first glance), but since I_S itself is all over the place, I couldn't even formulate it this way. Note that there is also case 2a (not to break your numeration completely), referential_constraints . Its specifics is that shows the referenced table, to which the user has no privileges at all.
            serg Sergei Golubchik added a comment -

            Right, sorry. Missed that.

            • second "a" case (referential_constraints) is defined via table_constraints. A row is shown if the TABLE_NAME is present in table_constraints. If REFERENCED_TABLE_NAME is not present in table_constraints, the column value will be NULL (the standard uses LEFT JOIN there).
            serg Sergei Golubchik added a comment - Right, sorry. Missed that. second "a" case ( referential_constraints ) is defined via table_constraints . A row is shown if the TABLE_NAME is present in table_constraints . If REFERENCED_TABLE_NAME is not present in table_constraints , the column value will be NULL (the standard uses LEFT JOIN there).
            elenst Elena Stepanova added a comment -

            Yes, in case of referential constraints the doubt is not about the column name, but about the table t1 itself. A user without any grants for a table normally isn't allowed to know about its existence at all.

            elenst Elena Stepanova added a comment - Yes, in case of referential constraints the doubt is not about the column name, but about the table t1 itself. A user without any grants for a table normally isn't allowed to know about its existence at all.
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Status Stalled [ 10000 ] In Testing [ 10301 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.4.32 [ 29300 ]
            Fix Version/s 10.5.23 [ 29012 ]
            Fix Version/s 10.6.16 [ 29014 ]
            Fix Version/s 10.10.7 [ 29018 ]
            Fix Version/s 10.11.6 [ 29020 ]
            Fix Version/s 11.0.4 [ 29021 ]
            Fix Version/s 11.1.3 [ 29023 ]
            Fix Version/s 11.2.2 [ 29035 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.10 [ 27530 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.0 [ 28320 ]
            Fix Version/s 11.1 [ 28549 ]
            Resolution Fixed [ 1 ]
            Status In Testing [ 10301 ] Closed [ 6 ]
            anel Anel Husakovic made changes -
            serg Sergei Golubchik made changes -

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.