This is a very conservative approach and requires the most maintenance but gives the most flexibility and is the most secure as it isolates the data from the SQL that manipulates it and the manipulation from external access.
Data Schema(s)
The schema owns all the tables their data but does not contain any procedures. Users never connect to these schemas because the owner of the tables has implicit rights on the table.This account"s user has the ability to manipulate the shape of the data objects that the schema contains. This is a very powerful user and the DBA should tightly lock this account down.
Since Oracle 9.2 DBA"s can manipulate these object without logging on to the account. Therefore, no-one, not even a DBA needs to logon to this account.
Procedural Schema
This is a moderately powerful account in that only this schema has the right to directly access a data schema. It cannot, however, manipulate the structure of the data schemas. That is to say it cannot alter/drop/create schema objects.This schema/user needs to be granted permission to use the data schema’s tables.
Separating the user schemas from the procedural (PL/SQL) schema helps to ensure that nobody inadvertently or maliciously changes the code.
The only effective way for your PL/SQL to act as your trusted agent is if it cannot be tampered with. This account should also be locked down by the DBA.
The Procedural Schema has the right to select from every table/sequence/view in all of our data schemas and also has the right to INSERT/UPDATE/DELETE on specific tables where there is a requirement (i.e. where the procedure contains is an insert/update/delete statement).
Static data tables, loaded with data at the creation of the database, rarely if ever change and the schema user will simply have Select access to these: again a least privileges environment.
All tables and sequences are accessed via PRIVATE synonyms created when the object is created. The first part of the synonym name is the data schema followed by the object name. For example if the table COUNTRY_CODES, owned by the Data Schema UK, is accessed within the PL/SQL code by reference to the synonym UK_COUNTRY_CODES.
Using this convention means that there is separation of data schemas, but the procedural schema can still have access to two tables of the same name owned by different Data Schemas, for example: UK_GOODS_ITEM and FR_GOODS_ITEM.
User Schemas
User Schemas are the schemas that are used by Java code and can call the PL/SQL code. The only privileges these schemas have are the right to connect to the database and the right to execute specific PL/SQL packages as determined by the authors/owners of the PL/SQL packages.Each package is accessed via a PUBLIC Synonym: there are NO public rights to execute packages.
The only public execute access is to TYPES (user defined objects) which are accessed via PUBLIC SYNONYMS and are used to pass information into and out of the Java layer to/from PL/SQL
If the data or procedural schemas are compromised serious corruption of code/data could occur. Compromises to the individual accounts help mitigate potential damage by restricting access to just the things the account can do. In security terms this compartmentalization is a fundamental tenet to security best practises as is the least privileges mantra of "give them just enough privileges to do the job, no more, no less"
Posts in this series:
