Search code examples
mysqlsyntaxsql-grant

Unknown reason for syntax error in MYSQL

Error message states "SQL syntax error NEAR 'table, alter table,..." NO ERROR NUMBER GIVEN

CREATE USER admin@localhost IDENTIFIED BY 'password';  

GRANT select, update, insert, delete, create table, alter table, drop table, 
    create index, drop index, create routine, create procedure, create function  
ON test.*   
TO admin@localhost;*

All of the items I am trying to grant are valid permissions but when it gets to the first "table" in the list it just does not like it, am I missing something or is there a conflict that I just don't understand.

TIA

Solution

  • table is not needed:

    GRANT select, update, insert, delete, create, alter, drop
ON test.*  TO admin@localhost;

    I'm not sure about that how to add procedure, function privileges, I'll find it.

    as fvu mentioned, you can find grant privileges MySQL Manual. especially for PROCEDURE, http://dev.mysql.com/doc/refman/5.5/en/grant.html#grant-routine-privileges

    UPDATED

    I have read your comment on your question. output is from SHOW GRANTS .... I have googled about it. output of SHOW GRANTS looks like that can run directly in MySQL. but it can't. (this fact I just didn't know. thanks)

    and how to find something like you want... can you try this?:

    SELECT *
FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE = 'user_id@hosname';

    You can see which real privileges name user@hostname has... good. but hard to make GRANT ... Percona has interesting feature like this:

     SELECT sql_grants
 FROM common_schema.sql_show_grants;

    I found it this question.

    Thanks alot! I have learned many skills for your question!!!