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

Another user MAINTENANCE_USR has no grants at all on that table so can’t read any data from that table

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

Right after getting this privilege the user MAINTENANCE_USR can already see the table definition but still has no access to the data

This is expected and the user can start fulfilling his duties by creating indexes on that table but only on his own schema

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

Now MAINTENANCE_USR can create a nice and simple function that populates the DATA_DUMP table with the values passed to the function itself

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

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

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.

Share this post on Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin
Email this to someone
email

Leave a Reply

Your email address will not be published. Required fields are marked *