General Information
Library Note
Morgan's Library Page Header
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 .
Be sure to view the full listing of monographs in Morgan's Library
Data Dictionary Objects
ALL_REFS
DBA_REFS
USER_REFS
CDB_REFS
REFCON$
System Privileges
CREATE ANY TABLE
CREATE TABLE
Primary Key Constraint
Object Identifier
CREATE TABLE <schema_name>.<table_name>OF <type_name>(
CONSTRAINT <constraint_name>
PRIMARY KEY (<column_name>))
OBJECT IDENTIFIER IS PRIMARY KEY;
CREATE OR REPLACE TYPE address_type AUTHID DEFINER AS OBJECT (
street VARCHAR2(30),
city VARCHAR2(30),
state VARCHAR2(2),
zipcode VARCHAR2(5));
/
desc address_type
SELECT text
FROM user_source
WHERE name = 'ADDRESS_TYPE';
CREATE OR REPLACE TYPE person_type AUTHID DEFINER AS OBJECT (
pername VARCHAR2(30),
dob DATE,
home_address address_type,
work_address address_type);
/
desc person_type
set describe depth all linenum on indent on
desc person_type
SELECT text
FROM user_source
WHERE name = 'PERSON_TYPE';
SELECT type_name, type_oid, typecode, incomplete, final, instantiable
FROM user_types;
CREATE TABLE people OF person_type (
CONSTRAINT pk_people
PRIMARY KEY (pername))
OBJECT IDENTIFIER IS PRIMARY KEY ;
desc people
-- not here
SELECT table_name, nested
FROM user_tables;
-- not here
SELECT table_name
FROM user_nested_tables;
-- here
SELECT table_name, nested
FROM user_all_tables;
col object_name format a30
SELECT object_name, object_type
FROM user_objects;
-- Note: use type name from above query
SELECT text
FROM user_source
WHERE name = 'SYS_YOID0000117802$';
SELECT constraint_name, constraint_type, table_name
FROM user_constraints;
DANGLING
Dangling Demo
CREATE OR REPLACE TYPE cust_address_t AUTHID DEFINER AS OBJECT (
street_address VARCHAR2(40),
postal_code VARCHAR2(10),
city VARCHAR2(30),
state_province VARCHAR2(2),
country_id VARCHAR2(2));
/
CREATE TABLE address_table OF cust_address_t;
INSERT INTO address_table
VALUES ('123 Main St.','98040','Mercer Island','WA','US');
CREATE TABLE customer_addresses (
add_id NUMBER(10),
address REF cust_address_t SCOPE IS address_table);
INSERT INTO customer_addresses
SELECT 11, REF(a)
FROM address_table a;
col table_name format a20
col column_name format a13
col scope_table_owner format a5
col scope_table_name format a16
SELECT *
FROM user_refs;
TABLE_NAME COLUMN_NAME WIT IS_ SCOPE SCOPE_TABLE_NAME OBJECT_ID_TYPE
-------------------- ------------- --- --- ----- ---------------- ----------------
CUSTOMER_ADDRESSES ADDRESS NO YES C##OE ADDRESS_TABLE SYSTEM GENERATED
OC_ORDERS CUSTOMER_REF NO NO SYSTEM GENERATED
SELECT *
FROM customer_addresses ca
WHERE ca.address IS DANGLING ;
DROP TABLE address_table PURGE;
col address format a75
SELECT *
FROM customer_addresses ca
WHERE ca.address IS NOT DANGLING ;
no rows returned.
SELECT *
FROM customer_addresses ca
WHERE ca.address IS DANGLING ;
ADD_ID ADDRESS
------- ---------------------------------------------------------------------------
11 0000220208FE900ADE9D0E4680B7D6026FA19B089A4E49FF1955FE44CEA710F82C7393AE94
DEREF
Returns the object reference of argument expr, where expr must return a REF to an object
standard.DEREF(r REF IN "<ADT_1>") RETURN "<ADT_1>";
See Demo below
MAKE_REF
Creates a REF to a row of an object view or a row in an object table
whose object identifier is primary key based
MAKE_REF(<table_or_view_name>, <key>) RETURN REF;
SELECT MAKE_REF(oc_inventories, 3003) FROM dual;
REF
Takes a correlation variable (table alias) associated with a row of an object table or an object view.
A REF value is returned for the object instance that is bound to the variable or row.
standard.REF (item "<ADT_WITH_OID>") RETURN REF "<ADT_1>";
See Demo below
REFERENCES
Create a table but with a referential integrity constraint on the
REF column that references the OID column of the parent table
<column_name> REF <data_type> REFERENCES <object_table>
CREATE OR REPLACE TYPE cust_address_t AUTHID DEFINER AS OBJECT (
street_address VARCHAR2(40),
postal_code VARCHAR2(10),
city VARCHAR2(30),
state_province VARCHAR2(2),
country_id VARCHAR2(2));
/
CREATE TABLE address_table OF cust_address_t;
CREATE TABLE customer_addresses (
add_id NUMBER,
address REF cust_address_t REFERENCES address_table);
SCOPE
Restrict the scope of a REF reference to a single table
<column_name> REF <data_type> REFERENCES <object_table>
CREATE OR REPLACE TYPE cust_address_t AUTHID DEFINER AS OBJECT (
street_address VARCHAR2(40),
postal_code VARCHAR2(10),
city VARCHAR2(30),
state_province VARCHAR2(2),
country_id VARCHAR2(2));
/
CREATE TABLE address_table OF cust_address_t;
CREATE TABLE customer_addresses (
add_id NUMBER,
address REF cust_address_t
SCOPE IS address_table);
Object Table Referential Constraints Demo
Foreign Key Without OID Reference
CREATE OR REPLACE TYPE cust_address_t AUTHID DEFINER AS OBJECT (
street_address VARCHAR2(40),
postal_code VARCHAR2(10),
city VARCHAR2(30),
state_province VARCHAR2(2),
country_id VARCHAR2(2));
/
CREATE TABLE address_table OF cust_address_t ;
desc address_table
INSERT INTO address_table
VALUES ('123 Main St.','98040','Mercer Island','WA','US');
INSERT INTO address_table
VALUES ('1 Broadway','10202','New York','NY','US');
INSERT INTO address_table
VALUES ('2462 Edgar Crest','V6L 2C4','Vancouver','BC','CN');
SELECT * FROM address_table;
SELECT REF (tabref ) FROM address_table tabref ;
CREATE TABLE customer_addresses (
add_id NUMBER(10),
address REF cust_address_t SCOPE IS address_table);
set describe depth all linenum on indent on
desc customer_addresses
INSERT INTO customer_addresses
SELECT 11, REF (a)
FROM address_table a
WHERE country_id = 'CN';
SELECT *
FROM customer_addresses;
ROLLBACK;
INSERT INTO customer_addresses
SELECT 11, REF (a)
FROM address_table a;
SELECT *
FROM customer_addresses;
col deref_add format a80
SELECT add_id, DEREF (address) DEREF_ADD
FROM customer_addresses;
Check Constraint
Conditions of check constraints cannot contain the following constructs:
Dereferencing of REF columns (for example, using the DEREF function)
Nested table columns or attributes
Calls to user-defined functions