Hi,
Is there any permission or grant command on a table which make users not able to delete records (row) that inserted by another user. But allow them to delete or update their own record?
Thanks, Stephen
A:
No, this should be addressed by the application that runs on top of MySQL.
Chuck Vose
2010-02-12 03:10:30
Thanks Chuck,But how if those users can access the mysql client command either local or remote, bypassing the application layer ?
Stephen
2010-02-12 03:13:45
You may have to look at some other solution to be honest. I don't know of any database that does this but I wouldn't be surprised if they do exist. Maybe if you give me more details I can help more.
Chuck Vose
2010-02-12 03:31:43
I mean my users are using a desktop application remotely. So this is not only a localhost users (i hope it always be a localhost). I gave them UPDATE and INSERT, yes they can UPDATE other users record too.
Stephen
2010-02-12 03:33:21
A:
Not entirely sure the following's workable for your case, but I thought I might as well suggest it -- perhaps it can help. You could create for each user an updatable view (with sql security definer) that selects only those user's records, and grant the user the ability to delete on the view but not the table it selects from -- with sql security definer, the user should then be able to delete from the view causing deletion from the table. You'll also have to update the view each time the user creates a new record (otherwise the view is "frozen" at the time it was created). See the docs for create view and updatable views.
Alex Martelli
2010-02-12 03:30:52
Thanks Alex. This could be a try but it's too hard to maintain for me. Is there a some kind validation based on client interface maybe?
Stephen
2010-02-12 03:52:23
@Stephen, there are many "client interfaces" to MySQL, none that I know performs the fine-grained authorization checks you want, but even if you found one a user could use another and bypass the checks. Only server-side enforcement can really help, or you could code (in any programming language) a specialized layer of your own to exclusively co-reside w/the server, so that no user can actually ever connect to the MySQL DB, only to your specialized layer which can then perform all the business logic you desire. I predict HUGE LOTS of work if you choose this route.
Alex Martelli
2010-02-12 04:29:37
That what i felt this morning. That this is going to be a web browser client only solution. No remote client at all for the sake of other owner's record. I read about oracle virtual private database. But this is a FreeBSD server i'm working on.
Stephen
2010-02-12 05:55:40
On FreeBSD or anywhere else, you can set MySql to only accept connections from the specific node where only your web server (Apache or whatever) is running, as well as take other precautions against unauthorized access. Your application's users need not correspond to users as defined in the DB, if you're implementing your own authorization scheme because the SQL one doesn't work well for you.
Alex Martelli
2010-02-12 06:05:38
This is a nice day for me, at least i found your solution using CREATE VIEW very useful. I tried it, and now user can't view others record except their own.
Stephen
2010-02-12 07:44:26
A:
u should implement acl with a programming language. some libraries like zend do the job for u (using php language).
rahim asgari
2010-09-24 11:57:38