Bug #179 A user with only CREATE TEMPORARY TABLES privilege can USE any table.
Submitted: 21 Mar 2003 14:30 Modified: 24 Mar 2003 8:58
Reporter: Alexander M. Turek Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.2 - 4.0.12 OS:independant
Assigned to: CPU Architecture:Any

[21 Mar 2003 14:30] Alexander M. Turek
Description:
If a user only owns a global CREATE TEMPORARY TABLES privilege, he can access the full database list and is also able to USE any table.

How to repeat:
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.12-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT USAGE ON *.* TO dummy1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT CREATE TEMPORARY TABLES ON *.* TO dummy2@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE somedb1;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE somedb2;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE somedb3;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
# mysql -u dummy1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 4.0.12-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| test     |
+----------+
1 row in set (0.00 sec)

mysql> USE mysql;
ERROR 1044: Access denied for user: 'dummy1@localhost' to database 'mysql'
mysql> exit
Bye
# mysql -u dummy2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 4.0.12-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| somedb1  |
| somedb2  |
| somedb3  |
| test     |
+----------+
5 rows in set (0.00 sec)

mysql> USE mysql;
Database changed
mysql> exit
Bye
[22 Mar 2003 5:44] Sinisa Milivojevic
I tested it and could not reproduce it.

You simply forgot to re-login as dummy1 or dummy2.
[22 Mar 2003 7:08] Sinisa Milivojevic
Sorry, did not see the rest of the report.

This is not a bug.

If you have any global privilege or any privilge at database
level you will be able to change database. You will not be able to do ANYTHING in db, including SHOW TABLES.
[22 Mar 2003 14:43] Alexander M. Turek
> You will not be able to do
> ANYTHING in db, including SHOW TABLES.

... also including working with the temporary table you are able to create there:

--- CUT ---
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.12-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT CREATE TEMPORARY TABLES ON *.* TO dummy@localhost;
Query OK, 0 rows affected (0.49 sec)

mysql> exit
Bye
# mysql -u dummy
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.12-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE mysql;
Database changed
mysql> CREATE TEMPORARY TABLE `foo` (`foo` VARCHAR(5) DEFAULT NULL);
Query OK, 0 rows affected (0.35 sec)

mysql> INSERT INTO `foo` VALUES ('a', 'b', 'c');
ERROR 1044: Access denied for user: 'dummy@localhost' to database 'mysql'
mysql> SELECT * FROM `foo`;
ERROR 1044: Access denied for user: 'dummy@localhost' to database 'mysql'
--- CUT ---

To summarize it:
A user with only CREATE TEMPORARY TABLES privilege is allowed to view the the whole database list just because he would be able to create temporary tables he cannot use afterwards in each of them?

I'm sorry, but this behavior does not make sense at all.

And by the way, what do I need the SHOW DATABASES privilege for, then?
[24 Mar 2003 5:56] Sinisa Milivojevic
This is all proper and documented behaviour and not a bug.

For databases that are visible, there is --safe-show-database
option.
[24 Mar 2003 7:33] Alexander M. Turek
BTW, your documentation says about "--safe-show-database":

--safe-show-database
With this option, the SHOW DATABASES command returns only those databases for which the user has some kind of privilege. From version 4.0.2 this option is deprecated and doesn't do anything (the option is enabled by default) as we now have the SHOW DATABASES privilege.

This is why I'd avoid using this parameter :-)

Well, it's not explicitly documented, as far as I can see. 
The thing is - documented or not - that this behavior does not make sense.
And since the "CREATE TEMPORARY TABLES" privilege is granted to each user if one upgrades from MySQL < 4.0.2 this results in widely unwanted behavior.

imho, the following solution would make sense:

CREATE TEMPORARY TABLES only should only affect those tables the user has other grants on like it is the case with the FILE privilege: A global FILE privilege alone does not allow to access the full database list.
Just image: what do I need a temporary table for? For database work, storing data temporarily, etc. 
How do they help me if I'm working with a database I have no privileges on?
Furthermore, a user has all privileges on his temporary tables. What do I need a temporary table for, if I'm unable to use it?

I'm sorry if I bother you with this bug report, but I just want to help the dev team to make MySQL better as it is already.

If this is rather a feature request than a bug, please tell me where to post it.
[24 Mar 2003 7:51] Sinisa Milivojevic
Current behaviour is correct and well documented.
User with global FILE privilege alone can also select any database.

CREATE TEMPORARY TABLES should not be linked with tables on which user has grants as table is created in database and not in the table.

