{"id":3584,"date":"2023-11-17T17:35:50","date_gmt":"2023-11-17T09:35:50","guid":{"rendered":"https:\/\/www.servergigabit.com\/guide\/?post_type=kb&p=3584"},"modified":"2026-03-17T11:12:49","modified_gmt":"2026-03-17T03:12:49","slug":"mysql-user-management","status":"publish","type":"kb","link":"https:\/\/www.servergigabit.com\/guide\/kb\/mysql-user-management","title":{"rendered":"The Ultimate Guide to MySQL: Creating and Managing User Accounts Safely"},"content":{"rendered":"
\n
\"MySQL\"<\/div>\n<\/header>\n
\n
<\/div>\n

MySQL<\/a> is a popular relational database management system (RDBMS) that allows users to create, read, update, and delete (CRUD) data. Managing user accounts and permissions is crucial for maintaining database security and ensuring that users have the appropriate level of access to perform their tasks. This guide provides a comprehensive overview of creating and managing user accounts in MySQL, including granting and revoking permissions.<\/p>\n

<\/span>Prerequisites\/Requirements\u00a0<\/strong><\/h2>\n

To follow this guide, you will need access to a MySQL database. The tutorial assumes that the database is installed on a virtual private server (VPS)<\/a> running Ubuntu or Debian. However, the process for creating a new MySQL user and assigning permissions is generally similar, regardless of the underlying operating system of your server.<\/p>\n

<\/span>Creating A MySQL User\u00a0<\/strong><\/h2>\n
    \n
  1. Login to the MySQL server as root:<\/li>\n<\/ol>\n
    \n
    mysql -u root -p<\/code><\/pre>\n<\/div>\n
      \n
    1. \n
      Enter your root password when prompted.<\/p>\n<\/li>\n
    2. \n
      Create a new user using the CREATE USER<\/code> command:<\/p>\n<\/li>\n<\/ol>\n
      \n
      CREATE<\/span> USER<\/span> 'username'<\/span>@'host'<\/span> IDENTIFIED BY<\/span> 'password'<\/span>;<\/code><\/pre>\n<\/div>\n
      Replace username<\/code> with the desired username, host<\/code> with the hostname from which the user will connect, and password<\/code> with the desired password. Enclose the username and host in single quotes for consistency and to avoid potential errors.<\/p>\n
      Granting Permissions to a User\u00a0<\/span><\/strong><\/h2>\n
        \n
      1. To grant permissions to a user,\u00a0use the\u00a0GRANT<\/code>\u00a0command:<\/li>\n<\/ol>\n
        \n
        GRANT<\/span> PRIVILEGE ON<\/span> database.table TO<\/span> 'username'<\/span>@'host'<\/span>;<\/code><\/pre>\n<\/div>\n
        Replace PRIVILEGE<\/code> with the specific privilege you want to grant, database<\/code> with the name of the database the privilege applies to, table<\/code> with the name of the table the privilege applies to, username<\/code> with the username of the user to grant the privilege to, and host<\/code> with the hostname from which the user will connect.<\/p>\n
          \n
        1. For example,\u00a0to grant a user the\u00a0SELECT<\/code>\u00a0privilege on all tables in the\u00a0<\/span>mydatabase<\/code>\u00a0database:<\/span><\/li>\n<\/ol>\n
          \n
          GRANT<\/span> SELECT<\/span> ON<\/span> mydatabase.*<\/span> TO<\/span> 'username'<\/span>@'localhost'<\/span>;<\/code><\/pre>\n<\/div>\n
            \n
          1. To grant multiple privileges in a single command,<\/span>\u00a0separate them with commas:<\/span><\/li>\n<\/ol>\n
            \n
            GRANT<\/span> SELECT<\/span>, INSERT<\/span>, UPDATE ON<\/span> mydatabase.*<\/span> TO<\/span> 'username'<\/span>@'localhost'<\/span>;<\/code><\/pre>\n<\/div>\n
              \n
            1. To grant global privileges (permissions that apply to all databases and tables),<\/span>\u00a0use asterisks (<\/span>*<\/code>) instead of specific database and table names:<\/span><\/li>\n<\/ol>\n
              \n
              GRANT<\/span> ALL<\/span> PRIVILEGES ON<\/span> *<\/span>.*<\/span> TO<\/span> 'username'<\/span>@'localhost'<\/span>;<\/code><\/pre>\n<\/div>\n
                \n
              1. After granting permissions,<\/span>\u00a0refresh the database to ensure the changes are applied:<\/span><\/li>\n<\/ol>\n
                \n
                FLUSH PRIVILEGES;<\/code><\/pre>\n<\/div>\n
                <\/span>Revoke Permissions from a User<\/strong><\/h2>\n
                To revoke permissions from a user,<\/span> use the <\/span>REVOKE<\/code> command:<\/span><\/p>\n
                \n
                REVOKE<\/span> type_of_permission ON<\/span> database_name.table_name FROM<\/span> 'username'<\/span>@'host'<\/span>;<\/code><\/pre>\n<\/div>\n
                Replace <\/span>type_of_permission<\/code> with the specific permission you want to revoke, <\/span>database_name<\/code> with the name of the database the permission applies to, <\/span>table_name<\/code> with the name of the table the permission applies to, <\/span>username<\/code> with the username of the user to revoke the privilege from,<\/span> and <\/span>host<\/code> with the hostname from which the user will connect.<\/span><\/p>\n
                <\/span>Other Useful MySQL Commands\u00a0<\/strong><\/h2>\n
                  \n
                1. Display the permissions of a user:<\/span><\/li>\n<\/ol>\n
                  \n
                  SHOW<\/span> GRANTS FOR<\/span> 'username'<\/span>@'host'<\/span>;<\/code><\/pre>\n<\/div>\n
                    \n
                  1. Delete a user:<\/span><\/li>\n<\/ol>\n
                    \n
                    DROP<\/span> USER<\/span> 'username'<\/span>@'localhost'<\/span>;<\/code><\/pre>\n<\/div>\n
                      \n
                    1. Exit MySQL:<\/span><\/li>\n<\/ol>\n
                      \n
                      exit;<\/code><\/pre>\n<\/div>\n
                      Conclusion<\/strong><\/h2>\n
                      This guide has provided a detailed explanation of how to create and manage user accounts in MySQL.<\/span> By following these steps,<\/span> you can effectively control user access and maintain the security of your database.<\/span> Remember to grant users the minimum permissions necessary for them to perform their tasks and revoke permissions when they are no longer needed.<\/span><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
                      MySQL is a popular relational database management system (RDBMS) that allows users to create, read, update, and delete (CRUD) data. Managing user accounts and permissions is crucial for maintaining database security and ensuring that users have the appropriate level of access to perform their tasks. This guide provides a comprehensive overview of creating and managing user accounts in MySQL, including…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"kbtopic":[1099],"kbtag":[1215],"class_list":["post-3584","kb","type-kb","status-publish","hentry","kbtopic-tutorials","kbtag-mysql"],"_links":{"self":[{"href":"https:\/\/www.servergigabit.com\/guide\/wp-json\/wp\/v2\/kb\/3584","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.servergigabit.com\/guide\/wp-json\/wp\/v2\/kb"}],"about":[{"href":"https:\/\/www.servergigabit.com\/guide\/wp-json\/wp\/v2\/types\/kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.servergigabit.com\/guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.servergigabit.com\/guide\/wp-json\/wp\/v2\/comments?post=3584"}],"version-history":[{"count":8,"href":"https:\/\/www.servergigabit.com\/guide\/wp-json\/wp\/v2\/kb\/3584\/revisions"}],"predecessor-version":[{"id":6349,"href":"https:\/\/www.servergigabit.com\/guide\/wp-json\/wp\/v2\/kb\/3584\/revisions\/6349"}],"wp:attachment":[{"href":"https:\/\/www.servergigabit.com\/guide\/wp-json\/wp\/v2\/media?parent=3584"}],"wp:term":[{"taxonomy":"kbtopic","embeddable":true,"href":"https:\/\/www.servergigabit.com\/guide\/wp-json\/wp\/v2\/kbtopic?post=3584"},{"taxonomy":"kbtag","embeddable":true,"href":"https:\/\/www.servergigabit.com\/guide\/wp-json\/wp\/v2\/kbtag?post=3584"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}