2

I have a certain user and i want to grant him permissions to alter tables, but only tables with his data, for example the user can change his profile data, but only his data. what i mean by this is altering the rows with his id on it.

grant update on table1 where table1.id_user = id_user to user;

is this possible to do?

GMB
  • 216,147
  • 25
  • 84
  • 135
João Miguel
  • 47
  • 5
  • Not sure what you mean here you mean record/rows user permissions here? – Raymond Nijland Jan 20 '19 at 02:04
  • If I understand, you want a user to be able to change his own profile information in a table of profiles? You would write function that when given the users ID (however you obtain it) it can update that record for that ID only, you don't need to give any users any permissions to the database itself. Your own function would handle this, all the user would have is a page for changing their own information, which calls this function, the user doesn't choose which ID it effects you script does, it knows who's logged in currently on the page and what ID they are using. – Pheonix2105 Jan 20 '19 at 02:05
  • i mean update rows of a certain table but only the rows with a given id – João Miguel Jan 20 '19 at 02:05
  • Maybe with a updateable view something like [this](https://www.sqlmaestro.com/resources/all/row_level_security_mysql/) – Raymond Nijland Jan 20 '19 at 02:06
  • ye that works aswell but isnt there a way to actually give a user permissions to alter his data? – João Miguel Jan 20 '19 at 02:07
  • 1
    I don't see why you would need to give them permissions to the database to do this, whenever you visit stackoverflow logged in as yourself when you change profile information you don't have DB access, you have a page that will call a function passing the new data and the id of the logged in user. which would update the DB. – Pheonix2105 Jan 20 '19 at 02:08
  • ok i understand what your saying its pointless to give the user acess to the database – João Miguel Jan 20 '19 at 02:10
  • 1
    It's not just pointless it's dangerous, a malicious user could use this access to grant themselves more permissions and potentially steal/delete data. Even gathering information from an input form must be escaped to prevent MySQL Injection attacks. – Pheonix2105 Jan 20 '19 at 02:11
  • Looks like you want row level security. According to the accepted answer in [this question](https://stackoverflow.com/questions/5527129/mysql-how-to-do-row-level-security-like-oracles-virtual-private-database) MySQL doesn't support that. The answer suggests a work around with views. – sticky bit Jan 20 '19 at 02:11
  • 1
    it is hard to give good advice we don't know what you want or what the situation is... extra record/row-level security implemention in the database can make some sense.. – Raymond Nijland Jan 20 '19 at 02:12
  • yes @stickybit i already also linked to something like that.. but it seams he wants row-level security based on application user accounts not MySQL user accounts. – Raymond Nijland Jan 20 '19 at 02:13

1 Answers1

1

This is not possible in a standard way in MySQL. You would have to use a trigger.

For example, the following compares the value of ID_USER that was passed in the update statement to the name of the current database user, and aborts the update if they are different, using the SIGNAL syntax to raise the error. 

DELIMITER $$

CREATE TRIGGER checkUpdateTable1
BEFORE UPDATE ON Table1
FOR EACH ROW
BEGIN

    SELECT 1 
    FROM mysql.user 
    INTO @is_root
    WHERE super_priv='Y' AND USER() = CONCAT(user, '@', host); 

    IF (@is_root IS NULL AND NEW.ID_USER <> USER()) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'error message';
    END IF
END
$$
DELIMITER ;

NB : after Raymond Nijland's comment, I edited the post to allow SUPER users to update any row.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • This would disable the root account (or anny global acces account) to move (update) records from one user to a other. – Raymond Nijland Jan 20 '19 at 02:15
  • @RaymondNijland : true... this whole scenario may not be a good idea from the start... – GMB Jan 20 '19 at 02:18
  • well implementing extra record/row-level security implemention in the database can make some sense even if it's simulated with a updateable view/stored procedure or trigger for that matter when customers/users share a database/tables in a application for example. – Raymond Nijland Jan 20 '19 at 02:20
  • @RaymondNijland : so... should I just add an pass-through for the root user in the trigger ? – GMB Jan 20 '19 at 02:22
  • 1
    "should I just add an pass-through for the root user in the trigger" well it's your answer,, i would check if the user has the `SUPER` privilege instead. – Raymond Nijland Jan 20 '19 at 02:31