Systems, methods, and machine-readable memories for partitioning a database

• US 9,489,407 B2
• Filed: 01/30/2014
• Issued: 11/08/2016
• Est. Priority Date: 05/02/2008
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Bristow, Neil, Thuringer, John Neil, Jones, Simon, Razdan, Ram, Sajja, Himavantha Rao
• Primary Examiner(s): BOWEN, RICHARD L
• Application Number:
  US14/168,689
• Publication Number:
  US 20140149460A1
• Time in Patent Office: 1,013 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures

Abstract

Systems, methods, and machine-readable memories are provided for partitioning a multi-tenant database between tenants. A first level partition of data in a table within the database is defined. A second level partition of the data in the table within the database is defined. A request is received from an entity associated with a tenant to access a first datum stored in the database. The tenant is determined to be assigned to one or both of the first level partition and the second level partition. Access to the first datum is granted to a first entity where the tenant corresponds to the first tenant and the first datum corresponds to the first level partition parameter value. Access to the first datum is granted to a second entity where the tenant corresponds to the second tenant and the first datum corresponds to the second level partition parameter value.

Claims

1. A method for partitioning a multi-tenant database between a plurality of tenants, the method comprising:

defining, by a computer system, a first level partition of data in a table within the database, the first level partition defined by a column of the table associated with the first level partition, each row of the column associated with the first level partition storing a first partition parameter value indicating whether data of the row of the table is assigned to the first level partition;

defining, by the computer system, a second level partition of the data in the table within the database, the second level partition defined by a column of the table associated with the second level partition, each row of the column associated with the second level partition storing a second partition parameter value indicating whether data of the row of the table is assigned to the second level partition;

receiving, by the computer system, a request from an entity associated with a tenant of the plurality of tenants to access a first datum stored in the database;

determining, by the computer system, whether the tenant with which the entity is associated is assigned to the first level partition, the second level partition, or both the first level partition and the second level partition, wherein the determining comprises identifying that a first tenant is uniquely assigned to the first level partition and/or a second tenant is uniquely assigned to the second level partition, wherein an assignment of the first level partition and an assignment of second level partition are mutually exclusive; and

granting, by the computer system, access to the first datum to;

a first entity where the tenant corresponds to the first tenant and the first datum corresponds to the first level partition parameter value for a row of the table in which the first datum is stored;

a second entity where the tenant corresponds to the second tenant and the first datum corresponds to the second level partition parameter value for the row of the table in which the first datum is stored.

2. The method of claim 1, wherein granting access to the first datum based on the first level partition parameter value and the second level partition parameter value for a row of the table in which the first data is stored comprises granting access to the first datum to the entity if the first datum is defined as exclusively assigned to the first level partition, the tenant with which the entity is associated is assigned to at least the first level partition, and the entity has security access for a first security type.

3. The method of claim 1, wherein granting access to the first datum based on the first level partition parameter value and the second level partition parameter value for a row of the table in which the first data is stored comprises granting access to the first datum to the entity if the first datum is defined as exclusively assigned to the second level partition, the tenant with which the entity is associated is assigned to at least the second level partition, and the entity is physically located within a first physical location.

4. The method of claim 1, wherein granting access to the first datum based on the first level partition parameter value and the second level partition parameter value for a row of the table in which the first data is stored comprises granting access to the first datum to the entity if the first datum is defined as assigned to both the first level partition and the second level partition, the tenant with which the entity is associated is assigned to both the first level partition and the second level partition, the entity has security access for a first security type, and the entity is physically located within a first physical location.

5. The method of claim 1, wherein the first level partition represents an organization.

6. The method of claim 5, wherein the second level partition represents one of a region, a legal grouping, a business unit, or a legal entity.

7. A system comprising:

a processor; and

a memory coupled with and readable by the processor and storing therein a set of instructions which, when executed by the processor, causes the processor to partition a multi-tenant database between a plurality of tenants by;

defining a first level partition of data in a table within the database, the first level partition defined by a column of the table associated with the first level partition, each row of the column associated with the first level partition storing a first partition parameter value indicating whether data of the row of the table is assigned to the first level partition;

defining a second level partition of the data in the table within the database, the second level partition defined by a column of the table associated with the second level partition, each row of the column associated with the second level partition storing a second partition parameter value indicating whether data of the row of the table is assigned to the second level partition;

receiving a request from an entity associated with a tenant of the plurality of tenants to access a first datum stored in the database;

