Confused deputy problem for databases: a method for privilege escalation in MySQL and PostgreSQL

next we've got Alexander Rubin he's going to be talking about some pretty cool microsequel and pro score sequel hacking techniques one of the quick bit of bookkeeping if you were waiting on a t-shirt we do have those available out front now for anyone who didn't buy them ahead of time you can go get them for a 20 bucks and I will got Alex Alexander thank you let me set it up [Music]

honestly

no

all right sounds good this is my first time speaking at a theater so I don't know if it's normal that I cannot see anyone because of the lights I probably use my cats [Music] all right thank you all right cool so um my name is Alex I am principal database engineer at Amazon web services and I will be talking today about databases and specific um security problem which is called confused Deputy problem and we'll talk about how this will apply to the databases a little bit about me I have been working with Maya skill my background is MySQL Consulting pretty much I started 2006 uh starting working for MySQL EB the company behind MySQL I was doing

uh Consulting there and my security interest started as a hobby about four years ago I started playing Capture the Flag games and developed a lot of interest into a security into red teaming and I joined Amazon web services about two years ago and I switched to doing security I created what we called RTS red team and currently leading that um a team at Amazon web services so we're working with a relational database as a service which make customers make it easier for customers to start and manage their relational databases so what is confused Deputy problem so confused Deputy the deputy here is a computer program so it's a computer program that have um a number of privileges high privilege

program and another program trick that program that high privilege program into doing something usually that's basically a privilege escalation and what I will do first I will show you how this can look at Linux and then we'll talk about the databases and see how it can apply to mySQL so let's say that I'm a system administrator and I wanted to fix a security problem so I realized that this unprivileged user ec2 user on my ec2 instance has some files owned by root and I created this simple Chrome tab a chrome job to fix this and basically every minute it will end putting it back to what it should be unprivileged user easy to use right so what can go wrong here

and to demonstrate that I recorded a quick video uh what may happen so here on one side I have a root access and I have this cronjo uh and um I am creating by mistake I'm creating a user in the home directory and this user will be owned by root and then my current job will start in a minute and we'll fix that right because we don't want to have uh files in a home directory of unprivileged user owned by by root because in this case user will not be able to do anything with that right so in a second here we go so it fixed right and then we have this unprivileged user and a privileged user can actually

confuse the crown job making bad stuff so I can create here a Sim link to Etc password so I can Create a Sim Link in any directory which will point to any file even if I obviously if I'm not oh no that file right so then in a minute the current job will run and what will happen is that this shown command Will Follow The Sim link and change the password file to my unprivileged user so an attacker a bad actor on that side on the unprivileged side will be able to track the privilege program the current job into doing something to obtain an additional privilege obtain a privilege escalation so now it is owned by ec2

user and I can as a easy to user I can edit Etc password I will create another root user or I can simply change my ID to be root so this is how this malicious user and privilege user got the privilege escalation right so this is the example of confused Deputy problem for the databases so let's go next so let's talk about the databases and in this case I will be presenting a sample architecture for MySQL in this architecture that's completely fictional scenario right so in this architecture we have some Health Care records service software as a service and they have their main application which is highly privileged and highly protected and they also have a WordPress site

they just wanted to tell about themselves right so there's nothing interesting here and then what happens our bad actor found a way through the SQL injection in some of the WordPress website modules for example and was able to get into the WordPress site but there's nothing interesting here so the WordPress database is pretty much the web pages so even if and this user MySQL user is freedom so there's nothing interesting there but what the attacker really wants to do is to get into the health records database where there's a Phi information health records and stuff like that so the question is how can an attacker can potentially do that so to answer this question we need to

look inside the database so as a privileged user I can see all the users in my database and what I can see here is that I have an admin user like a root user on Linux I have two dedicated users one user is for the WordPress database and the second user is for the healthcare service so my application has basically two applications here right so the first application uses the WordPress user and the second application use the health data service user so uh I also see this there is a monitor user so what is this monitor used so to answer this question we need to look into the actual permissions so we can see that the a WordPress user

only have privileges to the WordPress database and nothing else and the similar Health Data service user has privileges on the health health data service that is but the monitoring user which is used for performance monitoring usually has a Global Select but in addition to that it has the X you can execute the stored procedures and can execute um can do a function hold so now the question is can we can a bad actor can it act confuse this monitoring a solution that is using this monitor user to give a work as user privileged escalation to read the Healthcare System so if we are the Corp Fortress user we are unable to read the mindscale.user MySQL that user is basically uh a table

which stores both usernames and passwords so think about this as a both Etc password and Etc shadow if you have an access to that table you will have a username and you will also have a an access to Hash of the password but a WordPress user has no ability has no way it can uh it can read from that table at the same time the monitor user because it has a Global Select can read that and it can also read this authentication strain which is actually a hash of the password so you may be wondering what this monitor user is used for and what this database performance monitoring system do that's typically in the database world

we use database monitoring systems to collect database metrics to collect slow queries and to actually generate the explained plans so explain plan is the way to see why this query is slow what this query is doing so the typically database administrator again review the database metrics and collect the slope queries and a typical database administrator job is to actually run the explain plan and see what this query is doing and understand how to optimize that in some of the monitoring systems performance monitoring systems they are doing that for you so they are displaying the queries the slow queries and they also execute the explain plan they execute the explain command on the query so it will be very handy for

database administrator to use that because they they see it on a single page so great explain plan what either DBA or this performance monitoring system will do is to take the slow query and rerun this slow query with explain plan so we do explain and then we can see something in the MySQL world something like here right so it's using index it's using and scan this number of rows something but the question is will it re-execute the select right it will not re-execute the select because it's only need to take the statistics right right actually in some cases it will and this is the worst part and this is the part where Maya scale actually didn't do a very good job on

