Back

Oracle APEX Security Tutorial

Learn how to harden your Oracle APEX applications! Prevent cross site scripting, SQL injection and more.

Oracle Application Express (APEX) has many security related features that help developers create applications that are guarded from today’s web based threats. But if developers are unaware of these features, how they work and what they guard against, then it is likely they will create applications with at least a few security holes. In this tutorial you will see demos of certain exploits, including Cross Site Scripting and SQL injection, and subsequently learn how to protect against them using the correct feature(s) in APEX or Oracle in general.

Want more? April 10 INTERMEDIATE APEX Training with Expert Tyson Jouglet!

This free training is segmented into several separate lessons:

  1. Overview (1:21)
  2. Is APEX Secure (1:13)
  3. Controlling Access (0:57)
  4. Authentication Schemes (3:08)
  5. Conditions vs Authorization (12:45) (click on video below)
  6. Protect the Ends (12:45)
  7. Propagate the WHERE (3:47)
  8. Session State Protection (8:44)
  9. Other Session State Protection (3:24)
  10. SQL Injection (5:16)
  11. Cross Site Scripting (9:05)

Date: Sep 27, 2012

Want more? April 10 INTERMEDIATE APEX Training with Expert Tyson Jouglet!

NOTE: Some corporate firewalls will not allow videos hosted by YouTube.

Transcript

Conditions vs Authorization

Securing Oracle APEX – Conditions vs. Authorization

 

>> Dan:  If you’re starting to wonder, “Wait a second. I may not be using auth schemes but I am using conditions. What’s the difference between the two?” 

 

It’s subtle. In general, it’s recommended that you use conditions for things in general such as, everyone’s aware when you go to a forum and you’re in a create mode rather than edit, the “Delete” and “Apply Changes” buttons are not going to show and that’s because of general conditions, more business logic than anything else. So conditions are good in those cases. 

 

Authorization schemes should be used for anything related to security. So if you need to hide a certain button because a user’s not an admin for example, that’s when you’re going to leverage the auth schemes over conditions. 

 

[pause]

 

Okay, let’s go ahead and jump in. We’re going to start with a requirement here. 

 

[pause]

 

Only members of the admin and base user groups should be able to access the application. 

 

[pause]

 

Alright, I’m going to get logged in. I have a workspace here called innocent_developer. The developer is ILOVETODEV. 

 

[pause]

 

We get logged in. The first thing I want to show you under administration under manage users and groups – in addition to the developer here that I’m using I have three end user accounts, ILOVETOADMIN, ILOVETOSELL, and ILOVETOHACK. 

 

User accounts can be created a variety of ways and just because a user has an account doesn’t necessarily mean they should gain access to an application. But if we look right now 

 

[pause]

 

at the sample database application you’ll see here in the shared components that it is in fact locked down with an authentication scheme. It’s using application express credentials. It could be pointed at an [02:04 inaudible], it could be anything. Just because user has an account doesn’t mean they should be able to get in. 

 

[pause]

 

So when I run this app ILOVETOADMIN, it gets in. No problem. Unfortunately, 

 

[pause]

 

ILOVETOHACK can get in just as easily. The requirement is that only members of the admin and base user groups should be able to access this app. So how do we lock it down? 

 

[pause]

 

We’re going to go under administration, back to manage users and groups. We’re going to into groups. We’re going to create a group here. We’ll call this base user. Once we’ve created the group we can then go back to users and assign users to it. 

 

Obviously, ILOVETOADMIN should be a member of this group. We’ll scroll down here. Here’s our base user, slide that over, and apply. 

 

[pause]

 

Here we have ILOVETOSELL also supposed to be in here. We’ll slide that over and apply. 

 

Now, ILOVETOHACK we are not going to put in this group. So now that we have a couple of users in the right group, let’s go ahead and lock down the app. 

 

[pause]

 

I’ll go into the sample database app, back to the shared components. Our authentication’s working fine. Some folks do go the route of adding a little bit of authorization with their authentication and then the error message they get looks a little bit different, almost as though an invalid username or password are entered. But we’re going to lock down the entire app with an authorization scheme which is separate. 

 

[pause]

 

We’ll create the scheme, we’ll do it from scratch, and we’ll call this base user as well. 

 

You have a number of different types you can select from. My favorite is right here, PL/SQL function returning Boolean and the reason is it’s the most flexible you can get. Lots of options here. 

 

[pause]

 

We go over to my cheat sheet. You don’t have to watch me type some code. I’m going to paste this in here. You have to imagine you’re in the body of a function here and you have to return out a Boolean. So I’m running two checks. I guess they haven’t even created the admin group yet. Let’s remove that for now. 

 

What we’re doing is just using APEX util, current user in group, and we’re passing in that group name to base user. This is one of the ways you can check for group membership using the APEX user repository, but more often than not you’re not going to be using the APEX user repository. You’ll be using a different system. But this will demo what we need to demo. 

 

