LinuxQuestions.org - mySQL deny user access to database

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - mySQL deny user access to database (https://www.linuxquestions.org/questions/programming-9/mysql-deny-user-access-to-database-589911/)

mySQL deny user access to database

Hey all,
New to mySQL, but trying to create users who are allowed only certain access to certain databases.
Tried setting grants from terminal, as well setting permissions from phpmyAdmin.
But for some reason all users have privileges to all databases.

Can anyone help to provide me with just some basic commands to set up and database, and only allow access to a specific user?

THanks!!!

Regarding mysql access

Dear blizunt7

Would u please mention what command u used to grant a user specific access to a specific database ? I do believe to remedy the problem

Subrun Jamil

If you only want a user to have select permission; i.e., the user can see but not change any information, you can grant that with

Code:

grant select

on dbname.*

to username@localhost identified by 'userpassword';

flush privileges;

Replace dbname with the name of the data base, username with the user's name, etc.

You must do this as the administrative user; i.e., mysql, using the password you set for user mysql -- save the above in a file, grant.sql or something and execute

Code:

mysql --user=mysql --password=mysqlpassword < grant.sql

If you want to grant all permissions to a user, do this:

Code:

grant all privileges

on dbname.* to user@localhost

identified by 'userpassword';

flush privileges;

Again, save that in a file and execute as above.

See http://dev.mysql.com/doc/refman/5.0/en/grant.html

Hey,
As root, I created the user, and created a database, and then granted that user all permissions on that database. But from phpmyadmin that user cannot create new databases.

Code:

 mysql> GRANT ALL ON database_name.* TO user@'%';    // host = % (null = any host)

 mysql> GRANT ALL ON database_name.* TO user@'localhost';

So I just need to figure out how to give them permissions to create a new data base, but NOT view all databases.

THanks much:)