I have recently started a course on Database Security and had some difficulties in deciding whether to use MySQL or Oracle as the Database system for lab assignments. I got into the MySQL vs. Oracle debate that has been going on since before the web started. As you might imagine, the reasons to use either database over the other can fill entire books, so I tried to compare them based on their main feature sets.
MySQL is a multithreaded, multi-user SQL database management system (DBMS) which has more than 11 million installations. The program runs as a server providing multi-user access to a number of databases. MySQL is a robust database system and is the world's most used open source RDBMS. MySQL is the most popular choice for use in Web Applications.
Oracle has become a major presence in database computing. It is the database choice among large corporations and financial institutions since Oracle strives to implement security of the highest possible standards. Oracle includes provision for the high security measures demanded by government legislations. Oracle complies with various financial bodies worldwide and has the advantage of years of experience in providing security at several levels for large organizations on a distributed level.
This is the most notable difference between the two databases. MySQL is an open source database, and is completely free of cost. On the other hand, it will cost you tens of thousands of dollars to obtain a full featured standard edition or Enterprise edition of Oracle. Oracle has something called Oracle Express Edition, and this edition is completely free of cost. However, there are a limited amount of features available in this edition.
Purpose and Usage
As implied above Oracle is targeted towards large organizations handling very large databases and towards organizations requiring secure applications. MySQL can also work for large organizations or secure applications (The largest social networking site 'Facebook' runs on MySQL). But still Oracle is better built and has more features suited for these scenarios.
MySQL is more popular in websites and PHP integration. Oracle is popular in large databases like Banking, ERP, Insurance, finance companies.
While MySQL authentication mechanism may be safer with the addition of extra parameters (Discussed later) and focuses its entire security model around Access Control Lists, Oracle has many security features like Oracle Database Vault, Oracle Advanced Security Transparent Data Encryption (TDE), Audit Vault, etc. All of these features majorly improves the security of an oracle database.
When comparing Oracle security to the MySQL version there is a world of difference. Oracle has had years of experience, and has responded to changing security needs. In contrast, as good as MySQL is for an open source product, it would not be suitable for large corporations and the security aspects are still evolving. Security features of these databases are discussed later.
Programming Languages Support
Oracle database supports several programming languages which can be written, compiled and executed from inside the database and it also supports XML for transferring data. MySQL, on the other hand, does not support other languages being executed from within the system, and it has limited XML support.
Advantages/Disadvantages of MySQL:
- MySQL is characterized as a fast, robust database with a good feature set, so if you're writing code that's designed to be portable it's a pretty good choice.
- Administration and security are effective and the setup of these and other features isn't over-complicated. MySQL is a good choice for many a database that may have "middle of the road" requirements.
- MySQL is free and open source.
- MySQL does lack some sophistication and facilities, but it has an active development team and, as it goes from release to release, more capabilities are added. At certain times there will be a trade-off between speed and capabilities, and the MySQL team intends to keep their database engine fast and reliable.
- MySQL users enjoy this database because it is available on virtually all hosting companies.
- MySQL uses lots of memory.
- You can store lots of data with MySQL, but it will be slow on the server. In addition, backing up the MySQL database takes a very long time compared to other Database Management Systems.
Advantages/Disadvantages of Oracle:
- Oracle is much more versatile than MySQL. Oracle can run and handle more transactions than MySQL and therefore would be better if you are going to have lots of database usage.
- MySQL takes up resources that affects the server load. Oracle on the other hand is better with memory usage and handles multiple threads.
- Oracle is expensive, although they came out with Oracle Free Edition to attract MySQL users.
- Oracle is limited to large corporations due to licensing costs and not all hosting companies have Oracle (they might have SQL Server, but not Oracle).
- Oracle's tools makes database backups very fast and reliable .
- MySQL team aims to keep their database engine fast and reliable by managing the trade-offs between speed and capabilities.
Oracle Uses only username and password to authenticate a user.
MySQL Adds a location parameter when authenticating a user. The location parameter is usually the host name, IP address or a wildcard. This allows database access restriction based on host or IP address. It also allows different password and access privileges based on the location from which the connection is made.
Authorization and Access Control
Oracle uses role based access control to govern data access to database objects. Each role is associated with a set of privileges that allows users to view, create or modify data based on their role(s). When multiple users can access database objects, authorization can be controlled to these objects based on their privileges. Privileges control what operations a user can perform on a database object. Objects include tables, views, materialized views, synonyms, indexes, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages. There are two types of privileges supported in Oracle:
- System Privileges: A system privilege is the right to perform a particular action or to perform an action on any object of a particular type.
- Object Privilege: An object privilege is the right to perform a particular action on an object or to access another user's object.
MySQL Uses a Hierarchical privilege system that works with inheritance. MySQL have 5 privilege levels:
- Per Host Basis
- Database Level
- Table Specific
- Column Specific
Privilege granted at a higher level is implicitly passed down to lower levels. Lower levels can override privileges set by higher levels, i.e. Privileges granted at lower levels take precedence over privileges granted at higher levels.
There are two types of privileges supported by MySQL:
- Administrative Privileges: Global privileges that have server wide effect and are concerned with the functioning of MySQL.
- Per-Object Privileges: Can be granted with different scopes. These privileges are concerned with database objects like tables, columns, indexes and stored procedures.
Within MySQL there is no concept of role, therefore all users with the same permission level need to have their permissions assigned separately. This can be tedious in the case that a database has many users with the same permissions.
Oracle Security Features
Oracle Database Vault
Oracle Database Vault restricts access to specific areas in an Oracle database from any user, including users who have administrative access. For example, you can restrict administrative access to employee salaries, customer medical records, or other sensitive information.
This enables the application of fine-grained access control to sensitive data in a variety of ways. It hardens Oracle Database instance and enforces industry standard best practices in terms of separating duties from traditionally powerful users. Most importantly, it protects data from super-privileged users but still allows them to maintain the database.
With Oracle Database Vault, the most difficult security problems remaining today are addressed:
- Protecting against insider threats
- Meeting regulatory compliance requirements
- Enforcing separation of duty.
Oracle Advanced Security Transparent Data Encryption (TDE)
The Transparent Data Encryption (TDE) feature introduced in Oracle 10g Database Release 2 allows sensitive data to be encrypted within the data files to prevent access to it from the operating system. Data is automatically encrypted and compressed when it is written to disk and automatically decrypted and decompressed when accessed by the application. Key management is built-in, eliminating the complex task of creating, managing and securing encryption keys.
Oracle Audit Vault collects audit data from multiple databases and then consolidates this data in a set of audit reports. Oracle Audit Vault collects audit data from databases, organizes the data, and then provides a variety of reports that satisfy standard compliance regulations.
The policies and alerts features help you to detect security threats to an Oracle database. For example, an alert can notify you when a system administrator tries to view sensitive application data, such as employee salaries.
Because Oracle Audit Vault centralizes audit settings, the job of an auditor is easier and more efficient. Auditors can create, manage, and monitor audit information from one location. This also makes it easier to demonstrate compliance policies.
MySQL Security Features
Access Control Lists
MySQL bases its main security technique on an Access Controls List (ACL). ACLs work by allowing different users to have varying levels of access to databases and tables, and the ability to perform operations, as permitted in their individual user profiles. Some users are allowed full privileges which allow all of the standard database operations such as SELECT, DELETE, UPDATE and INSERT. A user with limited privileges would only be able to use the subset of the possible operation. The level of access is determined by the DBA and the needs of the user.
In conclusion (and in my opinion), we can safely say that choosing between MySQL and Oracle is mainly based on the use of the Database Management Systems. For very large systems and systems requiring high level of security such as banking systems, it would be more advisable to use Oracle mainly because of the security features provided by oracle and the better scalability capabilities of Oracle as compared to MySQL. But for simple to medium sized web and other applications, it would be preferable to use MySQL mainly because MySQL is free and robust, and it has a good feature set that should be able to handle all the tasks needed by simple and medium sized applications. However, this does not mean that MySQL is not suitable for large applications and applications requiring a high level of security or that Oracle is not suitable for simple and medium sized applications, it is solely up to you to decide which DBMS to use, this article just discusses some factors that may help with your decisions.
For my Database Security class, I chose to use both Oracle and MySQL because of the fact that it is a Database "Security" class, and I thought it would be a better to have hands on experiences with a wide variety of security features used across multiple platforms. Also, learning about multiple database platforms will help me to avoid being biased (in terms of using a system I already know rather than using something better suited for the application) in choosing a DBMS for future projects.