David S. Gale

Programming, Life, and what I think about it all

Trusted Clients: A Bad Idea

by 1 Comment

There’s been a flurry of news recently about how a recent video game, Ubisoft’s The Division, is easily hacked. I hadn’t paid too much attention to this, largely because it’s not a game I play, but today’s post on ArsTechnica caught my eye because of just how bad the fundamental design of the game appears to be–and how it highlights an important consideration when working on modern apps (including APEX!).

The Division is a modern multi-player game, which basically means that it’s running a straightforward Client/Server architecture. This should be easy; we’ve been doing Client/Server for a very long time now. The problem, though, is that Ubisoft seems to be using what’s called a “Trusted Client” system–the servers, which Ubisoft controls, trust the users’ clients to tell them how much ammo they have, how much health, and so on. But Ubisoft doesn’t control these clients; the players do. And so if a player figures out how to “adjust” how much ammo their client thinks they have, that gets reported to the server, accepted, and, well, you’ve got a broken game.

(The upside of trusting the client is that it improves the feeling of “responsiveness”–since the game client itself knows whether you hit your target, it can draw in the appropriate feedback immediately, rather than waiting for the server to tell it what to do. But needing to do this implies that your server isn’t capable of processing all of the data it needs to quickly enough–and thus that you’ve got other problems!)

The correct implementation, of course, is to only trust the data that the server has full control over. If the client reports that it is shooting even when, according to the server, it doesn’t have any bullets, then the server tells the client “you can’t do that” (and, potentially, adds the client to a list of potential cheaters for review).

APEX applications should be built the same. All interaction with the user (forms, dynamic actions, etc.) should use the server as the source of truth; it’s trivially easy on modern browsers to change the value of HTML items, even hidden ones. Luckily, APEX provides us with several tools to catch this sort of chicanery in the form of page/item access protection and checksums. Unfortunately, however, most of these security checks are turned off by default–you need to remember to turn them on yourself. The good guys over at Recx have a tool called ApexSec that will help you find these sorts of things. Check it out. And lock down your important apps.

APEX-Generated Table APIs

by 2 Comments

Last week, a coworker on the SQLDeveloper team linked via his Twitter account to a blog post he’d written in 2013 about using SQL Developer to generate table APIs. I mentioned that this seemed very similar to functionality in APEX, which he wasn’t familiar with; so this post is by request. First time ever!

The functionality I’m going to talk about has existed since at least APEX 4.0 (when I first started using APEX); I’m not sure when it was originally introduced. But it’s not talked about much or widely known, so you might consider this a hidden gem. [Read more…]

Packaged Application: Data Reporter

by 6 Comments

I was talking with one of my co-workers yesterday about the Data Reporter packaged application, and it occurred to me that I never actually blogged about it. Which is a real shame, as it is one of our more powerful applications!

One of the really nice features of APEX is the interactive report, which has been around since (I believe) the 3.1 release. It’s especially nice for developers, because we can build a report once, and then allow the users to modify it to meet their needs, within reason. If they want columns re-ordered, or filtered, highlighted, shown/hidden, etc., they can make those changes themselves, without waiting for a developer to make the changes, get it QA’d, and then rolled out to production. They’re very powerful. One of the downsides, however, is that it still requires a developer to build the initial report, or update the query if someone comes up with additional columns that should be included.

Data Reporter, which was originally released in APEX 4.2.3, is our solution to that. Once the application is installed, any user with contributor or administrative access can log in and create a new report, querying whatever data they need. If someone wants to create a report, but doesn’t know SQL–or isn’t very well versed in it–an administrator can create a “data source” (really, just a query) which the users can then use as the basis for their report. As you can imagine, given that we’re giving the users the ability to write SQL against the database, security was a chief concern as we built the application; one chief protection we implemented is the mandatory white-listing of objects. If your query refers to a table or view that’s not on the whitelist, you won’t be able to create it.

You can create a variety of report “types” within the application–Interactive Reports (mentioned above); Calendars; Filter Reports (I blogged about them here); and Dashboards (intended to be quick-glance summarizations of your data). Additionally, if you’re running APEX through Oracle REST Data Services–formerly known as the APEX Listener–you can harness its ability to create PDF reports on the fly.

My co-worker was looking at setting Data Reporter on an instance where we’re trying to eliminate the ability for users to log in and query data directly from the database, without also blocking them from getting the data they need. Through this packaged application, we’ll be able to lock down the database accounts, give the users the data they need in usable formats, and keep a record of how often they look at which reports. Seems like a win all around to me.

Merge

by Leave a Comment

This post is partly a reminder for myself; it’s about a part of SQL, which I tend to forget to use. But if I forget about it, then that means that others are likely to as well, right? (heh)

Anyhow, I was trying to create a process for maintaining sample data in an application–the users can install an application, muck up the sample data all they want, and then hit a “reset” button which takes everything back to the beginning. Pretty straightforward; just delete the data, and re-insert it. But the sample data is just to get people started using the apps, so we can’t just wipe the table–they may be using some of the data, or may have made changes that they want to keep, etc.

