SECURE NORMAL FORMS

• US 20090094193A1
• Filed: 10/09/2007
• Published: 04/09/2009
• Est. Priority Date: 10/09/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: WIGENSTAM, ROGER, KING, NIGEL, BUZSAKI, GEORGE
• Granted Patent: US 8,078,595 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries

Abstract

Techniques for implementing secure normal forms are provided. In one embodiment, in response to a request for data from a client, a database server is operable to enforce a column-level security policy that is defined for a particular column of some, but not necessarily all, rows of a table stored in a database. After retrieving a set of rows from the table and before returning a result set of rows to the client, the database server modifies the retrieved set of rows into the result set of rows by applying the security policy to each row of the retrieved set of rows, where applying the security policy to a row comprises: determining whether the security policy is satisfied for the particular column of that row; replacing, in the result set of rows, a data value in the particular column of that row with a security-NULL value when the security policy is not satisfied; and including the data value in the particular column of that row into the result set of rows when the security policy is satisfied. After generating the result set of rows, the database server returns the result set of rows to the client.

Claims

1. A computer-implemented method comprising:

at a database server, receiving from a client a request for data that is stored at least in part in a particular column of a table in a database managed by the database server;

wherein the database includes a security policy that is defined for the particular column of one or more particular rows stored in the table;

at the database server, processing the request and retrieving a set of rows from the database, wherein each row of the set of rows includes the particular column of the table;

after retrieving the set of rows and before sending a result set of rows to the client, at the database server modifying the set of rows into the result set of rows by applying the security policy to said each row of the set of rows, wherein applying the security policy to said each row comprises;

determining whether the security policy is satisfied for the particular column of said each row;

replacing, in the result set of rows, a data value in the particular column of said each row with a security-NULL value when the security policy is not satisfied for the particular column of said each row; and

including the data value in the particular column of said each row into the result set of rows when the security policy is satisfied for the particular column of said each row; and

after modifying the set of rows into the result set of rows, at the database server returning the result set of rows to the client.

2. The method of claim 1, wherein:

the security policy comprises metadata that specifies one or more conditions, wherein the one or more conditions indicate permissions for accessing one or more data values in the particular column of the one or more particular rows stored in the table; and

determining whether the security policy is satisfied comprises evaluating the one or more conditions for the particular column of said each row.

3. The method of claim 2, wherein the permissions for accessing the one or more data values are associated with one or more roles that are assigned to one or more users of the database.

4. The method of claim 2, wherein the metadata of the security policy is stored in one or more database objects that are stored in the database.

5. The method of claim 1, wherein the one or more particular rows are a subset of all rows stored in the table.

6. The method of claim 1, wherein processing the request comprises executing the request without semantically modifying the request to retrieve different data than the data specified in the request.

7. The method of claim 1, wherein the security policy is further defined, in the database, for one or more other columns of one or more other rows that are stored in one or more other tables.

8. The method of claim 1, wherein:

the table includes a second column that is different than the particular column;

the database includes a second security policy that is defined for the second column of one or more second rows stored in the table;

said each row of the set of rows includes the second column of the table; and

modifying the set of rows into the result set of rows further comprises applying the second security policy to said each row of the set of rows, wherein applying the second security policy to said each row comprises;

determining whether the second security policy is satisfied for the second column of said each row;

replacing, in the result set of rows, a second data value in the second column of said each row with the security-NULL value when the second security policy is not satisfied for the second column of said each row; and

including the second data value in the second column of said each row into the result set of rows when the second security policy is satisfied for the second column of said each row.

9. The method of claim 1, wherein:

the request for data further requests data that is stored in a second column of a second table in the database;

the database includes a second security policy that is defined for the second column of one or more second rows stored in the second table;

said each row of the set of rows includes the second column of the second table; and

modifying the set of rows into the result set of rows further comprises applying the second security policy to said each row of the set of rows, wherein applying the second security policy to said each row comprises;

determining whether the second security policy is satisfied for the second column of said each row;

replacing, in the result set of rows, a second data value in the second column of said each row with the security-NULL value when the second security policy is not satisfied for the second column of said each row; and

