| Setup As SYS - Create
Application Owner |
The following statements, executed from an
account with appropriate credentials is used to create the application
schema owner, EBRADMIN, wit appropriate permissions for the demonstrations
on this page. While this demo uses SYS, the user most likely to be used by
students, it is recommended that a properly privileged DBA management
account be used in any organization wishing to implement this technology.
Privileges granted here for demonstration purposes, similarly, should not be
granted in a non-training environment.
The demonstrations on this page are not intended to be fully inclusive of
all available functionality: Student need to read
Byrn's White Paper and
review the
Oracle docs to gain a comprehensive understanding. These demos are intended to illustrate specific aspects of
value in gaining an understanding of this valuable technology. |
conn / as sysdba
-- view available tablespaces
SELECT tablespace_name, contents
FROM dba_tablespaces
ORDER BY 2,1;
SELECT file_name
FROM dba_data_files;
-- if a suitable tablespace does not exist then
create a demo tablespace
CREATE TABLESPACE uwdata
DATAFILE 'c:\oracle\product\oradata\orabase\uwdata01.dbf' SIZE 100M
LOGGING ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
DEFAULT NOCOMPRESS
SEGMENT SPACE MANAGEMENT AUTO;
-- create the application owner
CREATE USER ebradmin
IDENTIFIED BY ebradmin
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 20M ON uwdata;
-- grant system privileges
GRANT create session, create table, create procedure, create synonym,
create view TO
ebradmin;
GRANT select ON dba_editions TO ebradmin;
GRANT select ON dba_objects_ae TO ebradmin;
GRANT select ON dba_source_ae TO ebradmin;
GRANT create any edition TO ebradmin;
GRANT drop any edition TO ebradmin;
-- enable editions for the application owner and user
ALTER USER ebradmin ENABLE EDITIONS;
ALTER USER uwclass ENABLE EDITIONS;
-- review data dictionary entries
SELECT username, account_status, created, editions_enabled
FROM dba_users
WHERE username IN ('EBRADMIN', 'UWCLASS');
SELECT *
FROM dba_sys_privs
WHERE grantee IN ('EBRADMIN', 'UWCLASS')
ORDER BY 1,2;
SELECT
d.username, u.account_status
FROM dba_users_with_defpwd d, dba_users u
WHERE d.username = u.username
ORDER BY 2,1; |
| |
| Edition Basics 1: CREATE and
DROP EDITION |
| This demonstration covers the first step in
mastering EBR. Creating an edition and navigating between editions. The
string returned by the function demo_func is hardcoded to eliminate any
misinterpretation of which function is visible to the user. |
conn ebradmin/ebradmin
-- view default edition
SELECT * FROM dba_editions;
SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_EDITION';
-- create and test a function under the default
edition
CREATE OR REPLACE FUNCTION demo_func RETURN VARCHAR2 AUTHID CURRENT_USER
IS
BEGIN
RETURN 'ORA$BASE';
END demo_func;
/
SELECT demo_func FROM dual;
-- examine data dictionary metadata
desc user_objects
SELECT object_name, object_type, edition_name
FROM user_objects;
-- create a child edition
CREATE EDITION demo_ed;
-- Oracle recommends that the "AS CHILD OF" syntax
not be used at this time
SELECT * FROM dba_editions;
sho edition
SELECT sys_context('USERENV',
'CURRENT_EDITION_ID') FROM dual;
SELECT sys_context('USERENV',
'CURRENT_EDITION_NAME') FROM dual;
-- make the child edition current
ALTER SESSION SET EDITION=demo_ed;
sho edition
SELECT sys_context('USERENV',
'CURRENT_EDITION_ID') FROM dual;
SELECT sys_context('USERENV',
'CURRENT_EDITION_NAME') FROM dual;
-- the editionable object was "copied" into the
child edition
SELECT demo_func FROM dual;
-- drop the function from the child edition
DROP FUNCTION demo_func;
-- the pointer to demo_func in the child edition has
been deleted
SELECT object_name, object_type, edition_name
FROM user_objects;
-- return to the parent edition
ALTER SESSION SET EDITION=ORA$BASE;
sho edition
-- dropping the child edition does not alter the
object in the parent edition
SELECT object_name, object_type, edition_name
FROM user_objects;
-- commenting an edition
col comments format a50
SELECT * FROM all_edition_comments;
COMMENT ON EDITION demo_ed
IS 'This is a demonstration edition';
SELECT * FROM all_edition_comments;
DROP EDITION demo_ed; |
| |
| Edition Basics 2: Editioning
A PL/SQL Object |
| This demonstration covers the second step in
mastering EBR. Creating an editionable object, in this case a simple
function written in PL/SQL and observing how both objects exist and function
in the same schema at the same time: Their independence maintained by
editioning. |
conn ebradmin/ebradmin
-- create a child edition
CREATE EDITION demo_ed;
-- make the child edition current
ALTER SESSION SET EDITION=demo_ed;
sho edition
-- editionable object was copied into the new
edition
SELECT demo_func FROM dual;
-- recreate and test a function with the same name
but different return value
CREATE OR REPLACE FUNCTION demo_func RETURN VARCHAR2 AUTHID CURRENT_USER
IS
BEGIN
RETURN 'DEMO_ED';
END demo_func;
/
-- should there be a compilation error
CREATE OR REPLACE FUNCTION demo_func RETURN VARCHAR2 AUTHID CURRENT_USER
IS
BEGIN
RETURN 'DEMO_ED';;
END demo_func;
/
SELECT * FROM user_errors;
SELECT * FROM user_errors_ae;
-- test the new function
SELECT demo_func FROM dual;
-- review the data dictionary presentation
SELECT object_name, object_type, edition_name
FROM user_objects
ORDER BY 1;
-- user_objects shows only the current edition
-- user_objects_ae and user_source_ae show All Editions (thus the AE)
SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 1;
col text format a60
SELECT edition_name, text
FROM user_source_ae
WHERE name = 'DEMO_FUNC'
ORDER BY edition_name, line;
-- drop the function in the child edition
DROP FUNCTION demo_func;
-- replace the function with a procedure of the same
name and test it
CREATE OR REPLACE PROCEDURE demo_func AUTHID CURRENT_USER IS
BEGIN
dbms_output.put_line('DEMO_ED');
END demo_func;
/
set serveroutput on
exec demo_func;
-- review the data dictionary presentation
SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 1;
SELECT edition_name, text
FROM user_source_ae
WHERE name = 'DEMO_FUNC'
ORDER BY edition_name, line;
-- return to the parent edition
ALTER SESSION SET EDITION=ORA$BASE;
show edition
-- attempt to drop the edition
DROP EDITION demo_ed;
-- learn from the exception then drop the edition
DROP EDITION demo_ed CASCADE; |
| |
| Edition Basics 3:
NON-EXISTENT Objects |
| It can be a bit of a shock seeing a
"NON-EXISTENT" object. So see it now and understand what it is (or isn't). |
conn ebradmin/ebradmin
DROP FUNCTION demo_func;
SELECT object_name, object_type
FROM user_objects
ORDER BY 1;
SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 1,2; |
| |
| Edition Basics 4: Switch Edition |
| In this final demo we start with a stored
procedure that inserts 5 records into a table. Execute on the procedure and
select on the table are granted to a second, application client, user. This
demo is intended to show how the grant of EXECUTE by the application to the
client is edition independent. |
conn ebradmin/ebradmin
-- create the application objects
CREATE TABLE ebr_tab (
testcol VARCHAR2(20));
CREATE VIEW ebr_tab_view AS
SELECT TRANSLATE(testcol, 'rgan','gens') testcol
FROM ebr_tab;
CREATE OR REPLACE SYNONYM ebr_syn FOR ebr_tab;
CREATE OR REPLACE PROCEDURE test_proc(instr IN VARCHAR2) AUTHID DEFINER IS
BEGIN
FOR i IN 1 .. 5 LOOP
INSERT INTO ebr_tab (testcol) VALUES (instr);
END LOOP;
COMMIT;
END test_proc;
/
-- grant privileges to the end-user
GRANT execute ON test_proc TO uwclass;
GRANT select ON ebr_tab TO uwclass;
GRANT select ON ebr_tab_view TO uwclass;
-- test the application
conn uwclass/uwclass
exec ebradmin.test_proc('Morgan');
SELECT * FROM ebradmin.ebr_tab;
SELECT * FROM ebradmin.ebr_tab_view;
SELECT * FROM ebradmin.ebr_syn; |
conn ebradmin/ebradmin
-- clean up the test data
TRUNCATE TABLE ebr_tab;
-- create the child edition and the child's version
of the procedure
CREATE EDITION demo_ed;
ALTER SESSION SET EDITION=demo_ed;
SELECT object_name, object_type, edition_name
FROM user_objects_ae;
CREATE OR REPLACE PROCEDURE test_proc(instr IN VARCHAR2) AUTHID DEFINER IS
BEGIN
FOR i IN 1 .. 10 LOOP
INSERT INTO ebr_tab (testcol) VALUES (REVERSE(instr));
END LOOP;
COMMIT;
END test_proc;
/
CREATE OR REPLACE VIEW ebr_tab_view AS
SELECT TRANSLATE(testcol, 'aeiou','uoiea') testcol
FROM ebr_tab;
CREATE OR REPLACE SYNONYM ebr_syn FOR ebr_tab_view;
SELECT object_name, object_type, edition_name
FROM user_objects_ae;
-- test the application
conn
uwclass/uwclass
-- who and where are we?
sho user
sho edition
exec ebradmin.test_proc('Morgan');
SELECT * FROM ebradmin.ebr_tab;
SELECT * FROM ebradmin.ebr_tab_view;
SELECT * FROM ebradmin.ebr_syn;
ALTER SESSION SET EDITION=demo_ed;
-- learn from the exception
-- how does uwclass get access to the new edition? |
-- return to the application
administrator and grant the required privilege
conn ebradmin/ebradmin
GRANT use ON EDITION demo_ed
TO uwclass;
-- retest the application
conn
uwclass/uwclass
-- who and where are we?
sho user
sho edition
ALTER SESSION SET EDITION=demo_ed;
sho edition
exec ebradmin.test_proc('Morgan');
SELECT * FROM ebradmin.ebr_tab;
SELECT * FROM ebradmin.ebr_tab_view;
SELECT * FROM ebradmin.ebr_syn; |
-- remove the client's access to the
edition
conn ebradmin/ebradmin
REVOKE use ON EDITION demo_ed
FROM uwclass;
DROP EDITION demo_ed CASCADE; |
|
This concludes Demo 1. The second demo, which
is linked below extends the scope to
include Editioning Views. |
|
