Posts Tagged security

Flashback Data Archive in #Oracle 12c

This article highlights some 12c changes of Flashback Data Archive (FDA). See here for an introduction. Remarkably, the feature is now included in all editions. I will focus here on the 12c enhancement that an application can be registered to FDA. That has two major benefits:

1. FDA can be turned on and off for all tables of that application with one command

2. Protection (better than READ ONLY) can be turned on and off for all tables of that application with one command

The playing field:

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0
SQL> grant dba to quaid identified by quaid;

Grant succeeded.

SQL> connect quaid/quaid
Connected.
SQL> create tablespace flatbs datafile '/home/oracle/prima/flatbs01.dbf' size 50m;

Tablespace created.

SQL> create table t1 as select * from dual;

Table created.

SQL> create table t2 as select * from dual;

Table created.

SQL> create flashback archive fla_10y tablespace flatbs retention 10 year;

Flashback archive created.

So far no new feature as the same can be done with 11g. No I register the application – that is just declarative:

SQL> begin
     dbms_flashback_archive.register_application
     (application_name=>'MYAPP',
     flashback_archive_name=>'FLA_10Y');
     end;
     /  

PL/SQL procedure successfully completed.

Now I add all tables of that application:

SQL> begin
     dbms_flashback_archive.add_table_to_application
     (application_name=>'MYAPP',
     table_name=>'t1',
     schema_name=>'QUAID');
     end;
     /  

PL/SQL procedure successfully completed.

SQL> begin
     dbms_flashback_archive.add_table_to_application
     (application_name=>'MYAPP',
     table_name=>'t2',
     schema_name=>'QUAID');
     end;
     /  

PL/SQL procedure successfully completed.

I can now turn FDA on and off for all the tables with one command:

SQL> select table_name,status from user_flashback_archive_tables;

no rows selected

SQL> exec dbms_flashback_archive.enable_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

SQL> select table_name,status from user_flashback_archive_tables;

TABLE_NAME STATUS
---------- ----------
T1         ENABLED
T2         ENABLED

SQL> exec dbms_flashback_archive.disable_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

SQL> select table_name,status from user_flashback_archive_tables;

no rows selected

Furthermore, I can now protect these tables with one command against modification and drop:

SQL> exec dbms_flashback_archive.lock_down_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

SQL> delete from t1;
delete from t1
            *
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "QUAID"."T1"

SQL> drop table t1;
drop table t1
           *
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "QUAID"."T1"

This is a much better protection than just making tables READ ONLY:

SQL> create table t3 as select * from dual;

Table created.

SQL> alter table t3 read only;

Table altered.

SQL> drop table t3;

Table dropped.

Please notice also that the tables need not to be in FDA mode to be protected by the lock_down_application call. The doc by the way doesn’t mention the unlock procedure:

SQL> exec dbms_flashback_archive.unlock_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

Conclusion: The 12c enhancements of Flashback Data Archive enable the fast change of the FDA mode for multiple tables. Furthermore, it is now easily possible to prevent tables from being modified and dropped.

About these ads

, , ,

5 Comments

Fine Grained Access Control with DBMS_RLS using UPDATE_CHECK=>true

Fine Grained Access Control aka Virtual Private Database (VPD) has been there since Oracle 8 and got enhanced in each subsequent version. One minor New Feature of 11.2 was the addition of the parameter UPDATE_CHECK to the DBMS_RLS.ADD_POLICY procedure. During the OCM Preparation Workshop that I deliver presently, one of the attendees asked me what this parameter is actually doing – the doc is not so clear about it – which is why I came up with this simplified example. Hope you find it useful also :-)

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.
SQL> create table emp (ename varchar2(5),salary number);

Table created.

SQL> insert into emp values ('SCOTT',3000);

1 row created.

SQL> insert into emp values ('KING',9000);

1 row created.

SQL> commit;

Commit complete.

SQL> grant create session to scott identified by tiger;

Grant succeeded.

SQL> grant select,update on adam.emp to scott;

Grant succeeded.

The user SCOTT is not supposed to see the salary of the other employees and VPD is an elegant way to achieve that. The following technique will silently attach a WHERE-condition to statements hitting the table emp:

