Menu
I've created database, for example 'mydb'.
CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_bin; CREATE USER 'myuser'@'%' IDENTIFIED BY PASSWORD '*HASH'; GRANT ALL ON mydb.* TO 'myuser'@'%'; GRANT ALL ON mydb TO 'myuser'@'%'; GRANT CREATE ON mydb TO 'myuser'@'%'; FLUSH PRIVILEGES; Now i can login to database from everywhere, but can't create tables.
How to grant all privileges on that database and (in the future) tables. I can't create tables in 'mydb' database. I always get:
CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin); ERROR 1142 (42000): CREATE command denied to user 'myuser'@'...' for table 't' 
657
To GRANT ALL privileges to a user , allowing that user full control over a specific database , use the following syntax: mysql> GRANT ALL PRIVILEGES ON database_name. * TO 'username'@'localhost';
GRANT USAGE ON *. * means "No privilege". In other word, the user has been created (with an entry in mysql. users table) with no privilege.
In this syntax: First, specify one or more privileges after the GRANT keyword. If you grant multiple privileges, you need to separate privileges by commas. Second, specify the privilege_level that determines the level to which the privileges apply.
Granting Privileges on Functions/Procedures: While using functions and procedures, the Grant statement can be used to grant users the ability to execute the functions and procedures in MySQL. Granting Execute Privilege: Execute privilege gives the ability to execute a function or procedure.
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' WITH GRANT OPTION; This is how I create my "Super User" privileges (although I would normally specify a host).
While this answer can solve the problem of access, WITH GRANT OPTION creates a MySQL user that can edit the permissions of other users.
The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.
For security reasons, you should not use this type of user account for any process that the public will have access to (i.e. a website). It is recommended that you create a user with only database privileges for that kind of use.
95
This is old question but I don't think the accepted answer is safe. It's good for creating a super user but not good if you want to grant privileges on a single database.
grant all privileges on mydb.* to myuser@'%' identified by 'mypasswd'; grant all privileges on mydb.* to myuser@localhost identified by 'mypasswd'; % seems to not cover socket communications, that the localhost is for. WITH GRANT OPTION is only good for the super user, otherwise it is usually a security risk.
Update for MySQL 5.7+ seems like this warns about:
Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation.
So setting password should be with separate commands. Thanks to comment from @scary-wombat.
ALTER USER 'myuser'@'%' IDENTIFIED BY 'mypassword'; ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword'; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
