MySQL Create User and Set User Privileges
From w3cyberlearnings
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
