Jump to: navigation, search

MySQL Create User and Set User Privileges

From w3cyberlearnings

Contents

MySQL Login

To login into your MySQL database is not that hard. You need the user name, password, and hostname. If you access to your MySQL database remotely, you also need to have the username and password, and along with MySQL database server IP address or host-name.

Login locally to MySQL

This is how you login into the MySQL database in the local host. The username is root and the password is caojiang. -uroot is stand for -u and root. The -u is for username.
-pcaojiang is stand for -p and caojiang. The -p is for password.
After you login, you are not going to do anything else but to seek for the database name or create a new database.

Login to MySQL

$mysql -uroot -pcaojiang 

or 

$mysql -u root -p caojiang

or

$mysql --user=root --password=caojiang

Login to MySQL with database name

Here, myDatabase is the database name.

$mysql -uroot -pcaojiang myDatabase

or
$mysql -u root -p caojiang myDatabase

or
$mysql -user=root -password=caojiang myDatabase

Login to MySQL with a host name

The hostname is localhost.

$mysql -uroot -pcaojiang -hlocalhost myDatabase

or

$mysql -u root -p caojiang -h localhost myDatabase

or

$mysql -user=root -password=caojiang -host=localhost myDatabase

Login without a password

You can login to your database without a password. Actually, you hide the password on the .my.cnf file.

.my.cnf file

caojiang is the password for our MySQL. You need to have the username.

[client]
password=caojiang
Login with no password
$mysql -u root 


MySQL Create user for the MysQL server using GRANT

For Local Host

We create a database called test1 and we create a new user for that database.

  • database name: test1
  • user name: john
  • password: @mypassword124
  • host: localhost
  • privilege for the test1 database: Select, Update, Delete, Create, Drop
mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.06 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON test1.* TO 'john'@'localhost' 
    -> IDENTIFIED BY '@mypassword124';
Query OK, 0 rows affected (0.04 sec)


For A Different Host or Domain

Assign users to access the database from a different domain. Here we grant the privilege for bob to access the database server from toyoma-PC and w3cyberlearnings.com. We can substitute the domain name with the IP address.


mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON
    -> test1.* 
    -> TO 'bob'@'toyoma-PC'
    -> IDENTIFIED BY '@password1';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, INSERT, UPDATE, CREATE On
    -> test1.*
    -> TO 'bob'@'w3cyberlearnings.com'
    -> IDENTIFIED BY '@password2';
Query OK, 0 rows affected (0.00 sec)

Grant user for any domains

Here, we setup the paul account to be accessible from any domains. We allow the paul account to have all privileges on the test1 database.

mysql> GRANT ALL PRIVILEGES ON test1.* TO 'paul'@'%'
    -> IDENTIFIED BY '@password1' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Check the current login access privilege by using SHOW GRANTS

SHOW GRANTS uses to show all the privileges for users.


mysql> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*C998F8D539063903077CB12B33D12BDDDAEADA9B' WITH GRANT OPTION | 
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

List all the show grants to show the current user

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER()

List All users

You are required to query the mysql.user table in order to get all the users. You need to have the privilege to do this query, if not you will get an error message.

mysql> SELECT user, host FROM mysql.user;
+------------------+----------------------+
| user             | host                 |
+------------------+----------------------+
| paul             | %                    | 
| root             | 127.0.0.1            | 
|                  | localhost            | 
| debian-sys-maint | localhost            | 
| druapluser       | localhost            | 
| john             | localhost            | 
| root             | localhost            | 
|                  | sophal-desktop       | 
| root             | sophal-desktop       | 
| bob              | toyoma-PC            | 
| bob              | w3cyberlearnings.com | 
| jack             | wlith                | 
+------------------+----------------------+
12 rows in set (0.01 sec)

Show grants for each individual account

Here we check the user named bob and for the toyoma-PC domain.

mysql> SHOW GRANTS for bob@'toyoma-PC';
+------------------------------------------------------------------------------------------------------------+
| Grants for bob@toyoma-PC                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bob'@'toyoma-PC' IDENTIFIED BY PASSWORD '*0E832C6D48E2D90C70C6D60001BA38B4E02FC1CF' | 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test1`.* TO 'bob'@'toyoma-PC'                       | 
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MySQL Create User using INSERT

The user and db tables in the mysql database. You need to be in the mysql database.

sophal@sophal-desktop:~/Desktop$ mysql -uroot -pcaojiang 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 92
Server version: 5.0.51a-3ubuntu5.8 (Ubuntu)

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| book               | 
| cyberlearnings     | 
| drupaldb           | 
| guide              | 
| job                | 
| memc               | 
| mysql              | 
| test1              | 
+--------------------+
22 rows in set (0.00 sec)

user Table

