Exploring Mysql on XAMPP

It is a good practice to create admin user instead of using the root account for managing the DB. Lets see how can we create users using Sql and assign them privileges.

Creating a user on MySql :
====================

As discussed in my previous post XAMPP provide a useful GUI interface for MySql. You can log into the GUI and create users and assign them specific privileges. Please refer to the post : http://lets-start-to-learn.blogspot.in/2013/12/working-with-mysql-on-xampp.html if you want to know how you can get into the WebUI.

Once you are into the sever goto the Privileges tab.

Click on the option "Add a new User" it will take you to the next screen where you can create a user and assign specific privileges to the user.

Click "go" to create the user with the assigned privileges.

Lets now see how we can create a new user using the CLI, we have already discussed in the previous post how to get the CLI and the GUI of the SQL server .

We can either use the command : Create to create a user or Grant command directly to create a user and assign some privileges to the user.

mysql> create user Sandy@localhost identified by 'Sandy123';

Query OK, 0 rows affected (0.00 sec)

Here Sandy is the username and Sandy123 is the password.

We used @localhost  to add the user to give it access to the local server, without this the user would be created however the host field would be blank. The result of this command on the WebUI will appear as:

You can user the command: Drop user to delete the user like:

mysql> drop user Sandy@localhost ;
Query OK, 0 rows affected (0.00 sec)

Now lets see how we can assign privileges to this user using the Grant command:

mysql> grant all
    -> on *.*
    -> to Sandy@localhost identified by 'Sandy123'
    -> with grant option;
Query OK, 0 rows affected (0.00 sec)

This will create Sandy username automatically and assign all privileges (Grant all) also with grant option i.e this user will able able to grant privileges to other users.

Similarly you can assign specific privileges instead of all.

mysql> grant select, insert, update, delete, index, alter, create, drop
-> on books.*
-> to sally;

We are assigning specific privileges to sally to the books DB.

You can refer to the following link for account management:

http://dev.mysql.com/doc/refman/5.0/en/account-management-sql.html