MySQL: Grant **all** privileges on database
Solution 1
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).
IMPORTANT NOTE
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.
Solution 2
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';
Solution 3
This will be helpful for some people:
From MySQL command line:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell.
Therefore, the first thing to do is to provide the user with access to the information they will need.
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.
Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.
FLUSH PRIVILEGES;
Your changes will now be in effect.
For more information: http://dev.mysql.com/doc/refman/5.6/en/grant.html
If you are not comfortable with the command line then you can use a client like MySQL workbench, Navicat or SQLyog
Solution 4
1. Create the database
CREATE DATABASE db_name;
2. Create the username for the database db_name
GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'localhost' IDENTIFIED BY 'password';
3. Use the database
USE db_name;
4. Finally you are in database db_name and then execute the commands like create , select and insert operations.
Solution 5
This SQL grants on all databases but just basic privileges. They're enough for Drupal or Wordpress and as a nicety, allows one developer account for local projects.
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
INDEX, ALTER, CREATE TEMPORARY TABLES
ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
marioosh
mostly Java programmer, Linux, Node.js and JavaScript technologies enthusiast...
Updated on January 16, 2022Comments
-
marioosh over 2 years
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'
-
simhumileco over 6 yearsYou should use
FLUSH PRIVILEGES;
only if you modify the grant tables directly using statements such asINSERT
,UPDATE
, orDELETE
-
-
Adonis K. Kakoulidis over 10 years+1 for not including
WITH GRANT OPTION
and targeting a specified database instead of all (*
). -
Pacerier over 9 years
-
Pacerier over 9 years
-
Rick James over 8 yearsNote: The exact list in the
GRANT
varies between Versions of MySQL. -
Admin about 8 yearsEither you wholesale copied from Linode, or they copied from you: linode.com/docs/databases/mysql/…
-
Evan Donovan about 7 yearsNote that you probably don't need the first command unless you want access from outside IP addresses, or from other computers on the local subnet. If you do, I would recommend using commands that are targeted to the specific IP addresses that you want to allow, such as [email protected] (for one on the local subnet), rather than myuser@% generally.
-
akostadinov about 7 years@EvanDonovan, there are a lot of things here that depend on use case as well environment. For example java driver does not support socket transport at all IIRC. But generally I agree - better specify specific IPs when possible, or run server only on
127.0.0.1
and firewall the server when listening to non-localhost. -
rubo77 over 5 yearsI needed to identify again with
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost' IDENTIFIED BY 'my_password' WITH GRANT OPTION;
(strange... , but true)