Search code examples
mysqlsqlphpmyadmindatabase-permissions

Prevent a particular table being modified in SQL by a particular user

As the title says, however i have tried everything to prevent a user from modifying this table with no luck

For some reason DENY do not exist in the mySQL 5.5 manual so the following doesn't work

DENY ALL ON OBJECT::database.table TO user
DENY ALL ON database.table TO 'user'@'localhost'
REVOKE ALL ON database.table TO 'user'@'localhost'

The last one gives me this error

There is no such grant defined for user 'user' on host 'localhost' on table 'table'

However, if that was the case then would it not be able to Insert, Select or Update?

Any help is appreciated, i will add the system information below.

Software: MySQL

Software version: 5.5.27 - MySQL Community Server (GPL)

Protocol version: 10

Apache/2.4.3 (Win32) OpenSSL/1.0.1c PHP/5.4.7

Database client version: libmysql - mysqlnd 5.0.10

Solution

  • You cannot lock out a MySQL user from just one table. If they already have permissions to the whole database, you'll need to revoke that and regrant them access on the specific tables they do need to be able to touch.