restrict mysql user to one database

You can create a user in mysql using the CREATE USER statement or you can insert the record directly in MySQL "user" table. MySQL stores user acces information inside mysql database and "user" table, other user related information e.g. full name, description email etc is stored in "user_info" table.

If you want restrict a mysql user to single database then you can use the following script.

In my test script both the database name and username are same as "sqldba" you can change them to fit your needs.

The above script will create a database name sqldba then create a user called sqldba, revoke all his permissions and then grant him permissions on sqldba database.

I am using sqldba.* on last line which means give acces on "sqldba" database and all objects inside that database.

 

CREATE Database sqldba ;

CREATE USER 'sqldba'@'%' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES,GRANT OPTION from 'sqldba'@'%'; 

GRANT ALL ON sqldba.* TO 'sqldba'@'%';



 4/29/2009


Add Comments
Name: *
Email: *
URL:
Comments: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

 I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more features and make it easy for me to update contents on regular basis.