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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt   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

User · Answer

GRANT ALL PRIVILEGES ON mydb   TO  myuser      WITH GRANT OPTION   This is how I create my  quot Super User quot  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

User · Answer

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

User · Answer

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

User · Answer

nbsp 1  Create the database     CREATE DATABASE db name     nbsp 2  Create the username for the database db name    GRANT ALL PRIVILEGES ON db name   TO  username   localhost  IDENTIFIED BY  password      nbsp 3  Use the database     USE db name     nbsp 4  Finally you are in database db name and then execute the commands like create   select and insert operations

User · Answer

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     Hope this helps

User · Answer

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

User · Answer

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

[mysql] MySQL: Grant all privileges on database

Examples related to mysql

Examples related to mariadb

Examples related to mysql-error-1142

[mysql] MySQL: Grant **all** privileges on database

Examples related to mysql

Examples related to mariadb

Examples related to mysql-error-1142

[mysql] MySQL: Grant all privileges on database