mysql.user table

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       | 
| User                  | char(16)                          | NO   | PRI |         |       | 
| Password              | varchar(41)                       | NO   |     |         |       | 
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       | 
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| File_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| References_priv       | enum('N','Y')                     | NO   |     | N       |       | 
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       | 
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       | 
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       | 
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       | 
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       | 
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       | 
| ssl_cipher            | blob                              | NO   |     | NULL    |       | 
| x509_issuer           | blob                              | NO   |     | NULL    |       | 
| x509_subject          | blob                              | NO   |     | NULL    |       | 
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       | 
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       | 
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       | 
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       | 
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.01 sec)

db Table

mysql.db table

mysql> desc db
    -> ;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       | 
| Db                    | char(64)      | NO   | PRI |         |       | 
| User                  | char(16)      | NO   | PRI |         |       | 
| Select_priv           | enum('N','Y') | NO   |     | N       |       | 
| Insert_priv           | enum('N','Y') | NO   |     | N       |       | 
| Update_priv           | enum('N','Y') | NO   |     | N       |       | 
| Delete_priv           | enum('N','Y') | NO   |     | N       |       | 
| Create_priv           | enum('N','Y') | NO   |     | N       |       | 
| Drop_priv             | enum('N','Y') | NO   |     | N       |       | 
| Grant_priv            | enum('N','Y') | NO   |     | N       |       | 
| References_priv       | enum('N','Y') | NO   |     | N       |       | 
| Index_priv            | enum('N','Y') | NO   |     | N       |       | 
| Alter_priv            | enum('N','Y') | NO   |     | N       |       | 
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       | 
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       | 
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       | 
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       | 
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       | 
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       | 
| Execute_priv          | enum('N','Y') | NO   |     | N       |       | 
+-----------------------+---------------+------+-----+---------+-------+
20 rows in set (0.01 sec)

Create User by using insert statement

We need to insert into two tables: user table and db table.

Insert into user and db table

Here how we insert a new user account and set the privilege for a database name.

  • user name: Johnny
  • password: @password123
  • database name: test1
  • user host: localhost
  • privileges: insert, select, update, delete, create, drop

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO user(user,password,host)
    -> VALUES('johnny',PASSWORD('@password123'),'localhost');
Query OK, 1 row affected, 3 warnings (0.03 sec)
mysql> INSERT INTO db(Db,Host,User, Select_priv, Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)                                                        
VALUES('test1','localhost','johnny','Y','Y','Y','Y','Y','Y');
Query OK, 1 row affected (0.00 sec)

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

MySQL Delete User

You can delete user permanently or you just want to take away some of the user privileges.

Remove User privilege with REVOKE

Remove all user privileges from user name called paul

$:~$ mysql -uroot -pcaojiang mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.51a-3ubuntu5.8 (Ubuntu)

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

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'paul'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> 

Remove a specific privilege from user

We revoke some of the privileges from user name called johnny.

  • List the user grant privileges
  • Remove some of the privilege
  • List the user to see the changes
