Oracle VPD as a safeguard for DML

Submitted by fpachot on Sun, 12/16/2018 - 22:18

Virtual Private Databases (VPD) is an Enterprise Edition feature related to security. It restricts the scope of Data Manipulation Language to a subset of the table rows by transparently adding a where clause before executing them. It is also called Row-Level Security (RLS). Where the policy is enabled, it is like having the selected DML (SELECT, INSERT, UPDATE, DELETE) operate on a transcient view. And the predicates for this view can be dynamic and even query tables that the user cannot see.

Minimal Oracle

The Oracle Database software is large, several gigabytes in Oracle Home for the part that is deployed on the operating system, and additional megabytes in SYSTEM tablespace for the part that is deployed as stored procedures (mainly the dbms_% packages). And this is not a problem with the traditional deployment methods where you can have a .zip golden image of the Oracle Home, and a database template to start a new DB. But this monolithic approach is not adapted to the current way people want to deploy software:

fpachot Sun, 12/09/2018 - 22:31
Oracle Index compression for range scan on file names

Do you have tables with a column storing filenames? Long filenames with full path? If this is the case, then you probably realized how an index on this can be large. And when looking at the values sorted, you have seen the inefficiency of it: a big part of the full name is reapeated because it has the same prefix for files in the same (sub)directory. The 12cR2 Advanced Index Compression (COMPRESS ADVANCED LOW) does not help here because it only compresses identical values, like the basic compression of tables. With unique filenames, we cannot expect any benefit.

fpachot Fri, 11/30/2018 - 13:44
Efficiently query DBA_EXTENTS for FILE_ID / BLOCK_ID
fpachot Thu, 09/27/2018 - 11:52
Oracle Cloud: upload large files through the Object Store REST API
fpachot Wed, 09/26/2018 - 14:28


The views expressed in this blog are those of the authors and cannot be regarded as representing CERN’s official position.

CERN Social Media Guidelines