Selective and total query redaction

Techniques are provided for selectively or completely redacting the text of database commands submitted to a database system. The database server receives the clear text version of the commands, parses the commands, and generates an execution plan, as normal. However, prior to providing the text of the commands to any location that is externally visible, the database server determines whether the command qualifies as “sensitive”. If the command qualifies as sensitive, then a redacted version of the command is generated. In the case of selective redaction, portions of the redacted version remain in clear text, while selected portions are replaced with encrypted text. In the case of total redaction, the entire command is replaced with encrypted text.

FIELD OF THE INVENTION

The present invention relates to query redaction and, more specifically, to selectively or totally redacting the text of queries issued to a database system.

BACKGROUND

Many users use database systems to store sensitive data. Modern database systems have extensive security capabilities to protect user data from theft, insider threat, and unauthorized access. These controls are all focused around protecting the user data that is managed by the database systems.

Unfortunately, the user data that is managed by a database system may not be the only sensitive information. In particular, there are situations when analysts or applications want privacy when querying the database, because their interest in a situation, company, customer, person or device etc. is highly sensitive. In these cases, the question/query may even be more sensitive than the answer/data.

For example, consider the query:

From Person P

This query clearly shows an analyst is looking for the first and last name of the person with social security number “011-45-4167”. This query may be issued, for example, as part of an investigation. It may be critical that the specific people targeted by the investigation remain secret.

The following queries are additional examples of queries where the query itself may reveal information that is confidential:

The following query looks up a Credit Card for a charge:

From Customer C

and C.Card_Exp>Sysdate;

The following query expresses interest in people with Financial Infractions over $1000.00:

From Person P, Per_of_interest POI

and P.Amount>1000;

The following query reflects investigative interest in people with names similar to John Smith:

From Person

The follow query may indicate a reduction in force for people with large sales exceeding an expense threshold:

from Sales

where Revenue >2,000,000

and (Expenses/Revenue)>35%;

Even when there is no data that satisfies a sensitive query, such as those set forth above, the query text is very sensitive and visible through (a) a shared cache (e.g. the Shared Global Area (SGA) of Oracle database systems) which stores the clear text version of the queries that have been issued to the database system), (b) tracing/logging tools, (c) SQL_Capture tools, and (d) audit tools. The shared cache is a shared memory area where a database system caches the SQL statements that it has received. Typically, anybody with the appropriate privileges can query the SGA and see the clear text versions of those cached SQL statements.

In conventional database systems, there is no way to hide a sensitive query. Consequently, instead of issuing sensitive queries that specifically target desired information, analysts instead ask very broad non-specific queries, hoping that the specific information they desire is somewhere within the results of the broader query. The results of such broad queries are typically much larger than the desired information, and must be further extracted or refined privately by the end user so as to not expose the true intent of the query. This strategy adds much more overhead to the system, complexity to the process, and latency to the answer, simply to try and maintain some semblance of privacy and to try to avoid the exposure the true interest.

DETAILED DESCRIPTION

General Overview

Techniques are provided for selectively or completely redacting the text of the database commands submitted to a database system. The database server receives the clear text version of the commands, parses the commands, and generates an execution plan, as normal. However, prior to providing the text of the commands to any location that is externally visible, the database server determines whether the command qualifies as “sensitive”. If the command qualifies as sensitive, then a redacted version of the command is generated. In the case of selective redaction, portions of the redacted version remain in clear text, while selected portions are replaced with encrypted text. In the case of total redaction, the entire command is replaced with encrypted text.

Techniques are described hereafter for encrypting sensitive query text and bind values with an authorized user/session Query Encryption Key (“QEK”). With total command redaction, SQL text and bind values remain encrypted at all times other than parse and execution time. Audit events and system logging occurs as normal, except that query text and bind values will be encrypted when written. In one embodiment, when a log includes a redacted command, an identifier of the QEK (a “QEK ID”) is also recorded in the log. Thus, the encrypted data of a redacted command can only be unencrypted with the appropriate QEK, and which QEK is appropriate may be determined by the QEK ID in the log.