mysql> SHOW GRANTS FOR 'johnny'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for johnny@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'johnny'@'localhost' IDENTIFIED BY PASSWORD '*34E21039A458966D25ECA0B4558FEC6D6AB7C7AA' | 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test1`.* TO 'johnny'@'localhost'                       | 
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> REVOKE DELETE, DROP ON test1.* FROM 'johnny'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'johnny'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for johnny@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'johnny'@'localhost' IDENTIFIED BY PASSWORD '*34E21039A458966D25ECA0B4558FEC6D6AB7C7AA' | 
| GRANT SELECT, INSERT, UPDATE, CREATE ON `test1`.* TO 'johnny'@'localhost'                                     | 
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Delete user with delete statement

sophal@sophal-desktop:~$ mysql -uroot -pcaojiang mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.51a-3ubuntu5.8 (Ubuntu)

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

mysql> SELECT user, host FROM user;
+------------------+----------------------+
| user             | host                 |
+------------------+----------------------+
| paul             | %                    | 
| root             | 127.0.0.1            | 
|                  | localhost            | 
| debian-sys-maint | localhost            | 
| druapluser       | localhost            | 
| john             | localhost            | 
| johnny           | localhost            | 
| root             | localhost            | 
|                  | sophal-desktop       | 
| root             | sophal-desktop       | 
| bob              | toyoma-PC            | 
| bob              | w3cyberlearnings.com | 
| sophal           | wlith                | 
+------------------+----------------------+
13 rows in set (0.00 sec)

mysql> DELETE FROM user WHERE user="" AND host="localhost" OR host="sophal-desktop";
Query OK, 3 rows affected (0.01 sec)

mysql> SELECT user, host FROM user;
+------------------+----------------------+
| user             | host                 |
+------------------+----------------------+
| paul             | %                    | 
| root             | 127.0.0.1            | 
| debian-sys-maint | localhost            | 
| druapluser       | localhost            | 
| john             | localhost            | 
| johnny           | localhost            | 
| root             | localhost            | 
| bob              | toyoma-PC            | 
| bob              | w3cyberlearnings.com | 
| sophal           | wlith                | 
+------------------+----------------------+
10 rows in set (0.00 sec)

MySQL Change Password

Change password for a user or change your own password!

Change the current login user password

  • You login to your MySQL
  • You wish to change your password

Here how you do that!

$ mysql -ujohnny -p@password123 test1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.51a-3ubuntu5.8 (Ubuntu)

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

mysql> SET PASSWORD=PASSWORD('@password_new');
Query OK, 0 rows affected (0.00 sec)

mysql> 

How to reset user password in mysql

If you are having the root or privilege to change other user password.

sophal@sophal-desktop:~$ mysql -uroot -pcaojiang mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.51a-3ubuntu5.8 (Ubuntu)

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

mysql> SELECT user, host FROM user;
+------------------+----------------------+
| user             | host                 |
+------------------+----------------------+
| paul             | %                    | 
| root             | 127.0.0.1            | 
| debian-sys-maint | localhost            | 
| druapluser       | localhost            | 
| john             | localhost            | 
| johnny           | localhost            | 
| root             | localhost            | 
| bob              | toyoma-PC            | 
| bob              | w3cyberlearnings.com | 
| sophal           | wlith                | 
+------------------+----------------------+
10 rows in set (0.00 sec)
mysql> SET PASSWORD FOR 'sophal'@'wlith'=PASSWORD('my_new_password');
Query OK, 0 rows affected (0.01 sec)

How to Reset Lost Root Password

You may somehow forget your root password so you can not login to the MySQL Server. Here you will learn how to reset the root password.

sophal@sophal-desktop:~$sudo /etc/init.d/mysql stop

sophal@sophal-desktop:~$sudo mysqld --skip-grant-tables &

sophal@sophal-desktop:~$ mysql -uroot mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.51a-3ubuntu5.8 (Ubuntu)

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

mysql>UPDATE user SET Password=PASSWORD("Your password") WHERE user='root';
mysql>FLUSH PRIVILEGES;

MySQL Limit User Access

You can limit user access to the MySQL.

  • Limit the total query a user allows per hour
  • Limit the update query a user allows per hour
  • Total time a user allows to connect to the MySQL server per hour

Limit User Access Example

We create a new database and a new user.

  • Allow 20 Queries per Hour
  • Allow 10 update per hour
  • Allow 10 connect to the server per hour
sophal@sophal-desktop:~$ mysql -uroot -pcaojiang 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.0.51a-3ubuntu5.8 (Ubuntu)

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

mysql> CREATE DATABASE sale;
Query OK, 1 row affected (0.04 sec)

mysql> GRANT ALL ON sale.* TO 'kwan'@'localhost'
    -> IDENTIFIED BY 'my@password'
    -> WITH MAX_QUERIES_PER_HOUR 20
    ->      MAX_UPDATES_PER_HOUR 10
    ->      MAX_CONNECTIONS_PER_HOUR 10;
Query OK, 0 rows affected (0.00 sec)

mysql> 

Reset maximum query to 150 for the kwan user

We reset the maximum query per hour for the user named kwan to 150.


sophal@sophal-desktop:~$ mysql -uroot -pcaojiang 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.0.51a-3ubuntu5.8 (Ubuntu)

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

mysql> GRANT USAGE ON *.* TO 'kwan'@'localhost' WITH MAX_QUERIES_PER_HOUR 150;
Query OK, 0 rows affected (0.01 sec)

mysql> 

Check User

sophal@sophal-desktop:~$ mysql -ukwan -pmy@password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.0.51a-3ubuntu5.8 (Ubuntu)

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

mysql> SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for kwan@localhost                                                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kwan'@'localhost' IDENTIFIED BY PASSWORD '*B940B525EBB726C52256309B68C2D39C961DF73D' WITH MAX_QUERIES_PER_HOUR 150 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 10 | 
| GRANT ALL PRIVILEGES ON `sale`.* TO 'kwan'@'localhost'                                                                                                                                        | 
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 

MySQL Bind IP for Remote Access

You want to access the MySQL database remotely, you need to bind the IP address with the MySQL. In order to bind the MySQL server with a specific IP, you need to modify the my.cnf file. The my.cnf file is located in the MySQL database installed directory.

my.cnf

Usually, the mysql database stored in /etc/mysql/my.cnf

[mysqld]
user = mysql 
pid-file = /var/run/mysqld/mysqld.pid 
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 65.45.25.4 
# skip-networking
....
..

Restart the MySQL to make change take affect

$/etc/init.d/mysql restart

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

This error is due to the MySQL Server has assigned to a specific IP address. You need to change back to the localhost IP address (127.0.0.1).

bind-address = 127.0.0.1
Navigation
Web
SQL
MISC
References