Database Security

When commercial computers originally hit the marketplace, data was primarily stored in one or more sequential "flat" files with each data element usually taking up the same space in each record of the file. For example, a file might be made up of employee records with each record arranged so that the "employee ID" value was eight characters long starting in position 1, the "first name" value might be twenty characters long beginning in position 9, etc. Each application program had to be coded to read each record on its own, extracting each data element to be used based upon its fixed position within each record. But typical application programs usually involved many files of related data and needed to match each record in one file with corresponding records in other files based upon some key value, such as "employee number". Multiply this by all of the application systems run by an organization, and it becomes apparent that programmers spent far too much time writing mundane data element extraction and matching program code rather than keeping all of their attention on satisfying the business need. Worse yet, the complex, tedious nature of extracting, matching and combining data elements made it more difficult to incorporate basic security-related functions, such as securing individual data elements, giving different users different privileges, etc. There was just not enough time! 

So, some smart individuals began to design more efficient, independent file access systems that application programmers could use to facilitate their data management tasks. These access systems gathered much of the business system data into centrally-managed sets of data sets and provided developers with flexible, easy-to-use command set that allowed those who have the privileges to do so to add, change and delete database information, and to retrieve sets of stored data elements matched against other data, filtered by the value of any data element or set of elements and presented in a specific sort order.

Database technology went through a number of design iterations, improving at each iteration. These improvements were mostly the result of quantum leaps in computer processing speed and storage. For quite some time, most database technology is built on a “relational database” model, i.e., a system where data relationships can easily be built on the fly among "databases", “tables”, “rows” and “columns” (which used to be called "file groups", “files”, “records” and “fields”) without the need for excessive program code. Its primary programming language, called Structured Query Language or SQL, provides an enormous amount of flexibility that can address very complex business requirements with a fraction of the code and effort that would have been required without it.

The security benefits of databases

The relational database design's ability to simplify creating, accessing, updating and deleting data and to manage data access centrally provides many security-related benefits. As a "gatekeeper" to the data, relational database systems provide valuable security services that are consistent across all of the applications that use the database. These services include:

• Requiring that a user identify and authenticate himself or herself with a password or other authenticating technology
• Allowing a logged in user to access only the databases, tables, rows and columns to which he or she has been granted access, and to perform only the functions on those databases, tables, rows and columns that he or she has been permitted to perform. Access permissions can very granular, even to extent of limiting access based on the value of one or more data elements value. For example, a person could have access permissions defined whereby he or she may only see employee data for individuals whose salary is less than $50,000 and who live in Houston.
• Selectively encrypting certain sensitive data elements or the whole database when they are stored, and decrypting them only when they are requested by program code that is being run by an authorized user
• Enabling programmers to add commonly-used data-related program code to the database itself, so that customized ways of accessing and updating data elements could be shared among multiple applications without having to reinvent the wheel for each application
• Documenting the activities that are performed against the database in an audit log. The logs effectively can facilitate:
• The tracking of transactions that have been performed against the database and the user ID that initiated each transaction
• The "backing out" of specific transactions when necessary
• The forensic analysis of any suspected attacks on the database, both reactively and proactively
• The synchronization of all data elements across databases and their data tables to ensure that all of the database updates associated with a transaction are performed either completely or not at all

Database security and user management

As previous section indicates, database technology has incorporated access controls and other mechanisms that are based upon the identity of the user who is logged into the database. Thus, it would seem logical that the database administrator must maintain user privilege tables within the database that indicate what each individual user or group of users is authorized to do. But often, the application software that uses the database's services requires its own user privilege tables as well. For example, the application may need to determine is a user is allowed to perform certain restricted transactions. In these cases, maintaining two sets of user privilege tables (three if you count operating system privilege tables) can become an administrative burden, especially when the application has a large authorized user community or there is a large amount of staff turnover or user role changes. As a result, two security implementation approaches emerged, each having its own benefits and drawbacks.

Approach 1 – Native database-level user privilege management is used fully as designed

In this approach, access privileges are managed for each user and user group at the database level. The application is programmed in a manner where each user must provide his or her database user account and password to the application that the application in turn will pass to the database so that the database system knows the identity of the user. In this manner:

• All database log records contain the identity of the user who performed the transaction without having to be specifically programmed to do so
• With the user’s identity known by the database, access privileges can be tailored for each user within the database itself regardless of the application being used. This enforces access privilege consistency across different applications using the same database.
• If a user has access to one of many programs that can allow the user to generate ad hoc queries against the database, he or she can access records out of the context of the application software but only to the extent that the database permission tables allow. If a user cannot read a data element within the application, he or she cannot read it using an ad hoc query tool.

This approach requires that the database administrator maintain access privileges for each user in the database either by user or by user role.

Approach 2 – Each application performs all required database functions on its users' behalf

In this approach, one application account is defined for each application that uses the database.  When the application is executed, it logs into the database system using its own assigned account credentials, and subsequently performs all authorized, user-requested transactions against the database under the authority of its own application account. Any account privileges that relate to the application user are entered into the application by an application administrator through an administrative interface developed for the application that maintains the privilege authorization information within the application's privilege tables. Once the user is authenticated to the application software, the application refers to these privilege tables to determine what database transactions each user can or cannot perform. If the application determines that the database functions being requested are authorized for that user, it will read, add, update or delete data as requested on the user’s behalf. In this manner:

