audit – bushmelev-aa.ru

Oracle Database 12c: Security 13 – 3

Enabling and Managing OLS

•Register and enable OLS:
– Using DBCA or
– Executing the LBACSYS.CONFIGURE_OLS and LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS procedures
•The The LBACSYS schema owns all OLS objects.
•Use Enterprise Manager Cloud Control (EMCC) SQL*Plus to manage the Label Security policies.
•In PDBs, which canhttp://bushmelev-aa.ru/wp-admin/edit-tags.php?taxonomy=post_tag be plugged in and out of CDBs:
– Manage OLS components on a subset of PDBs in a CDB:
— Policies (no policies in the root)
— Data labels
— User authorizations
– The LBACSYS schema is a common user schema.
– LBACSYS objects are automatically available to any PDB.

OLS: Features

OLS provides:
• Row-level security based on Virtual Private Database (VPD) technology
– All required packages for access mediation
– Complete data dictionary for managing OLS components
•A complete infrastructure for managing label security policies, sensitivity labels, and user security clearances
• Enterprise Manager pages containing a graphical user interface for managing OLS
• Integration with Oracle Identity Management starting Oracle Database 10g Release 1

OLS is built on the fine-grained access control technology of VPD. The major advantage of using OLS is that OLS is a complete system. It is a ready-to-use VPD. OLS provides sophisticated functions and procedures for evaluating and comparing sensitivity labels. It provides a sophisticated infrastructure for storing and managing sensitivity labels and user security clearances.

SELECT status FROM DBA_OLS_STATUS WHERE name = 'OLS_CONFIGURE_STATUS';

22:34:47 (1)[PDB1]c##bushmelev_aa@p00db1> SELECT status FROM DBA_OLS_STATUS WHERE name = 'OLS_CONFIGURE_STATUS';

STATUS
------
FALSE

22:39:02 (1)[PDB1]sys@p00db1> EXEC LBACSYS.CONFIGURE_OLS

PL/SQL procedure successfully completed.

22:39:06 (1)[PDB1]sys@p00db1> SELECT status FROM DBA_OLS_STATUS WHERE name = 'OLS_CONFIGURE_STATUS';

STATU
-----
TRUE

Check whether OLS is enabled.

22:40:33 (1)[PDB1]sys@p00db1> SELECT value FROM V$OPTION WHERE parameter = 'Oracle Label Security';

VALUE
----------------------------------------------------------------
FALSE

Enable OLS

22:48:46 (1)[PDB1]sys@p00db1>  EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.53
22:49:45 (1)[PDB1]sys@p00db1> SELECT value FROM V$OPTION WHERE parameter = 'Oracle Label Security';

VALUE
----------------------------------------------------------------
TRUE

ps: Registration and enabling execute at the container level.

pps:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Advanced Analytics, Real Application Testing and Unified Auditing options

Implementing an OLS Solution

check OLS:
http://www.oracle.com/technetwork/articles/idm/ls-093349.html

create user myco_emp identified by 1;
create user myco_mgr identified by 1;
create user myco_planning identified by 1;

 GRANT CREATE SESSION to MYCO_EMP ;
 GRANT CREATE SESSION to MYCO_MGR;
 GRANT CREATE SESSION to MYCO_PLANNING ;


grant select on hr.job_history to MYCO_PLANNING;
grant select on hr.job_history to MYCO_emp;
grant select on hr.job_history to MYCO_mgr;
 GRANT SELECT ON hr.LOCATIONS TO MYCO_EMP;
 GRANT SELECT ON hr.LOCATIONS TO MYCO_mgr;
 GRANT SELECT ON hr.LOCATIONS TO MYCO_PLANNING;
 GRANT SELECT, INSERT, UPDATE, DELETE ON HR.LOCATIONS TO  MYCO_PLANNING;

connect as sysdba or unlock LBACSYS and connect by it

 -- ****************************************************
 -- Creating FACILITY Policy
 -- ****************************************************
 BEGIN
 SA_SYSDBA.CREATE_POLICY('FACILITY','FACLAB',
 'READ_CONTROL,CHECK_CONTROL,LABEL_DEFAULT,HIDE');
 END;
 /

 -- ****************************************************
 -- Adding sensitivity levels to FACILITY policy:
 -- ****************************************************