So we wanted to do something like this:

insert into table1 ( id, col1, col2 )
select 1 id, 'Sample' c1, 'Value' c2 from dual
where not exists ( select null from table1 where id = 1;

That’d work, but in my case, I was loading 10 different tables, and anywhere from 3 to 15 different rows per table. I knew that I’d end up with a different id in the data I was inserting and in the not exists test sooner or later due to excessive cut-and-paste. (Alternatively, I could wrap each insert statement in a begin/end block and catch the duplicate ID exception–but who wants to do all that?) So I started trying to make something that would be simpler to maintain.

My solution was a private procedure that took a table name and an associative array of column names and values, and then built up the above insert statement using dynamic SQL. Effective, but rather ugly. I’m not even going to bother posting it here.

One of my co-workers then suggested that I use merge. The syntax is a little more complex than the above insert statement, but I can use one merge for all of the rows in a table–that’s a definite win. And I don’t have to type any of the values twice, so that source of error is gone!

merge into table1 dest using (
    select 1 id, 'Sample' c1, 'Value' c2 from dual
    union all
    select 2, 'More', 'Value2' from dual
    ...
    ) src
on ( dest.id = src.id )
when not matched then
    insert (id, col1, col2 )
    values (src.id, src.col1, src.col2 );

It’s fast, it’s secure, and it’s easy to maintain. The only downside is that it wasn’t my first approach.

Why security is important

by Leave a Comment

The technical side of the internet has been buzzing over the last week about the shutdown of Sony's PSN service (the PlayStation Network, which lets users buy & download games, among other things) after someone broke into the servers. Today, we learned that user information was compromised, and that things are worse than simply not being able to play some games.Here's the quote from the official FAQ:

14. What personally identifying information do you suspect has been compromised? Although we are still investigating the details of this incident, we believe that an unauthorized person has obtained the following information provided by PlayStation Network/Qriocity account holders: name, address (city, state, zip), country, email address, birth date, PlayStation Network/Qriocity password, login, and handle/PSN online ID. Other profile data may also have been obtained, including purchase history and billing address (city, state, zip). If an account holder has authorized a sub-account for a dependent, the same data with respect to that dependent may have been obtained. If an account holder provided credit card data through PlayStation Network or Qriocity, it is possible that the credit card number (excluding security code) and expiration date may also have been obtained. (emphasis added)

Now, security's hard to do right; there's always the possibility that someone's going to find a crack that you're simply not aware of. But the two items I highlighted above are especially troubling, since there should be extra security around them.

First of all, passwords should never, never, never be stored in a retrievable form. That's just asking for trouble. Encrypt them securely, and then when you need to authenticate someone, encrypt what they type in and compare it to the value you've stored. (Incidentally, this is what I do on this site and on WriteTrack). If someone compromises your database, all they get is a long string of random characters, with no way to back it out to the original value.

Secondly, although you can't encrypt credit card numbers (since you need the raw number to place a charge at time of purchase), you should work to make it as hard as possible to get that information. One straightforward option, and one which I'm astounded a company as large as Sony did not do, is to store the CC numbers on a separate, locked-down database with an API to update information & place charges (but not to retrieve the number; the PSN network itself should only ever need the last four digits for display purposes).

Like I said, security is hard, but that's no excuse for sloppy work. And I'm afraid Sony's sloppiness is going to cost them a lot of money, both immediately via stock price, but also later on–once the inevitable class action suit gets filed. This is, however, a good reminder for the rest of us to review our security practices–and tighten them up wherever we can.

Page sentry frustration

by 1 Comment

I love APEX. At least, 99% of the time that is. Sometimes, though, things just don't work the way you expect them to, and then things get…confusing. Last week, I ran into one of those moments, and although I found a workaround, I don't understand why what I tried didn't work.

First, the problem I'm trying to solve: We use Oracle E-Business Suite for our ERP data, but there are many people at the office (generally, people working out on the manufacturing floor) who don't have Oracle accounts because they don't need them. So, most of our tools will be using Oracle EBS authentication so we can properly populate the “who did what when” columns. But we're also going to be building applications which aren't specific to EBS, and which people without EBS accounts may need to use. So, for those, we're using their Windows AD accounts. And this presents a problem: do we maintain two different login pages and application menus, or can APEX be told which authentication method to use dynamically?

I didn't want to maintain two different menus, as I felt that would just be confusing to the users, so I built a table that maps applications to the correct authentication method. Our central login page then calls a single process which looks to see where the user is trying to go, and authenticates properly based on that. Well and good. If the user switches applications and they land in one that uses the same authentication method, they stay logged in, as you'd expect. The problem comes up when they switch to a different authentication method–we need to catch that, log them out, and take them to the login screen.

My first attempt was to create a “page sentry” function. Unfortunately, there doesn't seem to be much documentation on this. I started by creating a table to map the current session ID to the authentication method used and the current username; if either of those don't match, the session should be invalidated. Easy, right? Unfortunately, no. The big problem I ran into–and the one I don't understand–is that I could not consistently get the current username. I tried APEX_APPLICATION.G_USER, APEX_CUSTOM_AUTH.GET_USER(), APEX_CUSTOM_AUTH.GET_USERNAME(), and V('APP_USER'), but none of them worked all the time. The best was GET_USERNAME(), as it returned a non-null value more often than the others, but it still returned null or 'nobody' to my sentry function regularly, even when I was properly logged in. And failing to check the logged-in username would just be a security hole, so I kept looking.

My second attempt was to create a branch on page 0 that would conditionally route the user back to the login page. Turns out, though, that even though you can create branches on page 0, they don't actually do anything.

Then I found this (four-year old) post on Scott Spendolini's blog, which gave me an idea for a third route to attempt. I created an Application Process that runs before headers with the same condition* my page 0 branch had. Here's the code for the process:

htp.init;
owa_util.redirect_url('wwv_flow_custom_auth_std.logout?p_this_flow=10000&p_next_flow_page_sess=&APP_ID.:1');
apex_application.g_unrecoverable_error := true;

Very basic. If the condition fires, stop everything and pop over to the login app (100000); once the user authenticates, bring them back to page 1 of this application. And it works, every time.

* The condition checks APEX_APPLICATION.G_USER and APEX_APPLICATION.G_INSTANCE (session). No need to use the APEX_CUSTOM_AUTH wrapper functions.

Build a solid authorization system

by Leave a Comment

In order to make sure that all of our applications share a common look and feel, as well as some basic things like navigation and a central menu page, I've been working on setting up an underlying framework at my office prior to teaching my fellow developers how to build applications. (It's much easier to establish common ground from the start than to retroactively apply it.) Today, I finalized the authorization system, and I'm very pleased with how it turned out.

