MySQL: Grant all privileges on database

Question

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'

You should use `FLUSH PRIVILEGES;` only if you modify the grant tables directly using statements such as `INSERT`, `UPDATE`, or `DELETE` — simhumileco, Nov 07 '17 at 10:37

score 1022 · Accepted Answer · edited Jun 20 '20 at 09:12

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.

score 564 · Answer 2 · edited Jan 15 '22 at 16:31

564

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';

edited Jan 15 '22 at 16:31

Halo

1,730
1
8
31

answered Mar 29 '13 at 16:56

akostadinov

17,364
6
77
85

35

+1 for not including `WITH GRANT OPTION` and targeting a specified database instead of all (`*`). – Adonis K. Kakoulidis Nov 16 '13 at 13:27
1

Note 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 myuser@192.168.0.1 (for one on the local subnet), rather than myuser@% generally. – Evan Donovan Feb 14 '17 at 02:06
@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. – akostadinov Feb 14 '17 at 10:27

score 153 · Answer 3 · edited Apr 08 '14 at 06:58

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

`flush privileges` is not needed when you use `grant` commands. [x4](http://stackoverflow.com/questions/5016505/mysql-grant-all-privileges-on-database/26244137#comment44277771_26244137) — Pacerier, Jan 14 '15 at 10:39
Either you wholesale copied from Linode, or they copied from you: https://www.linode.com/docs/databases/mysql/install-mysql-phpmyadmin-ubuntu-14-04 — , Apr 26 '16 at 18:34
I needed to identify again with `GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost' IDENTIFIED BY 'my_password' WITH GRANT OPTION;` (strange... , but true) — rubo77, Oct 22 '18 at 16:03

score 35 · Answer 4 · edited Nov 07 '17 at 09:26

35

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.

edited Nov 07 '17 at 09:26

simhumileco

31,877
16
137
115

answered Mar 20 '16 at 05:13

Bhavnesh

451
5
2

score 24 · Answer 5 · edited Oct 08 '15 at 14:18

24

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';

edited Oct 08 '15 at 14:18

Machavity

30,841
27
92
100

answered Dec 09 '14 at 00:37

s6712

496
5
14

score 23 · Answer 6 · edited Nov 07 '17 at 09:32

23

GRANT ALL PRIVILEGES ON mydb.* TO myuser@localhost IDENTIFIED BY 'mypasswd';

Works for privileges on schema :)

Optional: after mypasswd you can add WITH GRANT OPTION

edited Nov 07 '17 at 09:32

simhumileco

31,877
16
137
115

answered Jun 07 '15 at 08:21

Dutch Glory

23,373
1
17
6

score 18 · Answer 7 · edited Feb 06 '16 at 21:20

18

I could able to make it work only by adding GRANT OPTION, without that always receive permission denied error

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost' WITH GRANT OPTION;

edited Feb 06 '16 at 21:20

Machavity

30,841
27
92
100

answered Oct 25 '15 at 14:49

GnanaPrakash

336
3
5

score 16 · Answer 8 · edited Nov 03 '15 at 20:40

16

Hello I used this code to have the super user in mysql

GRANT EXECUTE, PROCESS, SELECT, SHOW DATABASES, SHOW VIEW, ALTER, ALTER ROUTINE,
    CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP,
    EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, CREATE USER, FILE,
    LOCK TABLES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN,
    SUPER
        ON *.* TO mysql@'%'
    WITH GRANT OPTION;

and then

FLUSH PRIVILEGES;

edited Nov 03 '15 at 20:40

Rick James

135,179
13
127
222

answered Oct 07 '14 at 19:53

gastonnina

559
1
10
23

12

`flush privileges` is not needed when you use `grant` commands. [x4](http://stackoverflow.com/questions/5016505/mysql-grant-all-privileges-on-database#comment44277812_5016505) – Pacerier Jan 14 '15 at 10:39
Note: The exact list in the `GRANT` varies between Versions of MySQL. – Rick James Nov 03 '15 at 20:41

score 11 · Answer 9 · answered Sep 30 '20 at 16:53

I had this challenge when working on MySQL Ver 8.0.21

I wanted to grant permissions of a database named my_app_db to the root user running on localhost host.

But when I run the command:

use my_app_db;

GRANT ALL PRIVILEGES ON my_app_db.* TO 'root'@'localhost';

I get the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'my_app_db.* TO 'root'@'localhost'' at line 1>

Here's how I fixed:

Login to your MySQL console. You can change root to the user you want to login with:

mysql -u root -p

Enter your mysql root password

Next, list out all the users and their host on the MySQL server. Unlike PostgreSQL this is often stored in the mysql database. So we need to select the mysql database first:

use mysql;
SELECT user, host FROM user;

Note: if you don't run the use mysql, you get the no database selected error.

This should give you an output of this sort:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

Next, based on the information gotten from the list, grant privileges to the user that you want. We will need to first select the database before granting permission to it. For me, I am using the root user that runs on the localhost host:

use my_app_db;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';

Note: The GRANT ALL PRIVILEGES ON database_name.* TO 'root'@'localhost'; command may not work for modern versions of MySQL. Most modern versions of MyQL replace the database_name with * in the grant privileges command after you select the database that you want to use.

You can then exit the MySQL console:

exit

That's it.

I hope this helps

score 8 · Answer 10 · answered Jun 25 '15 at 11:32

8

To access from remote server to mydb database only

GRANT ALL PRIVILEGES ON mydb.* TO 'root'@'192.168.2.21';

To access from remote server to all databases.

GRANT ALL PRIVILEGES ON * . * TO 'root'@'192.168.2.21';

answered Jun 25 '15 at 11:32

Developer

3,857
4
37
47

score 6 · Answer 11 · answered Mar 29 '18 at 14:16

To grant all priveleges on the database: mydb to the user: myuser, just execute:

GRANT ALL ON mydb.* TO 'myuser'@'localhost';

or:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

The PRIVILEGES keyword is not necessary.

Also I do not know why the other answers suggest that the IDENTIFIED BY 'password' be put on the end of the command. I believe that it is not required.

MySQL: Grant all privileges on database

11 Answers11

IMPORTANT NOTE

Linked

Related

MySQL: Grant **all** privileges on database

11 Answers11

IMPORTANT NOTE

Linked

Related

MySQL: Grant all privileges on database