Reconciling foreign key references and table security policies

Patent metadata

Field	Value
Publication number	US-10437832-B2
Application number	US-201715592780-A
Country	US
Kind code	B2
Filing date	May 11, 2017
Priority date	May 19, 2016
Publication date	Oct 8, 2019
Grant date	Oct 8, 2019

Abstract

Some database tables include a relational field, where a record of the table stores a foreign-key reference value that identifies a related record of a second table. While fulfilling a request comprising a query over the database, the system may identify a context of the request, wherein the context is supplemental to the query, and wherein the context relates to the relational field. For example, a security policy may permit a requester to query the first table, but restrict the requester from accessing the second table that the relational field of the first table references, where dereferencing the relational field may cause unexpected results. Instead, the system may evaluate the context of the request, and may fulfill the request based on the context by providing a response that substitutes, for the value that references the second table, a substitute value that does not reference the second table.

First claim

What is claimed is:

1. A system that provides access to a database comprising a first table comprising a relational field comprising values that reference records of a second table of the database, the system comprising:
a processor; and
a memory storing instructions that, when executed by the processor, cause the system to:
receive a request comprising a query to select a record of the first table, where the record includes, for the relational field, a value that references a record of the second table;
identify a context of the request, wherein the context is supplemental to the query;
determine that the context of the request is not associated with access to the second table;
in response to the determination that the context of the request is not associated with access to the second table, generate a response that substitutes, for the value that references the record of the second table, a substitute value that does not reference any record of the second table; and
providing the response in response to the request.

2. The system of claim 1 , wherein the instructions, when executed by the processor, cause the system to:
for contexts of respective requests, identify a context-specific substitute value for the relational field when requested in the context; and wherein
providing of the record further comprises: responsive to a request in a context in which access to the second table is not permitted:
identification of the context-specific substitute value of the relational field for the context of the request; and
provision of the record responsive to the query with the context-specific substitute value for the relational field.

3. The system of claim 1 , wherein identification of the substitute value further comprises:
for a particular context, storing, in the table, a context-specific copy of the record that is provided for requests in the particular context, and
for respective contexts that are not permitted to access the second table, storing the substitute value for the relational field in the context-specific copy of the record; and
providing the records further comprises: responsive to a request in a specific context, provide the context-specific copies of the record that is responsive to the query and for the specific context of the request.

4. The system of claim 3 , wherein the instructions, when executed by the processor, cause the system to:
responsive to updating an updated value of a non-relational field of the record represented by at least two context-specific copies, propagate the updated value of the non-relational field to all context-specific copies of the record.

5. The system of claim 3 , wherein storage of the context-specific copy of the record further comprises:
partitioning the record into a non-protected portion and the relational field; and
storing the record as:
one copy of the non-protected portion of the record, and
context-specific copies of the relational field for the respective contexts; and

providing the records further comprises: responsive to receiving a request in a selected context, for the respective records that are responsive to the query, providing:
the one copy of the non-protected portion of the record; and
the context-specific copy of the relational field according to the selected context of the request.

6. The system of claim 5 , wherein:
respective access modes are applicable only to tables that satisfy at least one access mode condition; and
storage of the access mode further comprises: responsive to the configuration selection of the access mode, storage of the access mode for the relational field in the database configuration only after verifying that the table satisfies the access mode condition of the access mode.

7. The system of claim 1 , wherein the instructions, when executed by the processor, cause the system to:
indicate an access mode for the relational field, wherein the access mode is selected from an access mode set comprising:
a single-record master context access mode, and
a duplicate-record access mode; and

based on a configuration selection of an access mode for a relational field of a table, store, in a configuration of the database, the access mode for the relational field of the table.

8. A computer-readable storage medium storing instructions that cause a system to provide access to a database comprising a first table comprising a relational field comprising values that reference records of a second table of the database, by:
receiving a request comprising a query to select a record of the first table, where the record includes, for the relational field, a value that references a record of the second table;
identifying a context of the request, wherein the context is supplemental to the query;
determine that the context of the request is not associated with access to the second table;
in response to the determination that the context of the request is not associated with access to the second table, generating a response that substitutes, for the value that references the record of the second table, a substitute value that does not reference any record of the second table; and
providing the response in response to the request.

9. The computer-readable storage medium of claim 8 , wherein the instructions further cause the system to:
for contexts of respective requests, identify a context-specific substitute value for the relational field when requested in the context; and wherein
providing of the record further comprises: responsive to a request in a context in which access to the second table is not permitted:
identification of the context-specific substitute value of the relational field for the context of the request; and
provision of the record responsive to the query with the context-specific substitute value for the relational field.

10. The computer-readable storage medium of claim 8 , wherein identification of the substitute value further comprises:
for a particular context, storing, in the table, a context-specific copy of the record that is provided for requests in the particular context, and
for respective contexts that are not permitted to access the second table, storing the substitute value for the relational field in the context-specific copy of the record; and
providing the records further comprises: responsive to a request in a specific context, provide the context-specific copies of the record that is responsive to the query and for the specific context of the request.

11. The computer-readable storage medium of claim 10 , wherein the instructions further cause the system to:
responsive to updating an updated value of a non-relational field of the record represented by at least two context-specific copies, propagate the updated value of the non-relational field to all context-specific copies of the record.

12. The computer-readable storage medium of claim 10 , wherein storage of the context-specific copy of the record further comprises:
partitioning the record into a non-protected portion and the relational field; and
storing the record as:
one copy of the non-protected portion of the record, and
context-specific copies of the relational field for the respective contexts; and

providing the records further comprises: responsive to receiving a request in a selected context, for the respective records that are responsive to the query, providing:
the one copy of the non-protected portion of the record; and
the context-specific copy of the relational field according to the selected context of the request.

13. The computer-readable storage medium of claim 8 , wherein the instructions further ca

Assignees

• Microsoft Technology Licensing Llc

Inventors

• Blaaberg Lars Dragheim
• El Husseini Ahmad Mahdi
• Sadasivam Ganapathi Subramanian
• Gall Michael
• Anderson Jeffrey

Classifications

• G06F16/24575 Primary

  using context · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/20

  of structured data, e.g. relational data · CPC title

• G06F16/284

  Relational databases · CPC title

• G06F16/248

  Presentation of query results · CPC title