Transcript

Overview

1. Securing Oracle APEX – Overview

 

>> Dan:  What are we up to today? Well, over the last six years I’ve had the opportunity to learn a lot about web development and some of the security vulnerabilities that come along with web development. They’re not really APEX-specific although some of the things that we’ll see today are related specifically to APEX, some of the vulnerabilities are not. 

 

I’ve learned from some of the best, Anton, Tyler, Patrick, Nathan, Tim and I’m really excited today to be able to share with you some of what I’ve learned over that time. 

 

[pause]

 

It’s important however to keep in mind that we only have an hour today. In fact, even less now. So today we’ll not be what I would consider a complete security overview for APEX because that would be impossible to do in an hour. What we’re going to do is focus on some of the bigger things, some of the things that all developers should be aware of and should be using in their daily development practices. 

 

[pause]

 

We’re going to take a look first the controlling access both in the application and the data access and then later we’re going to look at how we can mitigate some of the threats we’re going to see in any web-based environment. 

Copyright SkillBuilders.com 2017

×
Transcript

Is APEX Secure

2. Securing Oracle APEX – Is APEX Secure

 

>>> Dan:  By now you’re probably thinking to yourself, is APEX even secure? Should we be using it? 

 

And absolutely the answer is yes. APEX itself is rigorously tested at Oracle. They have a very stringent process there. It’s not a young product anymore. It’s quite mature. It’s been on for a while, so holes have been fixed, and they continually hardened the applications that we create by default which is nice and you’ll get a feel for that today. 

 

[pause]

 

But it’s still possible for developers to make unsecure applications and this is usually going to happen if you’re just unaware of the threats that are out there and the features that you can leverage to combat those issues. So that’s what we’ll be looking at. 

 

[pause]

 

APEX offers all kinds of security features out of the box. Some of these features you may find in other products, others you may not. So it’s really nice to have some of these very declared easy options to leverage including authentication and authorization schemes, session state protection, and lots of others security attributes. I’ll just share just a few of those toward the end today.

 

Copyright SkillBuilders.com 2017

×
Transcript

Controlling Access

3. Securing Oracle APEX – Controlling Access

 

>> Dan:  Let’s talk a little bit more about controlling access. I break this up into two parts – the application itself as well as the data access from within. 

 

[pause]

 

Now, when it comes to the application you’re looking at things like, who can access the app? Who can access your functionality within it? Who can access a given component whether it’s a region, a page, an item, a button, whatever? 

 

That’s application level for me. And for that you’re going to use authentication and authorization schemes within APEX. I’ve actually seen developers recreate these features because they simply didn’t look in the shared components where they would’ve found them. 

 

[pause]

 

Then the other half of the equation is data and this has to do with what rows a given user can see, and for that you simply use WHERE clauses most of the time.

 

 

Copyright SkillBuilders.com 2017

×
Transcript

Authentication Schemes

4. Securing Oracle APEX – Authentication Schemes

 

>> Dan:  We’re going to start by looking at authentication schemes. These are your means to identify end users. 

 

Typically, in web based apps this is going to be done via some sort of username and password challenge although it’s gotten a little bit more complicated these days. You have some folks going the route of a single sign on servers. You have other folks going the route of no password authentication. So might get an email and click a link to log in to an app. Lots of different ways. From security cards to retinal scans, you can ID users a variety of ways. 

 

APEX comes with a number of preconfigured schemes available ready to go out of the box. Some of the more common ones include LDAP. So if you have active directory server you can integrate with that. There’s an HTTP header function or scheme which was added in 4.1, so if you have a custom single sign on solution that adds a HTTP header variable then you can leverage this very easily now. You might have had written a custom all scheme in the past, that’s no longer the case. 

 

[pause]

 

And then of course, there’s custom. Custom is as flexible as you can get and there’s really nothing you can’t do with that one. 

 

[pause]

 

One thing I do want to find out is that there’s an open door scheme which not a lot of folks know about, but if you want to test your applications as though you are other users such as the president of your organization, you can leverage the open door credentials very easily. It only requires then a username and we’ll pass you right on through without a password. Of course, you only want to use that in depth. 

 

