Dienstag, 29. November 2011

Extend the basic setup....


There was already some need to place logic into the database (view, instead of trigger). To get a better grip on the governance I decided to split my application into two database schemas. The already existing apex (MAINTENANCE in my case) schema will keep the representation layer. I will create a second schema MAINTENANCE_DB to manage the data and the logic. That may look a little bit oversized for this little application, but I'm also trying to find a good structure to build a "real" application with APEX. The new setup will not allow the Developer to create a GUI directly on the table. It will also prevent the frontend from deleting UNITS or DATA directly.

Run as SYS:

-- USER maintenance_db SQL
CREATE USER maintenance_db IDENTIFIED BY apex
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

-- ROLES
GRANT "RESOURCE" TO maintenance_db ;
GRANT "CONNECT" TO maintenance_db ;


-- SYSTEM PRIVILEGES
GRANT CREATE ANY SYNONYM TO maintenance_db ;
GRANT SELECT ANY DICTIONARY TO maintenance_db ;
GRANT CREATE TRIGGER TO "MAINTENANCE_DB" ;
GRANT CREATE TABLE TO "MAINTENANCE_DB" ;
GRANT CREATE VIEW TO "MAINTENANCE_DB" ;

--Other Privileges
GRANT EXECUTE ON sys.dbms_profiler TO maintenance_db WITH GRANT OPTION;

-- QUOTAS
ALTER USER maintenance_db QUOTA UNLIMITED ON USERS;
--Remove Privileges form user MAINTENANCE

-- SYSTEM PRIVILEGES
REVOKE CREATE ANY SYNONYM FROM "MAINTENANCE";
REVOKE SELECT ANY DICTIONARY FROM "MAINTENANCE";

--Other Privileges
REVOKE EXECUTE ON sys.dbms_profiler FROM "MAINTENANCE";


Source

Remove the database objects from the APEX Workspace Schema
Run as APEXUSER (MAINTENANCE):

--remove database objects
drop table plsql_profiler_data;
drop table plsql_profiler_units;
drop table plsql_profiler_runs;
drop sequence PLSQL_PROFILER_RUNNUMBER;
DROP VIEW PLSQL_PROFILER_RUNS_VW;

Source

Run the script proftab.sql as user MAINTENANCE_DB to create the profiler tables and sequence.

Create the view and instead of trigger as user MAINTENANCE_DB.

CREATE OR REPLACE FORCE VIEW plsql_profiler_runs_vw
AS
  (SELECT r.runid ,
    r.related_run ,
    r.run_owner ,
    TO_TIMESTAMP(TO_CHAR(r.run_date, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS') AS run_date ,
    r.run_comment ,
    r.run_total_time ,
    r.run_comment1,
    (SELECT MAX(run_total_time) FROM plsql_profiler_runs
    ) AS max_run_total_time
  FROM plsql_profiler_runs r
  )

Source

CREATE OR REPLACE TRIGGER plsql_profiler_runs_vw_io_trg INSTEAD OF
  DELETE OR
  UPDATE ON plsql_profiler_runs_vw REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE BEGIN IF DELETING THEN
  DELETE FROM plsql_profiler_data WHERE runid = :old.runid;
  DELETE FROM plsql_profiler_units WHERE runid = :old.runid;
  DELETE FROM plsql_profiler_runs WHERE runid = :old.runid;
ELSIF UPDATING THEN
  UPDATE plsql_profiler_runs
  SET run_comment = :new.run_comment
  WHERE runid     = :new.runid;
END IF;
END plsql_profiler_runs_vw_io_trg;
/

Source

Grant the necessary privileges to the APEX schema and to HR.

Run as MAINTENANCE_DB.

--Grant Privileges to user HR
GRANT INSERT,UPDATE,SELECT ON plsql_profiler_runs TO hr;
GRANT INSERT,UPDATE,SELECT ON plsql_profiler_units TO hr;
GRANT INSERT,UPDATE,SELECT ON plsql_profiler_data TO hr;
GRANT SELECT ON plsql_profiler_runnumber TO hr;

GRANT EXECUTE ON sys.dbms_profiler TO hr;

--Adjust Synonyms
CREATE OR REPLACE SYNONYM hr.plsql_profiler_runnumber FOR maintenance_db.plsql_profiler_runnumber;
CREATE OR REPLACE SYNONYM hr.plsql_profiler_runs FOR maintenance_db.plsql_profiler_runs;
CREATE OR REPLACE SYNONYM hr.plsql_profiler_units FOR maintenance_db.plsql_profiler_units;
CREATE OR REPLACE SYNONYM hr.plsql_profiler_data FOR maintenance_db.plsql_profiler_data;

--Grant privileges to APEX User
GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_runs_vw TO maintenance;

--Create synonym
CREATE OR REPLACE SYNONYM maintenance.plsql_profiler_runs_vw FOR maintenance_db.plsql_profiler_runs_vw;

Source

You should be able to use the application (except Page 4) without any changes.
It should be also still possible for the schema HR to execute the dbms_profiler package.

Keine Kommentare:

Kommentar veröffentlichen