As a database administrator one of my prime responsibilities is to secure my organization's database from unauthorized access. Developing a database is not difficult at all but developing a secure database is really a complicated task.

In this article I am going to discuss some guidelines that must be followed to keep your database secure from unauthorized users.

1. Encryption:

Whether the data in the database should be encrypted or not depends on the sensitivity of data. For example all passwords and credit cards numbers in the database must be encrypted. In MySQL you can use MD5() , SHA1() , or some other one-way hashing function.

Another important issue is the security of data being transmitted across the network. Hackers can access the data packets and access the sensitive information. In MySQL, you can encrypt the client-server connection by using secure shell utility or by enabling SSL in MySQL configuration.

2. Maintain Data Integrity:

Always keep a check on integrity of the data in the database. Before adding any sort of data whether sensitive or insensitive your system must ensure that the data is valid. Integrity prevents data from becoming invalid and your database always returns correct results.

3. Avoid SQL Injections:

Keep your server secure from SQL Injections. Always validate the data entered by the user.

4. Use Access Control System:

Always grant enough privileges to users and those privileges should be given to only those users who require them to accomplish their tasks. Never grant privileges to all the hosts. Excessive grant of unnecessary privileges can compromise the security of your database.

Be careful in assigning privileges to users. Using wild cards in database access control system on one hand ensures database security by setting permissions for the users, but on the other hand any unauthorized user can use a single compromised account to gain access to other parts of the system.

You can use MySQL GRANT and REVOKE statements. The GRANT statement creates MySQL user accounts and assigns privileges to them whereas the REVOKE statement removes the account privileges. Never allow SUPER privilege to users as it allows users to access all the databases and they can manipulate the server configuration.

5. Role Based Authentication:

Always grant role based authentication. You can segregate the roles into administrator, programmer, operator, user and so on. Permission to perform a certain task should be based on the privileges granted to that role. Also maintain a log of each role to keep track of each role's activities.

6. Password Protection:

Make your database root account password protected. Also maintain password for each user account in the database.

7. Use Views:

Views provide a powerful and flexible security mechanism for the database. By using views you can hide parts of database from certain users. The user is not aware of the parts missing from the view.

8. Protect Per-User Configuration Files:

Make sure that your per-user database configuration files are stored at safe locations and are not viewable by users.

9. Back Up and Recovery:

Use database back up facilities to recover the data in case of database failure. Make back up copies of database and log file and place them at secure locations.

In case of failure the database should be able to recover to the last known consistent state by using the back up copy and information contained in log files.

10. Maintain Log Files:

Maintain database log files to keep check on the client connections, queries and server errors. This will help you keep track of client's activities.

11. Disable Remote Access:

Ensure that no user can get remote access to your database system. This will reduce the risk of network attacks from unauthorized users.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Testing the Oracle Recovery Plan
   Go beyond running exp and imp - Follow Database Migration Tips!!
   DBA Tips: Limiting User Commands, Roles & Sessions!!
   Oracle Tuning Guide for Concurrent Disk I/O