[pause]

 

The next thing we’re going to look at are authorization schemes. The authorization schemes are how we lock down who can do what within an application. Typically, this is going to be done via group membership of some sort. 

 

Today I’m going to demo group membership in the APEX environment, but most often you’re going to leverage something like active directory, OID, some other sort of LDAP server. To create your users, authenticate via username and password, and then control authorization via creating groups and assigning users to those groups. 

 

[pause]

 

Of course, you can only do authorization after you’ve done authentication. You need to know who somebody is before you can determine what they can or cannot do within a system. 

 

[pause]

 

The authorization schemes in APEX are really flexible. You can configure them however you like. Typically, they’re going to be role-based, so you’re going to create one authorization scheme for each role that will be in your system. But they can also be based on functionality such as delete user. You may create an auth scheme called delete user and filter your roles up into that scheme. 

 

[pause]

 

You’ll use these to lock down all the necessary components including pages and [02:58 inaudible] starting with the app level all the way down to the most granular components in APEX.

 

Copyright SkillBuilders.com 2017

×
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

×
Transcript

Protect the Ends

6. Securing Oracle APEX – Protect the Ends

 

>> Dan:  What I recommend when it comes to locking things down with authorization schemes in APEX is that you protect the ends before you protect the means. 

 

[pause]

 

When you’re looking at things like tabs, buttons, really anything with a link in it in APEX that’s just a means to an end. it’s a means of providing navigation. The end itself is what you need to lock down. Things like pages, processes – these are the ends. Lock down the ends first then lock down the means to get to the ends. 

 

So how would we implement this in APEX? 

 

[pause]

 

We’ll start with the easy one. 

 

[pause]

 

When we look at the report – this is the reports main page here and then we have several others after that. You literally have to come in here. Let me just show you real quick – reports is page 26 so there’s no link to it but you can see right now I’m in as ILOVETOSELL and the user has no problem getting to this page. I show even though they don’t see a link to it here they can come up here in the URL, just change this one to 26, hit enter and it get to it, no problem. 

 

So what we really needed to lock down was not the tab or the means to the page but literally the page itself. I’m coming into the page of attributes. I’m going to focus on security and we’re going to apply the authorization scheme at this level here, admin. 

 

[pause]

 

So now if they try the same hack – go back to page one and run the app. 

 

[pause]

 

They don’t see the tab but they try manipulating the URL. 

 

[pause]

 

They’re denied access again. You’ve seen this message now in two places. The message that we’re putting inside of the authorization scheme – this message is displayed in two places. Number one, at the app level. Number two, at the page level. Anything more granular than that you can lock things down. However, it simply prevents them from either rendering or being executed. The error message will no longer be displayed when you get more granular. 

 

Of course, I’ve only locked down one of the reports pages. We then have to go through the rest, 27, 28, and so on. I’ll lock those down as well then the user cannot get to reports. 

 

[pause]

 

The second part was deleting and this unfortunately gets a little bit more complex. 

 

[pause]

 

We go into orders and we take a look at one. 

 

[pause]

 

We don’t see delete. We just see this “Apply Changes” button. 

 

[pause]

 

I’m going to drill into it here and show you here’s the button element and it says on click APEX.submit save. What if we change “Save” to “Delete” and hit “Apply Changes”? 

 

[pause]

 

We’re looking at order 10 Eugene Bradley, action processed, no more order 10. Ah, we need to protect the end itself. Let’s go back to page. 

 

[pause]

 

We’ll drill into the app. What deletes a row? Is it a button press or is it a process? You said process. You guessed right. We go into this process. This is the process that runs on submit. 

 

[pause]

 

We’ll scroll down a bit and you’ll see that it does all three – insert, update, and delete operations. What you have to do to secure this, it’s a little tedious but you need to copy it. You’re going to have to break it up into two. 

 

[pause]

 

