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.
Purpose
Analytic view validation
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
Upgrade Log Table
VERSION_12_2_0_1
NUMBER
1
VERSION_12_2_0_2
NUMBER
2
VERSION_NONE
NUMBER
3
VERSION_LATEST
NUMBER
VERSION_12_2_0_2
Data Types
TYPE ID3 IS RECORD (
comp1 VARCHAR2(128),
comp2 VARCHAR2(128),
comp3 VARCHAR2(128));
TYPE ID2 IS RECORD (
comp1 VARCHAR2(128),
comp2 VARCHAR2(128));
TYPE ID_SEQUENCE IS VARRAY(32767) OF VARCHAR2(128);
Creates a log table for use in validating hierarchies
dbms_hierarchy.create_validate_log_table(
table_name IN VARCHAR2,
owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'),
ignore_if_exists IN BOOLEAN DEFAULT FALSE);
dbms_hierarchy.create_view_for_star_rows(
analytic_view_name IN VARCHAR2,
dimension_alias IN VARCHAR2,
view_name IN VARCHAR2,
analytic_view_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
view_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
hier_qual_sep IN VARCHAR2 DEFAULT '_',
include_hier_attr IN BOOLEAN DEFAULT FALSE);
PRAGMA supplemental_log_data(create_view_for_star_rows, AUTO_WITH_COMMIT);
dbms_hierarchy.get_mv_sql_for_av_cache(
analytic_view_name IN VARCHAR2,
cache_idx IN NUMBER, -- 0 based cache index
analytic_view_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'))
RETURN CLOB;
Validates an analytic view writing output to the named log table
dbms_hierarchy.validate_analytic_view(
analytic_view_name IN VARCHAR2,
analytic_view_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'),
log_table_name IN VARCHAR2 DEFAULT NULL,
log_table_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'),
skip hiers IN VARCHAR2,
error_threshold IN NUMBER)
RETURN NUMBER;
SELECT dbms_hierarchy.validate_analytic_view('UWAVIEW', 'C##UWCLASS', 'UWLOGTAB', 'C##UWCLASS')
FROM dual;
*
ERROR at line 1:
ORA-18307: analytic view "C##UWCLASS"."UWAVIEW" does not exist
ORA-06512: at "SYS.DBMS_HIERARCHY", line 385
ORA-06512: at "SYS.DBMS_HIERARCHY", line 442
ORA-06512: at line 1