Database Command Redaction Overview

FIG. 1is a flowchart illustrating general steps for database command redaction, according to an embodiment. At step100, the database server receives a database command. At step102, the database server parses and executes the command based on the clear text version of the database command. At step104, the database server determines whether the database command should be selectively or totally redacted to external sources.

If, at step104, it is determined that the command is not to be selectively or totally redacted, then control proceeds to step106and the clear text version of the command is made available to the external sources. Thus, at step106, the clear text version of the command may be stored in the Shared Global Area of the computing device on which the database server is executing, written to an audit log, etc.

On the other hand, if it is determined at step104that the command is to be selectively or totally redacted, then control proceeds to step108and a redacted version of the command is generated. At step110, the redacted version of the command is made available to the external sources. The steps of determining whether to redact a command, and generating a redacted command shall be described in greater detail hereafter.

Determining Whether to Redact a Database Command

According to an embodiment, not all database commands received by a database system are redacted. Thus, as illustrated in step104ofFIG. 1, the database server must determine whether a database command qualifies for redaction. In one embodiment, a database command is not redacted unless the database server receives an indication that redaction should be performed.

A database server that employs the command redaction techniques described herein may support various levels of redaction indicators. For example, in one embodiment, redaction may be indicated by:

a database-wide system parameter

a session-specific system parameter

a user-specific system parameter

a command-specific flag

In addition, command redaction may be based on what data is targeted by a database command. Thus, a database server may be configured to redact database commands that:

target a particular table or a particular set of tables

target a particular column or a particular set of columns

target a particular table partition, or a particular set of table partitions

target a particular tablespace, or a particular set of tablespaces

Command redaction may also be based on other criteria. For example, command redaction may be automatically performed for all queries that are submitted during a certain time period, such as on particular days or during particular hours. As another example, command redaction may be automatically performed based on the number of rows returned by a query. For example, command redaction may be automatically performed when a query produces a single result row, or when the number of rows returned by the query is less than a threshold number, or is less than a certain percentage of all rows in the targeted table.

These are merely examples of the various ways redaction may be indicated and/or triggered. The techniques described herein are not limited to any particular manner of indicating that query redaction should be performed.

In one embodiment, certain users are granted a “redacted session” role, which is reflected in their user accounts. Users with such a role may initiate redacted sessions. During a redacted session, all database commands (or a subset of commands, such as queries) are redacted using a user-specific or session-specific QEK. In the case of a session-specific QEK, the user may set a QEK with a password, or request that the database server automatically generate a QEK for the session. The QEK may then be stored in a secure location within the database system, such as the user's “wallet” or other secure storage.

The following is an example of interactions with a database system to authorize a user to use database command redaction. A DBA or User Account Manager grants the Redacted Session role to a user by submitting the following database command:SQL> Grant Redacted command to user1 identified by ‘Pass Phrase’ |Wallet|External Key Manager;

The Key Manager or a User with the Redacted command role creates and sets a query encryption key for a user. Multiple QEKs may be specified for the same user. When a user has multiple QEKs, the QEKs may be used in a round-robin fashion, where the QEK for the user alternates from session to session, or from command to command within a session.

Two query keys can be created for user1, for example, by submitting the following database commands:SQL> Administer Key Management Create Key TAG ‘QEK:USER1:KEY1’ identified by welcome1;SQL> Administer Key Management Create Key TAG ‘QEK:USER1:KEY2’ identified by welcome1;

After the keys are created for user1, a current key may be established for user1 by submitting one of the following database commands:SQL> Administer Key Management use Key using TAG ‘QEK:USER1:KEY1’ identified by welcome1;SQL> alter user USER1 set key ‘KEY1’ identified by welcome1;
Generating a Redacted Version of a Database Command

Once the database server determines that a command qualifies for redaction, a redacted version of the command is generated, as is illustrated in step108ofFIG. 1. According to one embodiment, the redacted version of a database command is created by replacing clear text in the command with encrypted text. The encrypted text that is used to replace a particular string of clear text is created by encrypting the particular string of clear text using a Query Encryption Key (“QEK”). For example, assume that the following query is subject to total redaction:

