Method of partitioning a database

• US 8,645,423 B2
• Filed: 05/02/2008
• Issued: 02/04/2014
• Est. Priority Date: 05/02/2008
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Thuringer, John Neil, Jones, Simon, Bristow, Neil, Razdan, Ram, Sajja, Himavantha Rao
• Primary Examiner(s): Le, Debbie
• Assistant Examiner(s): BOWEN, RICHARD L
• Application Number:
  US12/114,532
• Publication Number:
  US 20090276477A1
• Time in Patent Office: 2,104 Days
• Field of Search: None
• US Class Current: 707/781
• 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

The invention is directed towards a method of partitioning data in a database. The database is configured to be accessed by an entity. The method comprises the steps of defining a first partition within the database, defining a second partition within the database and assigning selected data in the database to the first partition and the second partition, wherein data assigned to both the first partition and the second partition may only be accessed by an entity assigned to both said first partition and said second partition.

Claims

1. A method comprising:

defining, by a computer system, a first partition and a second partition within a multi-tenant database, wherein each of a plurality of tenants is assigned to one or more of the first and second partitions;

assigning, by the computer system, a first set of data stored in the database to both the first partition and the second partition, a second set of data stored in the database exclusively to the first partition, and a third set of data stored in the database exclusively to the second partition, wherein;

the first set of data is associated with a first instance of an application and a second instance of the application;

the second set of data is associated with only the first instance of the application;

the third set of data is associated with only the second instance of the application;

the first partition is associated with a first security type; and

the second partition is associated with a first physical location;

receiving, by the computer system, a request from an entity associated with one 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 partition, the second partition, or both the first partition and the second partition; and

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

the first datum is a member of the second data set assigned exclusively to the first partition, the tenant with which the entity is associated is assigned to at least the first partition, and the entity has security access for the first security type;

the first datum is a member of the third data set assigned exclusively to the second partition, the tenant with which the entity is associated is assigned to at least the second partition, and the entity is physically located within the first physical location;

or

the first datum is a member of the first data set assigned to both the first partition and the second partition, the tenant with which the entity is associated is assigned to both the first partition and the second partition, the entity has security access for the first security type, and the entity is physically located within the first physical location.

2. The method according to claim 1, wherein the method further comprises:

assigning a fourth set of data to one of the first and second partitions,

wherein an entity assigned to both the first and second partitions has permission to access the fourth set of data.

3. The method of claim 1, wherein a fourth set of data that is not assigned to the first partition or the second partition is accessible by the one or more entities.

4. The method of claim 2, wherein the entity is one of a user or the application.

5. The method of claim 1, wherein:

assigning, by the computer system, the first set of data comprises adding first and second columns to one or more data tables within the database, the first column representing one or more first level partitions and the second column representing one or more second level partitions, and

for one or more rows within a selected data table within the database, adding a first entry to the first column, the first entry representing assignment of the first set of data to the first partition, and adding a second entry to the second column, the second entry representing assignment of the first set of data to the second partition.

6. The method of claim 1, wherein the first partition represents one of:

an organization, a region, a legal grouping, a business unit, or a legal entity; and

the second partition represents another of;

an organization, 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 a set of instructions which, when executed by the processor, causes the processor to control access to data in partitions of a database by;

defining a first partition and a second partition within a multi-tenant database, wherein each of a plurality of tenants is assigned to one or more of the first and second partitions;

assigning a first set of data stored in the database to both the first partition and the second partition, a second set of data stored in the database exclusively to the first partition, and a third set of data stored in the database exclusively to the second partition, wherein;

the first set of data is associated with a first instance of an application and a second instance of the application;

the second set of data is associated with only the first instance of the application;

the third set of data is associated with only the second instance of the application;

the first partition is associated with a first security type; and

the second partition is associated with a first physical location;

receiving a request from an entity associated with one 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 partition, the second partition, or both the first partition and the second partition; and

granting access to the first datum to the entity if;

