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_CONSTRAINTS
CDB_INDEXES
DBA_TAB_COLS
ALL_INDEXES
CDB_TAB_COLS
USER_CONSTRAINTS
ALL_TAB_COLS
DBA_CONSTRAINTS
USER_INDEXES
CDB_CONSTRAINTS
DBA_INDEXES
USER_TAB_COLS
Record Level Integrity
Primary Keys
SELECT COUNT(*)
FROM user_tables;
SELECT COUNT(*)
FROM user_constraints
WHERE constraint_type = 'P';
Column Definition Integrity
CHAR
conn oe/oe@pdbdev
set linesize 131
set pagesize 25
col COLNAME format a30
col t1dt format a10
col t2dt format a10
SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type T1DT, t2.table_name T2T, t2.data_type T2DT
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = 'CHAR'
AND t2.data_type <> 'CHAR'
ORDER BY t1.column_name, t1.table_name;
DATE
SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type T1DT, t2.table_name T2T, t2.data_type T2DT
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = 'DATE'
AND t2.data_type <> 'DATE'
ORDER BY t1.column_name, t1.table_name;
FLOAT
SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type T1DT, t2.table_name T2T, t2.data_type T2DT
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = 'FLOAT'
AND t2.data_type <> 'FLOAT'
ORDER BY t1.column_name, t1.table_name;
NUMBER
SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type T1DT, t2.table_name T2T, t2.data_type T2DT
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = 'NUMBER'
AND t2.data_type <> 'NUMBER'
ORDER BY t1.column_name, t1.table_name;
TIMESTAMP
SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type T1DT, t2.table_name T2T, t2.data_type T2DT
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = 'TIMESTAMP'
AND t2.data_type <> 'TIMESTAMP'
ORDER BY t1.column_name, t1.table_name;
VARCHAR2
SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type T1DT, t2.table_name T2T, t2.data_type T2DT
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = 'VARCHAR2'
AND t2.data_type <> 'VARCHAR2'
ORDER BY t1.column_name, t1.table_name;
Data Length Integrity
NUMBER
conn oe/oe@pdbdev
SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type,
t1.data_length T1DL, t2.table_name T2T, t2.data_length T2DL
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = t2.data_type
AND t1.data_type = 'NUMBER'
AND t1.data_length <> t2.data_length
ORDER BY t1.column_name, t1.table_name;
VARCHAR2
conn uwclass/uwclass@pdbdev
CREATE TABLE t1 AS SELECT table_name FROM user_tables;
CREATE TABLE t2 AS SELECT table_name FROM user_tables;
ALTER TABLE t2
MODIFY (table_name VARCHAR2(43));
set linesize 131
col t1t format a25
col t2t format a25
col colname format a20
col data_type format a15
SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type,
t1.data_length T1DL, t2.table_name T2T, t2.data_length T2DL
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = t2.data_type
AND t1.data_type = 'VARCHAR2'
AND t1.data_length <> t2.data_length
ORDER BY t1.column_name, t1.table_name;
Data Precision Integrity
NUMBER
CREATE TABLE t1 AS
SELECT initial_extent
FROM all_tables;
CREATE TABLE t2 AS
SELECT * FROM t1
WHERE 1=2;
ALTER TABLE t2
MODIFY (initial_extent NUMBER(10,4));
set linesize 141
col data_type format a15
col T1T format a15
col T2T format a15
SELECT t1.table_name T1T,
t1.column_name COLNAME, t1.data_type,
t1.data_length T1DL, t1.data_precision T1DP, t2.table_name T2T,
t2.data_length T2DL,
t2.data_precision T2DP
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = t2.data_type
AND t1.data_type = 'NUMBER'
AND NVL(t1.data_precision,0) <> NVL(t2.data_precision,0)
ORDER BY t1.column_name, t1.table_name;
Removing Duplicates
Using an analytic function
conn uwclass/uwclass@pdbdev
CREATE TABLE t AS
SELECT table_name, tablespace_name
FROM all_tables;
INSERT INTO t
SELECT * FROM t;
COMMIT;
SELECT * FROM t ORDER BY 1,2;
EXPLAIN PLAN FOR
DELETE t
WHERE ROWID IN (
SELECT LEAD(ROWID) OVER (PARTITION BY table_name, tablespace_name ORDER BY NULL)
FROM t);
SELECT * FROM TABLE(dbms_xplan.display);
DELETE t
WHERE ROWID IN (
SELECT LEAD(ROWID)
OVER (PARTITION BY table_name, tablespace_name ORDER BY NULL)
FROM t);
