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!
There’s a mostly undocumented substitution string present in all of the standard APEX themes. If you look at the page template definitions (expect for the login templates, of course), you’ll see something like the following:
This displays as a simple little “Welcome, &APP_USER.” Very basic, but still useful–it’s nice to let people know they’re logged in (and who they’re logged in as, especially on public computers!) But there are problems.
First, you can’t configure it. The username displays in all caps, even if the user logged in using lowercase letters for their name. Second, it doesn’t play nice on public pages (such as this blog)–if the user’s not logged in, you get “Welcome,”, which is neither attractive nor useful. Third, it doesn’t substitute if you place it after #BOX_BODY#, which is a problem if you want to put it in a sidebar on the right of the page.
The third issue can be resolved by using div-based templates, putting the sidebar before the body, and floating it to the right with CSS (which this page does). But what about the other two?
Since you can’t change APP_USER (ok, you might be able to, but I can’t imagine it being a good idea), the best solution I’ve found is to create an application level item, replacing #WELCOME_USER# with somethign like “Welcome, &APP_ITEM.”, and creating an application computation to populate it. The nice thing about application computations is that they happen on every page (you can also set them to run per session if they’re intensive, but this one isn’t). Here’s the computation I use:
select decode(:APP_USER,'nobody','Guest',initcap(:APP_USER)) from dual;
It’s a small thing, but I think it adds a nice touch.
OK, it took me a little bit longer to get this together than I’d originally hoped, but this blog is finally live. It’s all hand-built using Oracle’s Application Express (APEX), and so it’s not quite as fully featured as most other blogs out there. I am going to be adding more features as we go (comments, for instance!), but at least now I have something I can show the world.
Rambling aside, let me talk a bit about the purpose of this blog. I’ve recently started using APEX at work, and decided that I wanted to get my own instance where I could poke into all of the musty corners, find out what its limitations are and how to get around them, and share what I learn along the way. (And if I happen to build something useful in the process, so much the better!) So here I am. I’ll be posting regularly of what I’ve found; if there’s a topic you’d like me to explore, or you just want to say “Hi!”, feel free to send me an e-mail–there’s a contact link at the top of the page.