SQL> connect adam/adam
Connected.
SQL> create or replace function whoisit(schema varchar2, tab varchar2) return varchar2 
as
begin 
return '''' || sys_context('userenv','session_user') || ''' = ename ';     
end; 
/    

Function created.

SQL> begin
 dbms_rls.add_policy
  (object_schema=>'ADAM',
   object_name=>'EMP',
   policy_name=>'EMP_POLICY',
   function_schema=>'ADAM',
   policy_function=>'WHOISIT',
   update_check=>true);
end;
/    

PL/SQL procedure successfully completed.

SQL> connect scott/tiger
Connected.
SQL> select * from adam.emp;

ENAME	  SALARY
----- ----------
SCOTT	    3000

Although there are two rows in the table, SCOTT sees only his own salary! So far, this has been the same in earlier versions already. Now to the effect of update_check:

SQL> update adam.emp set ename='KING';
update adam.emp set ename='KING'
            *
ERROR at line 1:
ORA-28115: policy with check option violation

Without that parameter setting, the update would succeed – and the row would vanish for the user SCOTT as if the update would have deleted the row. Imagine the confusion of the user about that weird behavior :-)

Talking about weird, by the way:

SQL> connect adam/adam
Connected.
SQL> select * from adam.emp;

no rows selected

SQL> connect / as sysdba
Connected.
SQL> select * from adam.emp;

ENAME	  SALARY
----- ----------
SCOTT	    3000
KING	    9000

SQL> grant exempt access policy to adam;

Grant succeeded.

SQL> connect adam/adam
Connected.
SQL> select * from adam.emp;

ENAME	  SALARY
----- ----------
SCOTT	    3000
KING	    9000

That was funny, wasn’t it?

Conclusion: The new parameter UPDATE_CHECK in the DBMS_RLS.ADD_POLICY procedure restricts updates that would else lead to the updated rows to fall out of the allowed visibility for that user. Check out the old behavior by just omitting that parameter. Because: Don’t believe it, test it :-)

, , , ,

2 Comments

Auditing ALTER SYSTEM commands

A recent OTN Thread inspired this posting: How can we track down initialization parameter changes that someone has done with ALTER SYSTEM? One way would be to monitor the alert.log, but I think auditing should be most appropriate here. Preparing the playing field:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0    Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter audit_trail

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
audit_trail                 string     NONE

When you use DBCA to create an 11g Database, AUDIT_TRAIL will show as DB – I did the creation on the command line, therefore it is still NONE. When we want to see the particular ALTER SYSTEM command like in the demonstration, the value EXTENDED is additionally required.

SQL> alter system set audit_trail='DB','EXTENDED' scope=spfile;

System altered.

SQL> shutdown immediate

ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size            2227944 bytes
Variable Size          255852824 bytes
Database Buffers       50331648 bytes
Redo Buffers            4747264 bytes
Database mounted.
Database opened.
SQL> audit alter system by access;

Audit succeeded.

SQL> alter system set processes=250 scope=spfile;

System altered.

SQL> alter system set parallel_degree_policy=limited;

System altered.

SQL> alter system set result_cache_mode=force scope=memory;

System altered.

You may notice that I was still SYS when doing the ALTER SYSTEM commands. That is important here, because they are not audited, as we can see below. If you want auditing for SYS, this posting shows how to do that.

 
SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.

SQL> alter system set processes=200 scope=spfile;

System altered.

SQL> alter system set parallel_degree_policy=manual;

System altered.

SQL> alter system set result_cache_mode=manual scope=memory;

System altered.

SQL> select username,sql_text from dba_audit_trail where sql_text like 'alter system%';

USERNAME   SQL_TEXT
---------- ----------------------------------------------------------------------
ADAM       alter system set processes=200 scope=spfile
ADAM       alter system set parallel_degree_policy=manual
ADAM       alter system set result_cache_mode=manual scope=memory

Conclusion: ALTER SYSTEM commands can be audited very comfortable into the audit trail as long as you don’t do them as SYS. All actions of SYS can be audited also with a different approach. As always: Don’t believe it, test it :-)

, ,

1 Comment

Follow

Get every new post delivered to your Inbox.

Join 2,427 other followers

%d bloggers like this: