Webapp & MySQL: Row Level Security

Question

I am trying to emulate row level security on a webapp I am developing using MySQL.

Using this method: Creating a database with the the required tables where the data pertaining to all the users will be stored with proper indexing of columns of the tables.

Creating mysql 'views' to specific users based on the user-id.

To achieve row level security I will also have to create mysql account for every user to and set 'grant' permissions on the views.

For the web interface a PHP based MVC framework will be used.

But, according to my research:

1] Having separate mysql account per user "make the webapp less secure".
2] Having separate mysql account per user "increases the disk I/O".

Questions:
1] How does creating mysql user per webapp user make the webapp less secure?
2] Does the disk I/O increase considerably?
3] Is there a better way to implement row-level-security in MySQL?
4] What are the pros/cons of implementing row-level-security by the above method?

Why am I looking at Row Level Security?
I need row level security because there are rows which will be shared between multiple users & have 1 or 2 owners to it. Only these owners can delete/modify them.

I am not able to add tags like 'row-level-security' 'disk-io' as my reputation points are less. If anyone else can, please do.

Answer 1

How does creating mysql user per webapp user make the webapp less secure?

• All you have to do is run SHOW GRANTS; within the app's DB Connections and all grants for the connected user is exposed. That may include seeing host IPs, subnets. Grants for specific tables, views and columns also become visible.

Does the disk I/O increase considerably?

• It would definitely do so. Although grants and views are stored in the information_schema database (in RAM), each view would be stringently checked to make sure the underlying tables exist for using the view definition. That would requiring accessing the .frm for every table involved. Any grants on tables or columns are also checked against the underlying .frm files.

Is there a better way to implement row-level-security in MySQL?

• This may be a little crazy, but you could probably use triggers

Example

delimiter $$
CREATE TRIGGER check_row_priv BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
 IF (USER()='user1@localhost') && (OLD.is_editable=0)
 THEN
    -- Make Trigger Fail Here
 END IF;
END; $$
delimiter ;

I wrote another post that show how to abort stored procedures and triggers. Other answers in the question may give you insght into aborting triggers or, at least, makikng data immutable.

What are the pros/cons of implementing row-level-security by the above method?

• IMHO In a nutshell, it would be the complexity of management.