Hi,
It seems that all of my users can issue the "SHOW DATABASES"
command and receive a list of all databases even though their
Show_db_priv setting is N. I am running mysql 4.0.16.
The manual states "As of MySQL 4.0.2, you will see only those
databases for which you have some kind of privilege, if you
don't have the global SHOW DATABASES privilege." so I see
two possibilities:
- users have the global SHOW DATABASES privilege (where might
that be stored?)
- my priviledge tables are still incorrect from the MySQL 3 to
MySQL 4 upgrade?
- the manual is wrong ;-)
Any suggestions would be greatly appreciated!
Here are my priviledge tables:
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+-----------------+
6 rows in set (0.00 sec)
mysql> desc columns_priv;
+-------------+----------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key |
Default | Extra |
+-------------+----------------------------------------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI |
| |
| Db | char(64) binary | | PRI |
| |
| User | char(16) binary | | PRI |
| |
| Table_name | char(64) binary | | PRI |
| |
| Column_name | char(64) binary | | PRI |
| |
| Timestamp | timestamp(14) | YES | |
NULL | |
| Column_priv | set('Select','Insert','Update','References') | | |
| |
+-------------+----------------------------------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> desc db;
+-----------------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| User | char(16) binary | | PRI | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
| Create_tmp_table_priv | enum('N','Y') | | | N | |
| Lock_tables_priv | enum('N','Y') | | | N | |
+-----------------------+-----------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
mysql> desc func;
+-------+------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+----------+-------+
| name | char(64) binary | | PRI | | |
| ret | tinyint(1) | | | 0 | |
| dl | char(128) | | | | |
| type | enum('function','aggregate') | | | function | |
+-------+------------------------------+------+-----+----------+-------+
4 rows in set (0.00 sec)
mysql> desc host;
+-----------------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
| Create_tmp_table_priv | enum('N','Y') | | | N | |
| Lock_tables_priv | enum('N','Y') | | | N | |
+-----------------------+-----------------+------+-----+---------+-------+
14 rows in set (0.00 sec)
mysql> desc tables_priv;
+-------------+-----------------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | |
|
| Db | char(64) binary | | PRI | |
|
| User | char(16) binary | | PRI | |
|
| Table_name | char(60) binary | | PRI | |
|
| Grantor | char(77) | | MUL | | |
| Timestamp | timestamp(14) | YES | | NULL |
|
| Table_priv |
set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter')
| | | | |
| Column_priv | set('Select','Insert','Update','References')
| | | | |
+-------------+-----------------------------------------------------------------------------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> desc user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key |
Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | varchar(60) binary | | PRI |
| |
| User | varchar(16) binary | | PRI |
| |
| password | varchar(16) | | |
| |
| Select_priv | enum('N','Y') | | | N
| |
| Insert_priv | enum('N','Y') | | | N
| |
| Update_priv | enum('N','Y') | | | N
| |
| Delete_priv | enum('N','Y') | | | N
| |
| Create_priv | enum('N','Y') | | | N
| |
| Drop_priv | enum('N','Y') | | | N
| |
| Reload_priv | enum('N','Y') | | | N
| |
| Shutdown_priv | enum('N','Y') | | | N
| |
| Process_priv | enum('N','Y') | | | N
| |
| File_priv | enum('N','Y') | | | N
| |
| Grant_priv | enum('N','Y') | | | N
| |
| References_priv | enum('N','Y') | | | N
| |
| Index_priv | enum('N','Y') | | | N
| |
| Alter_priv | enum('N','Y') | | | N
| |
| Show_db_priv | enum('N','Y') | | | N
| |
| Super_priv | enum('N','Y') | | | N
| |
| Create_tmp_table_priv | enum('N','Y') | | | N
| |
| Lock_tables_priv | enum('N','Y') | | | N
| |
| Execute_priv | enum('N','Y') | | | N
| |
| Repl_slave_priv | enum('N','Y') | | | N
| |
| Repl_client_priv | enum('N','Y') | | | N
| |
| ssl_type | enum('','ANY','X509','SPECIFIED') | | |
| |
| ssl_cipher | blob | | |
| |
| x509_issuer | blob | | |
| |
| x509_subject | blob | | |
| |
| max_questions | int(11) | | | 0
| |
| max_updates | int(11) unsigned | | | 0
| |
| max_connections | int(11) unsigned | | | 0
| |
+-----------------------+-----------------------------------+------+-----+---------+-------+
31 rows in set (0.00 sec)
Here is a listing of all the tables in the mysql database that contain any
information relevant to one given user, this user can list all databases ...
mysql> select * from columns_priv where user='somuser';
Empty set (0.00 sec)
mysql> select * from db where user='someuser';
+---------+------------------------+--------------+----------------+---------------+------------------+-----------------+-----------------+--------------+----------------+-----------------------+---------------+---------------+---------------------------------+-------------------------+
| Host | Db | User | Select_priv |
Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv
| References_priv | Index_priv | Alter_priv | Create_tmp_table_priv |
Lock_tables_priv |
+---------+------------------------+--------------+----------------+---------------+------------------+-----------------+-----------------+--------------+---------------+------------------------+---------------+---------------+---------------------------------+-------------------------+
| % | adatabase | someuser | Y | Y
| Y | Y | Y | Y
| N | Y | Y
| Y | N | N
|
+---------+------------------------+--------------+----------------+----------------+-----------------+-----------------+-----------------+--------------+---------------+------------------------+---------------+---------------+---------------------------------+-------------------------+
1 rows in set (0.01 sec)
mysql> select * from func ;
Empty set (0.00 sec)
mysql> select * from host;
Empty set (0.00 sec)
mysql> select * from tables_priv where user='someuser';
Empty set (0.00 sec)
mysql> select * from user where user='someuser';
+---------+---------------+------------------+----------------+---------------+------------------+-----------------+-----------------+--------------+-----------------+----------------------+-------------------+------------+---------------+------------------------+---------------+--------------+---------------------+---------------+----------------------------------+------------------------+-------------------+-----------------------+-----------------------+------------+---------------+-----------------+--------------------+----------------------+--------------------+--------------------------+
| Host | User | password | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv |
Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv |
Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv |
Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type
| ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------+---------------+------------------+----------------+---------------+------------------+-----------------+-----------------+--------------+-----------------+----------------------+-------------------+------------+----------------+-----------------------+---------------+---------------+--------------------+---------------+----------------------------------+------------------------+-------------------+-----------------------+-----------------------+------------+---------------+-----------------+--------------------+----------------------+--------------------+--------------------------+
| % | someuser | xxxxxxxxx | N | N
| N | N | N | N
| N | N | N
| N | N | N | N
| N | N | N | Y
| Y | N
| N | N |
| | | | 0
| 0 | 0 |
+---------+---------------+------------------+----------------+---------------+------------------+-----------------+-----------------+--------------+-----------------+----------------------+-------------------+------------+---------------+------------------------+---------------+---------------+--------------------+---------------+----------------------------------+------------------------+-------------------+-----------------------+-----------------------+------------+---------------+-----------------+--------------------+----------------------+--------------------+--------------------------+
1 rows in set (0.01 sec)
Thanks
--
Fraser Campbell <[EMAIL PROTECTED]> http://www.wehave.net/
Georgetown, Ontario, Canada Debian GNU/Linux
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
