User Management in MYSQL
Introduction:-
Effective user management is a critical aspect of MySQL database administration. Creating users and assigning access in MySQL involves creating, configuring, and maintaining user accounts to control access and permissions within the MySQL database system.
1. Connect to MySQL Server:
Open a command prompt or terminal.
Use the mysql command to connect to the MySQL server:
mysql -u root –p
Replace root with your MySQL username if you’re not using the root user.
2. Create a New User:
To create a new user, use the CREATE USER statement:
CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;
Replace ‘username’ with the desired username, ‘localhost’ with the hostname or IP address of the user (e.g., ‘%’ for any host), and ‘password’ with the user’s password.
3. Grant Privileges:
To grant privileges to the user, use the GRANT statement:
GRANT SELECT, INSERT, UPDATE, DELETE ON testnewone.* TO ‘ABC’@’localhost’;
Replace ‘database’ with the name of the database to grant access to and adjust the list of privileges as needed.
Alternatively, to grant all privileges:
GRANT ALL PRIVILEGES ON testnewone.* TO ‘ABC’@’localhost’;
4. Flush Privileges:
After granting privileges, use the FLUSH PRIVILEGES statement to apply the changes:
FLUSH PRIVILEGES;
5. View User Privileges:
To view the privileges assigned to a user, you can query the mysql.user table:
SELECT user, host, authentication_string FROM mysql.user WHERE user = ABC;
6. Exit MySQL:
After creating the user and assigning privileges, exit the MySQL shell:
EXIT;
Additional Considerations:
Wildcard Host (‘%’):
If you want to allow the user to connect from any host, use ‘%’ instead of a specific hostname or IP address in the CREATE USER statement.
Changing Password:
To change the password for a user, use the ALTER USER statement:
ALTER USER ‘ABC’@’localhost’ IDENTIFIED BY ‘ABC’;
Revoking Privileges:
To revoke privileges, use the REVOKE statement:
REVOKE ALL PRIVILEGES ON testnewone.* FROM ‘ABC’@’localhost’;
Deleting User:
To delete a user, use the DROP USER statement:
DROP USER ‘ABC’@’localhost’;”
Conclusion:-
User management plays important role in MYSQL database management system.By understanding the principles of creating users, granting appropriate privileges, and managing authentication methods, database administrators can ensure the integrity and security of their MySQL databases.