From Person P

Because the query is subject to total redaction, the entire text of the original query string is encrypted, which may produce the encrypted SQL text:

As illustrated in step110ofFIG. 1, the database server provides this encrypted SQL text to all external sources where the SQL text itself would normally be provided. For example, this encrypted SQL text is stored in the SGA of the database server, and is stored in all logs where the SQL itself would normally appear. Thus, the redacted version of the database command is stored in a log where the clear text version would have been stored if the database command had not qualified as sensitive.

For selective redaction, the redacted version of a command is created by encrypting selected portions of the original query using a QEK. The portions that are not encrypted remain in their original clear text form. For example, assume that only the specific social security number that is targeted by the query:

From Person P

is to be redacted. Under these circumstances, the redacted version of the query may be:

From Person P

According to one embodiment, when selective redaction is performed, different parts of the database command may be encrypted using different QEKs. For example, social security numbers may be encrypted using one QEK, while phone numbers may be encrypted using a different QEK. By using different QEKs for different parts of a selectively-redacted database command, different decryption keys will be required to recover the clear text of those portions. By making different decryption keys available to different parties, one may control which information, within redacted database commands, is available to different parties.

According to one embodiment, in a situation where all queries in an entire session are to be redacted, the interactions with the database may proceed as illustrated inFIG. 2. Referring toFIG. 2, at step1, a database client establishes a connection to the database server, to ensure network communication is encrypted. At step2, the database system validates the user's connection to ensure that the user has been authorized with the Redacted command role. In one embodiment, the redacted command role can be enabled by default or enabled on demand by the user/application. The following commands are examples of how the session can be set to Redacted_Query and how to specify the QEK that is to be used during the session.SQL> alter session set role Redacted_Command identified by welcome1;SQL> alter session set QEK using TAG ‘QEK:USER1:KEY5’ identified by welcome1;SQL> alter session set QEK using TAG ‘QEK:USER1:KEY23’ identified by welcome1;

At step3, the database system retrieves the appropriate QEK from a secure location “keystore” in the database. The secure location may be, for example, a user-specific wallet or a key vault.

At step4, the database client submits SQL statements to the database server for execution as normal. The fact that Redacted_Command is in effect is transparent to the application, so no application changes are required.

At step5, the database server parses, validates and encrypts the text and bind values of the relevant types of database commands. For example, in a database system that applies redaction only to SELECT statements, only the text and bind variables of those statements are redacted. Other types of statements will be processed as normal.

At step6, the database server executes the statements independent of whether the SQL text and bind values are encrypted. The database system can decrypt redacted text or bind values as needed if required for execution.

As illustrated inFIG. 3, the SQL Text and bind values are maintained in an encrypted state when:Cached in SGA or any cache that is visible to database administrators and/or other users, andPersisted in logs, such as Audit Trails, etc.
Recovering the Original Clear Text Version of the Command

Significantly, because the encrypted SQL text in the redacted version of a command is an encryption of the SQL that it is replacing, the original clear text SQL can be reproduced from the redacted version of the command by anyone with access to the appropriate decryption key. Thus, when the following encrypted SQL is encountered in an audit log:

a user with the appropriate decryption key can reproduce the original SQL text:

From Person P

In the same manner, selectively redacted commands are encrypted in such a way that one having the proper decryption key can reproduce the original SQL text. For example, the following selectively redacted command:

From Person P

may be converted back to the original clear text command by applying the appropriate decryption key to the encrypted text “F&77B0B3”. Doing so would reproduce the social security number specified in the original SQL command (i.e. ‘011-45-4167’).

Identifying the Encryption Key

As mentioned above, it is desirable to allow users that have the appropriate decryption key to recover the clear text version of a redacted command based on the redacted version of the query. However, to do so, it is necessary for those users to determine which decryption key is the appropriate decryption key.