We’ll get them right next to each other and we’ll reconfigure them accordingly. This first one I’m going to rename “Insert and Update” and I’m going to remove the delete ability from it. I’m going to make sure that it only fires when a certain button is pressed. I’d like to say, both – there’s quite a few buttons here. I will go with save or create. To handle multiple conditions we’re going to use a PL/SQL expression and refer to request. 

 

[pause]

 

So if save or create is the request guide and one of these two can fire, it knows which mappings to do using these here. 

 

[pause]

 

I don’t need to lock this one down with authorization schemes. The next one I do – here’s the one that does the delete. We’ll uncheck the “Insert and Update” and we’ll leave the condition set to the “Delete” button but here I’m going to apply the security must be an admin. 

 

[pause]

 

Now we have these processes setup a little bit better than before. Let’s try that same hack and see if we can get through. We’re looking at order 9, we come into the button, we change “Save” to “Delete.” 

 

[pause]

 

Notice you do not see a processed success message. Also you still see order number 9. This is important. You must protect the means to get to an end but you must protect the end as well. What I would do is protect the ends first then protect the links that get you there. 

 

[pause]

 

Let’s look at some more requirements. Only admins can edit orders they didn’t place and only admin should see the sales rep column. Okay, sounds easy. 

 

It looks like the way this app is set up by default. All the orders were placed by generic user called DEMO. We’re using different users here, ILOVETOHACK – I’m sorry, ILOVETOADMIN and ILOVETOSELL. ILOVETOHACK can’t get in this app. So let’s go ahead and set this up a little bit differently. 

 

I need to go to the SQL Workshop. We’re going to run a couple of updates. 

 

[pause]

 

You can see this here we’re updating DEMO orders, setting the username to ILOVETOADMIN for order IDs 2, 3, 8, and 9. Just some random IDs and then we’ll do the same, ILOVETOSELL gets everything else essentially. 

 

[pause]

 

Now when we look at the orders, we can see that some were put in by the admin, others were put in by a standard user. What we need to do, only admins can edit orders they didn’t place and only admin should be able to see this particular column. 

 

Let’s deal with the column first. This one’s easy enough. 

 

[pause]

 

We’ll go into a report. Here’s the sales rep. Focus on the authorization. We’ll lock this down to admin. As you can see, these are everywhere. So you can lock down even the most granular components in APEX. 

 

We’ll rerun the page. Excellent. Now the user cannot see that column. But this is where you move over into the next stage where we actually have to protect the data that’s coming through the app and the way that’s typically done is with the WHERE clause. 

 

[pause]

 

>> Moderator:  Pardon me, Dan.

 

>> Dan:  Yes.

 

>> Moderator:  A comment, if you will, in the queue. The comment is the user could also run the OnClick JavaScript in the address bar. I’m not sure I’ve got the pronunciation correct but something about Java script in the address bar. Is that a vulnerability I would say?

 

>> Dan:  Well, I suppose it got to be.

 

>> Moderator:  [09:28 inaudible]

 

>> Dan:  Yeah. You’d have to get yourself into the submission phase to get to where the processes were executed, but there are ways to do that as well if you know enough about APEX and URLs, so there are certainly some vulnerabilities there in URL. Absolutely.

 

[pause]

 

>> Moderator:  Thank you.

 

>> Dan:  Sure. Alright. What we’re going to do is add on the WHERE clause here. 

 

[pause]

 

Let’s say WHERE app_user = “ILOVETOADMIN” – and this is where we need to filter the person and place of the order, order_name. 

 

[pause]

 

Like that. Okay, looks good I think. We’ll apply the change on the page. Perfect. We’re seeing five rows of data, 7, 6, 5, 4. 

 

What we’re not seeing is what we removed or assigned to all of the admin, which was 2, 3, 8, and 9. Easy enough. Perfect. Or is it? 

 

[pause]

 

Let’s go back to the app and take a look at maybe something else a user might notice while using an APEX app. So if go to “Edit this order,” this is one of the ones that ILOVETOSELL is supposed to be able to see. We click on the “Edit” link and we notice up here in the upper right-hand corner – actually, I might have enabled this earlier or it’s enabled by default – what I wanted to show you in fact – let me set something up first. 

 

[pause]

 

