Methods and systems for controlling access to custom objects in a database

• US 8,095,531 B2
• Filed: 10/02/2007
• Issued: 01/10/2012
• Est. Priority Date: 10/03/2006
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Weissman, Craig, Oliver, Kevin, Jasik, Benji, Doshi, Kedar
• Primary Examiner(s): Lee, Wilson
• Assistant Examiner(s): Le, Jessica N
• Application Number:
  US11/866,184
• Publication Number:
  US 20080082540A1
• Time in Patent Office: 1,561 Days
• Field of Search: 707/3, 707/9
• US Class Current: 707/713
• CPC Class Codes: G06F 16/176 Support for shared access to files; File sharing support
• CPC Class Codes: G06F 16/289 Object oriented databases
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99939 Privileged access

Abstract

In embodiments, methods and systems for controlling access to custom objects are provided. These techniques for controlling access to custom objects can enable embodiments to utilize a key for the protection of the security of data that is to remain private while not compromising efficiency of a query. The key for a requested custom object is identified and then used so that only an appropriate portion of a custom entity share table is searched to locate access information. It is then determined whether the user can access at least a portion of the custom object, and the appropriate and allowed data is sent to the user.

Claims

1. A computer-implemented method for controlling access to objects in a database, wherein the database stores data specific to each one of a plurality of tenants such that at least two of the tenants store at least a portion of data specific to the at least two tenants in a common table within the database and wherein each tenant is permitted access only to data associated with that tenant, and wherein each tenant has one or more users, the method comprising:

(a) receiving, from a user associated with a first tenant, a request to access data of a first object in the common table of the database, the first object having a unique identification, wherein the common table includes a plurality of objects associated with the first tenant, and wherein the plurality of objects includes the first object, a second object, and a third object, each containing one or more data types specified by the first tenant;

(b) using information of the first object to identify a key associated with the first object and the third object, but not associated with the second object

wherein the key includes a key prefix that equals a prefix of the unique identification, the unique identification having additional characters after the prefix;

(c) retrieving the identified key from a table of the database;

(d1) after retrieving the key, searching only that portion of a entity share table appropriate to the retrieved key to locate access information for the first object;

(d2) selecting, by a processor, at least one rule from a plurality of permission rules that prevent a user associated with a particular tenant from seeing data in the database associated with the particular tenant to which the user is not permitted access;

(e) determining whether the user has permission to access at least a portion of the first object based at least in part on the access information; and

(f) sending, to the user, the requested data of the first object to which the user has permission to access.

2. The method of claim 1 wherein the request includes a request for data of other objects, and further compromising repeating steps (b), (c), (d1), (d2), and (e) for the requested data of the other objects.

3. The method of claim 1 wherein the entity share table includes access information for each object of the first tenant and includes another key associated with the second object associated with the first tenant, and wherein the access information includes a respective listing of users and/or groups of users that have access rights to each object and/or a portion of that object.

4. The method of claim 1 wherein the access information includes at least one of information related to the requestor'"'"'s sharing position and count of entities owned.

5. The method of claim 1 wherein the key is contained in the request.

6. The method of claim 1, wherein searching only that portion of a entity share table appropriate to the key to locate access information for the first object includes:

searching the database for the first object in a way that is likely to yield results using less search time than any alternate queries that would be formulated based on the request.

7. The method of claim 1, wherein searching only that portion of a entity share table appropriate to the key to locate access information for the first object includes:

creating an improved query so that the improved query indicates to the database how to search the data in the database that the user is permitted to search in a way that is likely to be improved over a way that the request received would have been searched in the database.

8. The method of claim 1, wherein searching only that portion of a entity share table appropriate to the key to locate access information for the first object includes:

improving a query for the request by rewriting or generating different Structured Query Language (SQL) based at least in part upon at least one of a tenant-level sharing model, statistics and metadata.

9. The method of claim 8, wherein improving the query received by rewriting or generating different SQL based at least in part upon a tenant-level sharing model, statistics and metadata includes:

rewriting or generating the improved query with an extra join in the denormalized-indexed table.

10. The method of claim 1, wherein searching only that portion of a entity share table appropriate to the key to locate access information for the first object includes:

rewriting or generating SQL of a query for the received request to provide a hint in the improved query, wherein the hint instructs a relational database management system to lead the query with an object determined to be likely to produce improved query results when a user has access to only a small percentage subset of rows in a portion of the common table associated with the tenant.

11. The method of claim 10, further comprising:

accessing a table that stores the number of rows of the common table that a user

and/or groups of users can access to determine when a user has access to only a small percentage

subset of rows in a portion of the common table associated with the tenant.

12. The method of claim 1, wherein searching only that portion of a entity share table appropriate to the key to locate access information for the first object includes:

rewriting or generating SQL of a query for the received query to provide an improved query that instructs a relational database management system to perform a hash semijoin against a full set of rows that are visible to a user when the user is not an administrator.

13. The method of claim 1, wherein searching only that portion of a entity share table appropriate to the key to locate access information for the first object includes:

rewriting or generating SQL of a query for the received query to provide an improved query that leads with the key and performs a nested loop to the entity share table when the query leads with a small number of entity rows.

