Managing Users and Permissions in MySQL

Página 23

MySQL is a relational database management system (RDBMS) that uses SQL (Structured Query Language) to add, access, and manage the contents of a database. It is a crucial tool for many websites and web-based applications due to its efficiency and ease of use. An important part of managing MySQL is managing users and permissions.

MySQL uses a privilege system to ensure that all users have the proper permissions. Permissions in MySQL are granted to users, not accounts, and each user can have different levels of access to different databases. This allows for a high degree of control over who can do what.

User Management

To create a new user in MySQL, you can use the CREATE USER command. For example, to create a user called 'newusername' with the password 'password', you would use the command:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

This will create a new user who can connect to the MySQL server from host 'localhost' and who has the password 'password'. Note that the username and host are always specified together; this allows you to create multiple users with the same name who can connect from different hosts.

To delete a user, you can use the DROP USER command. For example, to delete the user 'newusername', you would use the command:

DROP USER 'newusername'@'localhost';

This will delete the user 'newusername' and all its permissions. If the user is logged in when you run this command, the connection will not be broken, but the user will not be able to establish new connections.

Permissions Management

Permissions in MySQL are granted using the GRANT command and revoked using the REVOKE command. Both commands accept a list of permissions, followed by the name of the user to whom permissions should be granted or revoked.

For example, to grant user 'newuser' permission to select, insert, update, and delete rows in table 'table' in database 'bank', you would use the command:

GRANT SELECT, INSERT, UPDATE, DELETE ON 'bank'.'table' TO 'newuser'@'localhost';

This gives user 'newuser' permission to perform the specified actions on table 'table' in database 'bank'. Note that permissions are always granted for a specific database and table; you cannot grant permissions to all tables in a database or to all tables in all databases at once.

To revoke permissions, you would use the REVOKE command in the same way. For example, to revoke insert permission from user 'newuser' on table 'table' in database 'bank', you would use the command:

REVOKE INSERT ON 'bank'.'table' FROM 'newuser'@'localhost';

This revokes insert permission for user 'newuser' on table 'table' in database 'bank'. Note that unlike the GRANT command, the REVOKE command can only revoke one permission at a time.

Also, it's important to remember that permissions in MySQL are cumulative. This means that if you grant a user SELECT permission on a table and then grant the user INSERT permission on the same table, the user will have both permissions. Likewise, if you revoke a permission from a user, all other permissions the user has remain in effect.

In summary, managing users and permissions in MySQL is an important part of managing a MySQL database. By understanding how to create and delete users and how to grant and revoke permissions, you can ensure that your database is safe and efficient.

Next page of the Free Ebook:

24Backing Up and Restoring Databases in MySQL