This is a global privilege, because it is much simpler to
maintain and is not critical on resources and secutity.
[24 Mar 2003 8:07] Alexander M. Turek
> User with global FILE privilege 
> alone can also select any database.

Really?

--- CUT ---
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.12-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT FILE ON *.* TO dummy@localhost;
Query OK, 0 rows affected (0.37 sec)

mysql> SHOW GRANTS FOR dummy@localhost;
+------------------------------------------+
| Grants for dummy@localhost               |
+------------------------------------------+
| GRANT FILE ON *.* TO 'dummy'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| somedb1  |
| somedb2  |
| somedb3  |
| test     |
+----------+
5 rows in set (0.00 sec)

mysql> exit
Bye
# mysql -u dummy
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 4.0.12-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| test     |
+----------+
1 row in set (0.00 sec)

mysql> USE mysql;
ERROR 1044: Access denied for user: 'dummy@localhost' to database 'mysql'
mysql> exit
Bye
--- CUT ---
[24 Mar 2003 8:14] Sinisa Milivojevic
Yes, I was wrong.

FILE privilege will not allow db to be selected.

This is because FILE privilege is global only and is not shared on database level.

User with any global privilege that is a part of database privileges is able to select a database.

This will be better documented, though.
[24 Mar 2003 8:42] Alexander M. Turek
Ok then, what about my suggestion to automatically grant all privileges to a user on his temporary tables?
This shouldn't hurt anyone and would bring much more possibilities into the temporary table feature.
[24 Mar 2003 8:58] Sinisa Milivojevic
This is quite doable in 4.1, but as it is not that important, without sponsorship will not be done in the near future.

It would require a new startup option, though.
[16 May 2005 6:02] H j
</TD>
    </TR>
  </TABLE>
  <br>
  <table width="99%" border="0" cellspacing="0" cellpadding="0">
    <tr> 
      <td> 
        <p class="titel">Hjemme siden for alle - Skriv lige i Gb - Tak skal i Ha´:</p>
        <h1 align="center"><a href="http://www.jacob-3.arto.dk" Target="_top">Webdesign</a> - <a 

href="http://www.jacob-3.arto.dk" Target="_top">Denmarks hygligste chat</a> 
          - <a href="http://www.jacob-3.arto.dk" Target="_top">Website Opdating</a> - <a 

href="http://www.jacob-3.arto.dk" Target="_top">&#153copyright2005&#153;</a> 
          - <a href="http://www.jacob-3.arto.dk" Target="_top">Homepage-from Denmark</a></h1>
        <hr width="600" size="1" align="center">
      </td>
    </tr>
  </table>
</DIV>
<P align="center"><script language="JavaScript"><!--
refer = '<a href="http://www.fun-hits.de/click/?27" target="_blank">'
refer += '<img border="0" width="88" height="31" nosave 

src="http://www.fun-hits.de/premiumbutton/blank/?27;'
refer += escape(top.document.referrer) +'"></a>'; document.write(refer)
//--></script></P>
</body>
</html>

</html>
<html>
<head>
<title>Kostenlose Homepage von WebDesign Schneppat</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<META NAME="Title" CONTENT="Kostenlose Website von WebDesign Schneppat.  -Lachs-Frame-">
<META NAME="Author" CONTENT="J.O. Schneppat">
<META NAME="Publisher" CONTENT="">
<META NAME="Copyright" CONTENT=" © by J.O. Schneppat 1999-2000">
<META NAME="Revisit-After" CONTENT="7 days">
<META NAME="Coverage" CONTENT="Worldwide">
<META NAME="distribution" CONTENT="Global">
<META NAME="Classification" CONTENT="Kostenlos, webdesign, homepagedesign, webspace, service, 

dienstleister, Advertising and Marketing, Business and Economy, Catalogs, Commercial, Design, Family, 

Family Services, Internet, Internet Services, Media, Media Services, Science, Web Hosting, Web 

Programming">
<META NAME="Expires" CONTENT="nie">
<META NAME="Keywords" 

CONTENT="free,gratis,kostenlos,umsonst,download,www,internet,web,design,page,hilfe,help,site,seite,schne

ppat,bilder,regional,pictures,neu,new,leer,ostfriesland,zubehör,angebot,angebote,suche,suchen,finde,find

