Creating and granting permission to MySQL users
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
👨💼 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.
🔑 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
💡Role
💡Grant
💡Privileges
💡Database
💡Permissions
💡Revoke
💡Schema
💡Password
💡Tables
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.
Transcripts
okay let's talk about creating uh users
and then granting permissions to users
because up till this point we've pretty
much just been using this as
administrator or as the root user which
is great because you've got all power
and it works wonderful for you to do it
but sooner or later you're going to want
to give users access to your database as
well now typically we won't Grant
permissions to individual users just
like with anything else right so in
Windows Server we don't grab give
permissions individual users we give
them to groups well it's the same thing
here except instead of groups we have
roles so here's what I want to do I'll
let you know where we're going and then
we'll work on getting there I want to
create a user and I want to give them
permission to the world database not any
of my other databases that are over here
just to my world database and I want
them to be able to select data insert
data update data and delete records so
here's what I'm going to do I'm going to
start by creating a role so it's create
role World underscore user
and execute and that creates our roles
so now that we have the role we can
grant permissions to it so
we have a bunch of uh privileges we can
actually uh view so or that we can
actually use so I'm just going to do a
show privileges
just so you can see what all they are
and so here we have permissions the
privilege the context and then the
comments so alter applies to tables it
allows us to alter a table uh drop can
apply to databases or tables and allows
us to drive databases tables and Views
uh create we can give the permissions to
create views to create databases tables
or indexes we can give permissions to
select and so you'll see a bunch of them
here and then as we scroll down you're
also going to see a bunch of defined
server admin privileges as well
so that's where you can look and see
which privileges you can actually assign
so now that we've seen that I want to
assign certain permissions and I'm going
to do this using the grant so it's Grant
and then you give the Privileges you
want
in this case I want select I want I
don't want alter I do want update I want
insert and I want delete
now I need to Define where these
privileges are going to go so I'm going
to Grant these permissions on and then
I'm going to tell it what I want so I
want this on world dot asterisk so
basically every table in the world
database now if I didn't want I could
specify
world.country and then I could do it on
help if I spoke country correctly
world.country language and then not give
them permissions on cities or whatever I
want to do right so in this case I'm
doing it for the entire world database
but that's how you would specify
specific trade tables and I want to give
these to the world underscore user role
so I'm going to execute that and that
gives me my grant now
by the way to get rid of uh privileges
it's revoke so Grant gives privileges
revoke uh gets rid of privileges
so I need to create a user and assign
them to that role so here we have create
user and then I'm going to get the
username and I'm just going to use mine
if I can spell my own name correctly
create user and then I'm going to
specify a password so it's identified
and this is a little bit weird
identified by and then I'm going to put
in the password
and then you can also set an option for
password expire if you want the password
to start out immediately expired so
we'll do this with a one-time password
right and then they'll log in and then
the first thing they'll have to do is
change their password
identified by give them the specific
password and then I'm going to set the
default role is going to be
world
user
probably helpful to space there too so
I'm creating the user I'm giving them
username identified by and I'm going to
identifying the password and then
setting a default role and so I execute
that
okay now I've created the role created
the user and I do want to show you
something else real quick while we're
here we've been sitting on the schemas
let's come over to Administration and
look at users and computers and here
you're going to see the world user role
that I created
so we have
um
World user here are our administrative
roles which their iron on here are the
schema Privileges and you'll see on the
world they have these Privileges and
then for the user David we'll see that
they have basically nothing except for
the fact that they are part of that role
and if I want to expire a password by
the way I can do that right here Force
user to change password after next logon
okay
I'm not going to play with this for the
moment though
I'm going to Endeavor to close that and
then I am going to close my connection
now I'm going to create a new connection
to the local one and this is going to be
just David
and username David
and I'm going to test my connection put
in my password
and hit OK and successfully made the SQL
connection so life is good okay now let
me go ahead and connect
and I've been playing around in here
before so I am going to see if this
works now I'm going to start
by viewing my current role so that is
Select current underscore role open and
close parentheses and it tells me that
currently I'm a member of world user at
and then that little parentheses
remember that's the or not parentheses
punctuation mark That's the equivalent
of an asterisk so it's a wild card that
means everything so I'm World user at
and I can connect from any computer so
that's my current role and I can view
the permissions that I have by using the
command show grants
and that will show me I have Grant usage
to everything Grant select insert update
on world 2 me from any location Grant
World user everything to David okay so
this gives me my grants so now I should
be able to select asterisk from country
and I should have permission to view
whoops I need to select the database
first use World there we go and then
we'll select
asterisk from country and now I should
be able to see everything in country I
should be able to see everything in my
cities so I should have access to
everything at this point in the world
database now let me try to connect to
another one view my schemas over here
oh look I only see one thing in my
schemas now I know there is a Sakia
database
so let me
use Sakia
even though I'm not seeing it over here
I know it's there and it says no access
is denied to user security so you can
see that we have things actually blocked
okay
I also should not have the permission to
drop a table
or to create a table or anything like
that so I'm going to create table
oh let's give it a name of test
and then I'm just going to try to create
an empty table
and it says no the create command denied
to user so there you see we actually
have block out permissions and we've
given this user some permissions but not
all permissions so that's how we're
going to work on securing our databases
as when we start adding users into them
so we create the roles we Grant the
permissions that we want to the roles
once we Grant give the permissions then
we'll create the users add the users to
the roles and they should be ready to go
with what they need remember you can
always go back and we can grant more to
the role using the Grant and we can
revoke if we need to using the revoke
minute it's the same thing revoke
permission on to
and specify what it is we want to revoke
okay hopefully that gives you what you
need to start working on securing your
databases
Посмотреть больше похожих видео
Quick Tutorial - Users and Permissions in SQL Server
DVWA Setup in kali linux | dvwa blank page solution
Creating users and groups in Windows 10, and controlling file permissions
Setup Share Folders with NTFS Permission in Windows Server 2019
Express JS #5 - Post Requests
Self Host 101 - Set up and Secure Your Own Server
5.0 / 5 (0 votes)