How to Create a Read Only User on Mysql

March 02, 2022 Post a Comment

If you lot work with MySQL, there will be a point when you'll need to create a new user.

This could be for another part of the system or another developer on your team. Or perhaps you want to use a user other than the default "root" account on MySQL.

In this article, you'll learn:

How to create a new user
How to give information technology the permissions it needs
How to bank check that the new business relationship works
How to delete a user if you lot no longer need it

Let's get into the commodity.

Log in to MySQL

The first stride to creating a new MySQL user is to log in to the database. You lot'll need to log in using an account that has the privilege to create new users. This will well-nigh likely be the root account, simply if not, yous tin can adjust the commands here.

You can either log in using the command line (Terminal or Command Line) or using an IDE such every bit MySQL Workbench.

Control Line

To log in as root:

Open the control line or last.

And then, run the following command:

mysql -u root -p

This will run the mysql command and attempt to log in. The -u indicates that the following parameter is a username, which is root. The -p indicates that the password is non provided and you lot will need to enter it. This is for security reasons: you should enter your password when prompted, rather than accept it shown in plain text on the control line.

One time you run this command, enter your password, and you'll be at the MySQL prompt:

MySQL Workbench (or other IDE)

If y'all prefer to practice this using an IDE, such every bit MySQL Workbench, you can log in using the IDE.

Similar to the command line, you lot'll need to log in equally the root account or with another user that has privileges to create new users.

For example, here's my connection screen for root in MySQL Workbench on my calculator:

Here'southward what it looks like using JetBrains DataGrip:

Once you've logged in to your database, you lot should be set to create a new user.

Create User

To create a new user in MySQL, we run the MySQL CREATE USER control:

                CREATE USER 'new_username'@'localhost' IDENTIFIED Past 'user_password';

To run this control yourself:

Change the new_username to the username you want to create.
Change the user_password to the countersign you want for this new user.
Run the control in either the control line or a new SQL window in your IDE.

Also, the word "localhost" means this computer. If you run your database somewhere else, and so change the give-and-take localhost to your server'due south address.

For example, to create a new user called "bob" with the password "electric", and then the command would exist:

                CREATE USER 'bob'@'localhost' IDENTIFIED Past 'electrical';

If you lot run the MySQL CREATE USER at the final, information technology will await like this:

Press Enter and the command will run.

If you run the command in your IDE, such as MySQL Workbench, it will look similar this:

Click the Execute push button and the query will run.

MySQL Privileges

Nosotros've now created a new user.

However, the new user volition not be able to practise annihilation. This is considering they don't accept whatsoever privileges. Y'all may exist able to log in equally this user, but you won't be able to create any tables or select whatever information.

By default, a new user is not given any privileges. Users need privileges on the database to be able to take certain actions, such every bit creating tables and adding data.

Fortunately, it's piece of cake to add privileges to a user. You lot run a command while you're withal logged in as the root account to grant privileges to the new user.

Giving privileges to a user is chosen "granting", and removing privileges is called "revoking".

The command for granting privileges in MySQL is GRANT:

                GRANT privileges ON databasename.table_name TO [e-mail protected];

In that location are a few things to specify in this control:

The privileges you want to grant for the new account
The database you want to apply them to
The tables you want to apply them to
The username and server to receive the privileges

We know the username and server. Which privileges tin can we grant?

At that place's a full listing hither on the MySQL documentation. Some of the more common ones are:

CREATE: create databases and tables
Drop: drop databases and tables
SELECT: run Select statements to view data
INSERT: run Insert statements to add together data to a tabular array
UPDATE: run Update statements to change data in a table
DELETE: run Delete statements to remove information from a tabular array
ALL PRIVILEGES: all privileges available

Granting ALL PRIVILEGES may seem piece of cake, merely it substantially gives the user admin access. This is often something we want to avoid.

So, for this case, we'll requite them access to create tables and work with them.

To grant privileges for creating, modifying, and removing tables:

                GRANT CREATE, Change, DROP ON yourdb.* TO 'bob'@'localhost';

This volition give the privileges to all tables on the database "yourdb", which yous would substitute with your actual database name.

To grant privileges for working with data in the tabular array:

                GRANT SELECT, INSERT, UPDATE, DELETE ON yourdb.* TO 'bob'@'localhost';

Run these two commands on your database, either in the terminal or in your IDE. They will give you the privileges you lot need for now.

Viewing Privileges

You tin see which privileges are enabled for a specific account by running the MySQL Evidence GRANTS command:

                SHOW GRANTS FOR 'bob'@'localhost';

Upshot:

Grants for [e-mail protected]

GRANT USAGE ON *.* TO 'bob'@'localhost'

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, Change ON `yourdb`.* TO 'bob'@'localhost'

This will show the privileges for the new user. The showtime entry, "USAGE", is a default privilege indicating that they beginning with nix. The 2nd entry is a result of the commands we ran.

What Nigh Flush Privileges?

In one case you have updated the privileges for a user, y'all may have read or heard that you need to run a Affluent Privileges command:

                Flush PRIVILEGES;

Yet, this is not needed in about situations.

If you assign privileges using the GRANT command, like we have just done, MySQL will notice the changes immediately and apply them.

Some other fashion to assign privileges is to insert (or update or delete) data into the "grant tables", which are MySQL tables that shop the privileges for each user. If you adapt the rows in this tabular array (instead of running GRANT), so MySQL won't see the new privileges, and you lot'll accept to run FLUSH PRIVILEGES.

More information is hither on the MySQL documentation.

Log In equally New User

To test that the new user is working, you can login to the database every bit the new user.

Command Line

To log in with the new user on the control line:

First, exit your current connexion to MySQL by typing:

exit

Press enter, and you lot'll return to the command prompt.

Then, enter in the mysql command again, but with the new username.

mysql -u bob -p

Y'all'll be prompted to enter a password.

Once you lot enter the password (which yous ready before in this commodity), you'll be logged in.

MySQL Workbench

To log in with the new user in MySQL Workbench (or any other IDE), yous'll need to create a new connection:

Exam the connection, and if it works, click OK and log in with it.

Congratulations! Your new user is at present set and set to utilise!

Remove Privileges

Yous've granted privileges for a user. What if yous desire to remove privileges?

For instance:

Yous granted a privilege to the user by mistake.
The user requirements have inverse and they no longer demand the same access
Y'all were testing something and now the test has finished.

Privileges can exist removed easily by using the REVOKE control. It's similar to the GRANT command, except the REVOKE word is used:

                REVOKE privilege ON database.table FROM 'username';

For example, to remove the Select privilege from the client tabular array:

                REVOKE SELECT ON main.client FROM 'alice';

This example removes the Create privilege as well:

                REVOKE CREATE ON main.* FROM 'alice';

These commands can exist used to remove privileges from users in the database.

Delete a User

If y'all want to delete a MySQL user from the database, yous tin can utilise the Drib USER command.

                Drib USER 'username'@'localhost';

For example, to driblet the user bob that we just created:

                Driblet USER 'bob'@'localhost';

The user and all of the privileges are removed. You lot won't be able to log in with this user anymore.

Determination

And so that's how you create a new user. After reading this guide, you lot should be able to create a new user, give them privileges, log in with the user, and even delete the user if yous no longer demand it.

If you have any questions or issues with this, experience complimentary to exit a comment beneath.

harrisonspere1953.blogspot.com

Source: https://www.databasestar.com/mysql-create-user/