Strong Database Security
Today’s database systems have sophisticated security mechanisms designed to enable the protection of data and the tracking of the viewing and modification of the data. For example, Oracle has database roles that govern what database objects a user can see and edit. Oracle also has auditing features that enable us to track the activity of users. SQL Server has similar security features. When implemented properly, these features can be very effective at helping us secure our data.
Generic Application Logins
Unfortunately, the design of many applications, especially web applications, results in a circumvention of these sophisticated security mechanisms. Many applications are designed to use just one highly privileged login to the database for all users. In these scenarios, users log in to the application and the application controls the security from that point on.
Application security schemes generally allow the administrator to specify which functions a user can access or execute, instead of which database objects they can see or edit. Access to these functions is generally provided by means of a role or profile.
This arrangement makes it very difficult to know which users actually have access to a specific database object and it makes it even more difficult to track such activity.
Bridge the Gap
When applications are designed in this manner, it is critical to ensure that we understand what objects can be accessed by users in order to protect sensitive information. This involves a number of things:
- User Rights Data: A list documenting which roles or profiles each application user has been assigned to. Ideally, this information would be kept in a table that could be queried for this information.
- Role or Profile Rights Data: A list documenting which functions each application role or profile have access to. Ideally, this information would also be kept in a table that could be queried for this information.
- System Function Metadata: A list documenting each function in the application, which database objects that function provides access to, and the type of access (update,delete,etc). Ideally, this information would be kept in a meta data table that could be queried for this information.
- A cross reference that combines the above three sets of data and maps each application user to the database objects they can access, as well as what level of access they have to the object.
For packaged applications, most of this information is likely available in tables in the database, but the challenge may be finding out where it is kept. For applications that are developed in-house, it is important to document the location of this information so that it can be used for auditing purposes.
Make it Accessible
Best practice is to develop an application that can access this detailed user privilege information and provide easy report and query functionality. This is the cross reference data that combines the rights data and the system function meta data described above. Making this information easily available (and of course securing it) can help bridge the gap between database and application security.
How has your organization dealt with this issue? Let us know what methods you have used and what areas you found challenging.
Contact Buda Consulting to see how we can help you design an effective strategy for securing your data.
