Virtually partitioning user data in a database system

• US 6,587,854 B1
• Filed: 05/31/2001
• Issued: 07/01/2003
• Est. Priority Date: 10/05/1998
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Andersen, Jesper, McMahon, Douglas James, Guthrie, Christine Pae, Lei, Chon Hei
• Primary Examiner(s): Mizrahi, Diane D.
• Assistant Examiner(s): Wu, Yicun
• Application Number:
  US09/872,896
• Time in Patent Office: 761 Days
• Field of Search: 709/202, 709/203, 707/5, 707/1, 707/9, 707/10, 707/100, 340/5.54, 235/382
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: G06F 16/24575 using context
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• CPC Class Codes: G06F 2221/2141 Access rights, e.g. capability lists, access control lists, access tables, access matrices
• CPC Class Codes: Y10S 707/99939 Privileged access

Abstract

A method and apparatus are provided for storing data of multiple enterprises in a set of database objects in a database system and allowing multiple enterprises to interact with the database system as if those database objects contained only their data. According to an aspect of the present invention, a database command issued against a database object by a user is modified by adding predicates that limit access to data associated with the enterprise in which the user has data access. The predicates may specify conditions based on a column in the database object that identifies the enterprise. When a user issues a database command to add data to the database object, the column is populated in a manner transparent to the user. The data in the database object that is associated with a particular enterprise is stored in a separate tablespace.

Claims

1. A method for managing enterprise access to data, the method comprising the steps of:

detecting that a database command is issued by a user against a database object containing records for a plurality of users that includes said user;

before executing the database command against the database object, creating a modified database command based on the database command and issued against the same database object by selectively adding zero or more predicates to said database command that specify one or more criteria that is;

satisfied by records containing data for said user, and not satisfied by records containing data for other users of said plurality of users; and

executing said modified database command.

2. The method of claim 1,

wherein said database object includes a column that contains values, wherein each value of said values identifies a particular user from said plurality of users; and

wherein said one or more criteria is based on said column.

3. The method of claim 2, wherein said zero or more predicates are based on said column.

4. The method of claim 1, wherein the method further includes the step of storing a subset of the records which contain data for each user of said plurality of users in a partition contained in a separate tablespace.

5. The method of claim 4, wherein the method further includes the steps of:

receiving queries that request to store the records in the database object, and

determining which partition to store a particular record of said records based on the identity of each user of said plurality of users.

6. The method of claim 1, wherein prior to executing said modified database command, performing one or more query optimization operations based on said modified database command.

7. The method of claim 1, wherein records containing data for each user of said users are stored in a separate tablespace of a plurality of tablespaces.

8. The method of claim 1, further including the steps of

detecting that a second database command is issued by said user against another database object;

and

determining, independently of the identity of the user, which records in said other database object satisfy said second database command.

9. The method of claim 1, wherein the user is associated with a session for which a plurality of context attributes has been established;

and

wherein predicates to add to said database command are based at least in part on current values of one or more of said plurality of context attributes.

10. The method of claim 1, further including the steps of

invoking a policy function associated with said database object;

and

said policy function generating the zero or more predicates to add to said database command.

11. The method of claim 10, wherein:

the user is associated with a session for which a plurality of context attributes has been established; and

the policy function determines which predicates to add to said database command based at least in part on current values of one or more of said plurality of context attributes.

12. A method for managing access to data stored in a database system, the method comprising the steps of:

adding a column to a database object containing records for users;

transparent to said users, storing a value in the column for each row in the database object to indicate a user associated with the data in said each row, wherein said users do not expressly specify storing the value in the column for each row in the database object, and said value is generated according to metadata indicating how to generate values for said column; and

satisfying queries issued by a particular user against said database object by providing data only from those rows in the database object that contain a value in the column identifying the particular user.

13. The method of claim 12, wherein the step of storing a value in each row includes said database system storing a default value generated according to metadata indicating how to generate values for said column.

14. The method of claim 12, wherein

the user is associated with a session for which a plurality of context attributes has been established; and

the method further includes the step of generating the value based on one or more context attributes that have been established for the session for the user.

15. The method of claim 12, wherein records containing data for each user of said users are stored in a separate tablespace of a plurality of tablespaces.

16. A computer-readable medium carrying one or more sequences of instructions for managing user access to data, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

detecting that a database command is issued by a user against a database object containing records for a plurality of users that includes said user;

before executing the database command against the database, creating a modified database command based on the database command and issued against the same database object by selectively adding zero or more predicates to said database command that specify one or more criteria that is;

satisfied by record containing data for said user, and not satisfied by records containing data for other users of said plurality of users; and

executing said modified database command.

17. The computer-readable medium of claim 16,

wherein said database object includes a column that contains values, wherein each value of said values identifies a particular user from said plurality of users; and

wherein said one or more criteria is based on said column.

18. The computer-readable medium of claim 17, wherein said zero or more predicates are based on said column.

19. The computer-readable medium of claim 16, wherein the steps further include the step of storing a subset of the records which contain data for each user of said plurality of users in a partition contained in a separate tablespace.

20. The computer-readable medium of claim 19, wherein the steps further include the steps of:

receiving queries that request to store the records in the database object, and

determining which partition to store a particular record of said records based on the identity of each user of said plurality of users.

21. The computer-readable medium of claim 16, wherein the steps further include performing one or more query optimization operations based on said modified database command prior to executing said modified database command.

22. The computer-readable medium of claim 16, wherein records containing data for each user of said users are stored in a separate tablespace of a plurality of tablespaces.

23. The computer-readable medium of claim 16, wherein the steps further include the steps of

detecting that a second database command is issued by said user against another database object;

and

determining, independently of the identity of the user, which records in said other database object satisfy said second database command.

24. The computer-readable medium of claim 16, wherein the user is associated with a session for which a plurality of context attributes has been established;

and

wherein predicates to add to said database command are based at least in part on current values of one or more of said plurality of context attributes.

25. The computer-readable medium of claim 16, further including the steps of invoking a policy function associated with said database object;

and

said policy function generating the zero or more predicates to add to said database command.

26. The computer-readable medium of claim 25, wherein:

the user is associated with a session for which a plurality of context attributes has been established; and

the policy function determines which predicates to add to said database command based at least in part on current values of one or more of said plurality of context attributes.

27. A computer-readable medium for managing access to data stored in a database system, the computer-readable medium comprising the steps of:

adding a column to a database object containing records for users;

transparent to said users, storing a value in the column for each row in the database object to indicate a user associated with the data in said each row, wherein said users do not expressly specify storing the value in the column for each row in the database object, and said value is generated according to metadata indicating how to generate values for said column; and

satisfying queries issued by a particular user against said database object by providing data only from those rows in the database object that contain a value in the column identifying the particular user.

28. The computer-readable medium of claim 27, wherein the step of storing a value in each row includes said database system storing a default value generated according to metadata indicating how to generate values for said column.

29. The computer-readable medium of claim 27, wherein

the user is associated with a session for which a plurality of context attributes has been established; and

the steps further include the step of generating the value based on one or more context attributes that have been established for the session for the user.

30. The computer-readable medium of claim 27, wherein records containing data for each user of said users are stored in a separate tablespace of a plurality of tablespaces.