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
Purpose
Dependencies
ALL_EDITIONING_VIEWS
DBA_EDITIONING_VIEW_COLS
KU$_EDITION_SCHEMAOBJ_VIEW
ALL_EDITIONING_VIEWS_AE
DBA_EDITIONING_VIEW_COLS_AE
KU$_EDITION_TRIG_EXISTS_VIEW
ALL_EDITIONING_VIEW_COLS
CDB_EDITIONS
KU$_USER_EDITIONING_LIST_T
ALL_EDITIONING_VIEW_COLS_AE
DBA_EDITION_COMMENTS
KU$_USER_EDITIONING_T
ALL_EDITIONS
DBA_SOURCE_AE
KU$_USER_EDITIONING_VIEW
ALL_EDITION_COMMENTS
DBMS_EDITIONS_UTILITIES
USABLE_EDITIONS
ALL_SOURCE_AE
DBMS_EDITIONS_UTILITIES_LIB
USER_EDITIONED_TYPES
CDB_EDITIONED_TYPES
EDITION$
USER_EDITIONING$
CDB_EDITIONING_VIEWS
EDITIONING_TYPES$
USER_SOURCE_AE
CDB_EDITIONING_VIEWS_AE
USER_EDITIONING_VIEWS
USER_EDITIONING_VIEWS
CDB_EDITIONING_VIEW_COLS
USER_EDITIONING_VIEWS_AE
USER_EDITIONING_VIEWS_AE
CDB_EDITIONING_VIEW_COLS_AE
FED$EDITIONS
USER_EDITIONING_VIEW_COLS
CDB_EDITIONS
GV$EDITIONABLE_TYPES
USER_EDITIONING_VIEW_COLS_AE
CDB_EDITION_COMMENTS
GV_$EDITIONABLE_TYPES
V$EDITIONABLE_TYPES
CDB_SOURCE_AE
I_USER_EDITIONING
V_$EDITIONABLE_TYPES
DBA_EDITIONED_TYPES
KU$_EDITION_OBJ_VIEW
_ACTUAL_EDITION_OBJ
DBA_EDITIONING_VIEWS
KU$_EDITION_PROC_EXISTS_VIEW
_CURRENT_EDITION_OBJ
DBA_EDITIONING_VIEWS_AE
Object Privileges
USE
GRANT use ON EDITION ora$base TO public;
REVOKE use ON EDITION ora$base FROM public;
System Privileges
ALTER ANY EDITION
CREATE ANY EDITION
DROP ANY EDITION
Editionable Object Types
FUNCTIONS
PACKAGES
TYPES & TYPE BODIES
LIBRARYS
PROCEDURES
VIEWS
Non-Editionable Object Types
Functions used to create function based indexes
Segments
Types used to define object-tables: For example a VARRAY used as a table column
Create Edition
Create Edition
Oracle recommends that the AS CHILD OF syntax not be used
CREATE EDITION <child_edition_name> [AS CHILD OF <parent_edition_name>];
conn sys@pdbdev as sysdba
ALTER USER uwclass ENABLE EDITIONS;
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE PROCEDURE test_proc AUTHID CURRENT_USER IS
BEGIN
NULL;
END test_proc;
/
SELECT object_name, object_type, edition_name
FROM user_objects
ORDER BY 2,1;
SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 2,1;
desc user_source
desc user_source_ae
SELECT * FROM all_editions;
CREATE EDITION demo_ed;
SELECT * FROM all_editions;
PL/SQL Object Editioning
sho edition
SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;
-- created in default edition
CREATE OR REPLACE FUNCTION edition_test RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
RETURN 'ORA$BASE';
END edition_test;
/
SELECT edition_test FROM dual;
ALTER SESSION SET EDITION = demo_ed;
sho edition
SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;
SELECT edition_test FROM dual;
-- created in the new edition
CREATE OR REPLACE FUNCTION edition_test RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
RETURN sys_context('USERENV', 'CURRENT_EDITION_NAME');
END edition_test;
/
SELECT edition_test FROM dual;
ALTER SESSION SET EDITION = ora$base;
SELECT edition_test FROM dual;
DROP FUNCTION edition_name;
SELECT edition_name FROM dual;
ALTER SESSION SET EDITION = ora$base;
SELECT edition_name FROM dual;
Alter Edition
Alter Edition Object
ALTER EDITION <edition_name> <USABLE | UNUSABLE>;
See DROP EDITION Demo
Commenting An Edition
Adding a comment to an edition
COMMENT ON EDITION <edition_name> IS '<comment_text>';
col comments format a50
SELECT * FROM all_edition_comments;
COMMENT EDITION demo_ed IS 'This is a demonstration edition';
SELECT * FROM all_edition_comments;
COMMENT EDITION demo_ed IS '';
SELECT * FROM all_edition_comments;
Edition Enable Users
Enable a user to access and work with editions
ALTER USER <user_name> <ENABLE | DISABLE> EDITIONS [FORCE];
conn sys@pdbdev as sysdba
ALTER USER scott ENABLE EDITIONS;
ALTER USER scott DISABLE EDITIONS FORCE;
Drop Edition
Drop Edition Object
DROP EDITION <edition_name> [CASCADE];
conn sys@pdbdev as sysdba
CREATE EDITION uwed3;
DROP EDITION uwed3;
ALTER EDITION uwed3 UNUSABLE;
DROP EDITION uwed3 CASCADE;
Edition Related Queries
Object Information
conn / as sysdba
desc obj$
set linesize 121
SELECT obj#, name, namespace, type#, status, spare3
FROM obj$
WHERE name = 'ORA$BASE';
Default Edition Information
conn / as sysdba
desc props$
set linesize 121
col value$ format a30
col comment$ format a40
SELECT *
FROM props$
WHERE name LIKE '%EDITION%';
Edition Related Auditing
conn / as sysdba
desc audit_actions
SELECT *
FROM audit_actions
WHERE name LIKE '%EDITION%';
