Oracle Permission Generator
Oracle Permission Generator

Tutorial Part 1 - Overview

In order to introduce the capabilities and features of Oracle Permission Generator, this tutorial will work through setting up a small sample database schema, and explain how to use Oracle Permission Generator to manage privileges and synonyms for this schema.


The Sample Schema
The sample schema is a data model which supports an order management application for an e-commerce website called 'XYZ Online'. The ER diagram below depicts the tables and views in the schema...

Schema Version 1


Tables and Views
Details of the tables and views in the schema are described below...

Name Type Description
CUSTOMERS Table Stores details of all the customers of XYZ Online
ITEMS Table The items which XYZ Online sells
ORDERS Table Orders processed by the order management application
APPLICATION_STATS Table Stores statistics generated by the order management application
VW_CUSTOMERS View A de-normalised view of the data in table CUSTOMERS
VW_ITEMS View A de-normalised view of the data in table ITEMS
VW_ORDERS View A de-normalised view of the data in table ORDERS


Database Objects
The schema includes the stored procedures described below...

Name Type Description
SP_CUSTOMERS_INS Stored Procedure Handles inserting data into the CUSTOMERS table
SP_CUSTOMERS_UPD Stored Procedure Handles updating data in the CUSTOMERS table
SP_ITEMS_INS Stored Procedure Handles inserting data into the ITEMS table
SP_ITEMS_UPD Stored Procedure Handles updating data in the ITEMS table
SP_ORDERS_INS Stored Procedure Handles inserting data into the ORDERS table
SP_ORDERS_UPD Stored Procedure Handles updating data in the ORDERS table


Roles
The table below describes the roles created in the schema...

Name Description
XZYON_APP_ROLE Used by the order management application to log into the database. Has access to all stored procedures and views, to manipulate and read from the database. Also has read and write access to the APPLICATION_STATS table to log statistics.
XZYON_READ_ROLE Used by users and applications which create reports from the database. Has read only access to all tables and views.
XZYON_POWER_ROLE Used by database administrators to perform administrative operations on the database. Is able to read from any table or view, and execute any stored procedure, but does not allow direct writing to tables.


Users
The table below describes the additional user accounts (Oracle schemas) created...

User Role Description
XZYON_APP_USER XZYON_APP_ROLE The user associated with the 'APP' role.
XZYON_POWER_USER XZYON_POWER_ROLE The user associated with the 'POWER' role.
SMITH_JOHN XZYON_READ_ROLE A user who will connect directly to the database to run ad-hoc SQL reports.
JONES_SAM XZYON_READ_ROLE A user who will connect directly to the database to run ad-hoc SQL reports.

Continue to Part 2...