Question 396696 in Mysql Question posted in
The official MySQL documentation can be found here.

How to have separate username/password for each database in mysql?

When I install MySql server, root user is initialized. It has no password.

I have a query here.

Suppose I create few databases in the mysql server. Do all these databases have same user now ie root user?

How can I have separate username/password for each of these databases?

Tags:

2

Answers


  1. 0

    in MySQL , user accounts are managed through the server level not the database server it self what this is means that you cannot directly assign different unernames and password for each database . instead MySQL uses user accounts to control access across the entire server including a specified database.

    By default the root user is created as superadmin by default with full privileges and access , also it has no password set.

    However you can achieve the goal of having different usernames and passwords for accessing different databases with the help of MySQL user managment and the access privileges, here’s how you can achieve the disired result

    1_ first of all create a different MYSQL user account with each has unique credentials that you want to assign.

    -- Create a user 'exampleuser1' with password 'password1'
CREATE USER 'exampleuser1'@'localhost' IDENTIFIED BY 'password1';

    or you can simply create the provided users through the phpmyadmin provided gui in user account section

    2_ then grant specific Privileges for each user included the database they should be able to access to.

    GRANT ALL PRIVILEGES ON db1.* TO 'exampleuser1'@'localhost';

    in this example i let the exampleuser1 have all the privileges , adjust the privileges as per requirements of each user.

    For example :

    GRANT SELECT, INSERT, UPDATE, DELETE ON db1.* TO 'exampleuser1'@'localhost';

    also you can revoke a privilege from a user , you can simply use the 'REVOKE' statement with the provileges.

    Login or Signup to reply.
  2. 0

    In the default setting, the root user has full privileges to access all databases on the server
    When a database is created and a user is not automatically created with it, by default the root user accesses the database.
    But this can be controlled by creating groups with a different user name and password
    The following steps explain how this is possible :

    To create separate username/password combinations for different databases, you can follow these steps:
    1 Log in to your MySQL server as root user:

    mysql -u root -p

    2 To create a new user use CREATE USER. For example, to create a user named "user" with a password:

    Create user 'user'@'localhost' identified by 'password';

    3 Then specify the privileges you want using the GRANT statement. To continue the previous example, to grant all privileges to "user" in a database called "database1":

    GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

    4 Repeat steps 2 and 3 for each rule set for which you want to create separate users.

    A final note: It is recommended to set a password for the root user
    I recommend How To Create a New User and Grant Permissions in MySQL because it provides more detailed information about the topic

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search