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
Register OLS.
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.