Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have mysql 5.0.82sp1 running and i've noticed that all users can invoke select command on all databases' tables. They don't have privileges to do so, so there's nothing i can revoke. Is there some mysql setting granting this read-only access by default?

this seems to always give result

select * from some_database_name.some_table_name

and it shows whatever is in it.

it works even if i revoke all privileges by issuing command

revoke all privileges, grant option from 'username'@'localhost';

and i check it by issuing command

show grants;

it shows

GRANT USAGE ON *.* TO 'username'@'localhost' IDENTIFIED BY PASSWORD '...'

which means it doesn't have privileges to select

share|improve this question
    
Are you using a test database ??? –  RolandoMySQLDBA Sep 6 '11 at 21:09
    
as i'm writting user can read from ALL databases –  user398140 Sep 7 '11 at 9:24
add comment

3 Answers

Because you change global privileges, so you need to do the following to make it take effects:

  1. issue a FLUSH PRIVILEGES; statement to reload the grant tables
  2. disconnect the current client and reconnect

Read more...

share|improve this answer
add comment

There is something disturbing about this behavior.

I can only think of two circumstances when SELECTs can run unrestained like this:

SITUATION #1: skip-grant-tables in /etc/my.cnf

Please check that /etc/my.cnf does not have this:

[mysqld]
skip-grant-tables

If it does, remove the skip-grant-tables line and restart mysql right away.

SITUATION #2: skip-grant-tables from command line

It is possible to start mysql with the skip-grant-tables option without touching my.cnf. In Linux OS, please run this:

$ service mysql start --skip-grant-tables

From the Linux command line, to find out if skip-grant-tables is activated, pleasd run this:

$  mysqld --help --verbose | grep -C 3 "skip-grants"

You will see lines that look something like this:

secure-file-priv                                  (No default value)
server-id                                         106451130
show-slave-auth-info                              FALSE
skip-grant-tables                                 FALSE
skip-name-resolve                                 TRUE
skip-networking                                   FALSE
skip-show-database                                FALSE

If skip-grant-tables is TRUE, just restart mysql as normal. It should then disappear.

Give it a Try !!!

CAVEAT : If this is a MySQL/Windows issue, please inspect my.ini

Also, there is no global variable to change this feature. This option applies to the startup of mysql only.

UPDATE 2011-09-09 12:56 EDT

Try using this SQL Command once you login:

SELECT USER(),CURRENT_USER();

These functions report the way you authenticate

USER() reports how you attempted to authenticate in MySQL

CURRENT_USER() reports how you were allowed to authenticate in MySQL

Please update your question with these findings !!!

share|improve this answer
    
unfortunately skip-grant-tables says false. And as i checked on other server, i found same issue. So i was wondering if you could do me a favour, and loggin to any mysql server you have access to and log in as user and try to issue those commands with databasename user doesn't have privileges to use and tell me if this work 'use somedatabasename; show tables;' –  user398140 Sep 9 '11 at 9:08
add comment

As from your grant command (USAGE ON *.*) it seems like you have granted access to all the databases. Update the privilege by restricting access only to the particular database for the corresponding user. ie, USAGE ON databasename.* instead of *.*

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.