We go back to orders. We’re still only seeing what we’re supposed to be seeing. But when we click this URL, a user can easily say some things in this URL such as P29 order ID, P11 branch and if they figure it out what they’re note is that this 7 represents the order ID that they’re supposed to be viewing. 

 

One of the ones they’re not supposed to be viewing, one of the ones that was done by the admin is 2. But if the user manipulates the URL and hits enter then order 2 comes up no problem. 

 

[pause]

 

This is a big, big issue. It’s something known as a URL tampering and you have two basic ways you can protect against it.

 

Copyright SkillBuilders.com 2017

×
Transcript

Propagate the WHERE

7. Securing Oracle APEX – Propagate the WHERE

 

>> Dan:  The first is to propagate your WHERE clause. A WHERE clause that I added to that report page was not enough. As you saw, all that was doing was creating links and links are means to the end, not the end itself. 

 

[pause]

 

What we’re actually going to is a page to view that particular order and work with it. What we would need to do is propagate that same WHERE clause over that page or everywhere else in the apps for that matter. One of the ways we can do that in APEX is using a runtime WHERE clause. This is actually really tedious but it’s far safer than not using it. 

 

Another way is to use VPD. VPD unfortunately requires the Enterprise edition of the database. So if you have that edition there’s some more work involved in getting this set up but it’s definitely a step in the right direction. 

 

[pause]

 

Let’s take a look at the runtime WHERE clause. Unfortunately, we don’t have enough time to demo the Enterprise edition feature of the VPD but we’ll take a look at a runtime WHERE clause. 

 

[pause]

 

I’m going to go back to orders. 

 

[pause]

 

We’ll drill in here. We’ll take a look at that predicate that I added here. This one. I limited the rows which you’ll be able to see. 

 

Now what you have to do is bring that same predicate everywhere else you need this level of protection. When we drill down on an order, we go over to page 29, we’ll edit this. And here’s the fetch row process. I’m going to go in and focus on where it says WHERE clause and we paste it in here. You don’t actually put the WHERE in it. It’s already here. 

 

So app user =. And just like we had before, we’ll apply that change and now equally, if not more important, we need to do the same thing to the actual processes that do the processing. 

 

[pause]

 

That’s one, two, three different places we needed to propagate this WHERE clause. Not only now it is in four places, it must be maintained in four places. You can see what I mean by tedious, right? 

 

Also, there’s a chance you may forget to do this, which of course would leave you vulnerable. 

 

[pause]

 

Let’s give this a shot now. We start with 7. 

 

[pause]

 

This one brought back no data. 

 

[pause]

 

The user should be able to see order 7. 

 

[pause]

 

We have to modify this a little bit to pick up on a username appropriately. I’d say I couldn’t implement this here unfortunately. Let me try just removing the O. 

 

[pause]

 

Okay. There we go. Alright. Sorry about that. 

 

Now we’re able to view 7. But watch what happens if we come and manipulate the URL this time. If I try to say view 2 again. 

 

[pause]

 

It doesn’t work. No data found. That’s propagating the WHERE clause. I’m choosing that runtime WHERE clause and that’s why that exists.

 

Copyright SkillBuilders.com 2017

×
Transcript

Session State Protection

8. Securing Oracle APEX – Session State Protection

 

>> Dan:  But there’s another means to which you can protect the URL in APEX and it’s called session state protection, built for this very reason. It prevents editing of the item and value portions of the URL which are position 7 and 8 in APEX URL. 

 

[pause]

 

My recommendation when you start to use this feature is to leverage the default. But keep in mind, it must be maintained going forward. What I mean by that is when you enable this feature for your application, you do it in bulk. 

 

Let’s say you have 100% with 1,000 items. When you enable this feature, pretty much everything will be locked down. Tomorrow you create a new page, add some more items, guess what? They’re not protected. So you have to maintain going forward. 

 

[pause]

 

There is a means to create URLs that leverage this checksum ability programmatically and that’s with APEX util prepare URL. So keep that in mind, if you need to access that, you have a way to do so. 

 

[pause]

 

