Oracle Database Security - Expert Oracle and Java Security - page 23

Audit Trail Logs for the Sensitive View
Let's find the audit log entries for this access. HR user does not have access to read the audit logs (if he
tries, he will be audited), but if you will connect as secadm and execute the following commands, you will
see the audit logs that were generated by HR querying v_employees_public :
EXEC sys.p_check_secadm_access;
SELECT OBJECT_NAME, STATEMENT_TYPE, RETURNCODE FROM DBA_COMMON_AUDIT_TRAIL
WHERE DB_USER='HR'
ORDER BY EXTENDED_TIMESTAMP DESC;
While we are still here as secadm , we should try to access the sensitive view and see the audit log
entries for this failed attempt ( secadm cannot read the sensitive view). You will also see logs of the audit
trail SELECT statements executed by secadm .
SELECT OBJECT_NAME, STATEMENT_TYPE, RETURNCODE FROM DBA_COMMON_AUDIT_TRAIL
WHERE DB_USER='SECADM'
ORDER BY EXTENDED_TIMESTAMP DESC;
A RETURNCODE of 0 is a success, while a non-zero RETURNCODE indicates a failure.
Regarding Synonyms
Synonyms are like extra names for structures in Oracle, primarily for tables and views. The most
persuasive argument for synonyms is the use of public synonyms. If we create a public synonym for a
view, then anyone selecting from that view need not prefix the schema name on the view name. HR user
can do the following to create a public synonym. (Do not do this—this example is for discussion only.)
CREATE PUBLIC SYNONYM v_employees_public FOR hr.v_employees_public;
This does not change the security or accessibility of the view or data. However, it permits the role
hrview_role to select the data without the hr. schema name prefix, as in:
SELECT * FROM v_employees_public;
It is also possible to give a name to the synonym that is different from the name of the structure it
represents, like the following:
CREATE PUBLIC SYNONYM employees FOR hr.v_employees_public;
Perhaps this can simplify the name—and if we expected our users to type all their queries at the
command prompt, this might be helpful. (Do not do this—this example is for discussion only.) You can
imagine there might also be some confusion for the successor database administrator coming along
behind the DBA who set this up. An employee might complain that they cannot select from EMPLOYEES ,
and the successor administrator might reply that no one can, not knowing that the employee was talking
about the synonym, not the table or view. Also, if ever the view hr.v_employees_public is dropped
(deleted), the synonym will be broken.
An individual user might create a private synonym for her own use (use by only that specific
user/schema). She might do this so she can call HR.EMPLOYEES by her favorite term, “PEERS.” However,
when she tries to share any database queries or code with her associates, they will be frustrated by her
private synonyms. Queries using her private synonyms won't work for others.
I have also observed attempts to incorporate synonyms into a security strategy, hiding the original
table with a public or private synonym by the same name. It would be foolish to trust in such a ruse.