The requirements for the authorization system are fairly straightforward. APEX allows for a single authorization to be assigned to an object (page, page item, button, process, etc.), but we don't know how many different types of authorizations each of our applications will need. Some may not need any (pure reports, for instance), while others might have several different levels which may or may not overlap. This latter case precludes the simplistic “every authorization level includes all the lower levels”. I also wanted it to be as simple to implement as possible, to minimize the chances for error.

The core of the authorization system is derived from the setup I did for our (dynamic) central menu page. Basically, we have a set of tables to hold information about our applications and how they're grouped on the menu page. To this, I added two tables–one defines authorization levels for each application, and the other maps users to each of these authorization levels. So, for instance, if Application 1 has universal read access, some users with limited write privileges, and a very few users with overall write access, I only need to define two authorization levels and assign users to the two write ones as needed (a shuttle control works great for this, by the bye!).

The next step was to define the authorization framework. I did this by creating a central function:

function check_authorization(p_authorization_level in varchar2) return boolean is
  l_levels apex_application_global.vc_arr2;
  l_ok number := 0;
begin
  l_levels := apex_util.string_to_table(p_authorization_level);
  for i in 1..l_levels.count loop
    select count(*) into l_ok
    from authorized_users au,
      application_authorizations aa
    where au.authorization_id = aa.authorization_id
      and upper(au.username) = v('APP_USER')
      and aa.application_id = v('APP_ID')
      and au.authorization_level = l_levels(i);
    if l_ok > 0 then
      return true;
    end if;
  end loop;
  return false;
end check_authorization;

Then, when creating the authorization schemes in APEX, all of them follow the same pattern: the method is PL/SQL function returning boolean, and the function is simply “return check_authorization()”, where is either a simple number (50), or a colon-separated string of numbers ('10:25:786'). This allows easy compound authorizations–in the above example, the items a user with limited write access is authorized for are probably also items that users with universal write access are.

Finally, note that I'm using APP_USER and APP_ID to check the current user and application. This, again, helps reduce the chances for errors, since we don't need to worry about accidentally feeding the function the wrong application ID.

User-specific views

by Leave a Comment

One of the challenges in upgrading WriteTrack was a limitation of APEX's automatic row fetch process–it only accepts two columns for the primary keys of the table or view it's pulling from. Generally, this is fine; most of the time, you only have one column for your primary key, and for cross-reference tables, you're likely to only have two.

But, in WriteTrack's case, I've got a view that needs three columns to identify a record: user, challenge, and date. This view is what drives the date-edit window, which opens when you click on a specific date in the calendar so you can edit that day's weight and/or word count. In the initial release, I took the quick route and made it work by forcing the challenge ID (since everyone was working on the same challenge).

For the upgrade, I needed to fix this. My solution was to replace most of my views with user-specific ones, eliminating the user ID from the list of possibilities. (Note when doing this: the value in v('APP_USER') is always in upper case. Serious “duh” moment when I realized why my views were returning 0 rows.) This allows me to use the challenge ID and date ID as the two primary key columns, and get on with life. Oh, and it's got a nice security side benefit: no user can ever see any other user's data, short of breaking through the views and querying the underlying tables directly. I'm protected from my own mistakes (in that aspect, at least), as well as most minor hacking attacks. Win!