Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Notes on Updatable Views
An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.
To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view.
The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:
Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause (an unnested collection), then the view is not inherently updatable.
The view must not contain any of the following constructs:
A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions, as documented in Oracle Database Administrator's Guide
In addition, if an inherently updatable view contains pseudocolumns or expressions, then you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions.
If you want a join view to be updatable, then all of the following conditions must be true:
The DML statement must affect only one table underlying the join.
For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table.
A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.
For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view was created WITH CHECK OPTION,
then join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.
For a DELETE statement, if the join results in more than one key-preserved table, then Oracle Database deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION.
Data Dictionary Objects
ALL_TRIGGERS
DBA_TRIGGERS
USER_TRIGGERS
CDB_TRIGGERS
TRIGGER$
First Available
8.0
Object Privileges
DML access must be given to the view of which the trigger is associated
System Privileges
ALTER ANY TRIGGER
CREATE TRIGGER
DROP ANY TRIGGER
CREATE ANY TRIGGER
Demonstration Setup
Demo Tables
conn uwclass/uwclass@pdbdev
CREATE TABLE employee (
employee_no VARCHAR2(8),
last_name VARCHAR2(25) NOT NULL,
first_name VARCHAR2(10) NOT NULL,
dept_code VARCHAR2(3) NOT NULL,
active_flag VARCHAR2(1) DEFAULT 'Y',
mod_user_id VARCHAR2(30) DEFAULT USER,
mod_user_date DATE DEFAULT SYSDATE);
CREATE TABLE permission_code (
pcode VARCHAR2(2),
pcode_description VARCHAR2(40) NOT NULL,
mod_user_id VARCHAR2(30) DEFAULT USER,
mod_user_date DATE DEFAULT SYSDATE);
INSERT INTO permission_code
VALUES ('BO', 'BILLING OPTIONS', USER, SYSDATE);
INSERT INTO permission_code
VALUES ('CL', 'CLASS CODES', USER, SYSDATE);
INSERT INTO permission_code
VALUES ('CR', 'CREWS', USER, SYSDATE);
INSERT INTO permission_code
VALUES ('CT', 'CREW TYPES', USER, SYSDATE);
INSERT INTO permission_code
VALUES ('CU', 'CUSTOMER TYPES', USER, SYSDATE);
INSERT INTO permission_code
VALUES ('DH', 'WORKORDER DASH NUMBERS', USER, SYSDATE);
INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ADM', 'ADMINISTRATION');
INSERT INTO dept_code (dept_code, dept_name)
VALUES
('COO', 'COORDINATOR');
INSERT INTO dept_code (dept_code, dept_name)
VALUES
('ESE', 'ELECTRICAL SERVICE');
INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ESR', 'ELECTRICAL SERVICE REP');
INSERT INTO dept_code (dept_code, dept_name)
VALUES
('ENG', 'ENGINEER');
INSERT INTO dept_code (dept_code, dept_name)
VALUES ('LCR', 'LINE CREW');
INSERT INTO dept_code (dept_code, dept_name)
VALUES ('MCR', 'METER CREW');
INSERT INTO dept_code (dept_code, dept_name)
VALUES ('NWE', 'NETWORK ENGINEER');
INSERT INTO dept_code (dept_code, dept_name)
VALUES ('SKA', 'SKETCH ARTIST');
INSERT INTO user_role
(dept_code, pcode, access_level)
SELECT r.dept_code, p.pcode, 'R'
FROM dept_code r, permission_code p;
INSERT INTO user_permission
(employee_no, pcode, access_level)
SELECT e.employee_no, r.pcode, r.access_level
FROM employee e, user_role r
WHERE e.dept_code = r.dept_code;
COMMIT;
Non Key-Preserved Relational Views
-- word "view" used in naming for demo purposes only
CREATE OR REPLACE VIEW role_permission_view AS
SELECT r.dept_code, r.pcode, p.pcode_description, r.access_level
FROM user_role r, permission_code p
WHERE r.pcode = p.pcode;
desc role_permission_view
col data_type format a15
SELECT column_name, data_type, data_length
FROM user_tab_cols
WHERE table_name = 'ROLE_PERMISSION_VIEW';
col type format a30
SELECT column_name, nullable, data_type || '(' || data_length || ')' TYPE
FROM user_tab_cols
WHERE table_name = 'ROLE_PERMISSION_VIEW';
SELECT column_name, DECODE(nullable, 'N', 'NOT NULL', NULL) "Null?", data_type || '(' || data_length || ')' TYPE
FROM user_tab_cols
WHERE table_name = 'ROLE_PERMISSION_VIEW';
-- this will fail
INSERT INTO role_permission_view
(dept_code, pcode, pcode_description, access_level)
VALUES
('DAN', 'DM', 'Morgan', 'W');
-- this will fail too
UPDATE role_permission_view
SET access_level = 'W'
WHERE dept_code = 'SKA';
-- another relational view
CREATE OR REPLACE VIEW employee_permission_view AS
SELECT e.employee_no,
e.first_name || ' ' || e.last_name NAME, e.dept_code, r.pcode, r.access_level DEFACCLVL, u.access_level, p.pcode_description
FROM employee e, user_role r, user_permission u, permission_code p
WHERE e.dept_code = r.dept_code
AND e.employee_no = u.employee_no
AND r.pcode = u.pcode
AND r.pcode = p.pcode
ORDER BY 1,3;
desc employee_permission_view
SELECT column_name, DECODE(nullable, 'N', 'NOT NULL', NULL) "Null?", data_type || '(' || data_length || ')' TYPE
FROM user_tab_cols
WHERE table_name = 'EMPLOYEE_PERMISSION_VIEW';
SELECT * FROM employee_permission_view;
-- this will fail too
DELETE FROM employee_permission_view
WHERE dept_code = 'LCR';
Demos
Instead Of Insert Trigger
CREATE OR REPLACE [<EDITIONABLE | NONEDITIONABLE>]
TRIGGER <trigger_name>
INSTEAD OF <DELETE | [OR] INSERT | [OR] UPDATE>
ON [NESTED TABLE <nested_table_column> OF ] <[schema_name.]non_editioned_view_name>
REFERENCING NEW AS <synonym> OLD AS <synonym> PARENT AS <synonym>
[FOR EACH ROW]
[<FORWARD | REVERSE> CROSSEDITION] [<FOLLOWS | PRECEDES] <[scema_name.]trigger_name]
[<ENABLE | DISABLE>]
DECLARE
<variable definitions>
BEGIN
<trigger_code>
EXCEPTION
<exception clauses>
END <trigger_name>;
/
CREATE OR REPLACE TRIGGER ioft_insert_role_perm
INSTEAD OF INSERT
ON role_permission_view
FOR EACH ROW
DECLARE
x INTEGER;
BEGIN
SELECT COUNT(*)
INTO x
FROM permission_code
WHERE pcode = :NEW.pcode;
IF x = 0 THEN
INSERT INTO permission_code
(pcode, pcode_description, mod_user_id, mod_user_date)
VALUES
(:NEW.pcode, 'New Code', USER, SYSDATE);
END IF;
SELECT COUNT(*)
INTO x
FROM dept_code
WHERE dept_code = :NEW.dept_code;
IF x = 0 THEN
INSERT INTO dept_code
(dept_code, dept_name)
VALUES
(:NEW.dept_code, 'New Dept');
END IF;
INSERT INTO user_role
(dept_code, pcode, mod_user_id)
VALUES
(:NEW.dept_code, :NEW.pcode, 'Morgan');
INSERT INTO test
(test)
VALUES
('Z');
END ioft_insert_role_perm;
/
SELECT *
FROM permission_code
WHERE pcode = 'DM';
SELECT *
FROM dept_code
WHERE dept_code = 'DAN';
SELECT *
FROM user_role
WHERE dept_code = 'DAN';
SELECT * FROM test;
-- insert works
INSERT INTO role_permission_view
(dept_code, pcode,
pcode_description, access_level)
VALUES
('DAN', 'DM', 'Morgan', 'W');
-- view results
SELECT *
FROM permission_code
WHERE pcode = 'DM';
SELECT *
FROM dept_code
WHERE dept_code = 'DAN';
SELECT *
FROM user_role
WHERE dept_code = 'DAN';
SELECT * FROM test;
Instead Of Update Trigger
CREATE OR REPLACE TRIGGER ioft_role_perm
INSTEAD OF UPDATE
ON role_permission_view
FOR EACH ROW
BEGIN
UPDATE user_role
SET access_level = :NEW.access_level,
mod_user_id = USER,
mod_user_date = SYSDATE
WHERE dept_code = :OLD.dept_code
AND permission_code = :OLD.permission_code;
END ioft_role_perm;
/
SELECT trigger_name, trigger_type, action_type,
description
FROM user_triggers;
SELECT * FROM employee_permission_view;
UPDATE role_permission_view
SET access_level = 'W'
WHERE dept_code = 'SKA';
SELECT * FROM employee_permission_view;
UPDATE employee_permission
SET access_level = 'Z';
Instead Of Delete Trigger
/* what does it mean to delete LCR from employee_permission_view?
Does it mean delete the LCR department from the dept_code table?
Does it mean delete all employees that are in department LCR?
Does it mean set to null the dept_code for employees in department LCR? */
SELECT * FROM employee_permission_view;
SELECT * FROM dept_code;
SELECT * FROM employee;
-- let's delete the parent record and set the child to null and update two other columns
CREATE OR REPLACE TRIGGER ioft_emp_perm
INSTEAD OF DELETE
ON employee_permission_view
FOR EACH ROW
BEGIN
DELETE FROM dept_code
WHERE dept_code = :OLD.dept_code;
UPDATE employee
SET dept_code = NULL,
mod_user_id = USER,
mod_user_date = SYSDATE
WHERE dept_code = :OLD.dept_code;
DELETE FROM test
WHERE test = 'Z';
END ioft_emp_perm;
/
SELECT * FROM employee_permission_view;
DELETE FROM employee_permission_view
WHERE dept_code = 'LCR';
desc employee
DELETE FROM employee_permission_view
WHERE dept_code = 'LCR';
CREATE OR REPLACE TYPE emp_type AS OBJECT (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2));
/
CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_type;
/
CREATE OR REPLACE TYPE dept_type AS OBJECT (
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
emps emp_tab_type);
/
CREATE OR REPLACE VIEW dept_or OF dept_type
WITH OBJECT IDENTIFIER (deptno) AS
SELECT deptno, dname, loc, CAST(MULTISET(
SELECT empno, ename, job, mgr, hiredate, sal, comm
FROM emp
WHERE emp.deptno = dept.deptno) AS emp_tab_type)
FROM dept;
/
CREATE OR REPLACE TRIGGER dept_emplist_tr
INSTEAD OF UPDATE ON NESTED TABLE emps OF dept_or
REFERENCING NEW AS NEW PARENT AS PARENT
FOR EACH ROW
BEGIN
dbms_output.put_line('New: ' || :NEW.job);
dbms_output.put_line('Parent: ' || :PARENT.dname);
END;
/
set serveroutput on
UPDATE TABLE (
SELECT p.emps
FROM dept_or p
WHERE deptno = 10)
SET ename = LOWER(ename);
Object-Relational View Instead Of Trigger
Object View Instead Of Trigger
-- for demo table and data: See Object-Relational Views link at page bottom
CREATE OR REPLACE TRIGGER ioft_ov_empdept
INSTEAD OF INSERT
ON ov_empdept
FOR EACH ROW
BEGIN
INSERT INTO emp
(empno, ename, job, mgr, hiredate,
sal, comm, deptno)
VALUES
(:NEW.empno, :NEW.ename, :NEW.job, :NEW.mgr, :NEW.hiredate,
:NEW.sal, :NEW.comm, :NEW.dept.deptno);
INSERT INTO dept
(deptno, dname, location)
VALUES
(:NEW.dept.deptno, :NEW.dept.dname, :NEW.dept.location);
END ioft_ov_empdept;
/
