Create new user in MySQL and give it full access to one database

Question

I want to create a new user in MySQL and give it full access only to one database  say dbTest  that I create with a command like create database dbTest   What would be the MySQL commands to do that

User · Answer

mysql -u root -p -e  grant all privileges on dbTest   to   user     host   identified by   long-password    flush privileges     ignore -p option  if mysql user has no password or just press   Enter   button to by-pass  strings surrounded with curly braces need to replaced with actual values

User · Answer

The below command will work if you want create a new user give him all the access to a specific database not all databases in your Mysql  on your localhost   GRANT ALL PRIVILEGES ON test database   TO  user   localhost  IDENTIFIED BY  password     This will grant all privileges to one database test database  in your case dbTest  to that user on localhost   Check what permissions that above command issued to that user by running the below command   SHOW GRANTS FOR  user   localhost    Just in case  if you want to limit the user access to only one single table  GRANT ALL ON mydb table name TO  someuser   host

User · Answer

Syntax  To create user in MySQL MariaDB 5 7 6 and higher  use CREATE USER syntax   CREATE USER  new user   localhost  IDENTIFIED BY  new password     then to grant all access to the database  e g  my db   use GRANT Syntax  e g   GRANT ALL ON my db   TO  new user   localhost     Where ALL  priv type  can be replaced with specific privilege such as SELECT  INSERT  UPDATE  ALTER  etc   Then to reload newly assigned permissions run   FLUSH PRIVILEGES      Executing  To run above commands  you need to run mysql command and type them into prompt  then logout by quit command or Ctrl-D   To run from shell  use -e parameter  replace SELECT 1 with one of above commands      mysql -e  SELECT 1    or print statement from the standard input     echo  FOO STATEMENT    mysql   If you ve got Access denied with above  specify -u  for user  and -p  for password  parameters  or for long-term access set your credentials in    my cnf  e g    client  user root password root     Shell integration  For people not familiar with MySQL syntax  here are handy shell functions which are easy to remember and use  to use them  you need to load the shell functions included further down    Here is example     mysql-create-user admin mypass   CREATE USER  admin   localhost  IDENTIFIED BY  mypass     mysql-create-db foo   CREATE DATABASE IF NOT EXISTS foo    mysql-grant-db admin foo   GRANT ALL ON foo   TO  admin   localhost    FLUSH PRIVILEGES    mysql-show-grants admin   SHOW GRANTS FOR  admin   localhost    Grants for admin localhost                                                                                      GRANT USAGE ON     TO  admin   localhost  IDENTIFIED BY PASSWORD   6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4      GRANT ALL PRIVILEGES ON  foo    TO  admin   localhost     mysql-drop-user admin   DROP USER  admin   localhost     mysql-drop-db foo   DROP DATABASE IF EXISTS foo   To use above commands  you need to copy amp paste the following functions into your rc file  e g   bash profile  and reload your shell or source the file  In this case just type source  bash profile     Create user in MySQL MariaDB  mysql-create-user         -z   2     amp  amp    echo  Usage  mysql-create-user  user   password    return      mysql -ve  CREATE USER   1   localhost  IDENTIFIED BY   2        Delete user from MySQL MariaDB mysql-drop-user         -z   1     amp  amp    echo  Usage  mysql-drop-user  user    return      mysql -ve  DROP USER   1   localhost         Create new database in MySQL MariaDB  mysql-create-db         -z   1     amp  amp    echo  Usage  mysql-create-db  db name    return      mysql -ve  CREATE DATABASE IF NOT EXISTS  1       Drop database in MySQL MariaDB  mysql-drop-db         -z   1     amp  amp    echo  Usage  mysql-drop-db  db name    return      mysql -ve  DROP DATABASE IF EXISTS  1       Grant all permissions for user for given database  mysql-grant-db         -z   2     amp  amp    echo  Usage  mysql-grand-db  user   database    return      mysql -ve  GRANT ALL ON  2   TO   1   localhost     mysql -ve  FLUSH PRIVILEGES       Show current user permissions  mysql-show-grants         -z   1     amp  amp    echo  Usage  mysql-show-grants  user    return      mysql -ve  SHOW GRANTS FOR   1   localhost       Note  If you prefer to not leave trace  such as passwords  in your Bash history  check  How to prevent commands to show up in bash history

User · Answer

To create a user and grant all privileges on a database   Log in to MySQL   mysql -u root   Now create and grant  GRANT ALL PRIVILEGES ON dbTest   To  user   hostname  IDENTIFIED BY  password     Anonymous user  for local testing only   Alternately  if you just want to grant full unrestricted access to a database  e g  on your local machine for a test instance  you can grant access to the anonymous user  like so   GRANT ALL PRIVILEGES ON dbTest   To     hostname    Be aware  This is fine for junk data in development  Don t do this with anything you care about

User · Answer

Try this to create the user   CREATE USER  user   hostname     Try this to give it access to the database dbTest   GRANT ALL PRIVILEGES ON dbTest   To  user   hostname  IDENTIFIED BY  password     If you are running the code site accessing MySQL on the same machine  hostname would be localhost   Now  the break down   GRANT - This is the command used to create users and grant rights to databases  tables  etc   ALL PRIVILEGES - This tells it the user will have all standard privileges  This does not include the privilege to use the GRANT command however   dbtest   - This instructions MySQL to apply these rights for use in the entire dbtest database  You can replace the   with specific table names or store routines if you wish   TO  user   hostname  -  user  is the username of the user account you are creating  Note  You must have the single quotes in there   hostname  tells MySQL what hosts the user can connect from  If you only want it from the same machine  use localhost  IDENTIFIED BY  password  - As you would have guessed  this sets the password for that user

User · Answer

To me this worked   CREATE USER  spowner   localhost  IDENTIFIED BY  1234    GRANT ALL PRIVILEGES ON test   To  spowner   localhost    FLUSH PRIVILEGES    where    spowner   user name  1234   password of spowner  test   database  spowner  has access right to

User · Answer

In case the host part is omitted it defaults to the wildcard symbol    allowing all hosts   CREATE USER  service-api    GRANT ALL PRIVILEGES ON the db   TO  service-api  IDENTIFIED BY  the password   SELECT   FROM mysql user  SHOW GRANTS FOR  service-api

User · Answer

You can create new users using the CREATE USER statement  and give rights to them using GRANT

[mysql] Create new user in MySQL and give it full access to one database

Examples related to mysql

Examples related to access-control

Examples related to database-security