>> Moderator:  Dan, I’ve got a bunch of questions in the queue and I’ve been a little tardy on getting them to you, so some of them may be a little out of context.

 

>> Dan:  No worries, by all means.

 

>> Moderator:  First off, does APEX help with input validation like character whitelist?

 

>> Dan:  I believe there is a feature for this coming. I’m not too sure. Yeah. There’s a new feature coming in 4.2. Not quite available yet, but we’ll have something very soon. 

 

[pause]

 

>> Moderator:  Excellent. This is a little long, so bear with me on this. I’ll try to read this to you quickly. Where do you put ILOVETOADMIN in the WHERE clause? Could you put the admin group instead?

 

>> Dan:  Yes. You couldn’t obviously work with Booleans inside of a SQL query so you’d have to maybe make a RATHER function that returns some sort of a flag that you can work with in the SQL world. But absolutely, in fact, it would be much better for you to target it at that level. 

 

Of course, I can’t stress this enough, really the best way to do this is to use VPD. Using VPD you can setup some policy functions which are centralized at the database tier so you don’t have to propagate the WHERE clauses. You take all this WHERE clauses, you push them into the database and then nobody can get around them. No developer can forget to add them and really no one can get around them. So leverage VPD and you don’t have to worry about it at all. But absolutely you could target by groups, certainly.

 

>> Moderator:  Right. Back to the report column for the security. I’ve seen the column in the report. Are there any issues with using the security in the report column attributes and set that column security there instead of in the WHERE clause?

 

>> Dan:  There were two requirements we were implementing. One was to filter the number of rows that the end user saw. That needs to be done via the WHERE clause. 

 

The second requirement was to lock down that column. Now, you can do some things with VPD that limit data coming through at the column level but I’m not a big fan of that. I would just recommend using the feature here to lock down the column in the app using APEX.

 

[pause]

 

>> Moderator:  Great. Can or could you change the app user ID to admin in the address bar?

 

[pause]

 

>> Dan:  No. That’s going to be a protected item so app user you don’t have to worry about. You just have to worry about the items that you create essentially.

 

[pause]

 

>> Moderator:  Okay. I’m going to turn it back over to you, Dan. We do have some more but we are short on time, so back over to you. Thank you very much. 

 

>> Dan:  No problem. Where were we? 

 

[pause]

 

I think we’ve just finished locking down the app. Oh no, sorry. We’re digging into session state protection, doing a demo on that. 

 

[pause]

 

This is another way to protect URLs in APEX. This actually is what I have to disable just to show you the hack first. Now we’re going to turn it back on. When you first come to session state protection, look a little weird, it says “Disabled.” You go to set protection and of course you want to enable it. 

 

[pause]

 

Just because you enable it does not mean you’re protected. You have to configure things and the way that you do this in bulk that I was describing before, you come here to set protection and you use the configure option on the right. 

 

[pause]

 

Now you see all these settings. There are two levels where you have page level and then item level. When you look at the page, the default is arguments must have checksum. This is secure. This is a lot better than unsecure, but it’s not the most secure. 

 

The next level is no arguments allowed which means you cannot even pass parameters in 7 and 8, we’re going to a particular page and here you have a no URL access so you can’t even get to the page using a URL. This level is the most secure. It’s actually so secure I’ve never had a need to use it. At that point to even get to a page one would have to use a particular type of branch which of course we can control programmatically. 

 

[pause]

 

Again, leave the default most of the time and adjust as necessary. At the item level you see unrestricted of course, the least secure. The default here is checksum required session level but less secure is application level. What this means is that the checksum that would be created would be one that is only at the app level. Meaning I could take a URL with the checksum and I could email it to Dave and Dave could put it in his browser and it would work just fine. 

 

More secure would be user level. What gets included with that checksum is the actual user. So if I try to share that link with Dave, it wouldn’t work. But I could save it as a bookmark in my app and it would work just fine to me tomorrow. 

 

[pause]

 

More secure is session level. That checksum that’s generated is only going to be good for the duration of one session. That’s the default and I recommend you leave it there. 

 

