vaheeD khoshnouD

linux, mikrotik, macosx

How To Create A MySQL Database And Set Privileges To User

Written by vaheeD on January 18, 2013
5.00 avg. rating (93% score) - 1 vote

MySQL is a widely spread SQL database management system mainly used on LAMP (Linux/Apache/MySQL/PHP) projects.

In order to be able to use a database, one needs to create: a new database, give access permission to the database server to a database user and finally grant all right to that specific database to this user.

This tutorial will explain how to create a new database and give a user the appropriate grant permissions.

 

For the purpose of this tutorial, I will explain how to create a database and user for the music player Amarok. In order to index its music collection, Amarok quand use a mysql backend.
The requirement for this set up is to have access to a database. We are going to create a database called amarok which will be accessible from localhost to useramarok idetified by the password amarok….

Obviously, we need to to have a mysql server installed as well as amarok:

$ sudo apt-get install mysql-server amarok

On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as root on your mysql server using:

$ mysql -u root

If a password is required, use the extra switch -p:

$ mysql -u root -p
Enter password:

Now that you are logged in, we create a database:

mysql> create database amarokdb;
Query OK, 1 row affected (0.00 sec)

We allow user amarokuser to connect to the server from localhost using the password amarokpasswd:

mysql> grant usage on *.* to [email protected] identified by ‘amarokpasswd’;
Query OK, 0 rows affected (0.00 sec)

And finally we grant all privileges on the amarok database to this user:

mysql> grant all privileges on amarokdb.* to [email protected] ;
Query OK, 0 rows affected (0.00 sec)

And that’s it. You can now check that you can connect to the MySQL server using this command:

$ mysql -u amarokuser -p’amarokpasswd’ amarokdb
Your MySQL connection id is 12
Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

How To Grant Different User Permissions

Here is a short list of other common possible permissions that users can enjoy.

    • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
    • CREATE- allows them to create new tables or databases
    • DROP- allows them to them to delete tables or databases
    • DELETE- allows them to delete rows from tables
    • INSERT- allows them to insert rows into tables
    • SELECT- allows them to use the Select command to read through databases
    • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users’ privileges
  •  

    5.00 avg. rating (93% score) - 1 vote

    Posted Under: Linux

    About vaheeD

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    Protected by WP Anti Spam