“But it doesn’t work for me!” the user whines over the phone, all the while I’m mentally picturing what this person would look like being run over by a steamroller. I desperately try to clear my head of images of their pet cat and an industrial woodchipper……
The reason for my annoyance is every techie’s worst nightmare, a homebrew database and application security system so bizarre and byzantine it actively stops you from doing your job. We’ve all come across something like this over the years but in my case it’s a bit special.
It was all designed 20 years ago by a cowboy who has since ridden off into the sunset with not so much as a clink of their spurs.
So I’m understandably not very forgiving when it comes to users not being able to access things they probably shouldn’t be allowed to anyway. Especially under a security regime so old even Microsoft Stone Tablet can’t understand the few cryptic notes left behind
How on earth do you begin to understand or even debug an unholy mess like that?
One answer to this, and an oft forgotten tool in the oracle dba’s toolbox is something called a proxy user. This simple technique allows you to log in and assume the privileges and identity of someone else, even if they don’t want you to. So half way through the latest user whinge I wondered if this would still work on a shiny new 18c database. I was curious and without further ado I cut captain complaining short, fired up my least favourite oracle vm and decided to have a play
1. Connect to your favourite oracle pdb
SQL> alter session set container=orclpdb; Session altered.
2. Create a non-privileged account for yourself
SQL> create user darkside identified by password123; User created. SQL> grant create session to darkside; Grant succeeded.
3. Now create a “target” account. Ie a different user but the one you want to connect as. I’ll even be nice and give this user the ability to create tables
SQL> create user pleb identified by cabbage1; User created. SQL> grant create session to pleb; Grant succeeded. SQL> grant create table to pleb; Grant succeeded
4. Now let’s connect as our “target” user and create a dummy table
sqlplus pleb/cabbage1@orclpdb SQL*Plus: Release 220.127.116.11.0 - Production on Sat Aug 18 01:38:50 2018 Version 18.104.22.168.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 22.214.171.124.0 - Production Version 126.96.36.199.0 SQL> create table mytable ( col1 varchar2(10), col2 number);
5. As expected, connecting as our “original” non-privileged user you can’t see the new table
SQL> conn darkside/password123@orclpdb Connected. SQL> select * from pleb.mytable; select * from pleb.mytable * ERROR at line 1: ORA-00942: table or view does not exist
6. Now for the naughty bit. Go back to being a dba again and grant a “connect through” privilege
SQL> conn sys@orclpdb as sysdba Enter password: Connected. SQL> SQL> alter user pleb grant connect through darkside; User altered.
7. Does it still work? Note the unusual syntax around the connect statement. What you’re doing here is saying “yes I have a valid oracle account in the name of darkside, but I’m allowed to be the user “pleb” instead
SQL> conn darkside[pleb]/password123@orclpdb Connected. SQL> SQL> select * from mytable; no rows selected
Proxy users still work in 18c. Time I logged in as someone I don’t like and started using utl_mail to send emails to our CEO with subjects like “You smell”, “I know what you did with that hamster” etc
Logging in as someone else is bad. It can get you fired or even arrested. I’m lucky in that I work for a small company where we all know each other. If I tried this with one of our engineers their hobby and occupation of hitting things with big hammers might get expanded to include me so I at least always ask first – it’s only polite
It only works with a “full fat” oracle client or OCI based application. JDBC and other database drivers don’t understand the syntax for the username ( at least I haven’t found any yet) so your website is probably safe