Seamlessly adding a new MySQL user to database and assigning diverse access privileges. Whether you’re a beginner or need a quick refresher on managing MySQL users. Let’s dive in!

Creating a New MySQL User

To initiate a new user in your MySQL server, you’ll start with the CREATE USER statement. Here’s the basic syntax:

CREATE USER 'new_user'@'host' IDENTIFIED BY 'password';

Replace 'new_user', 'host', and 'password' with your preferred username, host, and password respectively. For instance:

CREATE USER 'lucy'@'localhost' IDENTIFIED BY 'SecurePass321';

Note: Set the hostname to localhost if you want the user to connect exclusively from the localhost (this computer). For global access from any host, use the % wildcard as the hostname.

Once created, this user will need additional privileges to operate within the databases.

Granting Privileges to a MySQL User

After creating the user, granting appropriate privileges is essential. These privileges are usually based on specific databases or tables. You can grant various privileges such as SELECT, INSERT, UPDATE, and more using the GRANT statement:

GRANT permission_type ON database_name.* TO 'username'@'hostname';

For example:

GRANT SELECT, INSERT ON companydb.* TO 'lucy'@'localhost';

Managing Privileges: The MySQL Superuser

There might be scenarios where you need a superuser akin to the MySQL root user. To grant such privileges to a user (e.g., ‘lucy’), utilize:

GRANT ALL ON . TO 'lucy'@'localhost' WITH GRANT OPTION;

Adjusting Passwords and Access

To modify a user’s password, execute:

ALTER USER 'lucy'@'localhost' IDENTIFIED BY 'NewSecurePass123';

Reviewing and Revoking Access

To view granted privileges for a user:

SHOW GRANTS FOR 'lucy'@'localhost';

Revoking privileges or deleting a user can be done through:


REVOKE ALL PRIVILEGES ON companydb.* FROM 'lucy'@'localhost';
DROP USER 'lucy'@'localhost';

Saving Changes and Finalizing

After making modifications or updates to MySQL user permissions, it’s crucial to save your changes. Execute the following command as a final step:

FLUSH PRIVILEGES;

This command ensures that the changes you’ve made take effect immediately. By flushing privileges, you’re essentially reloading the permission tables, allowing your MySQL server to recognize and apply the recent adjustments.

Remember, incorporating this step into your routine ensures that your MySQL users operate with the most up-to-date and accurate permissions. This practice contributes to a well-maintained and secure database environment.

Links:

Got any queries or feedback? Feel free to drop a comment below!

Categorized in:

How-ToMySQL

Tagged in:

, , , ,