CODEWITHSUNDEEP
mysqlsqlphpmyadmindatabase-permissions
Graeme
Graeme

Reputation: 124

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

Upvotes: 0

Views: 548

Answers (2)

Bohemian
Bohemian

Reputation: 424983

You could change your update statement to make use of the current_user() function:

update mytable set
...
where id = ?
and current_user() not in ('john', 'bob', 'sally')

Upvotes: 0

user149341
user149341

Reputation:

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.

Upvotes: 1

Related Questions