How do large sites accomplish row-level permissions?

Question

So I am making a small site using cakephp, and my ACL is set up so that every time a piece of content is created, an ACL rule is created to link the owner of the piece of content to the actual content. This allows each owner to edit/delete their own content. This method just seems so inefficient, because there is an equivalent amount of ACL rules as content in the database. I was curious, how do big sites, with millions of pieces of content, solve this problem?

Acerb

Answer 1

With large sites I have worked on access permissions were determined at the application level. The database associated the content with a user's record then in the data access/business logic layer it made the determination whether or not the user has sufficient rights to access the content.

For a large site with dynamic content I think this would probably be the best way to handle it.

EDIT: To add a more concrete example.

Example: Ok lets say we have a simple file storage site where a user can only access their data or data that has been explicitly shared with them by another user.

Since this application is fairly simple as it is just serving files it only has three database tables which are:

Users Table which has columns:
      UserId <int>  PK
      UserName <varchar>
      HashedPassword <varchar>
Files Table which has columns:
      FileId <int>  PK
      FileOwnerId <int> FK (this has a foreign key relationship with UserId in the users table)
      FileName <varchar>
      MimeType <varchar>
      FileData <blob>
SharedFile reference table which has columns:
      SharedFileIndex <int> PK
      FileId <int> FK
      UserId <int> FK

Now some basic rules that we will want to define in our data access layer is that when a user is logged in they can access files that they are the owner of and files that other users have shared with them. So either through stored procedures or building the query to send to the database server I would make sure that my queries only return those records which they have access to.

Here the basic GetUsersFileList sql query for when a user logs in:

SELECT FileId, FileName, FileType
FROM Files
WHERE FileOwnerId = @UserId

As you can see here we are using a parameterized query to get then files a user is the owner of. Additionally we would query for the shared files as well for displaying to the user.

Now if we assume that each file will have it's own unique url such as:

http://mydomain.com/filehandler.php?fileId=123546

Then when we try to get the file we use a similar query as above to try and get the file data:

SELECT FileName, FileType, FileData
FROM Files
LEFT OUTER JOIN SharedFiles on Files.FileId = SharedFiles.FileId
WHERE Files.FileId = @FileId AND (FileOwnerId = @UserId OR SharedFiles.UserId = @UserId)

So you see when we attempt to get the file we are still using the UserId in the query thus if the user does not have the file either shared with them or they are not the owner of the file the result from the query will be 0 rows.

So permissions are determined by what a user is mapped to in the database but the actual enforcement is done by carefully writing your data access code and/or additional checks in your business logic layer before serving the content.

EDIT2: I am most familiar with MSSQL so my queries above are in T-SQL so the syntax might be a little off for MySql.

EDIT3: Replaced business logic layer with data access layer as in this example the only checks that are made is within the data access queries themselves.

EDIT4: Ok put back in reference to the business logic layer as more complex apps would need more complex permission schemes which could necessitate additional checks in the business logic layer.

Patriciapatrician

Seems interesting, could you elaborate a little bit more (for example adding a little example of how you would accomplish this)? – Equipment

Answer 2

Instead of having a separate ACL for each content element, you can have a separate ACL for each different set of permissions. Most content items for a given user will have the same permissions, so they can all point to the same ACL. This could also allow you to cache permission checks (e.g. "user 123 has permission to read ACL 456"). In the end you will have very few ACLs -- just all the standard ones and the few exceptions.

Lyly

Can you go into more detail? – Favoritism

Answer 3

Same rule applies to both large and small sites - if you want more specific control, you have to store more data in database. The problem you are trying to solve [allow users to manage only their content] can be solved using simple user id link between tables [for example users.id <-> articles.userId], there's no need to link every row with user. I would suggest using more general rules and storing only exceptions [for example allowing specified users to edit other users content] as an external data.

Rader