How can I restrict a MySQL user to perform update or delete on rows?

user_9878967 0
Viewed 612

OK, so I may be wrong in terms of asking this question. but I have a scenario where there is one table being used by several teams, so we faced an issue where users were updating the records of other teams it was by mistaken of course.

So I thought can we have one extra column in my table having values as team name and when any user is updating the record they need to provide their team name in where clause, by making as many user accounts as many teams are and granting those users update delete permissions specific to that teams column. Is it possible?

Or does anyone have another idea?

amazon-rds mysql sql
1 Answers

There is no functionality in MySQL for row based permissions (you can have specific permissions on columns) and, as far as I know, no relational database would have this capability. Row based permissions are not part of relational database models just as most automobiles come from their factories without fishbowls.

At the lowest level someone has to make the teams 'play nice'. Good news is that you do not have to change anything other the the use of the database. Bad news is that some users are just jerks. The next level is somehow have someone approve updates/deletes before they go into the database and this is a pain in the rear to implement/use/manage. You can use view to obfuscate the table details from the users but you are going to make infrastructure changes.

The easiest answer is to have on person from each team RESPONSIBLE for updates/deletions, use triggers to record whom and what was changed (save what was changed in a secondary table), and notify their management each time they take action against another's teams data. Infrastructure changes are need, culture changes are needed, data loss is minimal, and it is still a pain (Welcome to life as a DBA).

user_39704040
Related