According to one embodiment, to assist users to determine the appropriate decryption key to decrypt a redacted command, a “QEK identifier” is stored as part of the redacted version of the query. A QEK identifier is any data from which the QEK used to create a redacted version of a database command may be determined. For example, upon creating a QEK for a user, the database server may generate a unique identifier for the QEK. The QEK identifier for a QEK is stored in conjunction with any redacted command that includes data encrypted using the QEK. For example, in the log generated for a session whose commands are encrypted using Key1, the identifier for Key1 may be stored.

The granularity at which QEK identifiers are stored in a log may vary based on the scope at which the corresponding QEK was used. For example, when a single QEK is used to redact all commands in a session, then the QEK identifier for the QEK may be stored once for the entire log of the session. On the other hand, multiple QEKs are used within a session, then a QEK identifier may be stored in conjunction with each command the QEK was used to redact. In the case where different QEKs are used to redact different portions of the same command (e.g. one QEK used to redact social security numbers and a different QEK used to redact telephone numbers), then the corresponding QEK identifiers may be stored in association with the portions of the database command they were used to redact.

In one embodiment, instead of or in addition to having user/session-specific command redaction, a database system may support policy-based command redaction. With policy based command redaction, what is redacted is based on stored policies. The policies may be simple or complex. For example, one policy may be to redact individual social security numbers using encryption with a particular QEK. Another policy may be to encrypt the entire commands of all users who are at the vice-president level or higher during the last three weeks of every quarter.

When multiple policies call for the encryption of the same piece of information, the coarser-granularity encryption is used. For example, if one policy calls for an entire command to be encrypted using QEK1, and another policy calls for a portion of the command to be encrypted using QEK2, then the entire command is encrypted using QEK1. In an alternative embodiment, a piece of data may be encrypted multiple times. Thus, QEK2 may be used to encrypt the portion of the command to produce a selectively redacted command, and then that selectively redacted command may be encrypted using QEK1 to produce a fully-redacted command.

Redacted Results

According to one embodiment, when a query is redacted, the database server redacts the results of the query using the same QEK as was used to redact the query. For example, assume that a user submits a query, in a redacted session, to retrieve certain rows from the database system. Assume further that all queries in the redacted session are being encrypted with Key1. Under these circumstances, Key1 is also used by the database server to encrypt the rows selected by the query prior to providing those rows back to the application that submitted the query. Once received at the application, those results may be decrypted using Key1.

According to one embodiment, upon starting a redacted session, a user may specify whether the query results are to be encrypted. If no result encryption is indicated, then the query is redacted by encrypting the query based on a QEK as described above, but the query results are returned without being encrypted using that same QEK.

Redacted Session Context

In one embodiment, some or all of a user's session context with a database system may be redacted. For example, when a user submits a query or Data Manipulation Language command (DML), the database system tracks the username, IP address of client, hostname of client, client program, and time. In one embodiment, some or all of these pieces of information are redacted from audit trails, logs, etc.

Hardware Overview

For example,FIG. 3is a block diagram that illustrates a computer system300upon which an embodiment of the invention may be implemented. Computer system300includes a bus302or other communication mechanism for communicating information, and a hardware processor304coupled with bus302for processing information. Hardware processor304may be, for example, a general purpose microprocessor.

Computer system300further includes a read only memory (ROM)308or other static storage device coupled to bus302for storing static information and instructions for processor304. A storage device310, such as a magnetic disk, optical disk, or solid-state drive is provided and coupled to bus302for storing information and instructions.

Computer system300may implement the techniques described herein using customized hard-wired logic, one or more ASICs or FPGAs, firmware and/or program logic which in combination with the computer system causes or programs computer system300to be a special-purpose machine. According to one embodiment, the techniques herein are performed by computer system300in response to processor304executing one or more sequences of one or more instructions contained in main memory306. Such instructions may be read into main memory306from another storage medium, such as storage device310. Execution of the sequences of instructions contained in main memory306causes processor304to perform the process steps described herein. In alternative embodiments, hard-wired circuitry may be used in place of or in combination with software instructions.

The received code may be executed by processor304as it is received, and/or stored in storage device310, or other non-volatile storage for later execution.

Cloud Computing