Home | Login      
Comparison of Database Security Models
 

In this post I'm going to compare security models of 5 popular database servers - Oracle, SQL Server, DB2, MySQL, and Sybase ASE. Database servers typically provide multiple layers of security, including: data access controls, internal and external user authentication, in database data encryption, backup file encryption, data access auditing, user activity auditing, and other. While internal data storage organizations of each compared database server and the available security layers are vastly different, some parallels can be still drawn. I'm going to focus on the data access security only. In other words, I'm going to highlight how users can gain access to data stored in database tables. For simplicity I'm going to ignore all non-data specific security functions, such as server control and management, user management, and similar.

For the record, Microsoft SQL Server, Sybase Adaptive Server Enterprise (ASE), and IBM DB2 servers implement 4-tier data storage architecture - a server instance hosting one or more databases, each database hosting one or more schemas, and each schema grouping together one or more schema objects. In this 4-tier organization, data access permissions can be managed to a certain degree at each tier. 

In comparison, Oracle and MySQL servers implement 3 tier data storage architecture - a server instance hosting one or more schemas, and each schema grouping together one or more schema objects. In this 3-tier organization, data access permissions also can be managed to a certain degree at each tier, but because there are less tiers, it is somewhat easier to manage and secure. Yet, there are huge differences between Oracle and MySQL security model. As of the time of this writing, MySQL most recent stable production version is 5.1.33. This version implements relatively primitive security model with fixed 2-level hierarchy (schema and object) and it doesn't support any types of role-based security methods.


SQL Server data access security model

Let's begin with the data-access security model for SQL Server. SQL Server's support for Windows authenticated logins is both its strength and weakness. On one hand, Windows authenticated logins make user management easier and allow applications connect to the database server without explicitly specifying passwords which in turn obviates a need to store passwords in open text application configuration files. This also obviates a need for manually updating configuration files after each password change cycle. On the other hand, all administrators and many power users by default get full access to all the data stored in the server just because they are members of domain administrators or local administrators groups and their group has explicit access to the SQL Server with system administrator privileges via "BUILTIN\\Administrators" or similar pre-defined group-type logins which are enabled by default. It also makes much easier for hackers to access the data. All they need is to break into the network and take control over some admin level network user account - in such cases they often automatically get full access to all SQL Server instances accessible by that account.

Data access permissions can be obtained implicitly along with fixed server-level and database-level roles such as System Administrator server role (sa), Database Owner database role (db_owner) and similar. Data access permissions can be also obtained explicitly and be issued using SQL GRANT commands for database users associated with user logins or issued for database roles, which in turn can be granted to database users. Fixed server-level roles feature pre-defined permissions which cannot be customized. There are two types of database-level roles: database roles and application roles. Database roles are statically associated with database users. An application can call sp_setapprole system stored procedure to activate an application role for a specific connection. As a result, the connection permanently loses all defined permissions for the duration of the connection, except the permissions defined by the application role. The user associated with the connection has only those permissions defined by the application role attached to the database to which they're currently connected.


Sybase ASE data access security model

Sybase ASE data-access security model is similar to SQL Server model and at the same time it is more sophisticated and confusing. ASE supports both regular SQL authenticated logins and externally authenticated logins. As compared to SQL Server, ASE doesn't come with group-type administrative logins enabled by default, which is a big plus. However, ASE security model supporting server-level user defined roles and database-level user-defined roles is very confusing. Both types of roles allow granting and denying access privileges for specific schema objects. From my point of view, scope of ASE server-level roles should be limited only to server and database level management functions and be completely separated from data access to schemas and individual schema objects. ASE support for multilevel hierarchical roles (a role granted to another role) does provides maximum flexibility, yet at the same time it makes the security model even more confusing and difficult to manage.

Data access permissions can be obtained implicitly along with fixed server-level roles such as System Administrator server role (sa). Data access permissions can be also obtained explicitly and be issued using SQL GRANT commands for database users  associated with user logins, issued for user-defined server roles granted to user logins, or issued for database roles, which in turn can be grated to database users associated with user logins.


DB2 data access security model

DB2 data access security model before version 9.5 was somewhat very primitive. Data access permissions can be either obtained implicitly with certain pre-defined server-level and database-level fixed roles called DB2 authorities such as Server Administrator, Database Administrator and similar or obtained explicitly by issuing SQL GRANT command for specific schema objects. Privileges can be granted to a user group and all the users in that group will automatically have those privileges. Schema scope privileges can be obtained only for very limited subset of DDL operations and not available for widely used DML operations.

Starting with version 9.5, DB2 supports user-defined database roles similar to database roles supported in Oracle database servers since long. DB2 support for multilevel hierarchical roles (a role granted to another role) greatly improves its data access security model making it much more flexible then simple 1-level group based authorization model.


