Creating and granting permission to MySQL users

David Dalton

7 Mar 202309:15

Summary

TLDRThis 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.

Outlines

00:00

👨‍💼 Creating Users and Assigning Roles

This section discusses creating users and assigning permissions to them. The author explains how they have been using the database as the root user, but as the system grows, other users need to access the database. Instead of assigning permissions to individual users, roles are created, similar to groups in Windows Server. The example walks through creating a 'world_user' role and assigning permissions such as SELECT, INSERT, UPDATE, and DELETE on the 'world' database. The author introduces the `CREATE ROLE` and `GRANT` commands, explaining how permissions can be applied to specific tables if necessary.

05:02

🔑 Exploring Database Privileges

The author highlights various database privileges, using the `SHOW PRIVILEGES` command to display a list. They explain privileges like ALTER, DROP, CREATE, and SELECT and where they apply (e.g., to tables, views, or entire databases). This step is crucial for determining which privileges should be assigned to roles. The process of granting these privileges to the 'world_user' role is demonstrated, followed by an explanation of how privileges can be revoked using the `REVOKE` command.

🧑‍💻 Creating Users and Setting Default Roles

This part covers creating a new user and assigning them to the previously created 'world_user' role. The author uses the `CREATE USER` command and specifies a password for the user. Additionally, they mention setting options like password expiration and default roles. The user creation process is completed with an example, followed by a demonstration of how to review user roles in the administration section of the database interface.

🛠️ Connecting as a New User and Verifying Permissions

After creating the user, the author shows how to connect to the database using the new user's credentials. They verify the connection and the user's current role using the `SELECT CURRENT_ROLE` and `SHOW GRANTS` commands, confirming the correct permissions have been assigned. The user is then able to perform operations like selecting data from the 'country' table in the 'world' database, demonstrating their ability to interact with the assigned tables.

🚫 Restricted Access to Other Databases

Here, the author verifies that the new user does not have access to other databases or certain operations, like creating or dropping tables. They attempt to access the 'Sakia' database and create a table, but receive 'access denied' errors. This confirms that the role-based permissions have been correctly enforced, allowing the user only limited access to the 'world' database.

🛡️ Managing Database Security with Roles and Permissions

In the conclusion, the author summarizes how to secure databases by creating roles, granting permissions, and assigning users to those roles. They reiterate the flexibility of the `GRANT` and `REVOKE` commands for managing permissions dynamically. This provides a comprehensive approach to securing databases by controlling what users can and cannot do through role-based access control.

Mindmap

Keywords

💡User

A 'user' refers to an individual who accesses the database. In the script, the user is someone who interacts with the database but doesn't have the same level of permissions as an administrator. The speaker explains the process of creating a user and assigning specific roles and permissions to control their access.

💡Role

A 'role' is a collection of permissions that can be assigned to users. Instead of assigning privileges to each user individually, roles allow for easier management. The script explains how a role, such as 'World_user,' is created and granted permissions, which can then be assigned to users.

💡Grant

'Grant' is the SQL command used to assign specific privileges to a role or user. It allows control over what actions a user or role can perform within the database. For example, the speaker grants the 'select,' 'insert,' 'update,' and 'delete' privileges on the 'world' database to the 'World_user' role.

💡Privileges

Privileges define what actions users or roles are allowed to perform in the database. They include actions like 'select,' 'insert,' 'update,' and 'delete.' The speaker demonstrates how to view available privileges using the 'show privileges' command and how to grant specific privileges to a role.

💡Database

A 'database' is a structured collection of data. In the script, the speaker focuses on the 'world' database, explaining how to manage user access to it. Databases store data in tables, and different privileges can be assigned to control what users can do with this data.

💡Permissions

'Permissions' refer to the access rights given to users or roles, controlling what they can do within the database. Permissions are granted through SQL commands and can be revoked if necessary. The script covers how permissions like 'select' and 'insert' are assigned to the 'World_user' role.

💡Revoke

'Revoke' is the SQL command used to remove previously granted privileges from a user or role. The script mentions how the 'revoke' command can be used to withdraw access rights, ensuring tighter control over database security.

💡Schema

A 'schema' is the organizational structure of a database, which includes tables, views, and other database objects. In the video, the speaker refers to schemas when showing the privileges assigned to users and roles within the 'world' database.

💡Password

A 'password' is a security measure used to authenticate a user before they can access the database. The speaker explains how to create a user with an initial password and the option to force the user to change it upon first login, adding an extra layer of security.

💡Tables

In a database, 'tables' are the structures that store data in rows and columns. The speaker explains how users are granted permissions to specific tables or entire databases. For example, they grant permissions on all tables in the 'world' database to the 'World_user' role.

Highlights

Creating users and granting permissions using roles instead of individual user permissions.

The process starts by creating a role with the necessary permissions for a specific database.

Viewing the available privileges to determine which can be granted to a role.

Granting specific permissions such as SELECT, INSERT, UPDATE, and DELETE on a database or specific tables within the database.

Demonstrating how to assign permissions to all tables in a database using database_name.* syntax.

Creating a new user and setting a password with an option to expire the password immediately.

Assigning the created role as the default role for the new user.

Using SHOW GRANTS to view the permissions assigned to a specific user or role.

Demonstrating how to test the user's connection to verify access and permissions to the database.

Explaining how users can view their current role using SELECT current_role().

Restricting access to other databases outside the scope of the assigned role permissions.

Blocking actions such as creating or dropping tables, ensuring the user only has specific privileges as granted.

Illustrating how permissions can be revoked using the REVOKE command, similar to how they are granted.

Highlighting the flexibility to modify roles by adding or removing permissions as needed.

The importance of using roles for managing permissions in a secure and organized manner, preventing over-permissioning of users.