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...
)
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. |