Oracle12 has implemented a couple of features that should help keep the nosey and the hackers from seeing sensitive data.
Oracle12 continues to plug holes in PL/SQL security. This blog will discuss three new PL/SQL security features:
This new feature overrides the CURRENT USER settings of a view.
CREATE or REPLACE MY_EMP_VIEW BEQUEATH CURRENT_USER as Select MY_EMP_FUNCTION;
This simple syntax tells Oracle when processing any function from within the view to use the permissions of the creator of the function rather than the logged in user. The default behavior is to use the permissions of the executing user.
Only allows certain PL/SQL routines (procedures/packages/functions) to access specific PL/SQL routines. This is a hard-coded list.
CREATE or RELACE FUNCTION MY_EMP_FUNC (v_empno number)Return varchar2ACCESSIBLE BY (MY_PKG.MY_PROC)asBegin…end;
This accessible by list will only allow the MY_PROC routine found in the MY_PKG package to execute this function. No other routine or SQL can execute this function. I believe the intent here is when working with API type programming that there are certain routines specific to the API interface perhaps that are pertinent to that interface.
…and my favorite…
Oracle12 NOW allows for specific read/write data object privileges to be applied to procedures and functions. In prior Oracle releases, the person executing the procedure or function would have to have permissions to the underlying tables being accessed.
Not any more! Now…the executing user doesn’t have to have ANY table access privileges as the procedures or functions they are executing can be granted the permissions to the underlying tables.
This plugs a huge SQL injection hole in that programs can now get access to the data they need without the logged in user having access to the underlying data structures.
GRANT SELECT on EMP to MY_FUNCTION;
The user now executing the MY_FUNCTION function does not have to have any kind of access to the EMP table. The function is coded to access what it needs without outside permissions.
Oracle12 continues to tighten down security on PL/SQL objects.
Dan HotkaOracle ACE DirectorInstructor/Author/CEO
again a great blog. I've tried the permissions section but got the error message "ora-01917: User or Function does not exist". Any idea what I did wrong?