[pause]

 

This is the error message that will be displayed if this scheme is violated. So you’re not an approved user of this app. 

 

One other thing I’ll point out is that the authorization schemes do allow for caching. So if you’re using a different system like an LDAP server, you can go ahead and cache the check on a per session basis. Only downside to that is to realize any changes, user would have to log out log back in, whereas if you use once per page view then it’s more immediate, but of course this performance had incurred. 

 

[pause]

 

We’ll go ahead and create the scheme. I’m going to show you the uppermost level at which these can be leveraged. We’ll go back to shared components. This time I’m going into security attributes. 

 

[pause]

 

This is sort of a hodgepodge of security attributes for the end of the app. I’m going to come down to authorization and we’re going to select base user. This is the highest level. We’re saying, it doesn’t matter if you have an account. It doesn’t matter if you can pass authentication. You must be authorized to get into this app. So we apply that change. 

 

[pause]

 

We run this again and right out of the box you can see that it’s not going to let this user anymore. ILOVETOHACK is none approved user. We can start off with a new session. 

 

[pause]

 

We can start off with ILOVETOADMIN. 

 

[pause]

 

ILOVETOADMIN gets in. No problem. 

 

[pause]

 

ILOVETOHACK is denied access. This is the simplest way you can keep certain users out of your application and at these really high levels you don’t have to worry so much about a lot of the more granular things that happen when you start to share pages. 

 

Definitely nice when you can secure things at that level. Really easy, right? That requirement was very easy. They make it a little bit harder as we go here. 

 

[pause]

 

Let’s take a look at the next. Only admins can access reports and only admins can delete orders. Okay. 

 

[pause]

 

>> Moderator:  Pardon me Dan, question in the queue.

 

>> Dan:  Sure.

 

[pause]

 

>> Moderator:  We have a student who’s planning to switch from APEX users to LDAP. How would they use roots for security?

 

>> Dan:  Really any LDAP server would attack the directory OID or some of the open source ones. Any of them will have groups. So there is group objects and there are user objects and you can create them as needed and then assign users to the group objects. 

 

[pause]

 

Of course, it does require a bit of communication oftentimes especially in larger companies or organizations. There’s sort of a division of duties there. You’re going to have to collaborate with some of those folks to get groups created for you and then users assigned to them. But this sort of hand off is actually I think a good thing when security matters. It also helps with user provisioning as things can become more consistent across the enterprise. You don’t have different systems or sources of truth for who your users are and that kind of stuff.

 

[pause]

 

>> Moderator:  Great, thank you.

 

>> Dan:  Sure. Once again, only admins can access reports and only admins can delete orders. 

 

[pause]

 

Thus far, we have a base user group but we need something more specific. 

 

[pause]

 

So we’re going to create a new group called the admin. 

 

[pause]

 

We’ll assign just the admin to this new group. 

 

[pause]

 

We’ll go back to the builder now and lock some things down. 

 

[pause]

 

One more authorization scheme is needed. We’re going to map to our new group. 

 

[pause]

 

We’ll call it admin to keep it easy. Use PL/SQL again, paste in our – check this time on that admin group. 

 

[pause]

 

And we’ll go ahead and create that scheme. Now, at this point my base user we could modify this as it was before. You could really check just in case an admin isn’t in the base user group. Of course, an admin is still an admin so you could modify this one a bit to let either or in. 

 

[pause]

 

We’re all set, let’s go ahead and secure the app. 

 

[pause]

 

We’ll run this and create ourselves a new session, this time coming in as the admin. 

 

[pause]

 

What we’re locking down access to are the reports and then the other requirement is only admins can delete orders. Let’s start with reports. Obviously, the way that users get to reports is by clicking the tab. 

 

[pause]

 

If you look inside of APEX, you’ll find that these can be locked down. APEX gets really granular with this. We’re going to start with reports, we’ll go into that. Using this authorization dropdown, I’m going to lock that down to admin. 

 

[pause]

 

We run the page. This user still sees reports. Under orders I drill down on an order. There’s this “Delete” button here. Obviously, you want to lock that down as well. 

 

[pause]

 

And you’ll find the same kind of dropdown here under security. I’ll lock that down to admin. 

 

[pause]

 

We run the page currently logged in as admin and everything is still available. Perfect. 

 

We know ILOVETOHACK can’t get in but ILOVETOSELL is not an admin, it’s just a base user but does have access to the app. So ILOVETOSELL gets logged in, but what they don’t see is the link or the tab rather to orders – I’m sorry, the reports. When they drill down in orders, 

 

[pause]

 

pick one up, they don’t see the “Delete” button. Easy enough, right? That was easy or was it?

 

Copyright SkillBuilders.com 2017

×
Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!

 

×