By Tim Boles

This is the third article in a series on Database Security. The previous article Easy Pickings for Database security, covered security practices that are the lowest in time, effort, risks, and costs. This article covers what I call the Low Hanging Fruit of Oracle Database security. They are a cost a little more in terms of time and effort but really introduce little if any risk or additional costs. I don’t consider them easy pickings because they will need to be tested and their usage integrated into your current systems.

First Fruit: Unified Auditing Mixed Mode

There are two aspects of traditional auditing that always has been of concern or an inconvenience. I believe that the greatest vulnerability with traditional auditing is the ability for a superuser to change the audit records. The biggest pain with traditional auditing has been that system configuration could write audit records to multiple locations hampering the DBAs ability to report or analyze audit records (Figure 1).

Most shops standardize the way they do auditing across systems which helps. Yet when a new team member comes on board or the shop takes over support of an established system the setup can be confusing. The Unified auditing capability of Oracle overcomes these issues. There are four things that Unified Auditing has over traditional auditing (Figure 2).

• Simplifies audit configuration because when the database is open all audit records are sent to a single repository.
• You can set up additional auditing capabilities including auditing activities from sources like RMAN, SQL*Loader direct loads, Data Pump, and Real Application Security just to name a few.
• The audit reporting is simplified with a single view UNIFIED_AUDIT_TRAIL to access all audit records.
• Security for Audit records is increased by:
  • Saving data in a single read-only table in Oracle Secure Files propriety formatted files.
  • The owner of the audit objects (AUDSYS) being locked by default.
  • Audit structures like the UNIFIED_AUDIT_TRAIL view are read-only.

I call this a low hanging fruit because starting with Oracle 12c by default databases are in what is called unified audit mixed mode. This means that your traditional auditing will work but you can also use unified auditing. You can have your standard auditing that you have had in place for years until you are satisfied that the unified audit policies you set are capturing all the information you need. This can let you get comfortable using the unified audit framework and how to maintain it and still have your previous setup in place. Leaving traditional auditing in place allows extended time to get comfortable using the unified auditing framework and update auditing processes to use that framework.

Second Fruit: READ Privilege

In the past the best privilege that you could use to limit privileges on an object as the SELECT privilege. Oracle 12c introduced the READ privilege. This privilege was created to overcome a possible security issue that exists with the SELECT privilege.

The SELECT privilege allows you to do a SELECT FOR UPDATE query. SELECT FOR UPDATE does Row Locks and can do exclusive row locks. It locks the table/row until a ROLLBACK or COMMIT is executed. This could allow someone that has only been given select privileges the ability to prevent the system from updating tables (Figure 3). The READ privilege does not allow the FOR UPDATE clause (Figure 3).

I consider this low hanging fruit because you could create a script that would change SELECT privilege to READ privilege for appropriate users. And you could easily have in place a script that resets it. However, this type of change would need to be evaluated and tested. You could at least push the granting of READ for all future requests for “READ ONLY” access.

Third Fruit: New Administrative Accounts

Many Oracle shops still assign SYSDBA privileges to the DBAs and the DBAs use it constantly. When someone logs in with SYSDBA privileges they are logging in as the SYS user (Figure 4). The SYS user has 219 system privileges, 60 role privileges, and all object grants on all schemas. This is overkill for most work. I am a firm believer in limiting privileges of users including administrators to only the ones they need to perform their job.

Oracle has attempted to follow the segregation of duties concept by creating new administrative users for specific tasks. The following system administrators exist in Oracle 12c+.

• SYSBACKUP used for RMAN operations
• SYSDG used for Data Guard operations
• SYSKM used for the transparent data encryption key store operations

These accounts are tailored for these types of administrative activities and contain a limited set of privileges. These new administrators each have administrative privileges with the same name as the user. For example, SYSDG privileges are associated with the SYSDG user (Figure 4). These users are, by default, locked, and is best practices to leave them locked and just assign the privilege of the same name to the individual users that need it.

Each role has been assigned privileges that cover the focus of work. Notice how the number of privileges is greatly reduced from the SYSDBA privileges (Figure 5).

I consider implementing these roles as low hanging fruit because although you would need to test out any scripts and processes already in place you could maintain the current user, scripts, and processes until new ones are thoroughly tested and implement. The implementation could be done in waves or even script by script and process by process.

I believe that all DBAs should be moving quickly towards implementing the Easy Pickings and Low Hanging Fruit security that has been covered in the last couple of articles. We will be talking in the new few weeks about security tasks that should still be within every DBAs reach but they may have to be On Your Tip-Toes to reach.

About the Author

Tim Boles is a Senior Manager at Hitachi Vantara and a member of the IOUG SELECT editorial board. He is an educator, having taught high school students, college students, and producing technical courses for Pluralsight. Tim is a computer scientist and businessman, holding a Computer Science Master’s Degree from West Virginia University and an MBA from the University of Phoenix. You can contact Tim at [email protected], follow him on twitter @timboles_dba, or view his courses at www.pluralsight.com.

Interested in Writing for SELECT?

Are you interested in getting your writing published and sharing your knowledge with other Oracle users? Quest Oracle Community is looking for writers to contribute to the SELECT Journal for our Quest IOUG Database & Technology Community audience. We are looking for writers with expertise in a variety of topics, including the following:

• Database sharding
• Database security
• Database performance
• Database development
• Database auditing
• And more!