mysql user row level access

Question

Have a database with the following

id | userid | name
1  |    1   | John
2  |    1   | John
3  |    2   | Joe
4  |    2   | Joe
5  |    2   | Joe
6  |    3   | Sue
7  |    3   | Sue

I need to get a way that I can create a database, then create users. Each user that I create in mysql limit them to access of data for their userid. Every database table in the database has the userid value.

So whether they are reading ,updating, insert or delete. If it is going through a specific mysql user that I attached to that database, I want that user to only read, update, insert or delete where their userid is.

I have read some things on mysql triggers but have not found anything that will work for me.

We have a backend that has data in it and restricted by userid.

The website pulls data from that table based in userid so select * from articles where userid=1. Right now, that code is modifiable by the user. I would like a way to go select * from articles and mysql only results rows that have userid=1 for that mysql user. The goal would be for every user to have their own mysql user login to the mysql database that would restrict to that specific value of userid that is theirs.

Any thoughts? Thanks so much!

Answer 1

GoogleResult[0] has this:

http://www.sqlmaestro.com/resources/all/row_level_security_mysql/

Abstract

The article contains a step-by-step guide to implementation of row level security in MySQL 5.0 and higher using such MySQL features as views and triggers.

Answer 2

Well! i will suggest to make a table for that. For the whole application

user_rights

id  |   user_id |   insert  |   update | delete | read
1   |   2       |   0       |   0      |    0   |   1

Note : 1 for allowed and 0 for disallowed.

Now before you do anything first check the rights then perform other actions.

Detailed method including parts of application :

screens

id  |   title
1   |   articles
2   |   blog

user_rights

id  |   user_id |   insert  |   update | delete | read  |   screen_id
1   |   2       |   1       |   0      |    0   |   1   |       1
2   |   2       |   0       |   0      |    0   |   1   |       2

In this method you can allow screen level access. User id 2 can add and view articles and he can aslo view blog but. I may be using inappropriate terms here but i hope you get the idea.