• Database administrators are not burdened with continually having to enter and modify access privileges in the database as users come and go or change roles. The application’s own internal user privilege table manages what each user can do both within the application and within the database.
• Since only the database administrator knows the application account name and password, no application user would have any direct access to the database with an ad hoc query and update tool unless he or she was given a database account of his or her own.

However,

• Each application must be programmed to allow application administrators to maintain individual user and group privileges
• Since the database only knows that the application account made all the query requests and made all the updates, no user accountability can be enforced through an analysis of the database logs alone. Rather, each application must be programmed to audit transactions at the user level.
• The application account and its password must be tightly controlled, and the privileges that are granted to the application account should be limited to permit only the functions that are absolutely necessary for the proper operation of the application software.

Which approach would be the best?

Both of the above approaches can be made to work in a secure manner. The decision of which one to choose is more driven by what resources will be available to develop, maintain and administer the access controls, both initially and ongoing. The Information Security Office can work with your application development and database teams to determine how best to secure the application. To schedule a review of your application requirements, contact the OIT Support Center at extension 2828 or supportcenter@uhcl.edu.

Views and column- and row-level security

For those who select approach 1 described above, databases allow you to restrict access to database information at the table level, at the column (data element) level and at the row (record) level on a per user or per user group basis. When a user needs to read, add, change or delete information in a database, he or she must be granted access by the administrator to the database itself, and to the specific tables and views that hold the data the user has been authorized to access. The privileges granted not only include the tables and views that can be accessed, but also how the user is permitted to access those tables and views. For example, a user may be able to read the data elements in a table but not update them. 

What is a view?

A view is basically a virtual table that represents a collection of data elements from a table or a set of database tables that have been joined together based upon a defined data relationship, e.g., two tables that hold social security number or SSN could be joined together by SSN to form a virtual table combining data elements from both. The use of views can be very helpful in a number of ways, most notably:

• Views can be used to define commonly used, complex data relationships across tables into a virtual table that can be used by other users without them having to "reinvent the wheel" each time they want to run a query that is built upon those relationships
• Views can be used to restrict access to individual columns and rows of a database table while allowing other data elements to be accessed

What is column-level security?

Individual columns in a table can be restricted merely by creating a view from the table that excludes any data element that a user of group of users should not be able to access. Those users or user groups could then only be granted access to the view, but not the underlying table, effectively hiding the sensitive data elements. For example, if one wanted to block a specific user from seeing the salary column in an employee table, he or she could set up a view up that includes all of the data elements of the employee table except the salary column. Any user who has access to the view but not the underlying table would not be able to access anyone's salary.

What is row-level security?

Users and user groups can also be restricted from accessing specific rows in a table based upon a certain condition. For example, we may want to allow a user or user group to see all employee data elements stored in the employee table, including salary, but only for employees whose salary is less than $50,000 per year. In this case, a view could be created that includes a conditional "where" clause indicating that only records with a salary value less than $50,000 would be included in the view. Thus, any user who has been granted access to the view but not to the underlying table would be stopped from viewing the restricted rows.

Granting and revoking access

Before a user of a database application that is set up using approach 1 above can access any database data, a database administrator must grant him or her the privilege to access the database itself and to the tables and views that he or she is authorized to access. Additionally, for the tables and views, the database administrator must specify the types of access that the user is being granted, i.e., to create/modify/delete tables, to read data (select), to add data (insert), to update data and/or to delete data. Once permissions are granted, the user can log into the database with a valid user ID and password and access the data as authorized. With approach 1, users will be able to access the database through a business application or through any available ad hoc database query and update tool that works with that specific database product.

Remember, if your database application is organized using approach 2 above, then the only database privileges that would need to be set up would be to grant the application's own user account the ability to access virtually all of the application's tables using all methods. Enforcement of user access rules must be performed within the application itself, so the application must maintain its own user privilege tables. On the positive side, since the user is not defined in the database's security tables, he or she cannot use an ad hoc database query and update tool to bypass the security built into the application, unless he or she knows the password for an application or user account that has been defined to the database. 

Methods of user authentication

Databases can be set up to use an existing directory that communicates via the Lightweight Directory Access Protocol or LDAP, such as Microsoft's Active Directory, or it can use its own internal tables to maintain user accounts and passwords. It should be noted that regardless of whether the password is defined to the database or to the LDAP directory, before anyone can access the database directly (i.e., not via an application program), he or she must have a user account defined to the database.  

Databases and encryption 

Encryption in databases is primarily used to protect individual data elements against someone taking a copy of a database and potentially seeing its sensitive data offline. Since the database encryption subsystem will automatically decrypt data for any authorized user who accesses the data, it provides no protection against someone who is using a valid user ID or is accessing the database through an authorized application. Encryption is not a way to stop a developer or tester in a testing environment from seeing sensitive production data in cases where the test database is merely a copy of the production system. That is the function of a data masking or obfuscation system.  Please visit the Data Obfuscation or "Masking" page for additional information about masking sensitive data in a test environment.