en,cgi,java,css,html,dhtml,webdesign,grafikdesign.">
<META NAME="Description" CONTENT=".">
<META NAME="Abstract" CONTENT=".">
<META NAME="page-topic" CONTENT="Alle, Dienstleistung">
<META NAME="page-topic" CONTENT="Service, Produktinfo">
<META NAME="audience" CONTENT=" Alle ">
<META NAME="Language" CONTENT="Deutsch">
<meta name="rating" content="general">
<meta NAME="robots" CONTENT="INDEX">
<meta NAME="robots" CONTENT="FOLLOW">
</head>
<body bgcolor="#7e7e7e" text="#FFFFFF" link="#FFFFFF" vlink="#000000" alink="#FF0000" leftmargin="0" 

topmargin="0" marginwidth="0" marginheight="0">
<p align=Center>Test</p>
</body>
</html>
<html>
<head>
<title>Kostenlose Homepage von WebDesign Schneppat</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<META NAME="Title" CONTENT="Kostenlose Website von WebDesign Schneppat.">
<META NAME="Author" CONTENT="J.O. Schneppat">
<META NAME="Publisher" CONTENT="WebDesign Schneppat Leer Ostfriesland - Flensburg">
<META NAME="Copyright" CONTENT=" © by J.O. Schneppat 1999">
<META NAME="Revisit-After" CONTENT="7 days">
<META NAME="Coverage" CONTENT="Worldwide">
<META NAME="distribution" CONTENT="Global">
<META NAME="Classification" CONTENT="Kostenlos, webdesign, homepagedesign, webspace, service, 

dienstleister, Advertising and Marketing, Business and Economy, Catalogs, Commercial, Design, Family, 

Family Services, Internet, Internet Services, Media, Media Services, Science, Web Hosting, Web 

Programming">
<META NAME="Expires" CONTENT="nie">
<META NAME="Keywords" 

CONTENT="free,gratis,kostenlos,umsonst,download,www,internet,web,design,page,hilfe,help,site,seite,schne

ppat,bilder,regional,pictures,neu,new,leer,ostfriesland,zubehör,angebot,angebote,suche,suchen,finde,find

en,cgi,java,css,html,dhtml,webdesign,grafikdesign.">
<META NAME="Description" CONTENT="Kostenlose Website von WebDesign Schneppat für den NICHT KOMMERZIELLEN 

Gebrauch.">
<META NAME="page-topic" CONTENT="Alle, Dienstleistung">
<META NAME="page-topic" CONTENT="Service, Produktinfo">
<META NAME="audience" CONTENT=" Alle ">
<META NAME="Language" CONTENT="Deutsch">
<meta name="rating" content="general">
<meta NAME="robots" CONTENT="INDEX">
<meta NAME="robots" CONTENT="FOLLOW">
<STYLE>A:link {
	FONT-WEIGHT: none; COLOR: #000000; TEXT-DECORATION: none
}
A:visited {
	FONT-WEIGHT: none; COLOR: #000000; TEXT-DECORATION: none
}
A:hover {
	FONT-WEIGHT: bold; COLOR: #ff0000; TEXT-DECORATION: underline overline
}
BODY {
	FONT-SIZE: 8pt; COLOR: #000000; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #7e7e7e
}
TABLE {
	FONT-SIZE: 8pt; COLOR: #000000; FONT-FAMILY: Verdana
}
.titel {
	FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Verdana
}
.nav {
	FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Verdana
}

</STYLE>
<script language="JavaScript">
<!--
function MM_reloadPage(init) {  //reloads the window if Nav4 resized
  if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
    document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
  else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);
// -->
</script>
</head>
<BODY background="Frame/Navi.jpg" leftmargin="7" topmargin="0" marginwidth="7" marginheight="0">
<P><A href="C:\Documents and Settings\JB\Desktop\Færdig.html" title="Forside." target="mainFrame"><SPAN 

class="nav">• HOME</SPAN><BR></A> 
<A href="Link1.html" title="IHR TEXT ZU LINK 001 HIER !!!" target="mainFrame"><SPAN class="nav">• LINK 

001</Span></A><BR>
<A href="Link1.html" title="IHR TEXT ZU LINK 002 HIER !!!" target="mainFrame"><SPAN class="nav">• LINK 

002</Span></A><BR>
<A href="Link1.html" title="IHR TEXT ZU LINK 003 HIER !!!" target="mainFrame"><SPAN class="nav">• LINK 

003</Span></A><BR>
<A href="Link1.html" title="IHR TEXT ZU LINK 004 HIER !!!" target="mainFrame"><SPAN class="nav">• LINK 

004</Span></A><BR>
<A href="Link1.html" title="IHR TEXT ZU LINK 005 HIER !!!" target="mainFrame"><SPAN class="nav">• LINK 

