Page 1 of 1

Restricting users to specific database and tables only

Posted: Sun Dec 17, 2006 2:22 am
by dibyendrah
Dear all,
I have created a database "np_dictionary_db" and created the user "np_dictionary".

I have granted this "np_dictionary" user to use "np_dictionary_db" database only with following SQL query :

Code: Select all

GRANT SELECT ,
INSERT ,

UPDATE ,
DELETE ,
CREATE ,
DROP ,
FILE ,
INDEX ,
ALTER ,
CREATE TEMPORARY TABLES ,
CREATE VIEW ,
SHOW VIEW ,
CREATE ROUTINE,
ALTER ROUTINE,
EXECUTE ON * . * TO 'np_dictionary'@ 'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 

Code: Select all

GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES , CREATE VIEW , SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `np\_dictionary\_db` . * TO 'np_dictionary'@'localhost'
But, logging with this user can still access other database. What do I need to do to make this user only use this database only ?

Please help.

Posted: Sun Dec 17, 2006 8:01 am
by printf
I don't see anything wrong with that...

By default MySQL will create the DB specific permissions but also create a user with NO permissions, then when a login is attempted it will look in the user table, then the db table, as the db over rules the user table. But the problem might be that you have other databases that only use the user table. Like when installing a forum or other applications, most never set up MySQL permissions, so MySQL uses the default user or any user listed in the user table that is not in the db table. So in your case, they might be able to login, but I don't see how they can access another db because they will only have permissions for the database you assigned them.

So in other words, they can login to other databases, if you don't have each database assigned to a specific user, but they can't do anything (select, update, ...) because they are restricted to np_dictionary_db db permissions because as a user, they have no permissions. That's why it is important that each database be assigned a specific user, so only that user can login to that database!


printf

Posted: Tue Dec 19, 2006 4:08 am
by dibyendrah
Yes, you are right and should be like that. But, what am I doing wrong here ? From the user created without grant option or any other administrative privilges can also access other database.