SQL users are individuals with unique usernames and passwords who utilize structured query language to interact with databases. They are granted specific privileges by the database administrator to regulate their actions within the database.
In database management systems, creating and managing users is important for data security and access control. SQL allows users to perform the creation of users and grant them specific privileges within a database system. By creating different users within the database, it improves the security of data and enhances access control.
In this article, we are going to explore how to create user profiles in SQL, as well as how to drop users. We will go through various examples of creating users, discussing their pros and cons along the way.
Terminologies:
- User: Users are accounts with unique usernames and passwords with specific privileges granted to them.
- Privileges: Permissions or rights granted to users within the MySQL Server. These are assigned using the GRANT command by the database admin and can be revoked using the REVOKE command.
How to Create and Drop User in MySQL
SQL CREATE USER command is used to create new users in a database system. Users are identified by unique usernames and passwords. The command allows specifying the username and password for the new user. Once created, users can be assigned specific privileges to control their access and actions within the database. This command is essential for managing user accounts and ensuring data security.
Let’s see the step-by-step process of creating and dropping a new user in the MySQL database server with the help of an example. To create the user in the MySQL server, we have to launch the MySQL server in our Windows command line prompt.
Step 1: Launch MySQL Server in the Command Prompt
We can launch the MySQL server using the following command in the Windows command line prompt.
mysql> mysql -u root -pOutput in MySQL Command Line Client:
Fig 1. Launch MySQL in Windows Command Line
Step 2: Create the New User
For creating a new user in the MySQL database, we have to first choose a unique username and password that have not yet been used in the database. If a non-unique username is used while creating the new user, an ERROR 1396 (HY000) will be displayed.
We can create a new user in the MySQL server using the following command:
Syntax:
mysql> CREATE USER ‘username’ IDENTIFIED BY ‘password’;
- Username is a unique username given to the user profile of the new user.
- Password is a Strong password given for a new user profile.
Example:
Let’s see one example of creating a new user in MySQL in Windows Command Line using the above command.
Query:
mysql> CREATE USER 'sahil1' IDENTIFIED BY 'Sahil1@1717'; Output in MySQL Cmd Line:
Create a new user example
Explanation: In the above query “sahil1” is a unique username used to create the new user. Strong passwords which are important for the security of the database.
Step 3: Check the All Users from the Database
We can check all users of the database using the following query. By checking the user list, we can verify, is our new user has been created successfully or not and which other usernames are already used in the MySQL database so we can avoid similar usernames in the future.
Let’s see How we can do that in,
Syntax of Checking all users:
mysql> SELECT User, Host FROM mysql.user;
Example:
mysql> SELECT User, Host FROM mysql.user;
Output:
fig3: Displaying all users from the database
Explanation: The SQL query retrieves user and host information from the ‘mysql.user‘ table. The output displays a list of users and their corresponding hosts, showing the user accounts configured in the MySQL system along with their associated hostnames.
Step 4: Drop the User
We can drop the user using drop query in MySQL database. Dropping the user is used in various aspects like removing unused users from the database or Reorganizing Access Control. Dop users are part of the process while routine maintenance is done.
In the testing and development environment creating and dropping users may used multiple times for creating and removing testing accounts from the applications. Let’s have a look at the process of removing or dropping the users from the database.
Syntax:
mysql> DROP USER ‘username’@’host’;
Example:
mysql> DROP USER 'sahil1'@'host';Output:
Dropping the users
So, we have successfully created a new user and dropped it from the database with the help of the above example.
Advantages
- Access Control: Creating users and permitting them helps in the fine access control over the database.
- Resource Management: Resources can be divided into different users which will result in fine resource management.
- Easy Auditing: Simplifies auditing by tracking user activities and executed queries.
Disadvantages
- Administration overhead: Managing the large number of users and their permission becomes difficult to manage.
- Security Risk: Weak passwords or incorrectly configured privileges can lead to unauthorized access to the database.
- Human Error: While granting permission to the user, a small mistake can lead to unauthorized access.
Conclusion
Understanding the user facility in a database management system is important in terms of security and access control. In this article, we walked through the important aspects of user creation as well as the basic terminologies related with the topic SQL Create Users In this article we have seen some examples for better understanding of the SQL Users.