MySQL User Administration - Creating new users, logging in with them , and dropping users.

Learn how to create a new user and explore multiple methods to connect to mysql with that user. Additionally, discover the steps to delete a user when no loner needed.


Let's look at the steps to create a MySQL user:

Step 1: First let's check what options are available when creating a user.

    help create user;


Step 2: To create user execute this command:

    create user if not exists 'user_name' identified by 'password' password expire never account unlock;


Step 3: Now let's grant the permissions to the user. In this case we are granting all the privileges to the user.

    grant all privileges on *.* to 'junior' with grant option;

Step 4: Flush the privileges.

    flush privileges;

    (To reload the privileges, ensuring any changes made to the account take effect immediately)

Step 5: To confirm whether all privileges are granted:

    show grants for user_name;


Now the question arises: how do we connect to mysql with the new user which we have just created through the terminal?

We can do this by executing the following query:

    mysql --host=<your-ip-address> --user=user_name --password



We can also connect to MySQL with the help of MySQL Shell.

    mysqlsh --host=<your-ip-address> --user=user_name --password


Make sure you have mysql-shell installed on your system.


Now let's look at how to delete a user.


First let's check which users are present on the mysql server. We can do so by using the default mysql database.

    select host,user from user;



Now let's drop a user, lets say john.

    drop user 'john';

Note: You can only drop one user if you have the privilege to drop a user.

To confirm whether the user has been dropped, let's execute the same command:

    select host,user from user;



Great! We have successfully deleted a user.



Great! We have successfully created a user, learned different ways to connect to MySQL with with the new user, and explored how to drop the users when needed. This covers the essential aspects of MySQL user administration.🎉






Comments

Post a Comment

Popular posts from this blog

MySQL Configuration File - Updating and changing the default location of my.cnf

Image
MySQL Option file or Configuration file Most MySQL programs can read startup options from option files (also called as configuration files ). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program. File path: /etc/my.cnf The   mysqld --verbose --help   is used to display detailed information about the mysql server's configurations, options, and supported features. To check the contents of the my.cnf file :     sudo nano /etc/my.cnf Let's update our configuration file now. We will remove all the comments and one option of 'server-id', for which we will assign the value as 1. To check for the serer-id in MySQL you execute this command:     show variables like '%server_id%'; As you can see the server_id is 1. Now let's update this in our option file. In our configuration file we will add the following line:     server-id=1 Notice, that our system ...
Read more

MySQL config editor , Executing MySQL commands from the terminal, mysqlimport and mysqlcheck

Image
We will log in to MySQL without entering credentials by using MySQL config editor, then execute mysql commands from the terminal, import the data into a mysql table, and check data integrity using mysqlcheck. mysql_config_editor is a command line utility provided by MySQL to securely manage login credentials. It can store credentials like username, password, host and port in an encrypted login file path. To learn more about this command and how to use it you can execute this query:          mysql_config_editor --help Now we will set the credentials by using mysql_config_editor:     mysql_config_editor set --user=root --password -login-path=client To verify whether the credentials are stored and encrypted :     mysql_config_editor print Now let's check whether we can log in to MySQL without providing user and password credentials.     mysql Great! We have successfully stored our credentials with the help of mysql_config_editor and...
Read more