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)
  1. After opening SQL*Plus in a terminal window right click on the window title: Select PROPERTIES
  2. On the Options tab page make sure the edit options "QuickEdit Mode" and "Insert Mode" are both selected
  3. On the Layout tab page set the Screen Buffer Size width to at least 161 and the height to at least 1000
  4. 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;

Related Topics
Built-in Functions
Built-in Packages
Container Database
SQL*Plus
Tables
Tablespaces
Users
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx