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://rameshkumarroy.com/working-with-mysql-on-xampp/ 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

 

Hope it was helpful…..

Working with MySql on XAMPP

Getting my hands dirty into some programming stuff. I have some networking background with zero programming knowledge. Was reading some article on hacking and Sql injection and was interesting in how these stuffs work.

Thought of trying my hands in programming… Installed PHP and XAMPP in my laptop and trying to get started with Web development programming.

Was working on getting some html pages created with forms getting user inputs using GET/POST and get it saved somewhere. The book I was following suggested to go through the basic SQL command that will help in understanding how things works.

So here I am trying to work with the MySql installed as a service by XAMPP.

The first question comes to my mind how to connect to the MySql server ?

When you install the XAMPP installer it creates a XAMPP control panel as a shortcut on the desktop when you start this, it looks like following with control of starting and stopping the services.

You see that I have the Apache and the MySql services are running. When you click on the Admin tab it takes you to the WebUI of the MySql server, or you can also access it using the following url on the web-browser :

http://localhost/phpmyadmin/

Following is the page you will get.. next it is to explore the various options. I am using an older version of XAMPP so the newer version might have a different look of the page.

You might be presented with a login page before this , the default user created is : root without any password. Use the username : root and hit login.

Also lets try to login to the server using the cmd prompt.

The XAMPP is by default installed in the c:\xampp directory. Goto the following directory : c:\xampp\mysql\bin>

Now login using the command: mysql –user==root.  // This will log you in as a root user.

 

In case you have created any username and password using the GUI and want to login using those credential use the command:

mysql –user=<username> –password=<password> <DB you want to connect to>

c:\xampp\mysql\bin>mysql –user=root

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 297
Server version: 5.5.8 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

 

In my case I had created a username Ramesh password Ramesh and DB ramesh from the WebUI, now I would login using the command line:

c:\xampp\mysql\bin>mysql –user=Ramesh –password=Ramesh ramesh

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 193
Server version: 5.5.8 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

I can view my DB now :

mysql> select * from shopping
-> ;

+——–+————-+———————-+———-+————–+——-
—–+——–+——————+—–+———————–+

| Name   | Customer ID | Address              | Address1 | Work Phone   | Home P

hone | Status | Job              | Age | Note                  |

+——–+————-+———————-+———-+————–+——-
—–+——–+——————+—–+———————–+

 

Now its time to get started with some Sql queries.

You can download XAMPP from the following link:

 

http://sourceforge.net/projects/xampp/

 

 

Hope this was helpful….