security there was a blog post a couple years ago written by percona the company that's doing MySQL consultant Consulting and what it actually describes is that there are certain conditions where MySQL explain plan will need to execute the part of the query so in the database world we have this select query but we also have this notion of sub query and a sub query is a way to execute part of the query basically materialize create a temporary table as a result of the sub query so to generate the the good explain plan MySQL will need to do this so if there's a sub query it needs to calculate the number of rows in this sub query to

generate the right explain plans so to do that it will materialize and re-execute this query so this is what happens here we have a sub query which is doing sleep and when we run the explain plane what we see that this is run more than an hour because it's sleeping it's waiting until this part the select slip part will be materialized so now we know what to do a bad actor knows what to do so how do we escalate our privilege how do we use this uh confused Deputy problem to escalate our privilege this database in the worst WordPress database but this user can create tables functions student procedures anything so we'll start with creating this

function which is called exploit in this function will be using this again that'd be a specific thing which is called SQL security so SQL security um works is um I need to talk about this if this slide though so what SQL security is it basically allows to run from the with the privilege of a specific user so with this example uh it will run normally whoever runs this a function will the the Privileges of that user will be applied here so what this function will do it will check what is the current user meaning the user that is running yet if it's if it's any user except for the monitor user which is sleep because we will need to

generate a slow query however if this user is monitored we will select the authentication string from MySQL user right but the monitoring user has Global Select so monitoring user uh if monitoring user will rerun this query will be able to retrieve the password but the question is how do we pass the password back so again we have the ability to specify this definer and SQL security clouds in the database so the definer part works like a sewage bit on lens so if you run this functional privileged user run this function it will still be run in the context of whatever user will be specifically specified as a definer defining sort of owned file so now we can create another function

which will save meaning insert into a table whatever will pass in this function will be run with the privilege of WordPress user so inside of that function we have the ability to actually do the right into the database that is controlled by attacker so now we can create this proof of concept here again we will use this SQL security invoker so it will run in the concept of in the context of user who invoked and function basically who did the select then inside of that we will select the password put it in a variable and then use another function press the variable into that function and that function in turn will be running in the context of

our user which will be able to write to the delay so my monitor user actually doesn't have any ability to write but because if we're using this function which has the definer part we will be able to write so this is a trick we can now select obtain the password and save it into the table that is controlled by an attacker so I have created this demo and in this demo I have everything here everything set up my two functions right and then I'm running from that um Corp WordPress user because this is the user that attack you got and then I have prepared this table to materialize my password into right and then the next thing what I

will do is I will generate the slow query I will make it slow and will make the sub query here and I will simply do select WordPress exploit all right this is slow query it will run in what 30 seconds right three seconds now a DBA or even the monitoring system will pick this query up and we'll need to run the explain because it's slow query so the GBA let's say DBA runs this and gpe doesn't understand what happened right it's not a slow query now so now but then now the attacker will select from the p here's our password so we have obtained the hash of the more privileged user so now the question is what we will do

with that right so this is our monitoring user and our monitoring user has much more uh interesting uh stuff it's a it's a obviously privilegious collection but the question is how do I log in right it's a hash of the password so my hashcad instance with the 8 GPU cores and we'll run the I can try to uxd um actually brute force that path so let's see what will happen right so this is my password I removed the story and from the beginning because it's my skill specific and then put it into a file and then run hash cam so let's see how long it will take wow it's done so the password is pass

p-a-s-f that's the password of the monitoring use right and the reason why is people never pay attention into securing this the you know users for the monitoring system right so they think that this is this is unprivileged user nothing nothing more than that it's just for monitor right so in this example I was able to get the privilege escalation retrieve the password take the hash of the password and hack the password so now an attacker is able to use that to connect to the database and pretty much download the whole Health Service database including Phi patient name whatever it is right so recap so what we did is this is a database specific we use the confused Deputy problem

and apply it to the database and we use Maya skill as an example so my SQL MySQL explain plan can actually execute the statement and it should not and another problem that I have demonstrated here is that monitoring user has Global Select and also execute problem execute privilege if the monitoring user don't have execute privilege then this whole attack will will not be possible so the result we got monitoring user bezel hash it's simple password easy to crack and uh an attacker can connect as monitoring user now and because monitoring user has a Global Select it can download the whole database and start selling it yeah whatever all right so this is the picture it's a

fictional scenario uh an SQL injection to the low um privileged database a database that has nothing in it pretty much um going to the MySQL allowing an attacker to actually switch to the database use the confused Deputy problem to switch to the database and download the calculator um what about postgres I don't have a proof of concept for podcast yet uh that's much more complicated but still possible when I will have a proof of concept on the next conference I will do a demo for process as well and that's all what I wanted to talk about today thank you very much [Applause] uh two minutes four questions

yes

but what's the difference

right so basically the questions there are two questions here right uh is it isn't there really a bug in in my scale uh we um it's it's debatable if it's bug or not we think that this is what I personally think it's bug MySQL developers don't think that's a bug so um technically it's uh it's a design choice but from the security perspective it's very bad uh other databases may also be susceptible to that there are certain conditions then I haven't talked about that but there are certain conditions where podcast SQL actually execute the uh query even if you just do explain so in both MySQL and postgis SQL there's explain and there's explain analyze when you run explain analyze it

is actually executing the query the normal explain should not but this is what it is right so um both MySQL and positive SQL potentials for that so to fix that you need to be super careful of what user is running the select queries and who is running the explain plans and whatever whatever privileges you have on on that user that is re-executing the the custom career pretty much any questions all right thank you very much [Applause]

thank you