By Tim Boles

Previously, SELECT published Why Your Database is Not Secure: A Prelude to the COLLABORATE 2019 Session. Over the next few weeks, I thought I would dive in a little deeper into the subject of database security based on that session. The presentation had four topic areas based on the time, effort, risk, and costs to perform. This article provides an overview on three areas that I believe are the lowest in time, effort, risks, and costs. I call these “Easy Pickings.”

Easy Pick 1: Database Parameters

One area often overlooked during upgrades and migrations is the parameter file.  This sometimes results in the setting of parameters unnecessarily and some of those parameters can have security implications. I would recommend evaluating the entire initialization file but of special concern are hidden (underscore) parameters and deprecated parameters.

Hidden Parameters

Hidden parameters should be left on the default value unless set at the recommendation of Oracle support.  During software and database upgrades, review these parameters with Oracle support check their validity on the new system.  If you are new to a system, find out why they are explicitly set.  It is easy to check for hidden parameters that are explicitly set in the initialization parameter file using the V$PARAMETER view (Figure 1).

Note: Parameters explicitly set to the default value will also be part of the query results.

Deprecated Parameters

Deprecated parameters are ones that may be removed in future versions of the database. In preparation of the future, evaluate all deprecated parameters and work to move away from them.  Again, use the V$PARAMETER view and find deprecated parameters that are explicitly set within the initialization parameter file (Figure 2).

Additionally, these deprecated parameters may also have security implications if set to non-default values.  In my experience, the parameters O7_DICTIONARY_ACCESSIBILITY and UTL_FILE_DIR are two of the highest offenders found in these scans. These two parameters provide a higher level of access to the database dictionary and system files than is recommended by Oracle.  There are better and more secure ways of dealing with these types of access. I would advise working with vendors and software developers to move away from the utilization of these two parameters.

Other Parameters That Effect Security

The DBA should understand parameters that directly relate to security and know the risks associated with them. Some of the more predominant ones are shown in Figure 3.

Easy Pick 2: Implement Least Privileges

Security best practices highlight granting the minimal privileges needed by a user based on their assigned work. I have found that there are four common reasons users receive more privileges than necessary.

• DBAs taking the easy way out and granting SELECT ANY TABLE particularly when packages and procedures are not working
• Assignment of DBA role to developers/analysts
• No review of privileges when people switch company roles
• Assignment “based on” another user

Considering compromised accounts are the most often used avenue in data breaches this should be of great concern. Limiting what individuals have access to in the database reduces what a hacker can do with a compromised account.

At the writing of this paper, Oracle had released the Privilege Analysis feature for use on Oracle Database Enterprise Edition version 12c and above as a no cost option. Use this feature to help identify the privileges need for a user or role.

There are four steps to privilege analysis (Figure 4).

• Define the type of capture to perform in a policy.
• Enable the capture policy at an appropriate time.
• Disable the capture after it has been running over the period of time.
• Generate and review the results for possible privileges to adjust.

The privilege analysis tool has four different types of analysis it can do.

• G_DATABASE: Analyzes all privileges used (except by the SYS user)
• G_ROLE: Analyzes privileges related to specified roles
• G_CONTEXT: Analyzes privileges based on a condition parameter set using the SYS_CONTEXT function
• G_ROLE_AND_CONTEXT: Analyzes privileges for specific roles based on a condition set using the SYS_CONTEXT function

Example of Analysis Feature Usage

I have worked in shops where the developers were granted DBA and/or RESOURCE roles. I believe the DBA role is generally overkill for the work assigned to most developers. As for the RESOURCE role or use of other Oracle provided roles, Oracle might change the privileges of provided roles in future releases, so I prefer to not grant those roles directly to users. A recommended approach would be to issue a capture for a normal workweek for a developer. Analyze the results of the capture and then assign privileges directly to a developer role and assign that role to the user(s). The following series of steps show an example of how to analyze the privileges being used by a specific developer and tailor a role for the developer.

First Create and Enable Capture Policy

In this example (Figure 5), a capture policy named TEST_DEV_POL of type G_ROLE_AND_CONTEXT is created and enabled. This type of policy allows the capture to focus on the DBA and RESOURCE role utilization of the user TEST_DEV.

Disable Capture Policy and Generate Results

After the period of interest is over disable the capture and generate the results (Figure 6). The results are stored in the system tables and accessed using the various DBA_USED_* and DBA_UNUSED_* privilege views.

Review Privilege Analysis

In this example (Figure 7), querying the DBA_USED_PRIVS view allows the determination of sys and object privileges used by TEST_DEV user.  It shows as well which role relates to the privileged used.

