Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.
share|improve this answer
    
Thanks a lot for all the info. The info was very clear. Is there a better way to manage users when some rows are shared between multiple users? Could you point me to some reference materials as well. Thanks in advance. –  ThinkingMonkey Dec 1 '11 at 12:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.