including the second data value in the second column of said each row into the result set of rows when the second security policy is satisfied for the second column of said each row.

10. The method of claim 1, wherein modifying the set of rows into the result set of rows is performed by a query engine included in the database server.

11. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, cause a database server to perform:

receiving from a client a request for data that is stored at least in part in a particular column of a table in a database managed by the database server;

wherein the database includes a security policy that is defined for the particular column of one or more particular rows stored in the table;

processing the request and retrieving a set of rows from the database, wherein each row of the set of rows includes the particular column of the table;

after retrieving the set of rows and before sending a result set of rows to the client, modifying the set of rows into the result set of rows by applying the security policy to said each row of the set of rows, wherein applying the security policy to said each row comprises;

determining whether the security policy is satisfied for the particular column of said each row;

replacing, in the result set of rows, a data value in the particular column of said each row with a security-NULL value when the security policy is not satisfied for the particular column of said each row; and

including the data value in the particular column of said each row into the result set of rows when the security policy is satisfied for the particular column of said each row; and

after modifying the set of rows into the result set of rows, returning the result set of rows to the client.

12. The computer-readable storage medium of claim 11, wherein:

the security policy comprises metadata that specifies one or more conditions, wherein the one or more conditions indicate permissions for accessing one or more data values in the particular column of the one or more particular rows stored in the table; and

the instructions that cause the database server to perform determining whether the security policy is satisfied comprise instructions which, when executed by the one or more processors, cause the database server to perform evaluating the one or more conditions for the particular column of said each row.

13. The computer-readable storage medium of claim 12, wherein the permissions for accessing the one or more data values are associated with one or more roles that are assigned to one or more users of the database.

14. The computer-readable storage medium of claim 12, wherein the metadata of the security policy is stored in one or more database objects that are stored in the database.

15. The computer-readable storage medium of claim 11, wherein the one or more particular rows are a subset of all rows stored in the table.

16. The computer-readable storage medium of claim 11, wherein the instructions that cause the database server to perform processing the request comprise instructions which, when executed by the one or more processors, cause the database server to perform executing the request without semantically modifying the request to retrieve different data than the data specified in the request.

17. The computer-readable storage medium of claim 11, wherein the security policy is further defined, in the database, for one or more other columns of one or more other rows that are stored in one or more other tables.

18. The computer-readable storage medium of claim 11, wherein:

the table includes a second column that is different than the particular column;

the database includes a second security policy that is defined for the second column of one or more second rows stored in the table;

said each row of the set of rows includes the second column of the table; and

the instructions that cause the database server to perform modifying the set of rows into the result set of rows further comprise instructions which, when executed by the one or more processors, cause the database server to perform applying the second security policy to said each row of the set of rows, wherein applying the second security policy to said each row comprises;

determining whether the second security policy is satisfied for the second column of said each row;

replacing, in the result set of rows, a second data value in the second column of said each row with the security-NULL value when the second security policy is not satisfied for the second column of said each row; and

including the second data value in the second column of said each row into the result set of rows when the second security policy is satisfied for the second column of said each row.

19. The computer-readable storage medium of claim 11, wherein:

the request for data further requests data that is stored in a second column of a second table in the database;

the database includes a second security policy that is defined for the second column of one or more second rows stored in the second table;

said each row of the set of rows includes the second column of the second table; and

the instructions that cause the database server to perform modifying the set of rows into the result set of rows further comprise instructions which, when executed by the one or more processors, cause the database server to perform applying the second security policy to said each row of the set of rows, wherein applying the second security policy to said each row comprises;

determining whether the second security policy is satisfied for the second column of said each row;

replacing, in the result set of rows, a second data value in the second column of said each row with the security-NULL value when the second security policy is not satisfied for the second column of said each row; and

including the second data value in the second column of said each row into the result set of rows when the second security policy is satisfied for the second column of said each row.

20. The computer-readable storage medium of claim 11, wherein the instructions that cause the database server to perform modifying the set of rows into the result set of rows are included in a query engine that is operable under the control of the database server.