John Campbell wrote:
On 9/28/07, Kenneth Downs <[EMAIL PROTECTED]> 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.
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
