Creating and granting permission to MySQL users

Summary

This video tutorial covers how to manage database user access by creating roles and assigning permissions. The process begins by setting up a user role, granting specific privileges such as select, insert, update, and delete on the 'world' database. The tutorial demonstrates how to create a user, assign a password, and link them to the role. It also shows how to test the user's access, verify permissions, and revoke or grant additional privileges as needed, emphasizing secure and controlled access to the database.

Takeaways

• 👤 Creating users and granting permissions is essential when managing databases beyond using an administrator or root user.
• 🛠 Permissions should generally be assigned to roles (similar to groups in Windows Server) rather than individual users.
• 🌍 The script demonstrates creating a user and granting them specific permissions on the 'world' database.
• ⚙️ A role called 'world_user' is created using 'CREATE ROLE world_user' to handle permissions for the world database.
• 📜 Permissions such as SELECT, INSERT, UPDATE, and DELETE are granted to the 'world_user' role using the 'GRANT' command.
• 🧑‍💻 The 'CREATE USER' command is used to create a new user, assign them a password, and set their default role as 'world_user'.
• 🔐 Password options, such as requiring the user to change the password on first login, can be set during user creation.
• 🔍 The 'SHOW GRANTS' command allows a user to see the specific permissions assigned to them.
• 🚫 Users with limited roles can be restricted from performing certain actions, such as creating or dropping tables in the database.
• 🔄 Permissions can be updated or revoked at any time using the 'GRANT' and 'REVOKE' commands, making database security flexible.

Q & A

Why is it important to create users and assign permissions rather than using the root user for database access?

Using the root or administrator account for all database access is risky because it grants unrestricted access to everything. Creating specific users with defined roles and permissions allows better security and control, limiting access to only what is necessary for each user.

What is the first step in creating a user with specific permissions for a database?

The first step is to create a role, which groups permissions together. In this case, the script creates a role called 'World_user' that will later be assigned permissions for accessing the 'world' database.

How are permissions granted to a role in the script?

Permissions are granted to a role using the `GRANT` command, followed by specifying the privileges (e.g., SELECT, INSERT, UPDATE, DELETE) and the scope (in this case, all tables in the 'world' database).

What is the purpose of the `SHOW PRIVILEGES` command in this context?

The `SHOW PRIVILEGES` command lists all the available permissions that can be granted to a role or user. It helps the administrator understand the types of permissions they can assign, such as SELECT, CREATE, and DROP.

How can you grant permissions to specific tables rather than an entire database?

To grant permissions to specific tables, you can specify the table names after the database name. For example, you could write `GRANT SELECT ON world.country TO World_user` to give access only to the 'country' table in the 'world' database.

What is the difference between the `GRANT` and `REVOKE` commands?

The `GRANT` command gives permissions to a role or user, while the `REVOKE` command removes permissions that have been previously granted.

How is a new user created and assigned to a role?

A new user is created using the `CREATE USER` command, where the username and password are specified. After that, the `DEFAULT ROLE` is set for the user to assign them to a specific role, such as 'World_user'.

What does setting a 'default role' for a user accomplish?

Setting a default role for a user automatically assigns the user to that role upon creation, ensuring that the user inherits the permissions of the role without needing to manually add them later.

How can you verify the roles and permissions assigned to a user?

You can verify the roles assigned to a user by using the `SELECT CURRENT_ROLE()` command to see the user's active role. Additionally, the `SHOW GRANTS` command will display all the privileges that the user has been granted.

What happens if a user attempts to access a table or perform an action they do not have permission for?

If a user tries to access a table or perform an action they do not have permission for, the system will deny access. For example, attempting to create a table without the `CREATE` privilege will result in a permission error.