CODEWITHSUNDEEP
mysql
Hazim Eid
Hazim Eid

Reputation: 835

MYSQL Update permission need to select permission also?

In MYSQL Database, I want to give update permission to the user without select, is it possible?

Upvotes: 2

Views: 2471

Answers (3)

Florian
Florian

Reputation: 175

It is possible but your problem is, that your UPDATE-Query contains a WHERE-clause which needs to query the fields at least internally. So it is intended that you need SELECT permission here.

Otherwise, one could try to use brute force techniques to get the actual contents of the table by using a UPDATE-Query and checking wether it has affected rows.

Example: UPDATE customers SET some_irrellevant_field=1 WHERE user = 'jimmy' AND sexual_orientation = '2' As soon as you have a affected rows count > 0, you would have the information. So it would possibly be to dangerous to allow that.

But you can explicitly give permissions for single columns. Lets say you want to update the password hash of a user with a certain id, you give select permission on the id-column to the database user and update permission to hash column.

PHPMyAdmin table specific permission dialog (sorry, some parts of the screenshots are in german): PHPMyAdmin table specific permission; sorry, some parts of the screenshots are in german

Then this should work fine: UPDATE users SET hash='0123456789abcdef' WHERE id = 1234

Alternatively, if column level permission should not be available in you DBMS or storage engine you could still use a separate table or database and join it when it is needed.

Upvotes: 2

Hazim Eid
Hazim Eid

Reputation: 835

Thanks All on your replies, but i Don't think you try this case before, so as the error which i got, you can't execute the update query without Select and Update Permission.

Thanks All.

Upvotes: 0

Aroniaina
Aroniaina

Reputation: 1252

Yes, you can do it.

These steps are example.

Step1 : create the user (if not already done)

CREATE USER 'username'@'%' IDENTIFIED BY 'password'; 
FLUSH PRIVILEGES;

Step 2 : grant only the update privilege

GRANT UPDATE ON *.* TO 'username'@'%' ;

NB : You have to connect with an user who has the grant privilege to execute these command. (E.g : root user)

The grant syntax is

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

Upvotes: 0

Related Questions