mysql create table user_Allow CREATE TABLE to the MySQL user

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

use as per below-

GRANT CREATE ON eh1.* TO [email protected]'%' 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 [email protected]'%' IDENTIFIED BY 'user1_password';

Update as per user requirement:

GRANT CREATE ON eh1.* TO [email protected]'%' IDENTIFIED BY 'user1_password';

GRANT SELECT, INSERT, UPDATE ON eh1.table1 TO [email protected]'%';

GRANT SELECT, INSERT, UPDATE ON eh1.table2 TO [email protected]'%';

注意：本文来自Hello, buddy!。本站无法对本文内容的真实性、完整性、及时性、原创性提供任何保证，请您自行验证核实并承担相关的风险与后果！

CoLaBug.com遵循[CC BY-SA 4.0]分享并保持客观立场，本站不承担此类作品侵权行为的直接责任及连带责任。您有版权、意见、投诉等问题，请通过[eMail]联系我们处理，如需商业授权请联系原作者/原网站。