Of course, there is restricted. This is important to use in certain items such as maybe a user ID which could be hidden. Make sure you lock those down entirely. 

 

[pause]

 

We’ll leave the defaults, enable it for the app, and when we run it now – let’s go back to orders. When I hover over the link and you look at the bottom of my browser, you’ll see the URL. To the right of the branch is 7,4 and now you see &cs=, that’s checksum equals and then 32 numbers and letters that are the checksum essentially locking down what we’re able to do here. Using this checksum if I then try to manipulate the 7 to a 2 and hit “Enter,” we get something completely different. Now we get an error. This is something that you could even write code that cache and then maybe shoot you an email. 

 

[pause]

 

That’s URL protection or protecting position 7 and 8 in the URL. It’s important to remember though that that’s just position 7 and 8. It does not protect me from going to certain pages. If you want to protect certain pages, you do need to use what we already saw before which is the admin schemes. 

 

[pause]

 

If you don’t want somebody going to certain reports – even with this enabled, they’re still able to get to certain pages. For that you need to lock down the page using an authorization scheme.

 

Copyright SkillBuilders.com 2017

×
Transcript

Other Session State Protection

9. Securing Oracle APEX – Other Session State Protection

 

>> Dan:  What else do you need to worry about when it comes to session state protection? 

 

One of the most important things are service side validations which prevent of course DOM manipulation. You may give somebody a particular type of control like a select list with a subset of all available options such as departments. You may say, “Well, department 10 is no longer being used.” We’ll use a predicate in the WHERE clause in your item and then they can’t select department 10. 

 

[pause]

 

But using tools like you’ve seen me use today like Firebug – and every browser has them now, you can manipulate the DOM and insert values that weren’t there when the page loaded. Your protection against that is to use validations to validate what you need. 

 

[pause]

 

There are also hidden items and these can be made protected. There’s this protected option with hidden items. A lot of folks don’t quite understand what it means. It doesn’t work like session state protection does. It works a little bit differently and that when the hidden item renders on the page, the value cannot be changed from the time the page loads to the time that it’s submitted. If it is you’ll get another error. So the user won’t see it but even if they did find it, a hacker type was to find it they would not be able to manipulate its value. Read-only is also protected. 

 

[pause]

 

Let’s take a look at some of these. 

 

[pause]

 

Here you have a state select list. And this is a silly example. You probably wouldn’t need to validate this but you see very easily I can see the values. If I want to change these values here, I can certainly do that. 

 

[pause]

 

So selecting Alabama would in fact map Florida to session state and push that into the database. Watch out for that user validations. 

 

Another one I’ll show you is hidden and protected. Now, this is used all over APEX already. When you go into hidden item like this customer ID, you’ll see that this is value protected. Interestingly enough this is set to no, it should be a yes. So let’s move that to a yes and we’ll run this page. 

 

[pause]

 

And what you’ll see now is – you don’t see the item but it is in fact in the page. 

 

[pause]

 

Here it is. It’s hidden. It has a value of 7. If I would’ve changed this to say 8, what would happen when I submit the page I would actually be pushing all of the data we see here into a completely different user, so to prevent this use hidden and protected. Now I’ve changed the 7 to an 8 and because we said it’s protected, when I hit “Apply Changes” here we get an error. That’s what hidden and protected is. Make sure you’re using that appropriately for the right items.

 

Copyright SkillBuilders.com 2017

×
Transcript

SQL Injection

10. Securing Oracle APEX – SQL Injection

 

>> Dan:  Let’s move on to some of the other vulnerabilities that you’re going to have to deal with as a developer, starting with SQL injection. We have two main classes of vulnerabilities with SQL injection. The first being use of substitution strings, you also have to watch out with dynamic SQL statements which hopefully would be rare. A lot of developers get in the habit of using them perhaps more than they should be or more than they need to. You should almost never have to use this really. 

 

[pause]

 

The first thing you need to watch out for is using substitution strings in concatenation. You have to learn the proper way to use bind variables or the V function. 

 

[pause]

 