the first datum is a member of the second data set assigned exclusively to the first partition, the tenant with which the entity is associated is assigned to at least the first partition, and the entity has security access for the first security type;

the first datum is a member of the third data set assigned exclusively to the second partition, the tenant with which the entity is associated is assigned to at least the second partition, and the entity is physically located within the first physical location;

or

the first datum is a member of the first data set assigned to both the first partition and the second partition, the tenant with which the entity is associated is assigned to both the first partition and the second partition, the entity has security access for the first security type, and the entity is physically located within the first physical location.

8. The system according to claim 7, further comprising:

assigning a fourth set of data to one of the first and second partitions,

wherein an entity assigned to both the first and second partitions has permission to access the fourth set of data.

9. The system of claim 8, wherein the entity is one of a user or the application.

10. The system of claim 7, wherein a fourth set of data that is not assigned to the first partition or the second partition is accessible by the one or more entities.

11. The system of claim 7, wherein:

assigning the first set of data comprises adding first and second columns to one or more data tables within the database, the first column representing one or more first level partitions and the second column representing one or more second level partitions, and

for one or more rows within a selected data table within the database, adding a first entry to the first column, the first entry representing assignment of the first set of data to the first partition, and adding a second entry to the second column, the second entry representing assignment of the first set of data to the second partition.

12. The system of claim 7, wherein the first partition represents one of:

an organization, a region, a legal grouping, a business unit, or a legal entity; and

the second partition represents another of;

an organization, a region, a legal grouping, a business unit, or a legal entity.

13. A computer-readable memory comprising a set of instructions stored therein which, when executed by a processor, causes the processor to control access to data in partitions of a database by:

defining a first partition and a second partition within a multi-tenant database, wherein each of a plurality of tenants is assigned to one or more of the first and second partitions;

assigning a first set of data stored in the database to both the first partition and the second partition, a second set of data stored in the database exclusively to the first partition, and a third set of data stored in the database exclusively to the second partition, wherein;

the first set of data is associated with a first instance of an application and a second instance of the application;

the second set of data is associated with only the first instance of the application;

the third set of data is associated with only the second instance of the application;

the first partition is associated with a first security type; and

the second partition is associated with a first physical location;

receiving a request from an entity associated with one 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 partition, the second partition, or both the first partition and the second partition; and

granting access to the first datum to the entity if;

the first datum is a member of the second data set assigned exclusively to the first partition, the tenant with which the entity is associated is assigned to at least the first partition, and the entity has security access for the first security type;

the first datum is a member of the third data set assigned exclusively to the second partition, the tenant with which the entity is associated is assigned to at least the second partition, and the entity is physically located within the first physical location;

or

the first datum is a member of the first data set assigned to both the first partition and the second partition, the tenant with which the entity is associated is assigned to both the first partition and the second partition, the entity has security access for the first security type, and the entity is physically located within the first physical location.

14. The computer-readable memory of claim 13, further comprising:

assigning a fourth set of data to one of the first and second partitions,

wherein an entity assigned to both the first and second partitions has permission to access the fourth set of data.

15. The computer-readable memory of claim 14, wherein the entity is one of a user or the application.

16. The computer-readable memory of claim 13, wherein a fourth set of data that is not assigned to the first partition or the second partition is accessible by the one or more entities.

17. The computer-readable memory of claim 13, wherein:

assigning the first set of data comprises adding first and second columns to one or more data tables within the database, the first column representing one or more first level partitions and the second column representing one or more second level partitions, and

for one or more rows within a selected data table within the database, adding a first entry to the first column, the first entry representing assignment of the first set of data to the first partition, and adding a second entry to the second column, the second entry representing assignment of the first set of data to the second partition.

18. The computer-readable memory of claim 13, wherein the first partition represents one of:

an organization, a region, a legal grouping, a business unit, or a legal entity; and

the second partition represents another of;

an organization, a region, a legal grouping, a business unit, or a legal entity.