14. The method of claim 1, wherein determining whether the user has permission to access at least a portion of the first object based at least in part on the access information includes accessing at least one of:

a role hierarchy metadata table that lists the number of people below a user; and

a group membership metadata table that lists the users that belong to a group.

15. The method of claim 1, wherein the common table includes at least two objects associated with a second tenant.

16. The method of claim 1, wherein at least one of the two objects is a table.

17. The method of claim 1, wherein the table from which the key is retrieved in (c) is the common table of the database.

18. The method of claim 1, wherein the first object and the second object are custom objects.

19. A machine-readable storage medium storing a plurality of instructions for programming one or more processors to control access to objects in a database, wherein the database stores data specific to each one of a plurality of tenants such that at least two of the tenants store at least a portion of data specific to the at least two tenants in a common table within the database and wherein each tenant is permitted access only to data associated with that tenant, and wherein each tenant has one or more users, the instructions including:

(a) code that causes the one or more processors to receive, from a user associated with a first tenant, a request to access data of a first object in the common table of the database, the first object having a unique identification, wherein the common table includes a plurality of objects associated with the first tenant, and wherein the plurality of objects includes the first object, a second object, and a third object, each containing one or more data types specified by the first tenant;

(b) code that causes the one or more processors to identify a key associated with the first object and the third object, but not associated with a second object, wherein the key is associated with more than one user of the first tenant and the key includes a key prefix that equals a prefix of the unique identification, the unique identification having additional characters after the prefix;

(c) code that causes the one or more processors to select at least one rule from a plurality of permission rules that prevent a user associated with a particular tenant from seeing data in the database associated with the particular tenant to which the user is not permitted access and to search only that portion of a entity share table appropriate to the key to locate access information for the first object;

(d) code that causes the one or more processors to determine whether the user has permission to access at least a portion of the first object based at least in part on the access information; and

(e) code that causes the one or more processors to send, to the user, the requested data of the first object to which the user has permission to access.

20. The machine-readable storage medium of claim 19, wherein the code that causes the one or more processors to search only that portion of a entity share table appropriate to the key to locate access information for the first object includes:

code that causes the one or more processors to select at least one from a plurality of row level sharing rules that prevent a user associated with a particular tenant from seeing data in the database associated with the particular tenant to which the user is not permitted to access; and

code that causes the one or more processors to create an improved query so that the improved query indicates to the database how to search the data in the database that the user is permitted to search in a way that is likely to be improved over a way that the request received would have been searched in the database.

21. The machine-readable storage medium of claim 19, wherein the code that causes the one or more processors to determine whether the user has permission to access at least a portion of the first object based at least in part on the access information includes code that causes the one or more processors to access at least one of:

a role hierarchy metadata table that lists the number of people below a user; and

a group membership metadata table that lists the users that belong to a group.

22. The machine-readable storage medium of claim 19, wherein the code that causes the one or more processors to search only that portion of a entity share table appropriate to the key to locate access information for the first object includes:

code that causes the one or more processors to rewrite or generate Structured Query Language (SQL) of a query for the received query to provide an improved query that leads with the key and performs a nested loop to the entity share table when the query leads with a small number of entity rows.

23. The machine-readable storage medium of claim 19, wherein the access information includes a respective listing of groups of users that have access rights to each object and/or a portion of that object, and wherein a group membership table contains the user identifiers for users in a group.

24. A multi-tenant database system comprising:

a database including at least one memory device that stores data specific to each one of a plurality of tenants such that at least two of the tenants store at least a portion of data specific to the at least two tenants in a common table within the database, wherein each tenant is permitted access only to data associated with that tenant, and wherein each tenant has one or more users;

one or more processors configured to;

receive, from a user associated with a first tenant, a request to access data of a first object in the common table of the database, the first object having a unique identification, wherein the common table includes a plurality of objects associated with the first tenant, wherein the plurality of objects includes the first object, a second object, and a third object, each contain containing one or more data types specified by the first tenant, and wherein the database contains a entity share table containing access information for the objects;

identify a key associated with the first object and the third object, but not associated with a the second object wherein the key includes a key prefix that equals a prefix of the unique identification, the unique identification having additional characters after the prefix;

search only that portion of a entity share table appropriate to the key to locate access information for the first object;

select at least one rule from a plurality of permission rules that prevent a user associated with a particular tenant from seeing data in the database associated with the particular tenant to which the user is not permitted access;

determine whether the user has permission to access at least a portion of the first object based at least in part on the access information, wherein the access information includes a number of rows of the common table that the user has permission to access; and

access the requested data of the first object to which the user has permission to access based on the number of rows;

send, to the user, the requested data of the first object to which the user has permission to access.

25. The system of claim 24 wherein the entity share table includes access information for each object of the first tenant and includes another key associated with the second object associated with the first tenant, and wherein the access information includes a respective listing of users and/or groups of users that have access rights to each object and/or a portion of that object.

26. The system of claim 24 wherein the access information includes at least one of information related to the requestor'"'"'s sharing position and count of instances of objects owned.