MySQL - Common commands of database user account management

SQL database are used to store various kind of data in order for future retrieval. But data stored in SQL database is not only for future retrieval, it is also stored in such a way for faster (repeated) retrieval, structured manner in data organization, interchangeable between applications and most importantly, it provides a mean to store and search for really huge data by using SQL statement, query.

There are a lot of database currently available, e.g. PostgreSQL & MySQL. MySQL is one of the most popular database among open source database, same as PostgreSQL. Due to the hip of LAMP (which denotes Linux, Apache, MySQL, PHP), MySQL have made known to the mass audience. No doubts its performance and usability are, both MySQL & PostgreSQL, in par.

This post will not attempt to compare both excellent databases, rather, it will try to list out a few common uses of MySQL database in tips & shortcut way. Below are the list of common usage of MySQL permission and privileges :

Commonly, MySQL are used within its command line prompt, "mysql>".

Start MySQL command line prompt

The subsequent tips are all run from the mysql command prompt tool. It is started using the below command syntax :

mysql -u <user-with-admin-privileges> -h <hostname> -p

• -u <user-with-admin-privileges> --> it is either root or another account with the same privileges
• -h <hostname> --> normally is localhost or the database server FQDN (Full Qualify Domain Name) or IP
• -p --> execute the command with password protected prompt
• e.g.

  user@localhost$ mysql -u bob -h localhost -p

Create MySQL database user login account

CREATE USER 'new-user-name'@'hostname' IDENTIFIED BY 'password';

• 'net-user-name'@... --> new database user login name
• ...@'hostname' --> database server FQDN (Full Qualify Domain Name) or IP
• 'password' --> password to be assigned to the new database user login name
• e.g.

  mysql> CREATE USER 'joe'@'localhost' IDENTIFIED BY 'secret';

Display grant privileges of a database user account

SHOW GRANTS FOR 'username'@'hostname';

• 'username'@... --> target user name to display the privileges
• ...@'hostname' --> database server FQDN (Full Qualify Domain Name) or IP
• e.g.

  mysql> SHOW GRANTS FOR 'joe'@'localhost';

Show the list existing database user account in MySQL

SELECT <field1>,<field2>,<...> FROM mysql.user;

• <field1>,<field2>,<...> --> database fields to show
• mysql.user --> the table that stores the list of existing database user account in MySQL
• e.g.

  mysql> SELECT User,Host FROM mysql.user;

Grant privileges to database table, but limit to localhost login only

GRANT ALL ON *.* TO 'username'@'hostname';

• *.* --> all privileges or permission to access or login to the database table
• 'username'@... --> user name to be granted access to the table
• ...@'hostname' --> database server FQDN (Full Qualify Domain Name) or IP
• e.g.

  mysql> GRANT ALL ON *.* TO 'joe'@'localhost';

Grant a database user account to login from any where to all database

GRANT ON *.* TO 'username'@'%' IDENTIFIED BY 'password';

• *.* --> all privileges or permission to access or login to the database table
• 'username'@... --> user name to be granted access to the table
• ...@'hostname' --> database server FQDN (Full Qualify Domain Name) or IP
• '%' --> wild card representation, aka "all"
• 'password' --> password to be assigned to the new database user login name
• e.g.

  mysql> GRANT ON *.* TO 'bob'@'%' IDENTIFIED BY 'secret';

Change password, using own account

SET PASSWORD = PASSWORD('new-password');

e.g.

mysql> SET PASSWORD = PASSWORD('new-secret');

Change password, using root (or same privilege)

SET PASSWORD FOR 'username'@'hostname' = PASSWORD('new-password');

e.g.

mysql> SET PASSWORD FOR 'bob'@'localhost' = PASSWORD('new-secret');

Delete database user account from database table

DROP USER 'username'@'hostname';

e.g.

mysql> DROP USER 'bob'@'localhost';

After changing some privileges or permission, it is a good idea to reload the privileges. Execute,

FLUSH PRIVILEGES;

to initiate the reload process.

Adios !!!