User management
User management in MySQL involves controlling access to the MySQL database server by creating and managing user accounts. These user accounts determine who can connect to the server, what actions they can perform, and which databases and tables they can access.
Some Basic User Management Operations
1.Create User
2.Drop User
3.Show User
4.Change User Password
1.Create User
- The MySQL Create User statement allows us to create a new user account in the database server.
Syntax
The following syntax is used to create a user in the database server.
CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY 'password';
2.Drop User
- The MySQL Drop User statement allows us to remove one or more user accounts and their privileges from the database server.
- If the account does not exist in the database server, it gives an error.
Syntax
The following syntax is used to delete the user accounts from the database server completely.
DROP USER 'account_name';
3.Show User
- The Show user use to displaying the list of all users available in the database server.
Syntax
> mysql -u root -p
Enter password: *********
mysql> use mysql;
Database changed
mysql> SELECT user FROM user;
Show Current User
We can get information of the current user by using the user() or current_user() function, as shown below:
mysql> Select user();
or,
mysql> Select current_user();
4.Change User Password
- MySQL user is a record that contains the login information, account privileges, and the host information for MySQL account to access and manage the database.
- The login information includes the user name and password. In some cases, there is a need to change the user password in the MySQL database.
Syntax
1 . SET PASSWORD FOR 'userName'@'localhost' = PASSWORD('newPass');
2. SET PASSWORD FOR 'userName'@'localhost' = newPass;
3. mysql> USE mysql;
mysql> UPDATE user SET password = PASSWORD('newPass') WHERE user = 'userName ' AND host = 'localhost';
mysql> FLUSH PRIVILEGES;
User Management in Details
1. User Accounts
- User accounts are used to identify and authenticate users when connecting to the MySQL server.
- Each user account is associated with a username and a password.
2. Access Privileges
- Access privileges define what a user can do within the MySQL server.
- Privileges include actions like SELECT, INSERT, UPDATE, DELETE, and more.
- Users can have different privileges for different databases and tables.
3. Root User:
- The root user is the superuser with full control over the MySQL server.
- It can create, modify, and delete other user accounts and grant or revoke privileges.
4. Creating Users:
- The CREATE USER command is used to create new user accounts.
- You specify the username and host from which the user can connect.
5. Setting Passwords:
- Users must have passwords to authenticate themselves.
- Use the SET PASSWORD command to set or change a user's password.
6. Granting Privileges:
- The GRANT command is used to give users specific privileges.
- You can grant privileges at the global, database, or table level.
7. Revoking Privileges:
- The REVOKE command is used to take away previously granted privileges.
- You can revoke privileges at the global, database, or table level.
8. Viewing Privileges:
- The SHOW GRANTS command displays the privileges assigned to a specific user.
- The `SHOW GRANTS FOR 'user'@'host'` format is used.
9. Deleting Users:
- The DROP USER command is used to delete a user account.
- Be careful, as this action is irreversible.
10. Hosts and Wildcards:
- Users are associated with specific hosts from which they can connect.
- Wildcards can be used in host names to match multiple hosts, such as `'%'` to allow connections from any host.
11. Password Security:
- Encourage users to create strong passwords to enhance security.
- MySQL stores passwords in encrypted form to protect sensitive information.
12. Authentication Methods:
- MySQL supports various authentication methods, including native password, SHA256, and caching_sha2_password.
13. Default Privileges:
- MySQL provides default privileges that apply to new databases or tables created after a user account is granted privileges.
14. Managing Remote Access:
- Remote access allows users from different machines to connect to the MySQL server.
- Ensure proper firewall and network security to prevent unauthorized access.
15. Security Best Practices:
- Regularly review and audit user accounts and their privileges.
- Remove unnecessary or unused accounts to reduce potential vulnerabilities.
User management is a critical aspect of database security and ensures that only authorized individuals can access and manipulate the data stored in a MySQL database.