The interesting thing is that you might find that the user TEST_DEV is not assigned the USED_ROLE shown.  In this example, the IMP_FULL_DATABSE role has not been assigned to the TEST_DEV user.  The IMP_FULL_DATABASE role is actually assigned to the DBA role which has been assigned to the TEST_DEV user.  You will need to understand the entire inheritance path of the role. You can check this using one of the DBA_USED_*_PATH views (Figure 8).

Adjust Privileges

For users that have responsibilities that are only used periodically, you may need to do several runs of the privilege capture.  Think of users that work in finance.  Finance users may have privileges that are used only during month-end or year-end activities. It may take a little research and a couple of runs of privilege captures at different periods of time but you can eventually use the views to understand the path to the privilege(s) used.

To make it easy to revert privilege changes you should create a new role (Figure 9) that contains the need privileges and assign it to the user.  You can then revoke other roles.  If problems arise then you can fall back easy to the previous roles and revoke the new role.

Items to Note

There are a few things that you should remember when using this feature. The CAPTURE_ADMIN role is needed to access the views generated. You should get a good breadth of captures over different time periods and system usage to ensure you understand the complete user privileges needed. You should always verify on a test system, particularly on a regression test system before implementing changes in a production environment. Use new roles to assign privileges where possible. It is easier to revoke and assign roles rather than on the privileges individually.

Easy Pick 3: Perform Security Scans

During my session at COLLABORATE 2019, I asked for a show of hands of individuals that perform security scans on their databases regularly.  The number of positive responses impressed me.  Performing these types of scans has become so easy using the Database Security Assessment Tool (DBSAT) there is no excuse for not performing them.  The Oracle Database Security Assessment Tool helps identify where your database configuration, operation, or implementation has introduced risks to the system.  What I find impressive as well is the information it provides about the issue and the recommended changes given to mitigate any identified risks. DBSAT has the following features:

• FREE – Included with active support contract (supports all versions starting at 10g)
• Quick low impact security assessment
• Can scan for sensitive data
• Provides benchmark recommendations
• Support for regulatory compliance
• Promote best practices

Using DBSAT for Security Scans

DBSAT has two components (Figure 10) that work together to provide detailed evaluations and reports on the security of a database.

1. The Collector: Does just as the name suggests. It collects data from the database and OS using a series of SQL queries and operating system commands.
2. The Reporter: Performs an analysis of the collected data and generates a Database Security Assessment Report. The nice thing about the reporter is that it can be run on any machine but needs the Java Runtime Environment (JRE) version 1.8+ as of the time of this writing.

The Database Security Assessment tool collector interacts with the database as well as the OS and creates a JSON file. The Jason file can be run through the database security assessment tool reporter, which analyzes the findings and able to create four different types of report formats.

• HTML for interactive reviews.
• TEXT in case data needs to be cut and pasted into other formats
• XLS  for creating checklists, work requests, or customizing information related to findings
• JSON for ingestion into other tools.

Collecting and reporting on security is a simple 2 step process using dbsat:

From my experience, it has negligible impact on the system. DBSAT reports the results of its analysis in the form of a series of Findings.

Pictured here (Figure 11) is a snip-it from the summary page of the DBSAT HTML report.  This summary page allows you to quickly review areas and select any you want to review in more detail.

Actionable Results

The finding notices (Figure 12) give risk levels, a summary, the recommendation, details of what the finding is about and references where appropriate.

Using DBSAT for Sensitive Data Scans

There is the third component to the DBSAT called the Discoverer. This is a stand-alone module for the DBSAT used to locate and report sensitive data in the database. I think of it in this manner:

• Collector captures security information.
• Discoverer searches for possible sensitive data.

The focus of the Collector and Discoverer (Figure 13) are separate and distinct. Working with the Discover feature of the DBSAT can be intense depending on what exactly you want to produce.  The dbsat.conf file is used to set up connection information and the behavior of the discoverer.  The most intensive area is setting up the sensitive_pattern_files.  You really need to know your data to adjust the pattern matching to meet your requirements and system

Because of all the possible setup (Figure 14) and tuning, I don’t really classify the Discoverer as “Easy Pickings.” Once you have set up all the configuration files the actual execution of DBSAT Discover is easy.  If dbsat.config is your configuration file and report is the destination directory then:

The resulting Discoverer report is called a Database Sensitive Data Assessment Report.  This report has a summary (Figure 15) and actionable information similar to the security report.

One of the most important jobs of a DBA is to secure the organization’s data.  Database security does not occur overnight, it is a step by step progression and diligence.  Reviewing database parameters, evaluation of the privileges and database security scans using DBSAT are all easy checks that do not really take much time to complete.  I encourage all DBAs to take these initial first steps in minimizing the risk of unwanted exposure of their organization’s data.

About the Author

Tim Boles is a Senior Manager at Hitachi Consulting and a member of the SELECT Journal 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!