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
Create multiple tables and views and perform multiple grants in your own schema in a single transaction. If all statements execute successfully, then the database commits them as a single transaction.
If any statement results in an error, then the database rolls back all of the statements.
Does not support CREATE BLOCKCHAIN TABLE in 20c.
Data Dictionary Objects
ALL_TAB_PRIVS_MADE
CDB_TABLES
USER_TAB_PRIVS_MADE
ALL_OBJECTS
CDB_VIEWS
USER_OBJECTS
ALL_TABLES
DBA_OBJECTS
USER_TABLES
ALL_VIEWS
DBA_VIEWS
USER_VIEWS
CDB_OBJECTS
System Privileges
CREATE SESSION
CREATE TABLE
CREATE VIEW
Create Schema
Create Schema Objects
CREATE SCHEMA AUTHORIZATION <schema_name>
<create table or view or grant statement>;
conn sys@pdbdev as sysdba
CREATE USER uwclass
IDENTIFIED BY uwclass
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 10M ON uwdata;
GRANT create session TO uwclass;
GRANT create table TO uwclass;
GRANT create view TO uwclass;
conn uwclass/uwclass@pdbdev
-- first one that doesn't work (t3 does not exist)
CREATE SCHEMA AUTHORIZATION uwclass
CREATE TABLE t1
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE TABLE t2
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE VIEW t1t2_view AS
SELECT t1.tid, t2.last_name FROM t1, t3 WHERE t1.tid = t2.tid
GRANT select ON t1t2_view TO system;
-- then one that does
CREATE SCHEMA AUTHORIZATION uwclass
CREATE TABLE t1
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE TABLE t2
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE VIEW t1t2_view AS
SELECT t1.tid, t2.last_name FROM t1, t2 WHERE t1.tid = t2.tid
GRANT select ON t1t2_view TO system;