{"id":911,"date":"2017-02-22T13:43:59","date_gmt":"2017-02-22T13:43:59","guid":{"rendered":"https:\/\/www.xsofthost.com\/help\/?p=911"},"modified":"2017-02-22T13:44:38","modified_gmt":"2017-02-22T13:44:38","slug":"how-to-create-user-and-grant-permission-in-mysql-on-linux-using-command-line","status":"publish","type":"post","link":"https:\/\/www.xsofthost.com\/help\/how-to-create-user-and-grant-permission-in-mysql-on-linux-using-command-line\/","title":{"rendered":"How to create User and Grant Permission In MySQL on Linux Command Line"},"content":{"rendered":"

MySQL Linux Commands<\/h2>\n

We are going to take a quick tour of common Linux command to deal with MySQL database:<\/p>\n

Login to MySQL in Linux using command line<\/h3>\n

To login to MySQL database run the following command:<\/p>\n

\n
mysql -u root -p<\/span><\/div>\n
Enter password:<\/div>\n
mysql><\/div>\n<\/div>\n

By passing -u parameter as login username and -p to enter the user password.<\/p>\n

Creating new MySQL user in Linux using command line<\/h3>\n

To create new MySQL user , use command CREATE USER by passing the new username and follow by IDENTIFIED BY with the new password as bellow:<\/p>\n

\n
CREATE USER ‘newmysqluser’@’localhost’ IDENTIFIED BY ‘NiwPwd!955’;<\/div>\n<\/div>\n

The example attempting to create new username newmysqluser with password NiwPwd!955<\/p>\n

Grant And Revoke Permissions to MySQL User<\/h2>\n

After you create MySQL user , the next step to grant permissions to database , the general syntax\u00a0 for granting permission is

GRANT permission<\/strong> <\/span>ON database.table TO ‘user’@’localhost’;<\/div>The permission syntax can be one of the following:
<\/p>\n
    \n
  • ALL \u2013 Allow complete access to a specific database. If a database is not specified, then allow complete access to the entirety of MySQL.<\/li>\n
  • CREATE \u2013 Allow a user to create databases and tables.<\/li>\n
  • DROP \u2013 Allow a user to drop databases and tables.<\/li>\n
  • INSERT \u2013 Allow a user to insert rows from a table.<\/li>\n
  • SELECT \u2013 Allow a user to select data from a database.<\/li>\n
  • SHOW DATABASES- Allow a user to view a list of all databases.<\/li>\n
  • DELETE \u2013 Allow a user to delete rows from a table.<\/li>\n
  • EXECUTE \u2013 Allow a user to execute stored routines.<\/li>\n
  • GRANT OPTION \u2013 Allow a user to grant or remove another user\u2019s privileges.<\/li>\n
  • UPDATE \u2013 Allow a user to update rows in a table.<\/div><\/li>\n<\/ul>\n

    Example of Grant Create permission for user newmysqluser\u00a0 to all MySQL objects:<\/p>\n

    \n
    GRANT CREATE ON *.* TO ‘newmysqluser’@’localhost’;<\/div>\n<\/div>\n

    Or Grant Drop permission to user newmysqluser on all tables of test_database<\/p>\n

    \n
    GRANT DROP ON test_database.* TO ‘newmysqluser’@’localhost’;<\/div>\n<\/div>\n

    Now once finish granting the permission to the MySQL user, run the FLUSH to reload all the privileges<\/p>\n

    \n
    FLUSH PRIVILEGES;<\/div>\n<\/div>\n

    Now we can View Grants for newmysqluser MySQL User<\/p>\n

    \n
    SHOW GRANTS FOR ‘newmysqluser’@’localhost’;<\/div>\n<\/div>\n

    Using Revoke permission command<\/h3>\n

    Now To Use the Revoke<\/strong> command the syntax is identical to grant but this command will remove the permission of the selected user when you runt the command, for example lets remove Drop permission to user newmysqluser to all tables in test_database , that already grant drop before:<\/p>\n

    \n
    REVOKE DROP ON test_database.* TO ‘newmysqluser’@’localhost’;<\/div>\n<\/div>\n

    Remove a MySQL User In linux using command line<\/h2>\n

    To remove a user from MySQL, we again use the DROP command.As example lets remove newmysqluser<\/p>\n

    \n
    DROP USER ‘newmysqluser’@’localhost’;<\/div>\n<\/div>\n

    if you are trying to remove none exists user you will get SQL error ERROR 1396 (HY000): Operation DROP USER failed for ‘newmysqluser’@’localhost’<\/strong><\/p>\n

    View a List of MySQL Users<\/h2>\n

    To view full list of MySQL users, including the host they\u2019re associated with,run the following SQL:<\/p>\n

    \n
    SELECT User,Host FROM mysql.user;<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

    MySQL Linux Commands We are going to take a quick tour of common Linux command to deal with MySQL database: Login to MySQL in Linux using command line To login to MySQL database run the following command: mysql -u root -p Enter password: mysql> By passing -u parameter as login username and -p to enter […]<\/p>\n","protected":false},"author":2,"featured_media":363,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[41,25],"tags":[42,50,149,26],"_links":{"self":[{"href":"https:\/\/www.xsofthost.com\/help\/wp-json\/wp\/v2\/posts\/911"}],"collection":[{"href":"https:\/\/www.xsofthost.com\/help\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.xsofthost.com\/help\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.xsofthost.com\/help\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.xsofthost.com\/help\/wp-json\/wp\/v2\/comments?post=911"}],"version-history":[{"count":15,"href":"https:\/\/www.xsofthost.com\/help\/wp-json\/wp\/v2\/posts\/911\/revisions"}],"predecessor-version":[{"id":926,"href":"https:\/\/www.xsofthost.com\/help\/wp-json\/wp\/v2\/posts\/911\/revisions\/926"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.xsofthost.com\/help\/wp-json\/wp\/v2\/media\/363"}],"wp:attachment":[{"href":"https:\/\/www.xsofthost.com\/help\/wp-json\/wp\/v2\/media?parent=911"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.xsofthost.com\/help\/wp-json\/wp\/v2\/categories?post=911"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.xsofthost.com\/help\/wp-json\/wp\/v2\/tags?post=911"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}