0

How can I get PRIVILEGES statement works for the value of three variables in MYSQL as the follows : 

set @myuser ='CHANGE ME';
set @ip ='localhost';
set @pass ='CHANGE ME';
CREATE DATABASE TEST;
USE TEST;
CREATE TABLE users(
    id int(11) not null auto_increment,
    username varchar(50) NOT NULL,
    password varchar(30) NOT NULL,
    first_name varchar(30) NOT NULL,
    last_name varchar(30) NOT NULL,
    ip BINARY(16),
    PRIMARY KEY (id)
);
GRANT ALL PRIVILEGES ON TEST.* TO myuser@@ip identified by @pass;

I have an error in PRIVILEGES statement. Thanks

Sam
  • 167
  • 3
  • 14
  • 1
    Possible duplicate of [How to use GRANT with variables?](https://stackoverflow.com/questions/14830493/how-to-use-grant-with-variables) – Solarflare Aug 05 '17 at 13:39
  • Thanks but that is too advanced for me what I see in the link. I tried to change that last line as GRANT ALL PRIVILEGES ON TEST.* TO contact(@myuser,'@',@ip) identified by @pass; but still did not work – Sam Aug 05 '17 at 13:53
  • The (first part of the) answer has 6 lines. You need all 6 of them. As they are. (Just `GRANT UPDATE ON` will of course be `GRANT ALL PRIVILEGES ON`, and you can of course use other values for the variables). – Solarflare Aug 05 '17 at 14:00
  • Sorry I could not fix it yet from the code that you mentioned already. Could you please change the part that has error in my code? I want to keep the structure of what I wrote here just change the last line in order everything works. thanks – Sam Aug 05 '17 at 14:19
  • If you, literally, just copy & paste the code from the other answer and execute it, it will add a new user named user1, with update rights. If you cannot adapt your code using the code in that link, please edit your question and add the adapted code you tried. – Solarflare Aug 05 '17 at 14:33
  • Thanks but I really want to understand why my code does not working. – Sam Aug 05 '17 at 14:36
  • The 3rd comment under the linked questions answered this too. You simply cannot do it, as the MySQL syntax does not allow it. So you need to use dynamic sql. And the best way to understand it is to do it yourself. So adapt your code using the information from the other question, and if it still doesn't work, we can have a look at it. – Solarflare Aug 05 '17 at 15:46

0 Answers0