NYCPHP Meetup

[bz-gmort at beezifies.com]

John Campbell wrote:
> On 9/28/07, Kenneth Downs <ken at secdat.com> wrote:
> 
>>   I will claim that putting security
>> directly into the database is better than any other way because it does what
>> is needed in the end with the least possible work.
> 
> I must be missing something.  Take a simple social networking
> scenario: A user can only see another user's complete profile if and
> only if they are mutual friends.  Implementing that in the tables
> would be a huge pain in the ass and incur a big performance penalty.
> Is there some super easy way to implement this that I am missing?


It would?

Think of the profile information as follows:
ProfileTable
Username, userid, fullname, address, emailaddress, bio, Recordid
FriendTable:
Recordid, friendid


So, in the classic sense, if someone loads a profile, you might do a 
join between friendtable and profiletable and add a where clause where 
the friendid equals your userid- if you get data from it you display it. 
  Otherwise, you would pull just the public information.


In a DB driven sense, you would have:
ProfileTable
Username, fullname, address, emailaddress, bio, Recordid
FriendTable:
Recordid, friendusername

PublicProfiles:
A select only view of ProfileTable having only:
username, fullname, bio

And then for each and every userid, you have created automatically a set 
of views:
Myusername.friendprofiles
select Username, fullname, address, emailaddress, bio from profiletable 
where recordid in (select recordid from friendtable where friendusername 
= 'myusername')

(and to extend it a bit, you would also have a view for update:
Myusername.myprofile
select Username, fullname, address, emailaddress, bio from profiletable 
where username = 'myusername')


Now from the application perspective, you have 2 queries:
First you do a select to the myusername.firendprofiles table and if you 
locate the user your looking for, display the full data.
Second, if no record is found, than check the publicprofiles table for data


The important point here is that the only view you have UPDATE authority 
to is myusername.myprofile, wheras you have select authority on 
myusername.friendprofiles and publicprofiles.  You have no direct access 
to the profiletable.


Now, assume for a moment that the user get's some SQL injected past the 
program.

In the first case, since all the queries are run against the 
profiletable, the user can then view the private informaiton of other 
users(and worse, can change it since they have edit authority to edit 
their own record!)

With the view level security, the only view they can update is 
myusername.myprofile - so they can only change their personal record. 
As for selecting data, they can only select from the 
myusername.friendsprofile and myusername.publicprofiles.

Since the only data in those views is the data they are allowed to see 
anyway, even if they manage an SQL injection they still can't view data 
they were not authorized to access.