BEGIN
SA_COMPONENTS.CREATE_LEVEL('FACILITY', 1000,'P','PUBLIC');
SA_COMPONENTS.CREATE_LEVEL('FACILITY',2000,'S','SENSITIVE');
SA_COMPONENTS.CREATE_LEVEL('FACILITY',3000,'HS','HIGHLY_SENSITIVE');
END;
/



 -- ****************************************************
 -- Adding groups to FACILITY policy:
 -- ****************************************************
 BEGIN
 SA_COMPONENTS.CREATE_GROUP('FACILITY', 1000,'Global','Global');
 SA_COMPONENTS.CREATE_GROUP('FACILITY',101,'US','United States','GLOBAL');
 SA_COMPONENTS.CREATE_GROUP('FACILITY',102,'EU','Europe','GLOBAL');
 SA_COMPONENTS.CREATE_GROUP('FACILITY',103,'Asia','Asia','GLOBAL');
 END;
 /


 -- ****************************************************
 -- Creating Labels for FACILITY policy
 -- ****************************************************
 EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY', 1000,'P');
 EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY',2101,'S::US');
 EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY',3101,'HS::US');
 EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY', 2103,'S::ASIA');
EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY',3103,'HS::ASIA');


 -- **************************************************
 -- Setting MYCO_EMP user label authorizations
 -- Setting MYCO_MGR user label authorizations
 -- Setting MYCO_PLANNING user label authorizations
 -- **************************************************
 exec SA_USER_ADMIN.SET_USER_LABELS ('PRIVACY', 'MYCO_MGR','C');
 exec SA_USER_ADMIN.SET_USER_LABELS ('FACILITY','MYCO_EMP','P');
 exec SA_USER_ADMIN.SET_USER_LABELS ('FACILITY','MYCO_MGR','S::US,EU,ASIA');
 exec SA_USER_ADMIN.SET_USER_LABELS ('FACILITY','MYCO_PLANNING','HS::GLOBAL');

-- Applying FACILITY policy to hr.locations table.
-- *************************************************
 Begin
 sa_policy_admin.apply_table_policy (
 POLICY_NAME => 'FACILITY',
 SCHEMA_NAME => 'HR',
 TABLE_NAME => 'LOCATIONS',
 TABLE_OPTIONS => NULL,
 LABEL_FUNCTION => NULL);
 END;
 /


 -- ****************************************************
 -- Update Labels for Sites In ASIA
 -- ****************************************************

 update hr.locations set faclab = char_to_label('FACILITY','S::ASIA') where upper(city) in ('BEIJING','TOKYO','SINGAPORE');

 -- ****************************************************
 -- Update Labels for Sites In US
 -- ****************************************************

 update hr.locations set faclab = char_to_label('FACILITY','HS::US') where upper(city) in ('SOUTH SAN FRANCISCO');


 -- ****************************************************
 -- Update Labels for all remaining locations
 -- ****************************************************

 update hr.locations set faclab = char_to_label('FACILITY','P') where faclab is NULL;


commit;


                                


where upper(city) in ('BEIJING','TOKYO','SINGAPORE');
-- ****************************************************
-- SETTING LABELS FOR PRIVACY POLICY
-- ****************************************************


connect hr/oracle_4U@localhost:1521/pdb1

last piece of code failed with error :

 update hr.job_history set privlab = char_to_label('PRIVACY','S') where ((to_char(sysdate,'YYYY') - to_char(end_date,'YYYY')) > 10);
 update hr.job_history set privlab = char_to_label('PRIVACY','C') where ((to_char(sysdate,'YYYY')- to_char(end_date,'YYYY')) <= 10);

now lets check emp:

01:13:40 not connected> select l.*,label_to_char(faclab) label from hr.locations l;

LOCATION_ID | STREET_ADDRESS                           | POSTAL_CODE  | CITY                           | STATE_PROVINCE            | CO | LABEL
----------- | ---------------------------------------- | ------------ | ------------------------------ | ------------------------- | -- | ----------
       1000 | 1297 Via Cola di Rie                     | 00989        | Roma                           | <NULL>                    | IT | P
       1100 | 93091 Calle della Testa                  | 10934        | Venice                         | <NULL>                    | IT | P
       1300 | 9450 Kamiya-cho                          | 6823         | Hiroshima                      | <NULL>                    | JP | P
       1400 | 2014 Jabberwocky Rd                      | 26192        | Southlake                      | Texas                     | US | P
       1600 | 2007 Zagora St                           | 50090        | South Brunswick                | New Jersey                | US | P
       1700 | 2004 Charade Rd                          | 98199        | Seattle                        | Washington                | US | P
       1800 | 147 Spadina Ave                          | M5V 2L7      | Toronto                        | Ontario                   | CA | P
       1900 | 6092 Boxwood St                          | YSW 9T2      | Whitehorse                     | Yukon                     | CA | P
       2100 | 1298 Vileparle (E)                       | 490231       | Bombay                         | Maharashtra               | IN | P
       2200 | 12-98 Victoria Street                    | 2901         | Sydney                         | New South Wales           | AU | P
       2400 | 8204 Arthur St                           | <NULL>       | London                         | <NULL>                    | UK | P
       2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB      | Oxford                         | Oxford                    | UK | P
       2600 | 9702 Chester Road                        | 09629850293  | Stretford                      | Manchester                | UK | P
       2700 | Schwanthalerstr. 7031                    | 80925        | Munich                         | Bavaria                   | DE | P
       2800 | Rua Frei Caneca 1360                     | 01307-002    | Sao Paulo                      | Sao Paulo                 | BR | P
       2900 | 20 Rue des Corps-Saints                  | 1730         | Geneva                         | Geneve                    | CH | P
       3000 | Murtenstrasse 921                        | 3095         | Bern                           | BE                        | CH | P
       3100 | Pieter Breughelstraat 837                | 3029SK       | Utrecht                        | Utrecht                   | NL | P
       3200 | Mariano Escobedo 9991                    | 11932        | Mexico City                    | Distrito Federal,         | MX | P

