/*
|| Listing 1: SALESADM Setup.sql
||
|| Creates:
|| - A new schema (SALESADM)
|| - New users
|| - New tables 
|| - Reporting views
|| for demonstration of Oracle Label Security features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of 
|| Oracle Label Security (OLS) and should be carefully proofread
|| before executing it against any existing Oracle database to insure
|| that no potential damage can occur.
||
*/

-----
-- Listing 1.1: Set up schema and users
-----
CONNECT SYS / AS SYSDBA;

-- Create a role (without password identification)
DROP ROLE salesadm_role;
CREATE ROLE salesadm_role;

-----
-- Grant appropriate privileges to SH schema objects
-----
CONNECT sh/sh;
GRANT SELECT on sh.sales TO salesadm_role WITH GRANT OPTION;
GRANT SELECT on sh.customers TO salesadm_role WITH GRANT OPTION;

-- Create SALESADM schema
DROP USER salesadm;
CREATE USER salesadm
    IDENTIFIED BY password
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    QUOTA 100M ON example
    PROFILE default;

-- GRANT system privileges to SALESADM user
GRANT CREATE TABLE TO salesadm;
GRANT CREATE VIEW TO salesadm;
GRANT CREATE PROCEDURE TO salesadm;
GRANT EXECUTE ANY PROCEDURE TO salesadm;
GRANT CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM TO salesadm;
GRANT CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM TO salesadm;
GRANT salesadm_role to SALESADM;

-- Create Sales Director user (SLSMGR)
DROP USER slsmgr;
CREATE USER slsmgr
    IDENTIFIED BY password
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    QUOTA 10M ON example
    PROFILE default;
GRANT CONNECT TO slsmgr;
GRANT salesadm_role TO slsmgr;

-- Create Regional Manager users (RGNMGR1-RGNMGR5)
DROP USER rgnmgr1;
CREATE USER rgnmgr1
    IDENTIFIED BY password
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    QUOTA 5M ON example
    PROFILE default;
GRANT CONNECT TO rgnmgr1;
GRANT salesadm_role TO rgnmgr1;

DROP USER rgnmgr2;
CREATE USER rgnmgr2
    IDENTIFIED BY password
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    QUOTA 5M ON example
    PROFILE default;
GRANT CONNECT TO rgnmgr2;
GRANT salesadm_role TO rgnmgr2;

DROP USER rgnmgr3;
CREATE USER rgnmgr3
    IDENTIFIED BY password
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    QUOTA 5M ON example
    PROFILE default;
GRANT CONNECT TO rgnmgr3;
GRANT salesadm_role TO rgnmgr3;

DROP USER rgnmgr4;
CREATE USER rgnmgr4
    IDENTIFIED BY password
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    QUOTA 5M ON example
    PROFILE default;
GRANT CONNECT TO rgnmgr4;
GRANT salesadm_role TO rgnmgr4;

DROP USER rgnmgr5;
CREATE USER rgnmgr5
    IDENTIFIED BY password
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    QUOTA 5M ON example
    PROFILE default;
GRANT CONNECT TO rgnmgr5;
GRANT salesadm_role TO rgnmgr5;

-----
-- Listing 1.2: Create the schema objects 
-----

CONNECT SALESADM/PASSWORD;

-----
-- Create SALES_REGIONS table
-----
DROP TABLE salesadm.sales_regions CASCADE CONSTRAINTS;
CREATE TABLE salesadm.sales_regions
(
  region_id   NUMBER(3)    NOT NULL,
  abbr        CHAR(4)      NOT NULL,
  description VARCHAR2(32) DEFAULT 'UNDEFINED' NOT NULL
)
  TABLESPACE example
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

ALTER TABLE salesadm.sales_regions
  ADD CONSTRAINT sales_regions_pk
  PRIMARY KEY (region_id)
  USING INDEX 
  TABLESPACE example
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

-----
-- Create SALES_DISTRICTS table
-----
DROP TABLE salesadm.sales_districts CASCADE CONSTRAINTS;
CREATE TABLE salesadm.sales_districts
(
  district_id   NUMBER(3)    NOT NULL,
  abbr          CHAR(4)      NOT NULL,
  description   VARCHAR2(32) DEFAULT 'UNDEFINED' NOT NULL,
  region_id     NUMBER(3)    NOT NULL
)
  TABLESPACE example
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

ALTER TABLE salesadm.sales_districts
  ADD CONSTRAINT sales_districts_pk
  PRIMARY KEY (district_id)
  USING INDEX 
  TABLESPACE example
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

ALTER TABLE salesadm.sales_districts 
  ADD CONSTRAINT sales_district_region_fk
  FOREIGN KEY (region_id)
  REFERENCES salesadm.sales_regions (region_id);

-----
-- Create SALES_ZONES table
-----
DROP TABLE salesadm.sales_zones CASCADE CONSTRAINTS;
CREATE TABLE salesadm.sales_zones
(
  district_id   NUMBER(3)    NOT NULL,
  geo_id        VARCHAR2(40) NOT NULL
)
  TABLESPACE example
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