Oracle data access security model

Oracle security model hasn't changed much for many versions. Since early Oracle versions it is easy to manage and suffiently robust to provide granular privileges that can be granted to individual users or be granted to database roles, which in turn can be granted to one or more database users and the users will automatically inherit those privileges. Oracle supports multilevel hierarchical role. Roles can be granted to other roles.

There are 2 types of privileges supported by Oracle - server-scope privileges and schema object-scope privileges. Both users and roles can obtain server-scope and object-scope privileges. Certain types of server-scope privileges are designed for administrative purposes and provide implicit access to all objects of a certain type. For example, SELECT ANY privilege allows users to read data from virtually any table in the database, EXECUTE ANY privilege allows users to execute virtually any procedure, function, type, and package in the database. If such privileges are granted to a role, all the users granted that particular role or one of its higher hierarchy roles will automatically have those privileges.


MySQL data access security model

MySQL data access security model is very simple. Users can obtain server scope data access privileges for all tables in all schemas, or obtain schema-scope privileges for all tables in a particular schema, or obtain explicit data access privileges for schema objects. Object-scope and schema-scope privileges can be granted to individual users only. Server-scope privileges can be granted to individual users and to all users connecting from a particular system. Server-scope privileges are stored in mysql.user and mysql.hosts tables. Schema-scope privileges are stored in mysql.db table. Object scope privileges are stored in mysql.table_priv and mysql.procs_priv table.

In addition to this already weak security model, MySQL server allows changing security settings directly in MySQL system tables. A user who gained access to "mysql" system schema and having update, delete, insert privileges for this schema is effectively able to modify, delete and add privileges for all users on the server for any schema and any schema object. Use of standard GRANT and REVOKE commands is recommended but not enforced.


Data-access Protection By Database Server Type

Characteristic

SQL
Server
2008

ASE
15

DB2
9.5

Oracle
11g

MySQL
5.1

Data access is limited only to users with explicitly granted data-access privileges 1 - 1 Yes Yes Yes Yes
Availability of granular data access controls (schema-level, table-level, column-level) Yes 2 Yes 2 Yes 3 Yes Yes
Efficient hierarchical role-based data access controls - Yes 2 Yes 3 Yes -
Out-of-the-box granular auditing of data-access - 6 Yes Yes Yes -
Out-of-the-box tools for access control management Yes Yes Yes Yes Yes
Out-of-the-box tools for access control analysis and reporting 4 - - - - -
Data access control encapsulation using database stored procedures and views, using access-as-self and access-as-definer methods Yes Yes Yes Yes Yes
Out-of-the-box in-database data encryption Yes Yes Yes Yes -
Out-of-the-box backup file data encryption 5 Yes - - Yes -
Encrypted communication protocols protecting data traveling between database servers and client applications 7 Yes Yes Yes Yes Yes
  1. Default settings allow any user having network or local system administrator account to access all data in a SQL Server instance.
  2. The system of multi-tier GRANT and DENY privileges coupled with multiple concurent access control methods makes access control extremely confusing, hard to manage, prone to human errors and therefore unreliable.
  3. The system of multi-tier user-level, group-level, and role-level access controls with multiple concurent access control methods makes access control management difficult, prone to human errors and therefore unreliable.
  4. While all compared servers provide graphical tools and non-graphical commands for data access control, none comes with analytical tools that can answer very simple questions like "Who has access to my data?" taking into account all data access paths based on explicit and implicit privileges.
  5. With the exception of SQL Server 2008 and Oracle 10g R2 and 11g, all database servers, including older versions of SQL Server and Oracle, require expensive add-ons or third party solutions for backup data encryption.
  6. 24x365 based use of graphical SQL Profile utility is not a viable option for auditors and security officers.
  7. Available for all servers, but not enabled by default. As a result, anyone successfully hacking into the network can monitor and capture the data sent between applications and databases without any access to database servers.

 

*** FYI: Database auditing, compliance reporting, security management have become very hot topics for SoftTree Technologies customers. That is why in recent versions of DB Audit Expert solution we have added an array of new tools and reports for comprehensive forensic analysis of database security and data access. I highly recommend checking the Effective Security Explorer utility that is able to perform multi-layer and multi-path analysis of hierarchical data access privileges. This unique utility is a must have for every database administrator, security auditor, and security officer responsible for the corporate data-access security and compliance. ***

 

Share this blog topic
Add to Digg it   Add to Twitter   Add to StumbleUpon   Add to Del.Icio.us   Add to Facebook   Add to Technorati   Add to Reddit   Add to YahooMyWeb   Add to Google bookmarks


Comments
 

This blog article does not have any comments.


  This blog article is locked. New comments are not accepted.
 
 
?>