- Hello everyone.
This is Dipankar Kushari.
I'm a senior specialist solutions architect with AWS.
And today I'm going to talk
about Role-Based Access Control or RBAC in Amazon Redshift.
I'm also going to show you how you can use
role-based access control to manage database privileges
in Amazon Redshift.
For RBAC or role-based access control,
the main idea is to assign privileges to roles
and then assign roles to users.
And now the user, who has been authorized with this role
will have all the privileges that this role has.
And with RBAC, now enterprises can have more
granular control of elevated user access
instead of just super user versus regular user.
Also with RBAC, now different administrative users
can be defined without full super user capability.
Here we have Role 1, which has been granted two privileges
and then Role 1 is granted to User 1.
This user can now perform tasks,
which are specified by the privileges A and B.
So all the action of the users are now monitored by roles.
And this user can now only confirm actions
specified by the two privileges,
but cannot do anything else.
Now let's look at an example.
In this example, we have SALES_RW role,
which has create table,
drop table, and also update column1 of table1 privileges.
These SALES_RW role is granted to Alice.
and Alice will be able to create and drop tables
and also update update column1 of table1,
but she cannot do anything else such as create users.
Next, let's talk about role nesting.
Here, we have two privileges.
System Privilege A, Object Privilege B
granted to Role 1.
We also have another role, role 2,
which has been granted Privilege C.
In addition to that Role 1 is granted to Role 2
and finally, Role 2 is granted to User 1.
This user now has all the privileges,
which are inherited from Role 2,
which also includes inherited privileges from Role 1.
However, we should not introduce cycles
in role authorization.
For example, if we have a third role, Role 3,
which was granted Role 2
and now tries to grant Role 3 back to Role 2
or Role 1, Redshift prevents that from happening
since it would have created circular dependency
between these roles.
Redshift issues an error message indicating
that it cannot run this role as a circular dependency
was detected between these roles.
RBAC in Redshift provides four system defined roles
with specific privileges.
These roles cannot be altered or customized.
They use sys column prefix,
and this prefix cannot be used by user defined roles.
RBAC in Redshift, also defines system privileges
which are available out of the box
and can be granted to role
for assigning specific permission.
Now let's move to AWS Redshift console
and see Redshift RBAC in action.
Here we are in Redshift Query Editor V2.
And we have a Redshift producer cluster,
and we have accessed the producer cluster
using a super user, which is AWS user.
It has a database named Dave
and also it has a schema named sales,
which has nine tables in it.
And we are going to use set session authorization
as we switch between different users.
So we are going to show you
that how we can create a role,
and then assign privileges to the role
and then assign that role to the user
and then how that user is going to perform
from based on the privileges, perform certain activities.
And also they will be denied to do certain activities,
because they don't have the necessary privilege.
So let's get started,
let's start with setting the authorization to AWS user,
and then using this command, we can actually
check who is the current user, if it has been set properly.
And then these are system views.
Using these, we can find out what role has been granted
to which user and then the second one shows
what role has been granted to that role
or which has been eventually granted to the user.
So in the first one,
you can see these dbadmin has been granted db_admin
and db_admin role has been granted to system defined roles,
which are sysdba and sys secadmin.
Now we can run the same queries using authorization
for dbadmin, and it will give the same results.
The difference would be when we have more roles,
the super user has more visibility
on each individual user and their roles.
And if those roles has been granted any other role,
versus non super user, they can only see their own user
and obviously the roles that has been granted to the user
and eventually to the role.
So let's get started with creating the first role,
which is a sales read only role.
This will be assigned to a salesanalyst,
so they only need permission
to perform certain analytical queries.
And let's go ahead and create the sales_ro role
and then let's create the salesanalyst
and finally grant the usage
on schema sales to role sales_ro.
So notice here that we are assigning all the privileges
to the roles, not to the user
and then the role finally will be granted
to the user salesanalyst.
And as you saw, we just assigned select
on all tables in schema sales to sales_ro.
So now let's switch the user to salesanalyst,
and let's make sure the switch has happened correctly.
And then let's run some simple queries.
So for example, if I want to run a select star
on the region table,
I should be able to run it using a salesanalyst privilege
and notice the comment here, comments from Africa.
Later we are to try to update this comment.
We can also run a simple query
against the nation and select some 10 random roles.
Let's also see the effect
on these svv user grants and svv role grants.
Those two are the system views, and see what it shows.
So it shows salesanalyst, which has been granted
sales_ro role, and then the sales_ro role
has not been granted any other role.
So this particular table hasn't returned any roles,
but if I set it to AWS user
and then run the same queries, now I see I'm able
to see two different user with two different roles.
And obviously earlier we saw db_admin
has been granted to system defined roles.
And again, db_admin is not a super user.
So you see here, it still shows only dbadmin.
So only the super user can see all those
different users and their roles.
And if those roles has been granted any other roles.
So coming back and again setting the session authorization
to salesanalyst, now you see
I can also select svv relation privileges
to see if whatever privilege
has been granted to the tables, right?
So I have 9 tables here in the sales schema under Dave,
and I see nine rows written because all tables
we have granted, select permission to sales_ro
and eventually granted sales_ro to salesanalyst,
but salesanalyst doesn't have update privilege.
So let's see what happens when I try to update.
Obviously, the permission is denied.
salesanalyst only had usage on sales schema.
So if I try to run any query on marketing schema
on any of the relation or the tables
that's residing in the marketing schema, it will fail.
Similarly, the region tables are in the sales schema
not owned by salesanalyst or the neither the nation
or neither any of the tables.
So if I want to analyze or vacuum or truncate,
all of them will fail
because they don't have,
salesanalysts don't have the necessary permission
and salesanalyst also doesn't own those tables.
Now let's look at some of the column level access control
that is still going to work
in the same fashion with RBAC.
So let's again, elevate it to dbadmin level
and then revoke select from nation table.
And then let's selectively grant only select to a nation key
and nation name to sales_ro
and then let's again change the authorization
back to salesanalyst
and make sure that it is salesanalyst indeed.
Now let's try to, now see nation has four columns right?
Nation key name, region key and comment, right?
And I have given select permission on nation key and name
and I'm trying to select nation key and region key.
And so let's see what happens.
Ideally it should say permission denied.
And that's exactly what is happening here,
but I did give select permission to nation key and name.
So ideally I should still be able to access it
based on how column filtration or access control works.
And that's exactly what it is.
So now let's switch gears
and create a read write role, which is, let's say an ETL
or a sales engineer who really needs permission
to perform certain write activities, such as the insert
or update or delete to the tables in the sales schema.
So again, let's elevate the permission to dbadmin
and then let's create the SALES_RW role.
The RW role has been created.
Now here, one interesting fact is we are
using role nesting, right?
So we are just granting sales_ro to sales_rw.
So basically all the privileges that came along
with sales_ro or has been granted to sales_ro
is now also inherited by sales_rw.
So now you see, I have to create a user
and now with all those grants or privileges
being granted to sales_rw,
let's now grant sales_rw to salesengineer, right?
And then let's skip this line for now.
I'll show you why. Let's run some queries at salesengineer.
So again, setting the session to sales engineer
and then let's run this simple query.
Again remember sales engineer inherited sales_rw,
but which in turn actually inherited sales_ro.
Remember sales_ro doesn't have select permission
on region key.
So if I run this query as a sales engineer,
it would still fail.
But it has select permission on nation key and nation name.
So if I run this query, like it happened earlier,
it is properly returning roles.
Again, I can run this system views
and I can see that sales engineer
and then it shows, this one is interesting, right?
So now sales_rw actually inherited sales_ro.
So it's coming up for sales engineer.
And again, going all the way up
if I run this queries as AWS user,
again you will see now sales engineer
also got added and here also after db_admin now
we have sales_rw because sales_rw
actually inherited sales_rw.
So again, coming back to the point where we left,
let's now also look at this, okay, actually
we need to change the session authorization again.
So now it's back to sales engineer
and let's run this session, svv relation privileges
and you see all, actually it is showing eight,
because all the privileges are there except on the nation.
So it's just showing eight.
Okay so you'll not see nation here
because it has a different level of permission
now with only two columns allowed permission.
Okay so now let's set the session authorization
to salesengineer, and let's try to update this,
but obviously it has only inherited all the permissions
from sales_ro and it doesn't have
update or insert permission.
So let's go back, elevate to dbadmin again.
And then let's grant insert update deletes
specifically to salesrw
and salesrw was already granted to salesengineer.
So now the salesengineer should be able to update.
And remember earlier, I told you to focus
on this comment from Africa.
So now we are going to add that date
and we are doing a back dated update.
So let's run this as a sales engineer
and perform the update.
And here you see it says, affected rows one.
So now, if I go back
and try to run a query on region as sales engineer,
let's make sure again, that its sales engineer.
So I should see that the comment has been updated.
So now you see comments from Africa
and 02/23/2022 has been updated.
Again, salesrw doesn't have usage permission
on marketing schema.
So it still fails.
And it doesn't own the region table.
So it fails because it doesn't have explicit permission
given or privileges assigned to salesrw
to perform either analyze or vacuum or truncate.
So we will perform those activities as dbadmin.
But before we do that,
I just want to show you two functions,
which are called role_is_member_of and user_is_member_of
so let's elevate to the super user.
You can run it as an individual user
but that only shows that specific user's details.
So we wanted to show you for all the users.
In this case, we are trying to basically say
if sales_ro has been granted sales_rw or not.
So that's how you should read it, role_is_member_of.
So if I run it, it either returns true or false.
So it is false because sales_ro is not inheriting sales_rw
but vice versa is true, which is sales_rw
is actually inheriting sales_ro,
so that's why it's true.
This one is also going to be true
because sysdba as we saw earlier,
has inherited db_admin
and has actually united sysdba.
And then salesanalyst has been assigned,
so now we are doing user_is_member_of
so this will also be true
because salesanalyst has been assigned sales_ro.
And interestingly, this will be true because this is by hops
via sales_rw sales_ro been granted to salesengineer.
So that's why it is true.
But again, salesanalyst has not been granted sales_rw.
So this is false.
And finally, I wanted to show you
that there is a validation.
So we haven't created any role called marketing RW.
That's why it does validate and it fails because it gives us
error message because there is no role a group
which is called marketing RW.
So now let's perform some database admin activities.
So let's change it to dbadmin.
And now we have done some changes to the region table.
So let's run the analyze and it runs successfully.
Let's also do some vacuum.
And finally let's truncate the dgn table.
So once I truncate, if I try to query
the count star will be zero,
and then let's make sure that I reinstate it back.
And if I do a count star now,
it will have five rows because five rows have been inserted.
Now, if I want to revoke these permissions
that I just created or assigned,
I just need to go in the reverse order.
so I can run all of them together.
And the last thing as you see,
I started with creating the sales_ro,
but the last thing I'm doing is actually
dropping the sales_ro,
starting with revoking the insert, update, delete
on all tables in schema, sales from sales_rw.
That's our last permission,
that the privilege that I have assigned.
So if I run all of them together,
you see these are all running,
and they are revoking all the privileges,
dropping all the roles that we have created for this demo.
So now we have cleaned up everything.
And if I go all the way up
and again set the AWS user as authorized session
and looked at it and see that it is actually correct.
So then when we run this, it's only showing dbadmin.
So it no longer have those rules.
So that's about RBAC
and how you can assign privileges perform certain activities
and then grant those privileges to roles
and then those roles to users.
Thanks again for watching the video.