005</Span></A><BR>
<A href="Link1.html" title="IHR TEXT ZU LINK 006 HIER !!!" target="mainFrame"><SPAN class="nav">• LINK 

006</Span></A><BR>
<A href="Link1.html" title="IHR TEXT ZU LINK 007 HIER !!!" target="mainFrame"><SPAN class="nav">• LINK 

007</Span></A><BR>
<A href="Link1.html" title="IHR TEXT ZU LINK 008 HIER !!!" target="mainFrame"><SPAN class="nav">• LINK 

008</Span></A><BR>
<A href="Link1.html" title="IHR TEXT ZU LINK 009 HIER !!!" target="mainFrame"><SPAN class="nav">• LINK 

009</Span></A><BR>
<A href="C:\Documents and Settings\JB\Desktop\Leve tid.html" title="De antal dage jeg har levet i !!!" 

target="mainFrame"><SPAN class="nav">• Antal dage</Span></A><BR>
<A href="http://arto.dk" title="Arto Danmarks hygligste chat !!!" target="mainFrame"><SPAN class="nav">• 

Arto</Span></A><BR>
<A href="http://wms.dr.dk/storage/p3/tjenesten/hvorblaavier.wma" title="Smølf ind smølf ud - hør dem 

!!!" target="mainFrame"><SPAN class="nav">• Hvor blå vi er</Span></A><BR>
<A href="http://wms.dr.dk/storage/p3/tjenesten/Minimalstat%20feat.%20Anders%20FoGG.wma" title="Hør 

minimalstat" target="mainFrame"><SPAN class="nav">• Hør minimalstat</Span></A><BR>
<A href="http://www.microsoft.com" title="Go to microsoft home page!!!" target="mainFrame"><SPAN 

class="nav">• gå til microsoft hp</Span></A><BR>
<A href="mailto:brix_jacob@hotmail.com" title="SenD en e-mail!!!"><SPAN class="nav">• 

E-MAIL</Span></A></P>
<P><A href="http://www.games.news.webbyen.dk" title="Made in Denmark!!!"><SPAN class="nav">• made by 

Jacob-3</Span></A></P>
</body>
</html>
<html>
<head>
<title>Kostenlose Homepage von WebDesign Schneppat</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<META NAME="Title" CONTENT="Kostenlose Website von WebDesign Schneppat.">
<META NAME="Author" CONTENT="J.O. Schneppat">
<META NAME="Publisher" CONTENT="WebDesign Schneppat Leer Ostfriesland - Flensburg">
<META NAME="Copyright" CONTENT=" © by J.O. Schneppat 1999">
<META NAME="Revisit-After" CONTENT="7 days">
<META NAME="Coverage" CONTENT="Worldwide">
<META NAME="distribution" CONTENT="Global">
<META NAME="Classification" CONTENT="Kostenlos, webdesign, homepagedesign, webspace, service, 

dienstleister, Advertising and Marketing, Business and Economy, Catalogs, Commercial, Design, Family, 

Family Services, Internet, Internet Services, Media, Media Services, Science, Web Hosting, Web 

Programming">
<META NAME="Expires" CONTENT="nie">
<META NAME="Keywords" 

CONTENT="free,gratis,kostenlos,umsonst,download,www,internet,web,design,page,hilfe,help,site,seite,schne

ppat,bilder,regional,pictures,neu,new,leer,ostfriesland,zubehör,angebot,angebote,suche,suchen,finde,find

en,cgi,java,css,html,dhtml,webdesign,grafikdesign.">
<META NAME="Description" CONTENT="Kostenlose Website von WebDesign Schneppat für den NICHT KOMMERZIELLEN 

Gebrauch.">
<META NAME="page-topic" CONTENT="Alle, Dienstleistung">
<META NAME="page-topic" CONTENT="Service, Produktinfo">
<META NAME="audience" CONTENT=" Alle ">
<META NAME="Language" CONTENT="Deutsch">
<meta name="rating" content="general">
<meta NAME="robots" CONTENT="INDEX">
<meta NAME="robots" CONTENT="FOLLOW">
<script language="JavaScript">
<!--
function MM_reloadPage(init) {  //reloads the window if Nav4 resized
  if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
    document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
  else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);
// -->
</script>
</head>

<body bgcolor="#7e7e7e" background="Frame/Oben-Lang.jpg" text="#000000" link="#000000" vlink="#999999" 

alink="#FF0000" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<div align="center"><img src="Frame/Banner.jpg" width="476" height="90"></div>
</body>
</html>