Sample Database Creation & Permissions in MySql

Most books talk about creating a database, but touch very lightly on setting appropriate permissions for the table. First, create the database that you wish to use:

# mysqladmin create my_new_database -u root -p

Now that you have the database, you need to set up some basic security permissions that govern how it can be accessed. Enter the mysql program and change to the mysql security database:

# mysql -u root -p
mysql> USE mysql

Now, we can get down to business. If you want to grant more than just the ability to select items, make the changes in the 'db' database. Otherwise you'll be granting access to other databases that you don't want the account to have access to.

mysql> INSERT INTO user (Host, User, Password) VALUES('localhost','new_user',password('new_password'));
mysql> INSERT INTO host (Host, Db) VALUES('localhost','my_new_database');
mysql> INSERT INTO db (Host, Db, User, Select_priv) VALUES('localhost', 'my_new_database', 'new_user', 'Y');
mysql> exit
# mysqladmin reload -u root -p