Database Reference
In-Depth Information
Probably the best reason for synonyms is to make it slightly easier for programmers to write code, by
not having to remember to prefix the table and view names with those pesky schema names. However, I
have observed application failures when a table or view is migrated from a development environment
(Oracle
instance
) to production, but the public synonym is left behind. I know this should never happen,
but it does. Then the data is there in production, but the application fails, because it is missing the
synonym or merely missing the schema name in the code. That can be very difficult to troubleshoot.
I recommend that you rarely if ever use synonyms, and that you just make the developers address
the tables and views where they reside, with the schema name prefix. I don't really expect everyone to
follow my advice, but please at least consider it.
One further argument for synonyms is to be a reference to a structure on another database instance.
In this case, the synonym can also include a database link. I can't really argue against that use of
synonyms, because it is certainly easier to specify and update if the database link name changes. I do
caution that you need to protect data wherever it appears, even across database links. There should also
be restrictions on the visibility and use of production data in non-production environments; and even
more so, vice-versa.
Chapter Review
Now you have a security administrator user who is not a DBA, but who can handle most of the Oracle
security tasks required. Also, you have an application security user who will be handling the transactions
we require for Oracle application security.
You have locked down our Oracle database and added some basic auditing, and learned about the
data dictionary views. We discussed roles: predefined roles, default roles, non-default roles, and secure
application roles. In our first foray into application security, we built a couple of secure application roles
and the procedures that protect them.
In the application data schema,
HR
, we distinguished between sensitive and non-sensitive data and
created a public view of only the non-sensitive data. We also created a view of both the sensitive and
non-sensitive data, but we haven't permitted anyone to see that yet. Then we viewed the audit trail logs
for entries related to our sensitive view of
EMPLOYEES
.
