User Account Management in MySQL

User Account Management in MySQL

Introduction

MySQL is an open source Relational Database Management Software that helps users store, organize, and later retrieve data. It has a variety of options to grant specific users nuanced permissions within the tables and databases—this blog will give a short overview of a few of the many options.

In this blog, we deal with creating a new User account in MySQL, granting specific access / permissions on global or database level for a particular or all hosts. It also deals with change of password for the user, renaming a user account and finally deleting the User account. We have comprised the set of SQL Commands to be executed to perform these actions.

Use Case

Let us have Use cases for every step from User creation to User deletion to get a better understanding.

What we need to do:

Create a new user in MySQL, by granting permission for a particular database for all hosts.

Create a User in MySQL with Password and grant permission for all hosts

The CREATE USER command, along with the ‘user name’ IDENTIFIED BY ‘the password’   is used to create a new user in the MySQL database.

The ‘%’ wildcard denotes all hosts.

We can verify the addition of a new user by querying the user table:

Output:

create user

  • Granting global privileges to user for a particular database

Database privileges apply to all objects in a given database. To assign database-level privileges, use ON db_name.*

  • Granting custom privileges to users who perform INSERT/UPDATE/DELETE and these kinds of activities on the tables, views and routines

MySQL stores database privileges in the mysql.db table.

Note: The privileges information is stored in different tables in the MySQL database depending on what context the privilege is for.

  • If we grant a privilege on *.*, it goes into the mysql.User table.
  • If we grant a privilege on test.*, it goes into the mysql.db table.
  • If we grant a privilege on test.table, it goes into the mysql.tables_priv table.
  • If we grant column privileges on test.table, it goes into the mysql.columns_priv table.

Create a User with Password and grant permission for a particular host

  • Granting global privileges to user for a particular database

Database privileges apply to all objects in a given database. To assign database-level privileges, use ON db_name.*

  • Granting custom privileges to users who perform INSERT/UPDATE/DELETE and these kinds of activities on the tables, views and routines
    • Challenges: When we grant specific database permission to a user, they will not be able to view any routines in the database. In order to view the routines we have to use the command given below:
    • Solution:
  •  Granting particular table-level privileges to the user:

Table privileges apply to all columns in a given table. To assign table-level privileges, use ON db_name.tbl_name

MySQL stores table privileges in the mysql.tables_priv table.

    • Challenges: When we grant particular database permission to a user, they will not be able to view any routines in the database. In order to view the routines we have to use the command given below:
    • Solution:

Create User in MySQL and grant all permissions on all databases

First the User is created:

Next, Grant All Global Privileges to the user for all databases. Global privileges are administrative or apply to all databases on a given server.

To assign global privileges, use ON *.*

MySQL stores global privileges in the mysql.user table.

  • Granting custom privileges to users who perform INSERT/UPDATE/DELETE and these kinds of activities on the tables, views and routines

Assign and Change password for MYSQL User

Initially, when  we create a new account with CREATE USER clause , a password is assigned  in the IDENTIFIED BY clause, as below:

Renaming a MySQL User

The account name of a MySQL user can be changed using the RENAME USER statement.

For example:

DROP USERS in MYSQL

List of MYSQL Grant privileges

Setting

Description

ALL [PRIVILEGES] Sets all simple privileges except GRANT OPTION
ALTER Enable the use of ALTER TABLE statement
ALTER ROUTINE Enable stored routines to be altered or dropped
CREATE Enable the use of CREATE TABLE statement
CREATE ROUTINE Enable creation of stored routines
CREATE TEMPORARY TABLES Enable the use of CREATE TEMPORARY TABLE statement
CREATE USER Enable the use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW Enable the use of CREATE VIEW statement
DELETE Enable the use of DELETE statement
DROP Enable the use of DROP TABLE statement
EXECUTE Enable the user to execute stored routines
FILE Enable the use of SELECT INTO OUTFILE and LOAD DATA INFILE
INDEX Enable the use of CREATE INDEX and DROP INDEX statements
INSERT Enable the use of INSERT
LOCK TABLES Enable the use of LOCK TABLES on tables for which the user has the SELECT privilege
PROCESS Enable the user to see all processes with SHOW PROCESSLIST
REFERENCES Not implemented
RELOAD Enable the use of the FLUSH statement
REPLICATION CLIENT Enable the user to ask for slave or master server locations
REPLICATION SLAVE Needed for replication slaves (reads binary log events from the master)
SELECT Enable the use of SELECT
SHOW DATABASES SHOW DATABASES shows all databases
SHOW VIEW Enable the use of SHOW CREATE VIEW
SHUTDOWN Enable the use of mysqladmin shutdown
SUPER Enable the use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows single connection if max_connections is reached
UPDATE Enable the use of UPDATE
USAGE Synonym for no privileges
GRANT OPTION Enable privileges to be granted

Conclusion

This brings us to the end of this blog. Thus we have been able to create a User that can either be an application or a person, who can connect to the MySQL database using a password. We have granted the user permissions to perform several activities on MySQL. We have also been able to change the password, rename the user and finally delete the User.

With all the privileges granted on the entire system, the User is entitled to perform any kind of Admin level activity as well. Hence, During a new user account creation, enough care should be taken to figure out the level of access that the user is entitled to, and provide the user with access to the information they will need.

This is one of the common database security issues. One of the ways we can address DB security concerns is by preventing unauthorized access of data. This can be initially accomplished at the User creation level while granting the user permissions and setting privileges based on necessity and is also the best practice.

References

4253 Views 9 Views Today