19 rows selected.

01:13:42 not connected> sho user
USER is "MYCO_EMP"

check mgr

01:16:10 not connected> sho user
USER is "MYCO_MGR"
01:16:12 not connected> select l.*,label_to_char(faclab) label from hr.locations l;

LOCATION_ID | STREET_ADDRESS                           | POSTAL_CODE  | CITY                           | STATE_PROVINCE            | CO | LABEL
----------- | ---------------------------------------- | ------------ | ------------------------------ | ------------------------- | -- | ----------
       1000 | 1297 Via Cola di Rie                     | 00989        | Roma                           | <NULL>                    | IT | P
       1100 | 93091 Calle della Testa                  | 10934        | Venice                         | <NULL>                    | IT | P
       1200 | 2017 Shinjuku-ku                         | 1689         | Tokyo                          | Tokyo Prefecture          | JP | S::ASIA
       1300 | 9450 Kamiya-cho                          | 6823         | Hiroshima                      | <NULL>                    | JP | P
       1400 | 2014 Jabberwocky Rd                      | 26192        | Southlake                      | Texas                     | US | P
       1600 | 2007 Zagora St                           | 50090        | South Brunswick                | New Jersey                | US | P
       1700 | 2004 Charade Rd                          | 98199        | Seattle                        | Washington                | US | P
       1800 | 147 Spadina Ave                          | M5V 2L7      | Toronto                        | Ontario                   | CA | P
       1900 | 6092 Boxwood St                          | YSW 9T2      | Whitehorse                     | Yukon                     | CA | P
       2000 | 40-5-12 Laogianggen                      | 190518       | Beijing                        | <NULL>                    | CN | S::ASIA
       2100 | 1298 Vileparle (E)                       | 490231       | Bombay                         | Maharashtra               | IN | P
       2200 | 12-98 Victoria Street                    | 2901         | Sydney                         | New South Wales           | AU | P
       2300 | 198 Clementi North                       | 540198       | Singapore                      | <NULL>                    | SG | S::ASIA
       2400 | 8204 Arthur St                           | <NULL>       | London                         | <NULL>                    | UK | P
       2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB      | Oxford                         | Oxford                    | UK | P
       2600 | 9702 Chester Road                        | 09629850293  | Stretford                      | Manchester                | UK | P
       2700 | Schwanthalerstr. 7031                    | 80925        | Munich                         | Bavaria                   | DE | P
       2800 | Rua Frei Caneca 1360                     | 01307-002    | Sao Paulo                      | Sao Paulo                 | BR | P
       2900 | 20 Rue des Corps-Saints                  | 1730         | Geneva                         | Geneve                    | CH | P
       3000 | Murtenstrasse 921                        | 3095         | Bern                           | BE                        | CH | P
       3100 | Pieter Breughelstraat 837                | 3029SK       | Utrecht                        | Utrecht                   | NL | P
       3200 | Mariano Escobedo 9991                    | 11932        | Mexico City                    | Distrito Federal,         | MX | P

22 rows selected.

now check myco_planning:

USER is "MYCO_PLANNING"
01:17:57 not connected> select l.*,label_to_char(faclab) label from hr.locations l;