ALTER TABLE salesadm.sales_zones
  ADD CONSTRAINT sales_zones_pk
  PRIMARY KEY (district_id, geo_id)
  USING INDEX 
  TABLESPACE example
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

ALTER TABLE salesadm.sales_zones
  ADD CONSTRAINT sales_zone_district_fk
  FOREIGN KEY (district_id)
  REFERENCES salesadm.sales_districts (district_id);

-----
-- Create CUSTOMER_CONTACTS table
-----
DROP TABLE salesadm.customer_contacts CASCADE CONSTRAINTS;
CREATE TABLE salesadm.customer_contacts
(
  dist_id      NUMBER(3)      NOT NULL,
  cust_id      NUMBER(3)      NOT NULL,
  contact_date DATE           NOT NULL, 
  contact_info VARCHAR2(512)
)
  TABLESPACE example
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

ALTER TABLE salesadm.customer_contacts
  ADD CONSTRAINT customer_contacts_pk
  PRIMARY KEY (dist_id, cust_id, contact_date)
  USING INDEX 
  TABLESPACE example
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

-----
-- Listing 1.3: Create reporting views
-----
CREATE OR REPLACE VIEW salesadm.sales_hierarchy AS
   SELECT
      SR.abbr "Rgn",
      SR.description "Region Name",
      SD.abbr "Dist",
      SD.description "District Name"
     FROM 
        salesadm.sales_regions SR,
        salesadm.sales_districts SD
    WHERE SD.region_id = SR.region_id
/

CREATE OR REPLACE VIEW salesadm.sales_assignments 
   (rgn_abbr, dst_abbr, geo_id)
AS
   SELECT
      SR.abbr,
      SD.abbr,
      SZ.geo_id
     FROM 
        salesadm.sales_regions SR,
        salesadm.sales_districts SD,
        salesadm.sales_zones SZ
    WHERE SD.region_id = SR.region_id
      AND SZ.district_id = SD.district_id
/    

CREATE OR REPLACE VIEW salesadm.sales_made
   (rgn_abbr, dst_abbr, geo_id, cust_id, total_sales, amount_sold)
AS
   SELECT
      SR.abbr,
      SD.abbr,
      SZ.geo_id,
      C.cust_id,
      SUM(SH.amount_sold),
      SUM(SH.quantity_sold)
     FROM 
        salesadm.sales_regions SR,
        salesadm.sales_districts SD,
        salesadm.sales_zones SZ,
        sh.customers C,
        sh.sales SH
    WHERE SD.region_id = SR.region_id
      AND SZ.district_id = SD.district_id
      AND C.cust_state_province = SZ.geo_id
      AND C.cust_ID = SH.cust_id
    GROUP BY 
      SR.abbr,
      SD.abbr,
      SZ.geo_id,
      C.cust_id    
/    

-----
-- Grant appropriate permissions to SALESADM_ROLE
-----
GRANT SELECT ON salesadm.sales_regions TO salesadm_role;
GRANT INSERT ON salesadm.sales_regions TO salesadm_role;
GRANT UPDATE ON salesadm.sales_regions TO salesadm_role;
GRANT DELETE ON salesadm.sales_regions TO salesadm_role;
GRANT SELECT ON salesadm.sales_districts TO salesadm_role;
GRANT INSERT ON salesadm.sales_districts TO salesadm_role;
GRANT UPDATE ON salesadm.sales_districts TO salesadm_role;
GRANT DELETE ON salesadm.sales_districts TO salesadm_role;
GRANT SELECT ON salesadm.sales_zones TO salesadm_role;
GRANT INSERT ON salesadm.sales_zones TO salesadm_role;
GRANT UPDATE ON salesadm.sales_zones TO salesadm_role;
GRANT DELETE ON salesadm.sales_zones TO salesadm_role;
GRANT SELECT ON salesadm.sales_assignments TO salesadm_role;
GRANT SELECT ON salesadm.sales_hierarchy TO salesadm_role;
GRANT SELECT ON salesadm.sales_made TO salesadm_role; -- Fails!

-----
-- Listing 1.4: Initial Data Loads
-----
-- Load SALESADM.SALES_REGIONS
INSERT INTO salesadm.sales_regions VALUES (1, 'NE00', 'Northeastern United States');
INSERT INTO salesadm.sales_regions VALUES (2, 'SE00', 'Southeastern United States');
INSERT INTO salesadm.sales_regions VALUES (3, 'CN00', 'Central United States');
INSERT INTO salesadm.sales_regions VALUES (4, 'SW00', 'Southwestern United States');
INSERT INTO salesadm.sales_regions VALUES (5, 'NW00', 'Northwestern United States');
COMMIT;
   
