MYSQL Database Security Part 2: User Account Management

     Another important thing in MYSQL Security is the user account management. User Account management allows us to manage the users that will be available to have access in the database. We already discuss how to create a user now we are going to discuss other functions you can do to a user.

     There are many different statements you can perform to a user. Today we’ll discuss the most basic statements. Those are the CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE and SET PASSWORD.

     In the previous discussion, specifically MYSQL Security Part 1, we have already discussed how to use the create user, grant and revoke statements. As a recall we create user by using the CREATE USER statement and the syntax would be like this:

  CREATE USER ‘the_username’@’localhost/ip_address’ IDENTIFIED BY 'the_password’;

And we grand and revoke privileges to a user by using the GRANT and REVOKE statements like these:

  GRANT <the_privilege> ON <databasename>.<tablename> TO ‘the_username’@’localhost /ip_address’;

  REVOKE <the_privilege> ON <databasename>.<tablename> FROM ‘the_username’@’localhost /ip_address’; 

Now we’ll discuss the other statements we have mentioned earlier. 

DROP USER

     Dropping a user would mean you’re going to remove that user.  To be able to drop a user the user logged in must have the global CREATE USER privilege or the DELETE privilege. The syntax for deleting a user is this:

  DROP USER ‘the_username’@’localhost /ip_address’;

This is an example: 

RENAME USER

The RENAME USER statement is used to rename the existing user. To be able to rename a user, the user logged in must have the UPDATE privilege. The syntax for this statement is this:

  RENAME USER ‘the_username’@’localhost/ip_address’ TO ‘new_username’@’localhost/ip_address’;

This is an example:

SET PASSWORD

The SET PASSWORD statement is used to assign a password to an already existing user account. It can be used to change the old password to a new one. The syntax for this statement is this:

  SET PASSWORD FOR ‘the_username’@’localhost/ip_address’ = PASSWORD(‘new_password’);

This is an example:

For further discussion about MSQL User Account Management you can click here.

Reference: mysql.com