How should permissions for anonymous users be modelled

database-designpermissions

I'm designing a web application. There will be users who log into the site, but also anonymous, non-authenticated users, i.e. any member of the public who accesses the site. Users will be assigned to groups, and those groups will be assigned permissions. Some site content will be accessible only to authenticated users, while other content may be marked as publicly accessible. I'm considering how best to model facts such as "this item is publicly accessible". Options that occur to me so far:

Create a special group that a special user "Anonymous" belongs to. Assign permissions to the "Anonymous" group, and pretend that unauthenticated users are all the Anonymous user.
On each content entity, include an attribute such as is_publicly_accessible.

Any thoughts on which is the right way to go? The first option feels like a more consistent approach to permissions, but the notion of some users and groups being special/fake/dummy doesn't feel right.

Best Answer

I'd go for option 1. With this approach there are no "hacks" in the app for anonymous users. The standard security model continues to apply with the only difference being that if you don't know who the user is (ie, they haven't authenticated) that you pretend they are the anonymous user, which in effect they are.

Option 1 is also the approach used by most (all?) web servers and file servers: they use an "anonymous" or "nobody" user.

Related Solutions

How to correctly design this database

Your first option is most likely the best. A table called users, a table called groups and a junction table called user_groups. User_groups allows for the many to many relationship between groups and users. As for size, what are you thinking in terms of "large"? 20,000 records? A million? It might be possible database types and programmers think differently in terms of "large", because I can't foresee this table having more than a few hundred thousand rows if the website gets popular and there are a lot of groups and that doesn't seem large. As long as the tables are indexed properly and the queries are written efficiently it should not be an issue...you would only be querying out of the table for specific users or specific groups anyway. Whichever RDBMS engine you use is designed to operate on tables structured this way. There's no good design reason to break the groups table into multiple tables - it means having to maintain new tables for each new group exactly as you said, plus you run into complicated queries where you want to bring back data from more than one group at once.

SQL Server – See Who Has Access to Databases via AD Group Membership

just seeing which groups the user is in that also have a login on the instance would be nice

You can accomplish this using following code:

exec xp_logininfo 'dom\acc', 'all';

Or you can impersonate that login and use sys.login_token like this:

exec as login = 'dom\acc';
select distinct name
from sys.login_token
where principal_id > 0
      and type = 'WINDOWS GROUP';
revert;

Best Answer

Related Solutions

How to correctly design this database

SQL Server – See Who Has Access to Databases via AD Group Membership

Related Question