The other thing you need to watch out for, if you have to execute dynamic SQL whether using DMS SQL or execute immediate, you need to sanitize using DBMS_ASSERT. Don’t trust the user input and of course make you’re using bind variables appropriately. 

 

[pause]

 

Let’s take a look at an SQL injection demo. This is one simple demo. 

 

[pause]

 

Create a new session here. Log in. 

 

[pause]

 

We’re going to go this customer report and to most folks this looks pretty standard. We type in a search string, and we get our results. The problem is in the way that this has been implemented. If we look inside this customer report, the developer chose to use a SQL query which is actually based on a PL/SQL function body returning a SQL query. He chose to build up the SQL query. Here’s a variable being declared and it said equal (=) to, and you can see what it’s doing, selecting some columns from a table where the last name equal (=). This is the wrong way to use bind variable syntax in your query. Ultimately we’re formulating a string and returning that string back out. 

 

[pause]

 

So what can happen when somebody starts to play with this? 

 

[pause]

 

They may decide to put in user input you didn’t expect. What I’ve just put in is test and I’ve ended a quote (“) here which causes a problem. 

 

Then really we could even without putting anything after that, just something funny like this, you hit “Go” and you get this error. And this is when a hacker’s eyes are going to light up and they’re going to say, wait a second. Let’s try something different. This time we’re going to union a select statement selecting from dull, see if we can get the syntax right. 

 

[pause]

 

Now we’re able to select from dull. Could we not work around maybe some of the security that’s been implemented here? 

 

[pause]

 

This one is supposed to just show us the customers that we search or appear. 

 

[pause]

 

What about all the customers? Now, the weird thing is that you can actually take this further rather than just getting customer-related data. You can learn more about the APEX workspace. 

 

[pause]

 

What I’ve put in now is actually a query going into one of the APEX dictionary views. We’re selecting information from APEX application page items. We hit go now. 

 

[pause]

 

That was too restrictive. 

 

Now I’m able to start finding items in an application. I can find out which items are hidden, which are protected, which are not. I can start to learn about your application to find the exploits. Once someone gets their foot in the door with a SQL injection vulnerability, it gets a lot easier for them to start hacking the rest of your app. So this is definitely something you have to watch out for.

 

[pause]

 

The way the bind variable should’ve brought in – should have been brought in, in this case – is like this. 

 

[pause]

 

The string’s being built up and the bind variables inside the string so when the query is executed, it’s inside. We apply that change, run it and try the same hack. Nothing comes back. So you have to learn how to use bind variables, the V function, execute immediate, dynamic SQL, all this stuff, you have to learn how to use it correctly. 

 

Copyright SkillBuilders.com 2017

 

×
Transcript

Cross Site Scripting

11. Securing Oracle APEX – Cross Site Scripting XSS

 

>> Dan:  Let’s look at another vulnerability. This one’s called cross-site scripting. 

 

[pause]

 

When we create forms for end users we expect them to use them as we design them, but they’re not tied to that. They don’t have to. You have to watch out for what can happen when they use it a different way. 

 

[pause]

 

If they put certain tags into fields where the data is the output, later the browser will actually just look at that as though it were a native HTML code for the webpage. So if they put in a script tag, guess what, the browser will execute it. 

 

[pause]

 

APEX provides us with some tools to work around this. One of the neat things is that as APEX has become more and more hardened over the years is that they’re now on by default. You actually have to undo them to shoot yourself on the foot. And that’s just what our developer ILOVETODEV has done here. 

 

[pause]

 

This is by far the hardest demo, so fingers crossed, I get this one right. 

 

[pause]

 

What I’m going to show you is a session hijacking demo. ILOVETODEV innocently enough created this app. 

 

[pause]

 

It’s called the shout out app. The intent is just to let everybody sort of share their thoughts and be able to comment in a shared location. ILOVETODEV spent some time locking this app down so that only certain people could do certain things. He took the URL when he was done with it. 

 

[pause]

 

And I’m coming now in IE. 

 

[pause]

 

He gave this to ILOVETOADMIN. So ILOVETOADMIN goes to log in. 

 

[pause]

 

