Secure normal forms

• US 8,078,595 B2
• Filed: 10/09/2007
• Issued: 12/13/2011
• Est. Priority Date: 10/09/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: King, Nigel, Buzsaki, George, Wigenstam, Roger
• Primary Examiner(s): Cottingham, John R.
• Assistant Examiner(s): Allen, Nicholas E
• Application Number:
  US11/869,618
• Publication Number:
  US 20090094193A1
• Time in Patent Office: 1,526 Days
• Field of Search: 707/757, 726/1, 726 26- 30
• US Class Current: 707/694
• 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 query requesting 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 query 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

wherein both invoking the security policy and the step of replacing are not a result of rewriting the query;

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;

wherein the method is performed by one or more computing devices.

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 query comprises executing the query without semantically modifying the query to retrieve different data than the data specified in the query.

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 query 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.

21. The method of claim 1, wherein the security policy is defined in terms of one or more entity attributes, further comprising:

at the database server, determining that the particular column stores data represented by the one or more entity attributes;

in response to determining that the particular column stores data represented by the one or more entity attributes, at the database server, mapping the security policy to the particular column.

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 query requesting 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 query 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

wherein both invoking the security policy and the step of replacing are not a result of rewriting the query;

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 query comprise instructions which, when executed by the one or more processors, cause the database server to perform executing the query without semantically modifying the query to retrieve different data than the data specified in the query.

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 query 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.

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

the security policy is defined in terms of one or more entity attributes; and

the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the database server to perform;

determining that the particular column stores data represented by the one or more entity attributes;

in response to determining that the particular column stores data represented by the one or more entity attributes mapping the security policy to the particular column.