MySql User Grant

[isysoftware]

Hi All,
with my code, I would like to create a MySql User and set permission on tables:

Code: Select all

var aQ:tuniquery;
....
....
   with aQ do
      begin
          connection.startTransaction;
          close;
          sql.clear;
          sql.add('CREATE USER [email protected] IDENTIFIED BY apwd');
          execsql;

          close;
          sql.clear;
          sql.add('GRANT SELECT ON TABLE aTbl TO [email protected] ');
          execsql;
          connection.commit
      end;

Here my problem is that User is created but permissions not: I don't know why!
Any ideas?

Thanks,
Flavio

[AlexP]

Hello,

We cannot reproduce the problem. When using your code, a user is created and get rights for selecting from the specified table.
Please specify the versions of MySQL, UniDAC and IDE. Also, please try to run this code in the MySQL console and check the result

[a.alekizoglou]

Flavio,

shouldn't this be

Code: Select all

var aQ:tuniquery;
....
....
   with aQ do
      begin
          connection.startTransaction;
          close;
          sql.clear;
          sql.add('CREATE USER '''anuser'''@127.0.0.1 IDENTIFIED BY '''apwd''' ');
          execsql;
          connection.commitTransaction;
          close;

even though I think the start/commit transaction is useless since MySQL mysql database is myisam.

[AlexP]

Yes, username and password must be quoted (otherwise, error will occur). Independently on the database, the above code will create a user and grant him appropriate rights.