Oracle | UWClass Lab Setup

Oracle Class Setup Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Description This page contains the SQL statements that can be used in an Oracle database 12c or above to create an environment compatible with running library code samples and demos. Some of the code in this section is repeated in the library but this page is designed to bring specific pieces together into a coherent structure to allow you to learn about and work with the new database CDB/PDB architecture.

The first instruction assumes you have already followed Oracle's published documentation and installed a 12cR1, or greater (12cR2, 18c, 19c, 20c), database on Windows though everything here is compatible with a Linux or other install as well except for pathing. When doing so you created a CDB with a single PDB named PDBDEV that contains Oracle's sample schemas such as HR, OE, PM, SH, and SCOTT. A second PDB is created as part of the setup, as indicated below, into which our class user, UWCLASS, is created.

Edition Enterprise

Version 21c

As ROOT

These alterations are recommended assuming the indicated conditions are met -- if the server has > 16GB RAM and SGA will be larger than 5GB configure HugePages memory_target=0 memory_max_target=0 sga_target=<integer> -- size based on your server's memory pga_aggregate_target=<integer> -- size based on your server's memory /* for Linux run the script hugepages_settings.sh copy the shell scripts output which will look something like this: vm.nr_hugepages = 9999 to the last line of sysctl.conf /etc/sysctl �p validate the change has taken affect grep Huge /proc/meminfo */

As SYSDBA

Modify GLOGIN.SQL Find the file {ORACLE_HOME}/sqlplus/admin/glogin.sql and rename it to glogin.bak. Create a new file in the same directory named glogin.sql with the as follows contents.

set arraysize 250 set define off set hist on set linesize 181 set long 1000000 set pagesize 45 set serveroutput on set trim on set trimspool on col argument_name format a30 col cluster_name format a30 col col_name format a30 col column_name format a30 col constraint_name format a30 col container_name format a30 col data_type format a30 col db_link format a30 col directory_name format a30 col directory_path format a30 col edition_name format a30 col file_name format a60 col granted_role format a30 col grantee format a30 col host_name format a20 col index_name format a30 col iot_name format a30 col limit format a30 col max_lag_time format a12 col name format a30 col object_name format a30 col object_type format a25 col owner format a25 col owner_name format a25 col package_name format a30 col param_name format a25 col partition_name format a30 col pdb format a20 col procedure_name format a30 col profile format a30 col queue_table format a30 col referenced_owner format a30 col role format a30 col schedule_name format a30 col segment_name format a30 col sequence_owner format a30 col sequence_name format a30 col service_name format a30 col subobject_name format a30 col synonym_name format a30 col table_name format a30 col table_type format a30 col triggering_event format a35 col type_name format a30 col type_owner format a30 col type_subname format a30 col username format a30 col value format a30 SET TERM OFF ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; SET TERM ON -- if *nix add this line too define _editor=vi

Enable Block Change Tracking SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;

Enable Block Change Tracking ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/app/oracle/product/21.0.0.0/dbhome_1/dbs/bctf01.log';

Setup Appropriate Initialization Parameters -- most people try to figure out which parameters require a shutdown and which do not -- it is irrelevant for initial configuration -- make all scope=SPFILE and then restart the instance ALTER SYSTEM SET sort_area_size=5120 COMMENT='Altered 2020-12-26' SID='*' SCOPE=SPFILE; ALTER DATABASE FORCE LOGGING; SHUTDOWN IMMEDIATE; STARTUP;

Enable Supplemental Logging ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered.

Remap the temporary tablespace to a tablespace group SELECT tablespace_name FROM dba_temp_files ORDER BY 1; TABLESPACE_NAME ---------------- TEMP ALTER TABLESPACE temp TABLESPACE GROUP tempgrp; Tablespace altered.

Modify SQL*Plus (Windows only)

After opening SQL*Plus in a terminal window right click on the window title: Select PROPERTIES
On the Options tab page make sure the edit options "QuickEdit Mode" and "Insert Mode" are both selected
On the Layout tab page set the Screen Buffer Size width to at least 161 and the height to at least 1000
Set the Window size width to 201 and the height to 50

SQL*Plus Logon / as sysdba

Verify you are in the root of your Container Database (CDB) -- you must see the following:

SQL> sho con_name CON_NAME ------------------------------ CDB$ROOT SQL> sho con_id CON_ID ------------------------------ 1

Log into the PDBDEV PDB

SQL> SELECT name

         2 FROM v$active_services

         3 ORDER BY 1;

      

      NAME

      ------------------------------

      SYS$BACKGROUND

      SYS$USERS

      orabase

      orabaseXDB

      pdbdev

-- listener changes ... then stop and restart

SID_LIST_LISTENER =

            (SID_LIST =

              (SID_DESC =

                (SID_NAME = CLRExtProc)

                (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)

                (PROGRAM = extproc)

                (ENVS = "EXTPROC_DLLS=ONLY:c:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")

              )

              (SID_DESC =

                (SID_NAME = PDBDEV)

                (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)

              )

              (SID_DESC =

                (SID_NAME = PDBTEST)

                (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)

              )

            )

          

          LISTENER =

            (DESCRIPTION_LIST =

              (DESCRIPTION =

                (ADDRESS = (PROTOCOL = TCP)(HOST = PERRITO4)(PORT = 1522))

                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))

              )

            )

          

          ADR_BASE_LISTENER = C:\app\oracle

-- it may be necessary to configure listener.ora and tnsnames.ora to 127.0.0.1
-- and restart the listener ... check in final release add an entry into tnsnames.ora file

PDBDEV =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

            (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = PDBDEV)

            )

        )