Now we see create. So ILOVETOADMIN can come over here and create a message and ILOVETOADMIN has this nice HTML editor so he can come in here and make things look really weird if he wants and hit “Create.” 

 

Oops we got a date there. 

 

[pause]

 

And then there’s the message for all the world to see because the app does not require authentication. 

 

ILOVETOHACK finds out about this app, he can see the page, he can see that there is a vulnerability. So ILOVETOHACK goes and creates their own workspace, HACKER, and gets logged in. 

 

[pause]

 

ILOVETOHACK then creates an application and inside the application in the shared components places an application level process innocently named GET REPORT DETAILS. What it does is not so innocent. What it’s doing with some variables declared, it’s getting cookie values from the request. It’s then looping through them building up a string and inserting both some cookie data as well as the URL. It’s passed to it into a table called hacked sessions. 

 

But how is this going to work in ILOVETODEV’s application? Well, now ILOVETOHACK gets the URL to the application as well. 

 

[pause]

 

ILOVETOHACK is in this browser, punches it in, gets logged in. Because this is open to the public, you just have to have an account to be able to post. And then goes to post a new comment. But what ILOVETOHACK post is not so innocent. 

 

[pause]

 

To do it he goes to the source and pastes something in. Let’s take a look at this. 

 

[pause]

 

I have to show you another way. Anyway, I’m going to go ahead and post this in here. We’ll just go through it really slowly I guess. 

 

You can see in the beginning some regular HTML but then come a script and what happens here is some AJAX. This is an AJAX call out and it’s going to the app that ILOVETOHACK created and it’s calling that app level process passing along some information and at the very end another nice easy message. 

 

[pause] 

 

He creates this message and it looks innocently enough on the surface. But what happens here with ILOVETOADMIN, just a simple refresh of the page and they fall into the trap. Because they see this message, what ILOVETOHACK needed has already happened. 

 

So if we go back to ILOVETOHACK, get him logged in, and go to the SQL workshop. Let’s do object browser actually. 

 

[pause]

 

Here’s a table called hacked sessions. So there’s one row in it right now. We go back to IE, refresh, go back to Chrome, now we have two. 

 

What do I have in here? I have this cookie data. It doesn’t really mean much to me but ILOVETOHACK, because he’s a good hacker, has a little script ready. Grabs this value, grabs this value. 

 

[pause]

 

And brings the script in into a browser that they’re like to work with. I’m going to use IE here – I’m sorry, Firefox here. I’m going to open up Firebug and I’ll show you the script. 

 

[pause]

 

Here’s the console, paste it in here. ILOVETOHACK then takes the cookie values they got as well as the URL that was given to them and they can run a script like this. It’s basically splitting up the string into a number of cookies then calling the set cookie function for each and every one it finds basically recreating the functions that were over here in IE inside now of Firefox. 

 

So we run this script, it executes and gives us this URL. We know this is the URL we’re trying to use up here. 

 

[pause]

 

And that failed for me. 

 

[pause]

 

I must have made a mistake. Let’s try it one more time. As I mentioned this is the tough one that could go wrong. 

 

[pause]

 

In Firefox using – this is not working for me. I seemed to be missing something in the cookie information. But you’ll have to take my word it. I tested this yesterday, it worked just fine. Unfortunately, that didn’t work but I think you get the idea. 

 

Cross-site scripting is a serious problem where – I’m showing you one demo here. It can be as simple as an alert which isn’t very dangerous but is certainly annoying. It could be as dangerous as bringing in code from another site that grabs cookies to do session hijacking. 

 

Again, this is not an APEX specific issue. This is something more serious. It has to do with web development in general so you need to lock things down. Using SSL is a step in the right direction in this case. 

 

[pause]

 

Session timeouts – one last thing I’ll mention here is session timeouts. You have this functionality built-in. The default session link is 8 hours max. There’s a session idle time you can configure too. We actually built a plugin to work with this a little bit better. It works like a bank when the idle time runs out you go to lunch, you come back, and it looks like it’s still logged in to APEX and you’re not. This will modify that a little bit. It will actually log the user out and take them to the log in screen.

 

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!

 

×