CODEWITHSUNDEEP
mysql
pvd
pvd

Reputation: 1343

Grant a specific user to modify a specific table rows

For security reason, I don't like the auditor has the privilege to modify all the rows of the table. So I tried:

GRANT UPDATE audit_comment ON cgis TO auditor IDENTIFIED BY 'audit@TE';

But it failed

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'audit_comment ON cgis TO auditor IDENTIFIED BY 'audit@TE'' at line 1

I've googled a lot, but I couldn't find the answer, maybe mysql doesn't allow us to do this?

Upvotes: 0

Views: 4224

Answers (3)

puk
puk

Reputation: 16782

You could also try using views instead of tables which can be used to restrict access to only a certain type of rows. Here is a very basic example

PICTURES table:

CREATE TABLE IF NOT EXISTS pictures_t
(
   id      INT NOT NULL auto_increment,
   public  BOOL NOT NULL DEFAULT TRUE,
   PRIMARY KEY(id)
)

PICTURES view:

CREATE VIEW pictures_v
AS
SELECT 
   P.*
FROM pictures_t AS P
WHERE
   P.public = TRUE
ORDER BY id;

Create Users:

GRANT SELECT, INSERT, UPDATE ON db.pictures_t TO 'god'@'localhost' IDENTIFIED BY 'heaven';
GRANT SELECT, INSERT, UPDATE ON db.pictures_v TO 'satan'@'localhost' IDENTIFIED BY 'hell';

Upvotes: 2

King Skippus
King Skippus

Reputation: 3826

Alternatively, consider using a stored procedure. Have the procedure update the rows, and set it to use SQL SECURITY DEFINER. Within the stored procedure, you can include whatever validation you need, and revoke the access to modify rows from the person or people you want to limit it on. It's a bit more complicated, but it's the only way I know of to limit the kind of access you're looking for.

Upvotes: 0

a1ex07
a1ex07

Reputation: 37364

You cannot specify privileges on rows, just on columns. Check the syntax here
You need something like
GRANT UPDATE (audit_comment) ON db_name.cgis TO 'auditor'@'host_name'

Upvotes: 0

Related Questions