View Full Version : Implementing db access levels

05-25-2011, 12:50 PM
Hi, i have a mySQL database that stores filepaths for images and a user table.

I have an application that queries the database.

What i am struggling to work out is a method to limit user access to a certain range of files.

So instead of logging in to my application and being able to query all the images i'd like to be able to recognoise which users have "demo" permissions and only return a limited number of results.

So short of having a totally seperate database, what methods are best to achieve this? Would this be better done in my PHP scripts?

Any help appreciated, thanks.

05-25-2011, 06:27 PM
I have an application that queries the database.

PHP-based website, or...?

I would avoid a database change if you can possibly achieve this programmatically. Do you just want to limit the number of images allowed in demo mode? You can use PHP for that. Are there "premium" images that you never want to show in demo mode? You'll probably have to add a column to your database to assign each image an attribute of demo or premium (or whatever).

05-25-2011, 08:10 PM
The question is, WHO do you want to have access to all the images. What makes them different from the rest of your users?

I would leave the demo-all-can-see-these images where they are at and move the rest to a new location. THEN add a column to the user DB - at the end so you do not need to change any programs that are running. And use that to mark the people you want to have access to the non-demo images.

05-25-2011, 09:27 PM
if you have a table storing the names of the image files, you can simply add a new col to that table, with the user privileges. NO need to store the images in another place - indeed there is every reason not to. look up 'premature optimisation'.

Basically, store the images in one place and use additional database columns to clarify who can use them - user, subject purpose etc. And if you have more than one use for an image, or more than one privilege can access them , just split off the privilege/purpose to another table for this 1:m relationship.

05-26-2011, 01:37 AM
thanks for all your replies. It is a program that queries the db for images to download. Adobe air.

Yes i am just trying to limit the number of images available to the demo users.

I'd prefer to keep the image paths where they are, so adding a column for "demo" or premium would be fine to do.

So in its simplest form:

when querying the image paths in i would compare, for example, the "access level" field of a file path to the "access level" field of the user in the user table.

If an image path is premium and the user access field is demo then return "this is for premium users...."

Does that sound ok? Anyone see any pitfalls?