How to grant persmission for this sql?

Question

How to grant permission for this? Thank you for your help! ERROR is : ERROR 1142 (42000) at line 2: CREATE command denied to user '@'localhost' for table 'student'

CREATE TABLE STUDENT(
    ID    INT PRIMARY KEY,
    SCORE INT
);

INSERT INTO STUDENT VALUES(1,20);
INSERT INTO STUDENT VALUES(2,50);
INSERT INTO STUDENT VALUES(3,50);
INSERT INTO STUDENT VALUES(4,68);
INSERT INTO STUDENT VALUES(5,95);

SELECT 'Student ' || ID || ' has grade: '||(CASE
WHEN SCORE<20 THEN 'F'
WHEN SCORE<40 THEN 'D'
WHEN SCORE<60 THEN 'C'
WHEN SCORE<80 THEN 'B'
ELSE 'A' END)
FROM STUDENT
ORDER BY ID;

Answer 1

Check out the GRANT Statement in the MySQL Reference Manual. You have to grant create and select, insert, update, delete rights.

Answer 2

My loose comment is that I view CREATE from a software developer's role in the application's initial design phase, which usually gets handed off to the SQL server's DB Admin role (who does have the necessary CREATE permissions). Then later INSERTs are used for populating the table and SELECTs for pulling table data.

I can't think of a use case where you would want to create a new table everytime a new record is created. If you remove the 'CREATE' statement, the error should go away.

Answer 3

Syntax... || is boolean OR. Instead, do:

SELECT CONCAT('Student ', ID, ' has grade: ', (CASE...)) ...

As for the GRANT: Something like:

GRANT SELECT, INSERT, CREATE TABLE
    ON studentdb.* TO user@localhost;

where studentdb is the name of the DATABASE where you want to place the TABLE student.

To run the GRANT, you need to have sufficient privileges. Typically this involves being MySQL's root user. (Sometimes 'admin'.)