MYSQL - Grant ALL Privileges On Database To New User via Command Lines

1) MySQL is The world's most popular open source database. It is bundled as part of the many LAMP/WAMP/MAMP packages including XAMPP.

2) XAMPP Package provides a PHP Script codes called PHPMyAdmin to help MySQL users to manage  databases via Graphical User Interface (GUI).

3) Despite the friendly PHPMyAdmin's GUI, some MySQL users find it more efficient to work with MySQL via command line. 

4) This tutorial provides steps to Grant ALL Privileges On Database To New User via Command Lines for  both Windows and Linux platform.

Most of the commands can be found at http://basic-steps.blogspot.com/2013/02/short-list-of-mysql-commands.html. Further reading on MySQL commands can be found at http://www.yolinux.com/TUTORIALS/LinuxTutorialMySQL.html .

5) Connect to the database:

> mysql -h localhost -u root -ppassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 130
Server version: 5.5.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, 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.

(blue=your command, purple=server response)
(-h = host parameter key)
(localhost = server name value)
(-u = user parameter key)
(root = username value)
(-p = password parameter key)
(password = password value)

6) Show list of databases on the server:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdcol              |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+
7 rows in set (0.00 sec)

7) Create a new database, myfirstdb:

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

8) Show list of databases:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdcol              |
| myfirstdb          |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+
8 rows in set (0.00 sec)

9) Create a new user, myfirstuser, identified by, p@ssword

mysql> CREATE USER 'myfirstuser'@'localhost' IDENTIFIED BY 'p@ssw0rd';
Query OK, 0 rows affected (0.00 sec)

10) Grant ALL privilege for myfirstdb to myfirstuser.

mysql> GRANT ALL PRIVILEGES ON myfirstdb.* TO 'myfirstuser'@'localhost' IDENTIFI
ED BY 'p@ssw0rd';
Query OK, 0 rows affected (0.00 sec)

11) Exit (root user) from mysql

mysql> exit;
Query OK, 0 rows affected (0.00 sec)

12) Login as myfirstuser@localhost. Enter password when prompted.

>mysql -h localhost -u myfirstuser -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 324
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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.

13) Show list of databases accessible by this user.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myfirstdb          |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql>

14) Test connection via PHP Script.
Create a PHP test script (e.g. testdbconnect.php) and insert the following codes to it. 
Save it to the public folder (REMEMBER: When you are done, delete this file)

<?php
if( isset($_POST['submit']) )
{
    echo $_POST['submit'];


// The variables have not been adequately sanitized to protect against SQL Injection attacks: http://us3.php.net/mysql_real_escape_string

 $hostname = trim($_POST['hostname']);
 $username = trim($_POST['username']);
 $password = trim($_POST['password']);
 $database = trim($_POST['database']);

 $link = mysql_connect("$hostname", "$username", "$password");
  if (!$link) {
   echo "<p>Could not connect to the server '" . $hostname . "'</p>\n";
         echo mysql_error();
  }else{
   echo "<p>Successfully connected to the server '" . $hostname . "'</p>\n";
//   printf("MySQL client info: %s\n", mysql_get_client_info());
//   printf("MySQL host info: %s\n", mysql_get_host_info());
//   printf("MySQL server version: %s\n", mysql_get_server_info());
//   printf("MySQL protocol version: %s\n", mysql_get_proto_info());
  }
 if ($link && !$database) {
  echo "<p>No database name was given. Available databases:</p>\n";
  $db_list = mysql_query("SHOW DATABASES");
  echo "<pre>\n";
  while ($row = mysql_fetch_array($db_list)) {
       echo $row['Database'] . "\n";
  }
  echo "</pre>\n";
 }
 if ($database) {
    $dbcheck = mysql_select_db("$database");
  if (!$dbcheck) {
         echo mysql_error();
  }else{
   echo "<p>Successfully connected to the database '" . $database . "'</p>\n";
   // Check tables
   
   $result = mysql_query("SHOW TABLES FROM ".$database);
   
   if (mysql_num_rows($result)!=FALSE){
   
    echo "<p>Available tables:</p>\n";
    echo "<pre>\n";
    while ($row = mysql_fetch_row($result)) {
     echo "{$row[0]}\n";
    }
    echo "</pre>\n";
    
   } else {
    echo "<p>The database '" . $database . "' contains no tables.</p>\n";
    //echo mysql_error();
   
   }
  }
 }
echo "<a href=".$_SERVER['PHP_SELF'].">Back</a>";
}

 
 
 
 

else { ?>

<h1>MySQL connection test</h1>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>?action=test" id="connection" method="post">
   Hostname:<input name="hostname" type="text" /><br/>
   Username:<input name="username" type="text" /><br/>   
   Password:<input name="password" type="text" /><br/>   
   Database:<input name="database" type="text" /><br/>   
   <input type='hidden' name='submit' />
   <input name="submit" type="submit" value="Submit">
</form>


<?php } ?>

15) Browse the URL testdbconnect.php



16) Upon successful connection, you will get success response.