Wednesday, 8 September 2010

Secure APEX

Yesterday I attended the ODTUG webinar "Securing APEX Development" by Scott Spendolini. Initially I thought it would be about the security features in APEX and how to use them. Scott surprised me pleasantly: he had a good story about the underestimation of (application) protection in general, but more important, he presented a solution that is so well thought and fits perfectly in a proper designed application.



I don't know if I will use this setup for all my (demo) applications (it requires some additional actions when building your data model) but when it is a serious production application I definitely think Scott's solution is a good Design Principle. This principle is also very usable for Java and .Net applications, where often an OBJECT_USER vs OBJECT_OWNER model is used. Although the OBJECT_USER usually already has limited privileges, Scott's solution goes further, making the connection setup as secure as possible.
Within APEX, most developers (including me) are tempted to assign the OBJECT_OWNER schema to the application's workspace, because we all think we do not need to worry about the way the connection pool in APEX is setup. Scott showed me I am wrong.

I learned a good lesson.

You can read about the webinar shortly on ODTUG's website.
I already found a pdf of the presentation on the UTah Oracle User Group site

Wednesday, 7 July 2010

APEX Listener and Oracle XE

The APEX listener has been announced as the "way to go" for most of the APEX implementations. You can reed about it in Mike Hichwa's post. I do agree that it is a better way then using the Embedded PL/SQL Gateway (EPG), especially in production environments.

However, when I installed it with the GlassFish 3 Open Source webserver, I had some problems with the configuration and I couldn't start the main APEX url (http://hostname:port/apex): it gave me a http 404 error with the message that I was not allowed to access that url. The error was quite confusing, since the server.log of Glassfish contained a very different error: java.sql.SQLSyntaxErrorException: ORA-00904: "OBJECT_TYPE": invalid identifier

Apparently, a wrong statement was issued by the Listener application. On the OTN discussion forum I found more people having this problem.

The view ALL_PROCEDURES seems to be used by the Listener application and this view has changed between versions 10.2.0.1 and 10.2.0.4. Oracle XE uses the old version, so the APEX listener has a problem with that.

So I updates the view definition in XE and it immediately solved my APEX Listener problem. I now have to check if I didn't break anything, but so far I did not see any problems in the database or in APEX.

This is the view definition that is required by the APEX Listener (version 1.10.179.10.43) , I took it from a 10.2.0.4 database:


create or replace force view all_procedures (owner,
object_name,
procedure_name,
object_id,
subprogram_id,
overload,
object_type,
aggregate,
pipelined,
impltypeowner,
impltypename,
parallel,
interface,
deterministic,
authid
)
as
(select u.name,
o.name,
pi.procedurename,
o.obj#,
pi.procedure#,
decode (pi.overload#, 0, null, pi.overload#),
decode (o.type#,
1, 'INDEX',
2, 'TABLE',
3, 'CLUSTER',
4, 'VIEW',
5, 'SYNONYM',
6, 'SEQUENCE',
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
11, 'PACKAGE BODY',
12, 'TRIGGER',
13, 'TYPE',
14, 'TYPE BODY',
22, 'LIBRARY',
28, 'JAVA SOURCE',
29, 'JAVA CLASS',
30, 'JAVA RESOURCE',
'UNDEFINED'
),
decode (bitand (pi.properties, 8), 8, 'YES', 'NO'),
decode (bitand (pi.properties, 16), 16, 'YES', 'NO'),
u2.name,
o2.name,
decode (bitand (pi.properties, 32), 32, 'YES', 'NO'),
decode (bitand (pi.properties, 512), 512, 'YES', 'NO'),
decode (bitand (pi.properties, 256), 256, 'YES', 'NO'),
decode (bitand (pi.properties, 1024),
1024, 'CURRENT_USER',
'DEFINER'
)
from obj$ o,
user$ u,
procedureinfo$ pi,
obj$ o2,
user$ u2
where u.user# = o.owner#
and o.obj# = pi.obj#
and pi.itypeobj# = o2.obj#(+)
and o2.owner# = u2.user#(+)
and ( o.owner# = userenv ('SCHEMAID')
or exists (select null
from v$enabledprivs
where priv_number in (-144, -141))
or o.obj# in (
select obj#
from sys.objauth$
where grantee# in (select kzsrorol
from x$kzsro)
and privilege# = 12)
))
union all
(select tabobj.owner,
tabobj.object_name,
null,
tabobj.object_id,
case tabobj.object_type
when 'TRIGGER'
then 1
else 0
end,
null,
tabobj.object_type,
'NO',
'NO',
null,
null,
'NO',
'NO',
'NO',
case tabobj.object_type
when 'TRIGGER'
then 'DEFINER'
else case pi.properties
when null
then null
else decode (bitand (pi.properties, 1024),
null, null,
1024, 'CURRENT_USER',
'DEFINER'
)
end
end case
from all_objects tabobj,
procedureinfo$ pi
where ( (tabobj.object_id = pi.obj#(+))
and (tabobj.object_type in ('TRIGGER', 'PACKAGE'))
and ((pi.procedure# is null) or (pi.procedure# = 1))
));




Currently I can access APEX on my XE database using both the EPG and the APEX Listener and they are both pretty fast.