Grant CREATE TABLE permission to MySQL User
Solution 1
use as per below-
GRANT CREATE ON eh1.* TO user1@'%' IDENTIFIED BY 'user1_password';
Note: '%' will provide access from all ips, so we should provide rights to specific ip instead of all ips, so change '%' with any ip like '191.161.3.1'
If user need select/insert/update/delete/create rights then syntax will be -
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON eh1.* TO user1@'%' IDENTIFIED BY 'user1_password';
Update as per user requirement:
GRANT CREATE ON eh1.* TO user1@'%' IDENTIFIED BY 'user1_password';
GRANT SELECT, INSERT, UPDATE ON eh1.table1 TO user1@'%';
GRANT SELECT, INSERT, UPDATE ON eh1.table2 TO user1@'%';
Solution 2
Following this, correct syntax is
GRANT CREATE ON eh1.* TO user1
With eh1 a database.
If you don't use ".*", your database is considered a table.
![Ahmad](https://i.stack.imgur.com/JoxbF.jpg?s=256&g=1)
Comments
-
Ahmad almost 2 years
I have a database that is shared between some users, and I want to manage their permissions on this.
I want to give permission for creating a new table, and accessing (select, insert, update, delete) to that table of course, to a user that doesn't have full permission on the database (only he has SELECT access to some tables).
So, I executed this query:
GRANT CREATE ON eh1 TO user1
Then, when I logged in with that user and tried to create a new table, I got this error:
1142 - CREATE command denied to user 'user1'@'localhost' for table 'folan'
What is the problem here? How can I do that?
UPDATE
The problem solved partially by changing the command to this:
GRANT CREATE ON eh1.* TO user1
Now there is another problem, that the user1 cannot select or insert into the newly created table. The reason is understandable, but is there a way to solve this?
Thanks
-
Ahmad almost 9 yearsYes, that worked, but there is still a problem. The user cannot select rows from the created table.
-
Ahmad almost 9 yearsYes, that worked, but there is still a problem. The user cannot select rows from the created table.
-
Zafar Malik almost 9 yearsfirst check what rights user need and assign all required rights..if user need to check tables data and insert/update/delete them then rights will be as I am updating in my answer..
-
Ahmad almost 9 yearsI don't want to permit the user to access all tables in database. Only some tables, and tables created by him.
-
Zafar Malik almost 9 yearsthen you can assign create rights to full db like eh1.* and other rights to only specific tables like eh1.table1.
-
Ahmad almost 9 yearsBut I don't know what will be the name of his table
-
Zafar Malik almost 9 yearsAs per my knowlege there is no table owner concept in mysql, so you have to list out on which table you want to provide access to whom.
-
Ahmad almost 9 yearsOK, it seems there is no way. This is because of poor privilege system of mysql.
-
Zafar Malik almost 9 yearsIf you are getting appropriate answer here then you can choose it as accepted answer..... :)