Last week, I attended a free ODTUG webinar put on by Scott Spendolini (from Sumneva) on setting APEX up securely. There was a lot of good information, and if you didn’t get a chance to go (or didn’t know about it in the first place), you should definitely watch it now.

One of Scott’s first points struck me as being very true, and yet often overlooked: security is hard. Perhaps I’m more sensitive to this than most, as I’m working on developing better security protocols for my employer, but everyone seems to expect secure environments to not require much work, and that’s just not the way it is. Of course, if you’re just now getting APEX set up, you’re going to have an easier time than if you’ve got a multitude of applications built, simply because you don’t have to retroactively fix a large codebase (and we all know how much fun that particular task is, don’t we?)

To sum up Scott’s recommendations: the parsing schema for APEX should have as little access as possible. Revoke all system privileges on the user (you can see what’s set in the SQL Workshop->Utilities->Object Reports->System Privileges) and create a database trigger to prevent drops, since drop doesn’t require a privilege, for whatever reason. Then create another “shadow” schema with read-only views of your data, and create views in your parsing schema of these shadow views; this prevents anyone who gains access to your schema from learning anything by examining the view code, while allowing you to use APEX’s wizards, many of which require that you work on a table or view, rather than a synonym. To handle data manipulation, create an API of procedures in the shadow schema and grant execute to the parsing schema.

One really cool feature Scott showed was that APEX provides functionality to automatically generate these data manipulation APIs, including the ability to recognize when two users try to update the same row at once. In the Object Browser, click “Create”, then “Package”; in the wizard that opens, you can select to create a specification, package body, or a “Package with methods on database table(s)”. This third option is where the magic is, and nothing on the page (even in the field help) indicates that there’s anything special about it. You can pick up to ten tables to generate an API for at once (which may or may not be useful), and for each table you get three procedures–one each for insert, update, and delete. No muss, no fuss, and you can edit them to add any extra security checks you want, such as making sure that APP_USER has permission to do what they’re trying to do.

Again, Scott’s presentation was extremely informative, and highly recommended for anyone building applications, especially any that are exposed to the internet (intranet apps get a little more leeway for sloppiness, generally, though the wisdom of that is open for debate…) Thanks, Scott, for taking the time to put it together and share it with us!