Hi ppl.

I recently began a project that requires a MySQL backend. I'm quite new to MySQL and find the GRANT command especially tricky.

My particular problems concerns restricting users to selecting/viewing only particular rows in a table, based on their username (which is a column in the candidate table).

Could someone please throw me a lifeline on this one OR tell me if indeed it's possible to do this.

Thanks.

The traditional method of achieving this is to grant
privileges on Views. Different users can
access only a certain set of views, and are thus
restricted to see only what the view filters.
MySQL doesn't have views, nor does it look like it
will for quit a while.

You could of course roll your own by adding an integer column
to every table that describes the access level a user must
have to obtain access to that row, but this is hard to implement
consistently and easy to mess up because you have to
remember to add ' AND access_level >= some_number '
to all queries.

This method breaks down if you have to
give some of your end users direct access to the database
via a command line tool such as mysql, because end-users
will forget to filter their requests on 'access_level' and perhaps
see stuff they shouldn't be allowed to see.
I guess it all depends on whether you are able to restrict
access to your database so that nobody accesses it
except via one of your carefully written programs that
does the filtering instead of the usual DB privileges system.

Thanks vanekl,

I do appreciate the useful insight on this subject. It will save me a great deal of time that I would have spent looking for a solution that does not really exist.

I do hope mysql soon implements views. I guess I will have to implement the filtering on the front end (ms access) for now.

Thanks again.