MYSQL Database Security Part 1: Access Privilege System

     The database is the central source and storage of data a system has so securing the database is one of the main concerns of system developers. In the MYSQL database, there are security guidelines and methods made available to be used to secure the data and restrict the access in the database. Here are some security guidelines:

• Do not give anyone (except MySQL root accounts) access to the user table
• Learn how the MySQL access privilege system works
• Do not store plaintext passwords in your database
• Do not choose passwords from dictionaries
• Invest in a firewall
• Applications that access MySQL should not trust any data entered by users
• Do not transmit plain (unencrypted) data over the Internet

      These are the security guidelines everyone should be aware to have a secured database but today we are going to discuss some methods and syntaxes that would make our database more secure.
     In the security guidelines above, the second line talked about learn how the MYSQL access privilege works. Now we’ll explain how it works.
     The MYSQL access privilege system is created to authenticate which user accesses the database and it grants different kinds of privileges a user can access in the database. It can make the database accessible to users but make them have limited access to the functions one can do to the database or in its structure.      There are different kinds of privileges provided by MYSQL but before we discuss those, we are going discuss to how to create a user in MYSQL.
     Creating a user is important because using the root user is unsafe and unsecure because the user has all the privileges that could ruin the database. We create a user to limit the access of a user to the database. To create a user we’ll use the syntax like this:
     CREATE USER ‘the_username’@’localhost/ip_address’ IDENTIFIED BY ‘the_password’;
     Here is an example:

     To provide privileges to the user we are going to use the “GRANT” statement. The syntax would be like this: 
  GRANT <the_privilege> ON <databasename>.<tablename> TO ‘the_username’@’localhost/ip_address’;
     Here is an example:

     The ‘SELECT’ above is the privilege that is given to the user ‘chochologist’@’localhost’. The ‘college’ is the name of the table in the database named ‘clsu’. 

     Here are some privilege that are available for MYSLQ:

     Sometimes you may need to remove the privileges granted to a user. To do this you can use the ‘REVOKE’ statement. The syntax would be like this:

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

     Here is an example:  

     For more kinds of privileges you can visit the MYSQL privileges site here.

     References: mysql.com