SantaMufasa
Technical User
"USE <db>" is successful by any user for any database on our MySQL installation. How do I prevent access to one or more databases by a given user?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I prohibit user from unwanted "USE <db>"? 1
- Thread starter SantaMufasa
- Start date
- Status
- Thread starter
- #3
SantaMufasa
Technical User
vbkris, Thanks for your suggestion, but I was unsuccessful. Here is what I did, along with my findings: 1) As root, I changed the value of mysql.user.file_priv = 'N' for the user I wanted to restrict. After doing a "reload", I logged on as the user I tried to restrict, but was still able to "USE <dbname>" for every database in MySQL. 2) I could not locate a program called "mysqlmanager.exe". Did you mean "mysqladmin"? (Please pardon my dimness on all this. I've been an Oracle DBA and DBA instructor for 13 years, but I am a MySQL newbie, and there are some pieces that just don't seem to translate.
vbkris or anyone else, do you have anymore ideas on this? (My user wants to burn CDs on this 18Jul p.m. but we can't until we resolve this security issue.) Thanks !
vbkris or anyone else, do you have anymore ideas on this? (My user wants to burn CDs on this 18Jul p.m. but we can't until we resolve this security issue.) Thanks !
Read about tha GRANT command
anyway it will be somethign like
grant privilege
on dbname.table
to user@'host'
identified by "password"
If you previously granted all users then use the 'revoke' command
Bye
Qatqat
Life is what happens when you are making other plans.
anyway it will be somethign like
grant privilege
on dbname.table
to user@'host'
identified by "password"
If you previously granted all users then use the 'revoke' command
Bye
Qatqat
Life is what happens when you are making other plans.
- Thread starter
- #5
SantaMufasa
Technical User
QatQat, Thanks for your suggestion, as well. Before I created this thread, I pored over the information at looking for some insight.
I also tried your suggestion of artificially granting to the user in question all privileges to the database in question, then revoking all privileges from that user (hoping to ultimately restrict "USE" access from the user). But following all this, the user in question STILL has "USE db" access.
So, I'm still hoping for a solution suggestion that prevents a user from doing a "USE db-name". Any additional thoughts?
I also tried your suggestion of artificially granting to the user in question all privileges to the database in question, then revoking all privileges from that user (hoping to ultimately restrict "USE" access from the user). But following all this, the user in question STILL has "USE db" access.
So, I'm still hoping for a solution suggestion that prevents a user from doing a "USE db-name". Any additional thoughts?
-
1
- #6
Hi SantaMufasa,
I think you misunderstood QatQat:
You shouldn't
1.grant all privileges
2.revoke those privileges
but instead:
1.revoke all privileges you ever granted the user
2.grant the user just to use the database you specified with the following command:
grant privilege
on dbname.table <--specify here the db name
to user@'host'
identified by "password"
Hope that helps
Smash your head on keyboard to continue...
I think you misunderstood QatQat:
You shouldn't
1.grant all privileges
2.revoke those privileges
but instead:
1.revoke all privileges you ever granted the user
2.grant the user just to use the database you specified with the following command:
grant privilege
on dbname.table <--specify here the db name
to user@'host'
identified by "password"
Hope that helps
Smash your head on keyboard to continue...
- Thread starter
- #7
SantaMufasa
Technical User
Thanks, liuwt, for jumping into the fray. To clarify, we've never explicitly GRANTed to user "yada" any access to any db, yet when we log in as user "yada", we can "USE db-name" for any db in this installation. Even after saying:
"REVOKE all on <dbname>.* from yada;"
I receive this error:
"Error 1141: There is no such grant defined for user yada..."
Therefore, my followup question to you is, "How can I, in one fell swoop, do as you say, '...revoke all privileges [we] ever granted the user.' if we have never done any explicit GRANTs?" (At this point, we're not even worried about your step 2; we just want to prevent user yada from being able to "USE db-name". So far, I haven't seen anyone suggest how I can prevent yada from "USE db-name". Thanks to all.
Dave Hunt
"REVOKE all on <dbname>.* from yada;"
I receive this error:
"Error 1141: There is no such grant defined for user yada..."
Therefore, my followup question to you is, "How can I, in one fell swoop, do as you say, '...revoke all privileges [we] ever granted the user.' if we have never done any explicit GRANTs?" (At this point, we're not even worried about your step 2; we just want to prevent user yada from being able to "USE db-name". So far, I haven't seen anyone suggest how I can prevent yada from "USE db-name". Thanks to all.
Dave Hunt
Hi,
it's intended to delete the user "yada" completely out of your mysql-system before, with the revoke-command. Therefore don't use "REVOKE all on <dbname>.* from yada;", but instead
REVOKE all on *.* from yada@%;
REVOKE all on *.* from yada@localhost;
(you must do both commands, as % stands for all hosts except localhost)
Then grant "yada" the right to use the specified db. Afterwards he won't be able to use other dbs.
Greetings
Smash your head on keyboard to continue...
it's intended to delete the user "yada" completely out of your mysql-system before, with the revoke-command. Therefore don't use "REVOKE all on <dbname>.* from yada;", but instead
REVOKE all on *.* from yada@%;
REVOKE all on *.* from yada@localhost;
(you must do both commands, as % stands for all hosts except localhost)
Then grant "yada" the right to use the specified db. Afterwards he won't be able to use other dbs.
Greetings
Smash your head on keyboard to continue...
Also, to your question:
GRANT does nothing else than setting up a user account and give it the necessary rights. You might have never used the GRANT command, but setting up the user account manually is actually the same. Since you have this user account, we just treat it as if you had used the grant command, which makes no difference.
Greetings
Smash your head on keyboard to continue...
GRANT does nothing else than setting up a user account and give it the necessary rights. You might have never used the GRANT command, but setting up the user account manually is actually the same. Since you have this user account, we just treat it as if you had used the grant command, which makes no difference.
Greetings
Smash your head on keyboard to continue...
- Thread starter
- #10
SantaMufasa
Technical User
Thanks, Liuwt ! I appreciate the good explanation. Since I couldn't figure this out today before our developers' deadline, they had to devise a temporary workaround for today's package building. The developers want me to wait until Monday to test your solution, so I'll try it on Monday, and update this thread with results.
Cheers,
Dave Hunt
Cheers,
Dave Hunt
I don't know mySQL as well as those who've already offered suggestions... but on the chance that you go in Monday morning and find that doesn't work, I would install myadmin. You can then go to the privelleges page and get a nice easy to read display of who has privelleges to way. You can also easily check the privelleges of each database.
The reason I suggest this is because chances are you don't have a problem with a user who has access to yada, but rather you have the default setup which gives Any access to your database.... and since I don't know the commands to modify it, I can point you there.
-Rob
The reason I suggest this is because chances are you don't have a problem with a user who has access to yada, but rather you have the default setup which gives Any access to your database.... and since I don't know the commands to modify it, I can point you there.
-Rob
- Thread starter
- #12
SantaMufasa
Technical User
Friends,
I executed, without error, the commands that "liuwt" suggested from 18July:
REVOKE all on *.* from yada@%;
REVOKE all on *.* from yada@localhost;
Unfortunately, "yada" still can "USE <dbname>" on any dbname in the installation. Any other ideas on how to prevent user yada from using the "USE <dbname>" command? (Please pardon my "MySQL Naïveté", but it seems to me that solving my need should not be such an ordeal. What complexity am I missing?)
Skiflyer, when you suggested my using "myadmin", did you mean "mysqladmin"? If so, what "mysqladmin" command(s) do you suggest I use? If not, how do I locate and install "myadmin", and upon what command(s) should I focus.
Thanks,
Dave
I executed, without error, the commands that "liuwt" suggested from 18July:
REVOKE all on *.* from yada@%;
REVOKE all on *.* from yada@localhost;
Unfortunately, "yada" still can "USE <dbname>" on any dbname in the installation. Any other ideas on how to prevent user yada from using the "USE <dbname>" command? (Please pardon my "MySQL Naïveté", but it seems to me that solving my need should not be such an ordeal. What complexity am I missing?)
Skiflyer, when you suggested my using "myadmin", did you mean "mysqladmin"? If so, what "mysqladmin" command(s) do you suggest I use? If not, how do I locate and install "myadmin", and upon what command(s) should I focus.
Thanks,
Dave
Hi,
I found the problem: It seems that revoke doesn't exactly work as I thought on the newer versions of MySql(It doesn't delete the account). Try instead deleting the user yada manually with(as root):
use mysql;
delete from user where user="yada";
delete from db where user="yada";
Then:
grant <privileges> on <db>.* to yada@localhost identified by "<password";
That should do it once for all (I hope ;-P )
Greetings
--
Smash your head on keyboard to continue...
I found the problem: It seems that revoke doesn't exactly work as I thought on the newer versions of MySql(It doesn't delete the account). Try instead deleting the user yada manually with(as root):
use mysql;
delete from user where user="yada";
delete from db where user="yada";
Then:
grant <privileges> on <db>.* to yada@localhost identified by "<password";
That should do it once for all (I hope ;-P )
Greetings
--
Smash your head on keyboard to continue...
<mutter> I'm sorry about the typo, I meant
phpmyadmin
from
I was tired that day though, and I forget to mention it requires having a functioning PHP setup.
-Rob
phpmyadmin
from
I was tired that day though, and I forget to mention it requires having a functioning PHP setup.
-Rob
- Thread starter
- #15
SantaMufasa
Technical User
Okay MySQL Gurus, we're back in the saddle with this issue, and precious little progress. The developers gave me permission to try to resolve this issue once again.
I did as Liuwt suggested, above: 1) deleted the user "yada" (confirming its removal with an unsuccessful login attempt to "yada"
, then 2) did a 'grant <privileges> on <db>.* to yada@localhost identified by "<password";' to re-establish the user and grant yada privileges to JUST ONE database.
The net result of this was that "yada" could no longer "use mysql;" (as wanted and expected), but "yada" could successfully still "use <db>" on all other databases (not what I wanted).
So, I still need your brains and enlightenment. How do I prevent user "yada" from being able to "USE" all databases except for the one I specify in the the 'grant...' command?
Thanks,
Dave
I did as Liuwt suggested, above: 1) deleted the user "yada" (confirming its removal with an unsuccessful login attempt to "yada"
, then 2) did a 'grant <privileges> on <db>.* to yada@localhost identified by "<password";' to re-establish the user and grant yada privileges to JUST ONE database.The net result of this was that "yada" could no longer "use mysql;" (as wanted and expected), but "yada" could successfully still "use <db>" on all other databases (not what I wanted).
So, I still need your brains and enlightenment. How do I prevent user "yada" from being able to "USE" all databases except for the one I specify in the the 'grant...' command?
Thanks,
Dave
bahwalpuri
IS-IT--Management
Hi Dave,
I am confronted with the same problem. I am adminitrating mysql and I dont want to give certain users to see the other databases ( use <db> ) but upto now I am not successful to do that. I am working on it.
cheers
I am confronted with the same problem. I am adminitrating mysql and I dont want to give certain users to see the other databases ( use <db> ) but upto now I am not successful to do that. I am working on it.
cheers
- Thread starter
- #17
SantaMufasa
Technical User
Thanks, Bahwalpuri, for confirming the need for code to deal with this issue. Your response and the apparent lack of straightforward command(s) to resolve this issue confirm that this is not as stupid a question that I feared it was.
I can't believe that this is surfacing as such a tough issue for MySQL. I'm hoping that seasoned gurus that have recently logged in (such as "Sleipnir214", "swampBoogie", "r937", "abombss", "likelad", and others) will jump into this fray and redeem MySQL on this topic.
In the meantime, I'll continue to do detective work on my own; but I still need some guidance: What is the name of the MySQL data dictionary table that confirms which databases a user can currently "USE"?
I can't believe that this is surfacing as such a tough issue for MySQL. I'm hoping that seasoned gurus that have recently logged in (such as "Sleipnir214", "swampBoogie", "r937", "abombss", "likelad", and others) will jump into this fray and redeem MySQL on this topic.
In the meantime, I'll continue to do detective work on my own; but I still need some guidance: What is the name of the MySQL data dictionary table that confirms which databases a user can currently "USE"?
- Thread starter
- #18
SantaMufasa
Technical User
My bad, with apologies to "Liuwt". (Bahwalpuri, take note...I've successfully tested Liuwt's commands and they do what we wanted.) I went back and re-traced my steps with Liuwt's commands from his earlier post:
=================================================
use mysql;
delete from user where user="yada";
delete from db where user="yada";
Then:
grant <privileges> on <db>.* to yada@localhost identified by "<password";
=================================================
I found that I had not successfully executed the second delete, 'delete from db where user="yada";', which is the key to removing "USE" privileges for a user. Once I did that (along with the other commands Liuwt recommends), I achieved my goal. Thanks, Liuwt ! As you will see, you get my vote for "...helpful/export post".
=================================================
use mysql;
delete from user where user="yada";
delete from db where user="yada";
Then:
grant <privileges> on <db>.* to yada@localhost identified by "<password";
=================================================
I found that I had not successfully executed the second delete, 'delete from db where user="yada";', which is the key to removing "USE" privileges for a user. Once I did that (along with the other commands Liuwt recommends), I achieved my goal. Thanks, Liuwt ! As you will see, you get my vote for "...helpful/export post".
- Thread starter
- #19
SantaMufasa
Technical User
Okay, now I'm really puzzled. (It appears that my kudos were premature.) After confirming that user "yada" could not longer "USE <dbname>" (as I wanted), now, a couple of hours later, when I connect as user "yada", I can "USE <any_db_name>" successfully. The contents of my DB privileges table are:
mysql> select host,db,user from db;
+-----------+--------------+-----------------+
| host | db | user |
+-----------+--------------+-----------------+
| % | test% | |
| % | tsunami_appl | yada |
| % | tsunami_appl | tsunamiappluser |
| % | tsunami_appl | tsunamiappuser |
| localhost | tsunami_appl | yada |
+-----------+--------------+-----------------+
5 rows in set (0.00 sec)
mysql> show databases
-> ;
+--------------+
| Database |
+--------------+
| mysql |
| test |
| tsunami_appl |
| tsunami_dict |
+--------------+
4 rows in set (0.03 sec)
When I connect as user "yada", I can successfully "USE" each of the above databases.
PLEASE HELP !!!! My developers are frantic now. Thanks, Dave.
mysql> select host,db,user from db;
+-----------+--------------+-----------------+
| host | db | user |
+-----------+--------------+-----------------+
| % | test% | |
| % | tsunami_appl | yada |
| % | tsunami_appl | tsunamiappluser |
| % | tsunami_appl | tsunamiappuser |
| localhost | tsunami_appl | yada |
+-----------+--------------+-----------------+
5 rows in set (0.00 sec)
mysql> show databases
-> ;
+--------------+
| Database |
+--------------+
| mysql |
| test |
| tsunami_appl |
| tsunami_dict |
+--------------+
4 rows in set (0.03 sec)
When I connect as user "yada", I can successfully "USE" each of the above databases.
PLEASE HELP !!!! My developers are frantic now. Thanks, Dave.
bahwalpuri
IS-IT--Management
Hi Dave,
name of the MySQL data dictionary table that confirms which databases a user can currently "USE"?
The answer according to my knowledge is db in database mysql.
cheer
amir
name of the MySQL data dictionary table that confirms which databases a user can currently "USE"?
The answer according to my knowledge is db in database mysql.
cheer
amir
- Status