LOCATION_ID | STREET_ADDRESS                           | POSTAL_CODE  | CITY                           | STATE_PROVINCE            | CO | LABEL
----------- | ---------------------------------------- | ------------ | ------------------------------ | ------------------------- | -- | ----------
       1000 | 1297 Via Cola di Rie                     | 00989        | Roma                           | <NULL>                    | IT | P
       1100 | 93091 Calle della Testa                  | 10934        | Venice                         | <NULL>                    | IT | P
       1200 | 2017 Shinjuku-ku                         | 1689         | Tokyo                          | Tokyo Prefecture          | JP | S::ASIA
       1300 | 9450 Kamiya-cho                          | 6823         | Hiroshima                      | <NULL>                    | JP | P
       1400 | 2014 Jabberwocky Rd                      | 26192        | Southlake                      | Texas                     | US | P
       1500 | 2011 Interiors Blvd                      | 99236        | South San Francisco            | California                | US | HS::US
       1600 | 2007 Zagora St                           | 50090        | South Brunswick                | New Jersey                | US | P
       1700 | 2004 Charade Rd                          | 98199        | Seattle                        | Washington                | US | P
       1800 | 147 Spadina Ave                          | M5V 2L7      | Toronto                        | Ontario                   | CA | P
       1900 | 6092 Boxwood St                          | YSW 9T2      | Whitehorse                     | Yukon                     | CA | P
       2000 | 40-5-12 Laogianggen                      | 190518       | Beijing                        | <NULL>                    | CN | S::ASIA
       2100 | 1298 Vileparle (E)                       | 490231       | Bombay                         | Maharashtra               | IN | P
       2200 | 12-98 Victoria Street                    | 2901         | Sydney                         | New South Wales           | AU | P
       2300 | 198 Clementi North                       | 540198       | Singapore                      | <NULL>                    | SG | S::ASIA
       2400 | 8204 Arthur St                           | <NULL>       | London                         | <NULL>                    | UK | P
       2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB      | Oxford                         | Oxford                    | UK | P
       2600 | 9702 Chester Road                        | 09629850293  | Stretford                      | Manchester                | UK | P
       2700 | Schwanthalerstr. 7031                    | 80925        | Munich                         | Bavaria                   | DE | P
       2800 | Rua Frei Caneca 1360                     | 01307-002    | Sao Paulo                      | Sao Paulo                 | BR | P
       2900 | 20 Rue des Corps-Saints                  | 1730         | Geneva                         | Geneve                    | CH | P
       3000 | Murtenstrasse 921                        | 3095         | Bern                           | BE                        | CH | P
       3100 | Pieter Breughelstraat 837                | 3029SK       | Utrecht                        | Utrecht                   | NL | P
       3200 | Mariano Escobedo 9991                    | 11932        | Mexico City                    | Distrito Federal,         | MX | P

23 rows selected.

short way ~~truncate table sys.aud$~~ =)))

BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 24 /* hours */);
END;
/

SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/

BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-31);
END;
/

create purge audit job

--create STANDARD_AUDIT_TRAIL_PURGE_JOB
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_Purge_Job',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

Or audit all purge job:

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => 'ALL_Audit_Trail_Purge_Job',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

create job to update what can job delete from audit

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'
      ,start_date      => trunc (SYSTIMESTAMP) 
      ,repeat_interval => 'FREQ=DAILY;INTERVAL=1'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => '
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   last_archive_time => SYSTIMESTAMP-31);
'
      ,comments        => NULL
    );
end;
/

BEGIN
DBMS_SCHEDULER.set_attribute( name => '"SYS"."MOVE_LAST_TIMESTAMP_FORWARD"', attribute => 'auto_drop', value => FALSE);
DBMS_SCHEDULER.enable(name=>'"SYS"."MOVE_LAST_TIMESTAMP_FORWARD"');
END; 
/

SELECT * FROM dba_audit_mgmt_config_params;
SELECT * FROM dba_audit_mgmt_last_arch_ts;
select * from DBA_AUDIT_MGMT_CLEAN_EVENTS;

Standard_Audit_Trail_Purge_Job

STOP:

BEGIN
  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'STANDARD_AUDIT_TRAIL_PURGE_JOB',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE);
end;
/

Drop:

BEGIN
DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
  AUDIT_TRAIL_PURGE_NAME  => 'STANDARD_AUDIT_TRAIL_PURGE_JOB');
END;

Manual run:

BEGIN
  DBMS_AUDIT_MGMT.clear_last_archive_timestamp(
    audit_trail_type     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);
END;
/

list of DBMS_AUDIT_MGMT. –>AUDIT_TRAIL_ALL<-- can be found here
almost all material from oracle-base.com

bushmelev-aa.ru

Posts categoriezed as audit

set the operating system user for a PDB

enable Oracle Label Security

Enabling and Managing OLS

OLS: Features

Implementing an OLS Solution

audit purge

Enabling and Managing OLS

OLS: Features

Implementing an OLS Solution

Primary Sidebar