Database Security

      
The Database is the storage of all data of systems, and SQL has security guidelines to be able to secure the data and restrict access to the database. Here are some of the security guidelines to be followed:

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

 MYSQL Access Privilege System

 Create User

 Using the root user is unsecure  because the root has all the access/privileges and no restrictions, thus we should create a user with restricted access privileges to prevent database ruin. In the example above, a user 'joed' was created without a password.

• To enable the user to connect with no password (which is insecure), include no IDENTIFIED BY clause:
  

  CREATE USER 'joed'@'localhost';

• To assign a password, use IDENTIFIED BY with the literal plaintext password value:
  

  CREATE USER 'joed'@'localhost' IDENTIFIED BY 'mypass'; 

  Grant User

   Grant is the command to grant priviliges to a user. To use grant, you must first have an existing user account created.In our example, the use 'joed' was granted SELECT to table department in database college.

       Revoke

        Revoke is used to revoke/remove account privileges granted on a user.
        Example:

In this example, the user revoked the SELECT on college.department, meaning, the user 'joed' can no longer use the SELECT command in college.department.

Here is the list of some privileges in MYSQL:

User Account Management

User Account management allows us to manage the users that will be available to have access in the database. Since we already discussed the how to CREATE a user, we'll discuss the remaining Account Management like dropping, renaming or setting a new password for the user.

SET PASSWORD

 In the example above, the user 'joed' set new password which is 'newpass'.User can change their passwords anytime they want, just be sure to consult the security guidelines when choosing your password.

RENAME USER

In the example above, the user 'joed' has been changed to 'joedsantiago'. This does not alter the password for the user, the password remains the same. 

DROP USER

DROP USER does what is it supposed to do, drop/erase the user, also removing any grants and privileges on that user.