-- Load SALESADM.SALES_DISTRICTS
INSERT INTO salesadm.sales_districts VALUES (1,  'NE10', 'New England', 1);
INSERT INTO salesadm.sales_districts VALUES (2,  'NE20', 'New York', 1);
INSERT INTO salesadm.sales_districts VALUES (3,  'SE10', 'Mid-Atlantic', 2);
INSERT INTO salesadm.sales_districts VALUES (4,  'SE20', 'Deep South', 2);
INSERT INTO salesadm.sales_districts VALUES (5,  'CN10', 'Midwest', 3);
INSERT INTO salesadm.sales_districts VALUES (6,  'CN20', 'Great Plains', 3);
INSERT INTO salesadm.sales_districts VALUES (7,  'SW10', 'Pacific Coast', 4);
INSERT INTO salesadm.sales_districts VALUES (8,  'SW20', 'West Central', 4);
INSERT INTO salesadm.sales_districts VALUES (9,  'NW10', 'Oregon-Washington', 5);
INSERT INTO salesadm.sales_districts VALUES (10, 'NW20', 'Mid NorthWest', 5);
COMMIT;

-- Load SALESADM.SALES_ZONES
INSERT INTO salesadm.sales_zones VALUES (1 , 'CT');
INSERT INTO salesadm.sales_zones VALUES (1 , 'ME');
INSERT INTO salesadm.sales_zones VALUES (1 , 'NH');
INSERT INTO salesadm.sales_zones VALUES (1 , 'RI');
INSERT INTO salesadm.sales_zones VALUES (1 , 'VT');
INSERT INTO salesadm.sales_zones VALUES (2 , 'DE');
INSERT INTO salesadm.sales_zones VALUES (2 , 'MA');
INSERT INTO salesadm.sales_zones VALUES (2 , 'MD');
INSERT INTO salesadm.sales_zones VALUES (2 , 'NY');
INSERT INTO salesadm.sales_zones VALUES (2 , 'NJ');
INSERT INTO salesadm.sales_zones VALUES (3 , 'DC');
INSERT INTO salesadm.sales_zones VALUES (3 , 'FL');
INSERT INTO salesadm.sales_zones VALUES (3 , 'GA');
INSERT INTO salesadm.sales_zones VALUES (3 , 'NC');
INSERT INTO salesadm.sales_zones VALUES (3 , 'SC');
INSERT INTO salesadm.sales_zones VALUES (3 , 'VA');
INSERT INTO salesadm.sales_zones VALUES (4 , 'AR');
INSERT INTO salesadm.sales_zones VALUES (4 , 'AL');
INSERT INTO salesadm.sales_zones VALUES (4 , 'MO');
INSERT INTO salesadm.sales_zones VALUES (4 , 'LA');
INSERT INTO salesadm.sales_zones VALUES (4 , 'MS');
INSERT INTO salesadm.sales_zones VALUES (5 , 'KY');
INSERT INTO salesadm.sales_zones VALUES (5 , 'OH');
INSERT INTO salesadm.sales_zones VALUES (5 , 'PA');
INSERT INTO salesadm.sales_zones VALUES (5 , 'TN');
INSERT INTO salesadm.sales_zones VALUES (5 , 'WV');
INSERT INTO salesadm.sales_zones VALUES (6 , 'IL');
INSERT INTO salesadm.sales_zones VALUES (6 , 'IN');
INSERT INTO salesadm.sales_zones VALUES (6 , 'MI');
INSERT INTO salesadm.sales_zones VALUES (6 , 'MN');
INSERT INTO salesadm.sales_zones VALUES (6 , 'WI');
INSERT INTO salesadm.sales_zones VALUES (7 , 'IO');
INSERT INTO salesadm.sales_zones VALUES (7 , 'KS');
INSERT INTO salesadm.sales_zones VALUES (7 , 'NE');
INSERT INTO salesadm.sales_zones VALUES (7 , 'ND');
INSERT INTO salesadm.sales_zones VALUES (7 , 'SD');
INSERT INTO salesadm.sales_zones VALUES (8 , 'AZ');
INSERT INTO salesadm.sales_zones VALUES (8 , 'CO');
INSERT INTO salesadm.sales_zones VALUES (8 , 'NM');
INSERT INTO salesadm.sales_zones VALUES (8 , 'OK');
INSERT INTO salesadm.sales_zones VALUES (8 , 'TX');
INSERT INTO salesadm.sales_zones VALUES (9 , 'AK');
INSERT INTO salesadm.sales_zones VALUES (9 , 'WA');
INSERT INTO salesadm.sales_zones VALUES (9 , 'WY');
INSERT INTO salesadm.sales_zones VALUES (9 , 'ID');
INSERT INTO salesadm.sales_zones VALUES (9 , 'MT');
INSERT INTO salesadm.sales_zones VALUES (10 , 'CA');
INSERT INTO salesadm.sales_zones VALUES (10 , 'HA');
INSERT INTO salesadm.sales_zones VALUES (10 , 'NV');
INSERT INTO salesadm.sales_zones VALUES (10 , 'OR');
INSERT INTO salesadm.sales_zones VALUES (10 , 'UT');
COMMIT;