determining whether the tenant with which the entity is associated is assigned to the first level partition, the second level partition, or both the first level partition and the second level partition, wherein the determining comprises identifying that a first tenant is uniquely assigned to the first level partition and/or a second tenant is uniquely assigned to the second level partition, wherein an assignment of the first level partition and an assignment of second level partition are mutually exclusive; and

granting access to the first datum to;

a first entity where the tenant corresponds to the first tenant and the first datum corresponds to the first level partition parameter value for a row of the table in which the first datum is stored;

a second entity where the tenant corresponds to the second tenant and the first datum corresponds to the second level partition parameter value for the row of the table in which the first datum is stored.

8. The system of claim 7, wherein granting access to the first datum based on the first level partition parameter value and the second level partition parameter value for a row of the table in which the first data is stored comprises granting access to the first datum to the entity if the first datum is defined as exclusively assigned to the first level partition, the tenant with which the entity is associated is assigned to at least the first level partition, and the entity has security access for a first security type.

9. The system of claim 7, wherein granting access to the first datum based on the first level partition parameter value and the second level partition parameter value for a row of the table in which the first data is stored comprises granting access to the first datum to the entity if the first datum is defined as exclusively assigned to the second level partition, the tenant with which the entity is associated is assigned to at least the second level partition, and the entity is physically located within a first physical location.

10. The system of claim 7, wherein granting access to the first datum based on the first level partition parameter value and the second level partition parameter value for a row of the table in which the first data is stored comprises granting access to the first datum to the entity if the first datum is defined as assigned to both the first level partition and the second level partition, the tenant with which the entity is associated is assigned to both the first level partition and the second level partition, the entity has security access for a first security type, and the entity is physically located within a first physical location.

11. The system of claim 10, wherein the first level partition represents an organization and the second level partition represents one of a region, a legal grouping, a business unit, or a legal entity.

12. A machine-readable memory comprising a set of instructions stored therein which, when executed by a processor, causes the processor to partition a multi-tenant database between a plurality of tenants by:

defining a first level partition of data in a table within the database, the first level partition defined by a column of the table associated with the first level partition, each row of the column associated with the first level partition storing a first partition parameter value indicating whether data of the row of the table is assigned to the first level partition;

defining a second level partition of the data in the table within the database, the second level partition defined by a column of the table associated with the second level partition, each row of the column associated with the second level partition storing a second partition parameter value indicating whether data of the row of the table is assigned to the second level partition;

receiving a request from an entity associated with a tenant of the plurality of tenants to access a first datum stored in the database;

determining whether the tenant with which the entity is associated is assigned to the first level partition, the second level partition, or both the first level partition and the second level partition, wherein the determining comprises identifying that a first tenant is uniquely assigned to the first level partition and/or a second tenant is uniquely assigned to the second level partition, wherein an assignment of the first level partition and an assignment of second level partition are mutually exclusive; and

granting access to the first datum to;

a first entity where the tenant corresponds to the first tenant and the first datum corresponds to the first level partition parameter value for a row of the table in which the first datum is stored;

a second entity where the tenant corresponds to the second tenant and the first datum corresponds to the second level partition parameter value for the row of the table in which the first datum is stored.

13. The machine-readable memory of claim 12, wherein granting access to the first datum based on the first level partition parameter value and the second level partition parameter value for a row of the table in which the first data is stored comprises granting access to the first datum to the entity if the first datum is defined as exclusively assigned to the first level partition, the tenant with which the entity is associated is assigned to at least the first level partition, and the entity has security access for a first security type.

14. The machine-readable memory of claim 12, wherein granting access to the first datum based on the first level partition parameter value and the second level partition parameter value for a row of the table in which the first data is stored comprises granting access to the first datum to the entity if the first datum is defined as exclusively assigned to the second level partition, the tenant with which the entity is associated is assigned to at least the second level partition, and the entity is physically located within a first physical location.

15. The machine-readable memory of claim 12, wherein granting access to the first datum based on the first level partition parameter value and the second level partition parameter value for a row of the table in which the first data is stored comprises granting access to the first datum to the entity if the first datum is defined as assigned to both the first level partition and the second level partition, the tenant with which the entity is associated is assigned to both the first level partition and the second level partition, the entity has security access for a first security type, and the entity is physically located within a first physical location.

16. The machine-readable memory of claim 12, wherein the first level partition represents an organization.

17. The machine-readable memory of claim 16, wherein the second level partition represents one of a region, a legal grouping, a business unit, or a legal entity.