Create a new PDB: ORADEV -- create pdb attributes method
CREATE PLUGGABLE DATABASE oradev ADMIN USER devdba IDENTIFIED BY devdba FILE_NAME_CONVERT = ('\pdbseed\', '\oradev\') STORAGE (MAXSIZE 400M MAX_SHARED_TEMP_SIZE 100M) DEFAULT TABLESPACE oradata DATAFILE 'c:\app\oracle\oradata\orabase\oradev\oradata01.dbf' SIZE 100M; SELECT * FROM pdb_status; ALTER PLUGGABLE DATABASE oradev OPEN; SELECT * FROM pdb_status;

Create another new PDB: ORATEST -- create pdb clone method
CREATE PLUGGABLE DATABASE pdbtest FROM pdbdev FILE_NAME_CONVERT = ('\pdbdev\', '\pdbtest\'); CREATE PLUGGABLE DATABASE pdbtest FROM pdbdev * ERROR at line 1: ORA-65081: database or pluggable database is not open in read only mode ALTER PLUGGABLE DATABASE pdbdev CLOSE; SELECT * FROM pdb_status; ALTER PLUGGABLE DATABASE pdbdev OPEN READ ONLY; SELECT * FROM pdb_status; CREATE PLUGGABLE DATABASE pdbtest FROM pdbdev FILE_NAME_CONVERT = ('\pdbdev\', '\pdbtest\'); ALTER PLUGGABLE DATABASE pdbdev CLOSE; ALTER PLUGGABLE DATABASE ALL OPEN; SELECT * FROM pdb_status;

Create tablespace and UW class user in all three PDBs ALTER SESSION SET CONTAINER = pdbdev; SELECT sys_context('USERENV', 'CON_NAME') FROM dual;

-- make sure PDBDEV is open and, if not, ALTER PLUGGABLE DATABASE OPEN;

ALTER PLUGGABLE DATABASE SET DEFAULT BIGFILE TABLESPACE; SELECT file_name FROM dba_data_files; -- if the following exception is raised (red), change the storage maxsize for the PDB as -- shown and repeat the tablespace creation. If no exception continue to the next step CREATE TABLESPACE uwdata DATAFILE 'c:\app\oracle\oradata\orabase\pdbdev\uwdata01.dbf' SIZE 100M LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE uwdata * ERROR at line 1: ORA-65114: space usage in container is too high SELECT file_name, bytes/1024/1024 AS MB FROM dba_data_files ORDER BY 1; FILE_NAME MB --------------------------------------------------- ------ C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\SYSAUX01.DBF 340 C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\SYSTEM01.DBF 270 C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\UNDOTBS01.DBF 100 C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\USERS01.DBF 5 ALTER PLUGGABLE DATABASE pdbdev STORAGE (MAXSIZE 1G); CREATE TABLESPACE uwdata DATAFILE 'c:\app\oracle\oradata\orabase\pdbdev\uwdata01.dbf' SIZE 100M LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K SEGMENT SPACE MANAGEMENT AUTO; SQL> create tablespace uwdata 2 datafile '+DATA/UWDATA01.DBF' SIZE 250M 3 LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8K 4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K 5 SEGMENT SPACE MANAGEMENT AUTO;
SELECT file_name, bytes/1024/1024 AS MB FROM dba_data_files ORDER BY 1; FILE_NAME MB ----------------------------------------------------- ---------- C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\SYSAUX01.DBF 340 C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\SYSTEM01.DBF 270 C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\UNDOTBS01.DBF 100 C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\USERS01.DBF 5 C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\UWDATA01.DBF 100 /* ALTER PROFILE UWDEFAULT composite_limit 1 sessions_per_user 1 cpu_per_session 1 cpu_per_call 1 logical_reads_per_session 1 logical_reads_per_call 1 idle_time 1 connect_time 1 private_sga 1 failed_login_attempts 1 password_life_time 1 password_reuse_time 9999 password_reuse_max 0 password_verify_function ora12c_strong_verify_function; password_lock_time unlimited password_grace_time 0 inactive_account_time 1 container=current; CREATE PROFILE pdbdba failed_login_attempts 3 inactive_account_time 30 password_life_time 60 password_reuse_max 0 password_reuse_time 9999 password_verify_function ora12c_strong_verify_function; */ CREATE USER uwclass IDENTIFIED BY uwclass DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE tempgrp QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON uwdata PROFILE orabase_dba ACCOUNT UNLOCK ENABLE EDITIONS CONTAINER=CURRENT; GRANT create session TO uwclass; GRANT create cluster TO uwclass; GRANT create database link TO uwclass; GRANT create operator TO uwclass; GRANT create procedure TO uwclass; GRANT create role TO uwclass; GRANT create sequence TO uwclass; GRANT create synonym TO uwclass; GRANT create table TO uwclass; GRANT create trigger TO uwclass; GRANT create type TO uwclass; GRANT create view TO uwclass; GRANT execute ON dbms_lock TO uwclass; GRANT select any dictionary TO uwclass;

Build Sample Schemas -- create the user scott with password tiger -- someone at Oracle didn't take the time to test their code and the script -- {$ORACLE_HOME/rdbms/admin/scott.sql will fail in a PDB because the scott -- logon is invalid by any criterion. Therefore here's how you reinstall scott. CREATE USER scott IDENTIFIED BY tiger TEMPORARY TABLESPACE users TEMPORARY TABLESPACE tempgrp QUOTA UNLIMTED ON users; GRANT create session, create table TO scott; -- open the scott.sql script in an editor and copy the CREATE TABLE and INSERT -- statements into SQL*Plus and execute them. Now you have scott and Oracle -- should hang its head in shame. The script utlsamp.sql is equally deplorable. -- https://github.com/oracle/db-sample-schemas/releases/tag/v12.2.0.1

Revoke unnecessary grants to PUBLIC SELECT 'REVOKE SELECT ON ' || table_name || ' FROM PUBLIC;' AS RUN_SCRIPT FROM dba_tab_privs WHERE grantee = 'PUBLIC' AND table_name LIKE 'DBA%' ORDER BY 1; RUN_SCRIPT ------------------------------------------------------------- REVOKE SELECT ON DBA_AUTO_SEGADV_CTL FROM PUBLIC; REVOKE SELECT ON DBA_AUTO_SEGADV_SUMMARY FROM PUBLIC; REVOKE SELECT ON DBA_COL_PENDING_STATS FROM PUBLIC; REVOKE SELECT ON DBA_COL_USAGE_STATISTICS FROM PUBLIC; REVOKE SELECT ON DBA_DBFS_HS_FIXED_PROPERTIES FROM PUBLIC; REVOKE SELECT ON DBA_EDITIONING_VIEW_COLS FROM PUBLIC; REVOKE SELECT ON DBA_EDITIONING_VIEW_COLS_AE FROM PUBLIC; REVOKE SELECT ON DBA_EXPRESSION_STATISTICS FROM PUBLIC; REVOKE SELECT ON DBA_FLASHBACK_ARCHIVE FROM PUBLIC; REVOKE SELECT ON DBA_FLASHBACK_ARCHIVE_TABLES FROM PUBLIC; REVOKE SELECT ON DBA_FLASHBACK_ARCHIVE_TS FROM PUBLIC; REVOKE SELECT ON DBA_HEAT_MAP_SEGMENT FROM PUBLIC; REVOKE SELECT ON DBA_HEAT_MAP_SEG_HISTOGRAM FROM PUBLIC; REVOKE SELECT ON DBA_IND_PENDING_STATS FROM PUBLIC; REVOKE SELECT ON DBA_JAVA_CLASSES FROM PUBLIC; REVOKE SELECT ON DBA_SDO_MAPS FROM PUBLIC; REVOKE SELECT ON DBA_SDO_STYLES FROM PUBLIC; REVOKE SELECT ON DBA_SDO_THEMES FROM PUBLIC; REVOKE SELECT ON DBA_SR_PARTN_OPS FROM PUBLIC; REVOKE SELECT ON DBA_SR_STLOG_STATS FROM PUBLIC; REVOKE SELECT ON DBA_SYNC_CAPTURE_TABLES FROM PUBLIC; REVOKE SELECT ON DBA_TAB_HISTGRM_PENDING_STATS FROM PUBLIC; REVOKE SELECT ON DBA_TAB_PENDING_STATS FROM PUBLIC; REVOKE SELECT ON DBA_TAB_STAT_PREFS FROM PUBLIC; REVOKE SELECT ON DBA_TSTZ_TABLES FROM PUBLIC; REVOKE SELECT ON DBA_XMLSCHEMA_LEVEL_VIEW FROM PUBLIC; -- bold faced REVOKES, below, are based on specific known security threats set pagesize 300 SELECT 'REVOKE EXECUTE ON ' || table_name || ' FROM PUBLIC;' AS RUN_SCRIPT FROM dba_tab_privs dtp WHERE dtp.grantee = 'PUBLIC' AND dtp.privilege = 'EXECUTE' AND dtp.type = 'PACKAGE' AND ((dtp.table_name LIKE 'DBMS%') OR (dtp.table_name LIKE 'UTL%')) ORDER BY 1; RUN_SCRIPT ------------------------------------------------------------- EVOKE EXECUTE ON DBMS_ADDM FROM PUBLIC; REVOKE EXECUTE ON DBMS_ADVISOR FROM PUBLIC; REVOKE EXECUTE ON DBMS_APPLICATION_INFO FROM PUBLIC; REVOKE EXECUTE ON DBMS_APP_CONT_PRVT FROM PUBLIC; REVOKE EXECUTE ON DBMS_AQJMS FROM PUBLIC; REVOKE EXECUTE ON DBMS_AQ_EXP_CMT_TIME_TABLES FROM PUBLIC; REVOKE EXECUTE ON DBMS_AQ_EXP_DEQUEUELOG_TABLES FROM PUBLIC; REVOKE EXECUTE ON DBMS_AQ_EXP_HISTORY_TABLES FROM PUBLIC; REVOKE EXECUTE ON DBMS_AQ_EXP_INDEX_TABLES FROM PUBLIC; REVOKE EXECUTE ON DBMS_AQ_EXP_QUEUES FROM PUBLIC; REVOKE EXECUTE ON DBMS_AQ_EXP_QUEUE_TABLES FROM PUBLIC; REVOKE EXECUTE ON DBMS_AQ_EXP_SIGNATURE_TABLES FROM PUBLIC; REVOKE EXECUTE ON DBMS_AQ_EXP_SUBSCRIBER_TABLES FROM PUBLIC; REVOKE EXECUTE ON DBMS_AQ_EXP_TIMEMGR_TABLES FROM PUBLIC; REVOKE EXECUTE ON DBMS_AQ_IMP_INTERNAL FROM PUBLIC; REVOKE EXECUTE ON DBMS_AQ_INV FROM PUBLIC; REVOKE EXECUTE ON DBMS_ASSERT FROM PUBLIC; REVOKE EXECUTE ON DBMS_AUTO_REPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_AUTO_TASK FROM PUBLIC; REVOKE EXECUTE ON DBMS_AW FROM PUBLIC; REVOKE EXECUTE ON DBMS_AW_EXP FROM PUBLIC; REVOKE EXECUTE ON DBMS_AW_STATS FROM PUBLIC; REVOKE EXECUTE ON DBMS_AW_XML FROM PUBLIC; REVOKE EXECUTE ON DBMS_CLOBUTIL FROM PUBLIC; REVOKE EXECUTE ON DBMS_COMPRESSION FROM PUBLIC; REVOKE EXECUTE ON DBMS_CREDENTIAL FROM PUBLIC; REVOKE EXECUTE ON DBMS_CRYPTO_TOOLKIT FROM PUBLIC; REVOKE EXECUTE ON DBMS_CSX_INT FROM PUBLIC; REVOKE EXECUTE ON DBMS_CSX_INT2 FROM PUBLIC; REVOKE EXECUTE ON DBMS_CUBE FROM PUBLIC; REVOKE EXECUTE ON DBMS_CUBE_ADVISE FROM PUBLIC; REVOKE EXECUTE ON DBMS_CUBE_ADVISE_SEC FROM PUBLIC; REVOKE EXECUTE ON DBMS_CUBE_EXP FROM PUBLIC; REVOKE EXECUTE ON DBMS_CUBE_LOG FROM PUBLIC; REVOKE EXECUTE ON DBMS_CUBE_UTIL FROM PUBLIC; REVOKE EXECUTE ON DBMS_DATAPUMP FROM PUBLIC; REVOKE EXECUTE ON DBMS_DATA_MINING FROM PUBLIC; REVOKE EXECUTE ON DBMS_DATA_MINING_TRANSFORM FROM PUBLIC; REVOKE EXECUTE ON DBMS_DB_VERSION FROM PUBLIC; REVOKE EXECUTE ON DBMS_DDL FROM PUBLIC; REVOKE EXECUTE ON DBMS_DEBUG FROM PUBLIC; REVOKE EXECUTE ON DBMS_DEBUG_JDWP FROM PUBLIC; REVOKE EXECUTE ON DBMS_DEBUG_JDWP_CUSTOM FROM PUBLIC; REVOKE EXECUTE ON DBMS_DESCRIBE FROM PUBLIC; REVOKE EXECUTE ON DBMS_DIMENSION FROM PUBLIC; REVOKE EXECUTE ON DBMS_DM_MODEL_EXP FROM PUBLIC; REVOKE EXECUTE ON DBMS_DM_MODEL_IMP FROM PUBLIC; REVOKE EXECUTE ON DBMS_EDITIONS_UTILITIES FROM PUBLIC; REVOKE EXECUTE ON DBMS_EPG FROM PUBLIC; REVOKE EXECUTE ON DBMS_ERRLOG FROM PUBLIC; REVOKE EXECUTE ON DBMS_EXPORT_EXTENSION FROM PUBLIC; REVOKE EXECUTE ON DBMS_FBT FROM PUBLIC; REVOKE EXECUTE ON DBMS_FILE_GROUP_EXP FROM PUBLIC; REVOKE EXECUTE ON DBMS_FILE_GROUP_IMP FROM PUBLIC; REVOKE EXECUTE ON DBMS_FREQUENT_ITEMSET FROM PUBLIC; REVOKE EXECUTE ON DBMS_GOLDENGATE_EXP FROM PUBLIC; REVOKE EXECUTE ON DBMS_GOLDENGATE_IMP FROM PUBLIC; REVOKE EXECUTE ON DBMS_GSM_NOPRIV FROM PUBLIC; REVOKE EXECUTE ON DBMS_HEAT_MAP FROM PUBLIC; REVOKE EXECUTE ON DBMS_HIERARCHY FROM PUBLIC; REVOKE EXECUTE ON DBMS_HS_PARALLEL FROM PUBLIC; REVOKE EXECUTE ON DBMS_ILM FROM PUBLIC; REVOKE EXECUTE ON DBMS_INDEX_UTL FROM PUBLIC; REVOKE EXECUTE ON DBMS_INMEMORY FROM PUBLIC; REVOKE EXECUTE ON DBMS_ITRIGGER_UTL FROM PUBLIC; REVOKE EXECUTE ON DBMS_JAVA FROM PUBLIC; REVOKE EXECUTE ON DBMS_JAVASCRIPT FROM PUBLIC; REVOKE EXECUTE ON DBMS_JOB FROM PUBLIC; REVOKE EXECUTE ON DBMS_JSON FROM PUBLIC; REVOKE EXECUTE ON DBMS_LCR FROM PUBLIC; REVOKE EXECUTE ON DBMS_LDAP FROM PUBLIC; REVOKE EXECUTE ON DBMS_LDAP_UTL FROM PUBLIC; REVOKE EXECUTE ON DBMS_LOB FROM PUBLIC; REVOKE EXECUTE ON DBMS_LOBUTIL FROM PUBLIC; REVOKE EXECUTE ON DBMS_LOGREP_EXP FROM PUBLIC; REVOKE EXECUTE ON DBMS_LOGREP_IMP FROM PUBLIC; REVOKE EXECUTE ON DBMS_LOGSTDBY_CONTEXT FROM PUBLIC; REVOKE EXECUTE ON DBMS_MACOLS_SESSION FROM PUBLIC; REVOKE EXECUTE ON DBMS_MACSEC_ROLES FROM PUBLIC; REVOKE EXECUTE ON DBMS_MDX_ODBO FROM PUBLIC; REVOKE EXECUTE ON DBMS_METADATA FROM PUBLIC; REVOKE EXECUTE ON DBMS_METADATA_DIFF FROM PUBLIC; REVOKE EXECUTE ON DBMS_MVIEW_STATS FROM PUBLIC; REVOKE EXECUTE ON DBMS_NETWORK_ACL_UTILITY FROM PUBLIC; REVOKE EXECUTE ON DBMS_OBJECTS_UTILS FROM PUBLIC; REVOKE EXECUTE ON DBMS_ODCI FROM PUBLIC; REVOKE EXECUTE ON DBMS_OUTPUT FROM PUBLIC; REVOKE EXECUTE ON DBMS_PARALLEL_EXECUTE FROM PUBLIC; REVOKE EXECUTE ON DBMS_PART FROM PUBLIC; REVOKE EXECUTE ON DBMS_PCLXUTIL FROM PUBLIC; REVOKE EXECUTE ON DBMS_PICKLER FROM PUBLIC; REVOKE EXECUTE ON DBMS_PLSQL_CODE_COVERAGE FROM PUBLIC; REVOKE EXECUTE ON DBMS_PREDICTIVE_ANALYTICS FROM PUBLIC; REVOKE EXECUTE ON DBMS_PREPROCESSOR FROM PUBLIC; REVOKE EXECUTE ON DBMS_PROFILER FROM PUBLIC; REVOKE EXECUTE ON DBMS_PSP FROM PUBLIC; REVOKE EXECUTE ON DBMS_RANDOM FROM PUBLIC; REVOKE EXECUTE ON DBMS_REFRESH FROM PUBLIC; REVOKE EXECUTE ON DBMS_REPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_RESCONFIG FROM PUBLIC; REVOKE EXECUTE ON DBMS_RESOURCE_MANAGER FROM PUBLIC; REVOKE EXECUTE ON DBMS_RESOURCE_MANAGER_PRIVS FROM PUBLIC; REVOKE EXECUTE ON DBMS_RESULT_CACHE_API FROM PUBLIC; REVOKE EXECUTE ON DBMS_RMGR_GROUP_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_RMGR_PACT_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_RMGR_PLAN_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_RMIN FROM PUBLIC; REVOKE EXECUTE ON DBMS_ROWID FROM PUBLIC; REVOKE EXECUTE ON DBMS_RULE FROM PUBLIC; REVOKE EXECUTE ON DBMS_RULEADM_INTERNAL FROM PUBLIC; REVOKE EXECUTE ON DBMS_RULE_ADM FROM PUBLIC; REVOKE EXECUTE ON DBMS_RULE_EXP_EV_CTXS FROM PUBLIC; REVOKE EXECUTE ON DBMS_RULE_EXP_RULES FROM PUBLIC; REVOKE EXECUTE ON DBMS_RULE_EXP_RULE_SETS FROM PUBLIC; REVOKE EXECUTE ON DBMS_RULE_EXP_UTLI FROM PUBLIC; REVOKE EXECUTE ON DBMS_RULE_IMP_OBJ FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHEDULER FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHED_ATTRIBUTE_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHED_CHAIN_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHED_CLASS_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHED_CONSTRAINT_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHED_CREDENTIAL_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHED_EXPORT_CALLOUTS FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHED_FILE_WATCHER_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHED_JOB_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHED_PROGRAM_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHED_SCHEDULE_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHED_WINDOW_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCHED_WINGRP_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SCN FROM PUBLIC; REVOKE EXECUTE ON DBMS_SESSION FROM PUBLIC; REVOKE EXECUTE ON DBMS_SNAPSHOT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SNAPSHOT_UTL FROM PUBLIC; REVOKE EXECUTE ON DBMS_SODA_DOM FROM PUBLIC; REVOKE EXECUTE ON DBMS_SPACE FROM PUBLIC; REVOKE EXECUTE ON DBMS_SPD FROM PUBLIC; REVOKE EXECUTE ON DBMS_SPM FROM PUBLIC; REVOKE EXECUTE ON DBMS_SQL FROM PUBLIC; REVOKE EXECUTE ON DBMS_SQLDIAG FROM PUBLIC; REVOKE EXECUTE ON DBMS_SQLPA FROM PUBLIC; REVOKE EXECUTE ON DBMS_SQLTUNE FROM PUBLIC; REVOKE EXECUTE ON DBMS_SQLTUNE_UTIL2 FROM PUBLIC; REVOKE EXECUTE ON DBMS_SQL_MONITOR FROM PUBLIC; REVOKE EXECUTE ON DBMS_SQL_TRANSLATOR FROM PUBLIC; REVOKE EXECUTE ON DBMS_SQL_TRANSLATOR_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_STANDARD FROM PUBLIC; REVOKE EXECUTE ON DBMS_STATS FROM PUBLIC; REVOKE EXECUTE ON DBMS_STATS_ADVISOR FROM PUBLIC; REVOKE EXECUTE ON DBMS_STAT_FUNCS FROM PUBLIC; REVOKE EXECUTE ON DBMS_STAT_FUNCS_AUX FROM PUBLIC; REVOKE EXECUTE ON DBMS_STREAMS FROM PUBLIC; REVOKE EXECUTE ON DBMS_STREAMS_PUB_RPC FROM PUBLIC; REVOKE EXECUTE ON DBMS_SUMMARY FROM PUBLIC; REVOKE EXECUTE ON DBMS_SUM_RWEQ_EXPORT FROM PUBLIC; REVOKE EXECUTE ON DBMS_SYNC_REFRESH FROM PUBLIC; REVOKE EXECUTE ON DBMS_TF FROM PUBLIC; REVOKE EXECUTE ON DBMS_TRACE FROM PUBLIC; REVOKE EXECUTE ON DBMS_TRANSACTION FROM PUBLIC; REVOKE EXECUTE ON DBMS_TRANSFORM_EXIMP FROM PUBLIC; REVOKE EXECUTE ON DBMS_TYPES FROM PUBLIC; REVOKE EXECUTE ON DBMS_UTILITY FROM PUBLIC; REVOKE EXECUTE ON DBMS_WARNING FROM PUBLIC; REVOKE EXECUTE ON DBMS_XA FROM PUBLIC; REVOKE EXECUTE ON DBMS_XDB FROM PUBLIC; REVOKE EXECUTE ON DBMS_XDBNFS FROM PUBLIC; REVOKE EXECUTE ON DBMS_XDBRESOURCE FROM PUBLIC; REVOKE EXECUTE ON DBMS_XDBUTIL_INT FROM PUBLIC; REVOKE EXECUTE ON DBMS_XDBZ FROM PUBLIC; REVOKE EXECUTE ON DBMS_XDBZ0 FROM PUBLIC; REVOKE EXECUTE ON DBMS_XDB_CONFIG FROM PUBLIC; REVOKE EXECUTE ON DBMS_XDB_CONSTANTS FROM PUBLIC; REVOKE EXECUTE ON DBMS_XDB_CONTENT FROM PUBLIC; REVOKE EXECUTE ON DBMS_XDB_PRINT FROM PUBLIC; REVOKE EXECUTE ON DBMS_XDB_REPOS FROM PUBLIC; REVOKE EXECUTE ON DBMS_XDB_VERSION FROM PUBLIC; REVOKE EXECUTE ON DBMS_XEVENT FROM PUBLIC; REVOKE EXECUTE ON DBMS_XLSB FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLDOM FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLGEN FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLINDEX FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLINDEX0 FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLPARSER FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLQUERY FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLSAVE FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLSCHEMA FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLSCHEMA_ANNOTATE FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLSCHEMA_INT FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLSCHEMA_LSB FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLSTORAGE_MANAGE FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLSTORE FROM PUBLIC; REVOKE EXECUTE ON DBMS_XMLTRANSLATIONS FROM PUBLIC; REVOKE EXECUTE ON DBMS_XPLAN FROM PUBLIC; REVOKE EXECUTE ON DBMS_XQUERY FROM PUBLIC; REVOKE EXECUTE ON DBMS_XQUERYINT FROM PUBLIC; REVOKE EXECUTE ON DBMS_XSLPROCESSOR FROM PUBLIC; REVOKE EXECUTE ON DBMS_XS_SESSIONS FROM PUBLIC; REVOKE EXECUTE ON DBMS_ZHELP_IR FROM PUBLIC; REVOKE EXECUTE ON UTL_CALL_STACK FROM PUBLIC; REVOKE EXECUTE ON UTL_COLL FROM PUBLIC; REVOKE EXECUTE ON UTL_COMPRESS FROM PUBLIC; REVOKE EXECUTE ON UTL_ENCODE FROM PUBLIC; REVOKE EXECUTE ON UTL_FILE FROM PUBLIC; REVOKE EXECUTE ON UTL_GDK FROM PUBLIC; REVOKE EXECUTE ON UTL_HTTP FROM PUBLIC; REVOKE EXECUTE ON UTL_I18N FROM PUBLIC; REVOKE EXECUTE ON UTL_IDENT FROM PUBLIC; REVOKE EXECUTE ON UTL_INADDR FROM PUBLIC; REVOKE EXECUTE ON UTL_LMS FROM PUBLIC; REVOKE EXECUTE ON UTL_MATCH FROM PUBLIC; REVOKE EXECUTE ON UTL_NLA FROM PUBLIC; REVOKE EXECUTE ON UTL_RAW FROM PUBLIC; REVOKE EXECUTE ON UTL_REF FROM PUBLIC; REVOKE EXECUTE ON UTL_SMTP FROM PUBLIC; REVOKE EXECUTE ON UTL_TCP FROM PUBLIC; REVOKE EXECUTE ON UTL_URL FROM PUBLIC; set pagesize 100 SELECT 'REVOKE SELECT ON ' || table_name || ' FROM PUBLIC;' AS RUN_SCRIPT FROM dba_tab_privs WHERE grantee = 'PUBLIC' AND table_name LIKE 'ALL%' ORDER BY 1; REVOKE SELECT ON ALL_ALL_TABLES FROM PUBLIC; REVOKE SELECT ON ALL_DB_LINKS FROM PUBLIC; REVOKE SELECT ON ALL_EDITIONING_VIEWS_AE FROM PUBLIC; REVOKE SELECT ON ALL_ENCRYPTED_COLUMNS FROM PUBLIC; REVOKE SELECT ON ALL_JAVA_ARGUMENTS FROM PUBLIC; REVOKE SELECT ON ALL_OBJECTS FROM PUBLIC; REVOKE SELECT ON ALL_OBJECTS_AE FROM PUBLIC; REVOKE SELECT ON ALL_OPERATORS FROM PUBLIC; REVOKE SELECT ON ALL_OPERATOR_COMMENTS FROM PUBLIC; REVOKE SELECT ON ALL_PROCEDURES FROM PUBLIC; REVOKE SELECT ON ALL_SOURCE FROM PUBLIC; REVOKE SELECT ON ALL_SOURCE_AE FROM PUBLIC; REVOKE SELECT ON ALL_TABLES FROM PUBLIC; REVOKE SELECT ON ALL_TAB_COLS FROM PUBLIC; REVOKE SELECT ON ALL_TRIGGERS FROM PUBLIC;

-- far more revokes of privs from public should be performed but this short list is a good starting point

Alter init params ALTER SYSTEM SET control_file_record_keep_time=32 CONTAINER=CURRENT SID='*' SCOPE=BOTH; ALTER SYSTEM SET global_names=TRUE CONTAINER=CURRENT SID='*' SCOPE=BOTH; ALTER SYSTEM SET sec_protocol_error_trace_action=log CONTAINER=CURRENT SID='*' SCOPE=BOTH; ALTER SYSTEM SET undo_retention=43200 CONTAINER=ALL SID='*' SCOPE=BOTH; ALTER SYSTEM SET filesystemio_options=SETALL CONTAINER=ALL SID='*' SCOPE=SPFILE; ALTER SYSTEM SET remote_login_passwordfile=NONE CONTAINER=ALL SID='*' SCOPE=SPFILE; ALTER SYSTEM SET sec_max_failed_login_attempts=3 CONTAINER=ALL SID='*' SCOPE=SPFILE; ALTER SYSTEM SET sec_protocol_error_further_action=1 CONTAINER=ALL SID='*' SCOPE=SPFILE; ALTER SYSTEM SET use_large_pages = TRUE CONTAINER=ALL SID='*' SCOPE=SPFILE;
-- restart the database so these changes take effect immediately
-- the sec_protocol_error_trace_action alter needs to performed separately for each container

Multiplex Redo Logs ALTER DATABASE ADD LOGFILE MEMBER '/u01/orabase19/fast_recovery_area/orabasexix/redo01b.LOG' TO GROUP 1; ALTER DATABASE ADD LOGFILE MEMBER '/u01/orabase19/fast_recovery_area/orabasexix/redo02b.LOG' TO GROUP 2; ALTER DATABASE ADD LOGFILE MEMBER '/u01/orabase19/fast_recovery_area/orabasexix/redo03b.LOG' TO GROUP 3; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; SELECT group#, status FROM v$log; SELECT group#, status FROM v$logfile;

Enhance AWR SELECT file_name, autoextensible FROM dba_data_files WHERE UPPER(file_name) LIKE '%SYSAUX%'; -- if the result is not 'YES' then make the SYSAUX tablespace autoextendable ALTER DATABASE DATAFILE 'uo1/apps18/oradata/orabase/sysaux01.DBF' AUTOEXTEND ON; -- get the database's dbid SELECT dbid FROM v$database; SELECT retention, snap_interval, topnsql FROM wrm$_wr_control WHERE dbid = (SELECT dbid FROM v$database); -- set retention to 30 days, interval to 20 minutes, and topnsql to 50,000 exec dbms_workload_repository.modify_snapshot_settings((24*60*31), 20, 2000000000); col retention format a20 col snap_interval format a20 SELECT retention, snap_interval, topnsql FROM wrm$_wr_control WHERE dbid = (SELECT dbid FROM v$database);

Collect Stats -- collect system stats
SELECT pname, pval1 FROM aux_stats$ WHERE sname = 'SYSSTATS_MAIN'; exec dbms_stats.gather_system_stats('INTERVAL', 15); -- after 15 minutes of CRUD load SELECT pname, pval1 FROM aux_stats$ WHERE sname = 'SYSSTATS_MAIN'; -- fixed object stats exec dbms_stats.gather_fixed_objects_stats; -- gather dictionary stats exec dbms_stats.gather_dictionary_stats;

Secure the file system (perform actions as root)

Location	Command
cd $ORACLE_BASE/	chmod -R 600 audit
cd $ORACLE_BASE/diag/rdbms/orabase/orabase	chmod -R 700 trace
cd $ORACLE_HOME/network/admin	chmod 644 listener.ora sqlnet.ora
cd $ORACLE_HOME/bin	chmod 750 sqlplus	SQL*Plus command line utility
cd $ORACLE_HOME/bin	chmod 750 exp imp	Database export and import
cd $ORACLE_HOME/bin	chmod 750 expdp impdp	Datapump export and import
cd $ORACLE_HOME/bin	chmod 750 orapwd dg4pwd	Password file generators
cd $ORACLE_HOME/bin	chmod 750 sqlldr	SQL*Loader
cd $ORACLE_HOME/bin	chmod 750 dbca dbua netca netmgr lsnrctl
cd $ORACLE_HOME/bin	chmod 750 kfed kfod
cd $ORACLE_HOME/bin	chmod 750 oraenv	Oracle environment control
cd $ORACLE_HOME/bin	chmod 750 owm	Oracle Wallet Manager
cd $ORACLE_HOME/bin	chmod 750 wrap	PL/SQL code wrap utility

Secure the Listener -- add the following lines to the listener.ora file at $ORACLE_HOME/network/admin ADMIN_RESTRICTIONS_LISTENER_NAME = ON LOGGING_LISTENER = ON SECURE_CONTROL_LISTENER = (TCPS, IPC) SECURE_PROTOCOL_LISTENER = (TCPS, IPC) SECURE_REGISTER_LISTENER = (TCP, IPC) TRACE_DIRECTORY_LISTENER = /app/oracle/product/12.2.0/db_1/network/trace TRACE_FILE_LISTENER = listener.trc -- add the following lines to the sqlnet.ora file at $ORACLE_HOME/network/admin ALLOW_LOGON_VERSION = 18 TRACE_DIRECTORY_CLIENT = ORACLE_BASE/diag/clients/user_oracle TRACE_DIRECTORY_SERVER = ORACLE_BASE/diag/tnslsnr

Secure the Database -- prevent access from remotely created db links later, when running db link demos, you will need to allow specific server connections exec dbms_distributed_trust_admin.deny_all;

Set Stats Collection Preferences exec dbms_stats.set_database_prefs('CASCADE', 'DBMS_STATS.AUTO_CASCADE'); exec dbms_stats.set_database_prefs('DEGREE','2');

Create Profiles conn sys@pdbdev as sysdba CREATE PROFILE orabase_dba LIMIT COMPOSITE_LIMIT UNLIMITED SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED PRIVATE_SGA UNLIMITED IDLE_TIME 45 CONNECT_TIME 600 FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LIFE_TIME 60 PASSWORD_REUSE_TIME 9999 PASSWORD_LOCK_TIME 1 PASSWORD_GRACE_TIME 7 INACTIVE_ACCOUNT_TIME 30 PASSWORD_REUSE_MAX 1 PASSWORD_VERIFY_FUNCTION NULL;

Create Non-Installation Roles conn sys@pdbdev as sysdba -- role for autotrace SQL> @?/sqlplus/admin/plustrce.sql SQL> SQL> drop role plustrace; drop role plustrace * ERROR at line 1: ORA-01919: role 'PLUSTRACE' does not exist SQL> create role plustrace; Role created. SQL> grant select on v_$sesstat to plustrace; Grant succeeded. SQL> grant select on v_$statname to plustrace; Grant succeeded. SQL> grant select on v_$mystat to plustrace; Grant succeeded. SQL> grant plustrace to dba with admin option; Grant succeeded. SQL> set echo off -- role for tkprof SQL> @?/rdbms/admin/utltkprf.sql SQL> drop roles tkprofer; drop roles tkprofer * ERROR at line 1: ORA-01919: role 'TKPROFER' does not exist SQL> create role tkprofer; Role created. SQL> Rem SQL> Rem Dynamic views that TKPROF needs to dereference wait events. SQL> grant select on v_$datafile to tkprofer; Grant succeeded. SQL> grant select on v_$latchname to tkprofer; Grant succeeded. SQL> grant select on v_$log to tkprofer; Grant succeeded. SQL> grant select on v_$logfile to tkprofer; Grant succeeded. SQL> grant select on v_$thread to tkprofer; Grant succeeded. SQL> Rem SQL> Rem View extent_to_object is defined in catio.sql SQL> grant select on extent_to_object to tkprofer; grant select on extent_to_object to tkprofer * ERROR at line 1: ORA-00942: table or view does not exist SQL> Rem SQL> Rem let's grant this role to dba with admin option SQL> grant tkprofer to dba with admin option; Grant succeeded. SQL> Rem SQL> set echo off; GRANT plustrace TO uwclass; GRANT tkprofer TO uwclass;

As UWCLASS

Create Class Schema Objects conn uwclass/uwclass@pdbdev

Create the airplanes tables: Click Here

-- save this file as
c:\temp\airplanes.sql

Create the servers and serv_inst tables: Click Here

-- save this file as
c:\temp\servers.sql

Create the postal_code table: Click Here

-- save this file as
c:\temp\postal_code.sql

-- logged on as uwclass create the airplanes table
SQL> @c:\temp\airplanes.sql

-- logged on as uwclass create the servers and serv_inst tables
SQL> @c:\temp\servers.sql

-- logged on as uwclass create the postal_code table
SQL> @c:\temp\postal.sql

Create Additional Demo Users

Create Class Schema Objects conn sys@pdbdev as sysdba CREATE TABLESPACE "EXAMPLE" DATAFILE 'C:\APPS18\ORADATA\ORABASE18\PDBDEV\EXAMPLE01.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO; ALTER DATABASE DATAFILE 'C:\APPS18\ORADATA\ORABASE18\PDBDEV\EXAMPLE01.DBF' RESIZE 1421475840 CREATE USER child_a IDENTIFIED BY child_a DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE tempgrp QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON uwdata PROFILE orabase_dba ACCOUNT UNLOCK ENABLE EDITIONS CONTAINER=CURRENT; GRANT create session, create table, ??? TO sh; CREATE USER child_b IDENTIFIED BY child_b DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE tempgrp QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON uwdata PROFILE orabase_dba ACCOUNT UNLOCK ENABLE EDITIONS CONTAINER=CURRENT; GRANT create session, create table, ??? TO sh; CREATE USER finapp IDENTIFIED BY "N0Access!" DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE tempgrp QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON uwdata PROFILE orabase_dba ACCOUNT UNLOCK ENABLE EDITIONS CONTAINER=CURRENT; GRANT create session, create table, ??? TO sh; CREATE USER mlib IDENTIFIED BY "N0Access!" DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE tempgrp QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON uwdata PROFILE orabase_dba ACCOUNT UNLOCK ENABLE EDITIONS CONTAINER=CURRENT; GRANT create session, create table, ??? TO sh; CREATE USER noaa IDENTIFIED BY noaa DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE tempgrp QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON uwdata PROFILE orabase_dba ACCOUNT UNLOCK ENABLE EDITIONS CONTAINER=CURRENT; GRANT create session, create table, ??? TO sh; CREATE USER oe IDENTIFIED BY oe DEFAULT TABLESPACE example TEMPORARY TABLESPACE tempgrp QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON example QUOTA UNLIMITED ON users PROFILE orabase_dba ACCOUNT UNLOCK ENABLE EDITIONS CONTAINER=CURRENT; GRANT create session, create procedure, create sequence TO oe; GRANT create table, create trigger, create type, create view TO oe; CREATE USER pm IDENTIFIED BY pm DEFAULT TABLESPACE example TEMPORARY TABLESPACE tempgrp QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON example QUOTA UNLIMITED ON users PROFILE orabase_dba ACCOUNT UNLOCK ENABLE EDITIONS CONTAINER=CURRENT; GRANT create session, create table, create view TO pm; CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE tempgrp QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON uwdata PROFILE orabase_dba ACCOUNT UNLOCK ENABLE EDITIONS CONTAINER=CURRENT; GRANT create session, create table TO scott; CREATE USER sh IDENTIFIED BY sh DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE tempgrp QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON uwdata PROFILE orabase_dba ACCOUNT UNLOCK ENABLE EDITIONS CONTAINER=CURRENT; GRANT create session, create table, ??? TO sh;