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.

Thursday, 7 May 2009

Wicked wizards

Usually when I create an Apex application I use the Create Application wizard just for creating the application and not for specifying the pages within it (except for the one blank html page that is mandatory).
I know it is possible to directly specify the pages in the Create Application wizard, but normally I am not quite finished with the application design when I start building.
Recently I found out that this approach has an advantage: it saves you from the "assumptions" that the Create Application wizards makes.

For example, if you want Apex to use a sequence to populate the primary key, the wizard has no step for this. Moreover, if you add a Master Detail page, the wizard skips some questions about using a report and editing the details on the same page. The Create Page wizard contains more steps and is more accurate.

Of course, this is not really a big problem, as long as you are aware of the shortcuts in the Create Application wizard. So let's say, when you are level 1.5 in Dimitri's Apex Knowledge Levels

Conclusion: use the Create Application wizard to create (empty) applications, use the Create Page wizard to create pages.

Wednesday, 12 November 2008

"Generating" interactive reports

I was recently working on an Apex application where user can define views on a bunch of technical tables. It works like this: a user chooses the columns that he or she wants to see (based on some meta information) and a view can be generated and installed on the fly.
Of course the user wants to browse the data in the view immediately. Using a Classic Report based on a procedure that returns the query would be an option. But then I would miss all the nice features of an interactive report. However, you can not define an interactive report based on a procedure.
The solution: the Apex API.

The Apex API offers you a set of procedures where you can create new pages and -this was where I was looking for- new interactive reports. I created a very simple application with only one interactive report and exported it to a sql-file. If you then look at the sql-file, you can see how easy it is to add new pages to your application.

Below is a sample pl/sql procedure that creates a new page with a new interactive report based on a custom query. Of course you probably need to adjust it to your own Apex application, but it should not be very difficult to get it to work.

Have fun with it !


declare
--
-- Cursor to check if the report already exists
--
cursor c_ape (b_page_id in apex_application_pages.page_id%type) is
select 1
from apex_application_pages ape
where ape.application_id = v('APP_ID')
and ape.page_id = b_page_id
;
--
l_page_id apex_application_pages.page_id%type;
l_dummy pls_integer;
l_region_id number := wwv_flow_id.next_val;
l_worksheet_id number := wwv_flow_id.next_val;
l_table varchar2(30);
l_query varchar2(32767);
l_report_columns varchar2(32767);
--
begin
--
wwv_flow_api.set_security_group_id(p_security_group_id=>979127191492671);
wwv_flow.g_flow_id := 129;
l_page_id := 1000;
--
open c_ape(b_page_id => l_page_id);
fetch c_ape into l_dummy;
if c_ape%found
then
--
-- Delete the page if it already exists
--
wwv_flow_api.remove_page(p_flow_id => v('APP_ID')
,p_page_id => l_page_id
);
end if;
close c_ape;
--
-- Create a new page
--
wwv_flow_api.create_page(p_id => l_page_id
,p_flow_id => v('APP_ID')
,p_tab_set => 'TS1'
,p_tab_name => 'Browse Data'
,p_alias => 'BROWSE'
,p_name => 'My Query'
,p_step_title => 'My Query'
,p_step_sub_title => 'My Query'
,p_step_sub_title_type => 'TEXT_WITH_SUBSTITUTIONS'
,p_include_apex_css_js_yn => 'Y'
,p_page_is_public_y_n => 'Y'
,p_last_upd_yyyymmddhh24miss => to_char(sysdate, 'YYYYMMDDHH24MISS')
,p_page_comment => 'Inserted with script'
);
--
l_query := 'SELECT EMPNO, ENAME, SAL FROM EMP';
l_report_columns := 'EMPNO:ENAME:SAL';
--
wwv_flow_api.create_page_plug (p_id => l_region_id
,p_flow_id => v('APP_ID')
,p_page_id => l_page_id
,p_plug_name => 'My Query'
,p_plug_template => 0
,p_plug_display_sequence => 10
,p_plug_display_column => 1
,p_plug_display_point => 'AFTER_SHOW_ITEMS'
,p_plug_source => l_query
,p_plug_source_type => 'DYNAMIC_QUERY'
,p_plug_display_error_message => 'Unable to show report.'
,p_plug_query_row_template => 1
,p_plug_query_headings_type => 'COLON_DELMITED_LIST'
,p_plug_query_row_count_max => 500
,p_plug_display_condition_type => ''
,p_plug_caching => 'NOT_CACHED'
,p_plug_comment => ''
);
wwv_flow_api.create_worksheet(p_id => l_worksheet_id
,p_flow_id => v('APP_ID')
,p_page_id => l_page_id
,p_region_id => l_region_id
,p_name => 'My Query'
,p_alias => ''
,p_report_id_item => ''
,p_max_row_count => '10000'
,p_max_row_count_message => 'This query returns more then 10,000 rows, please filter your data to ensure complete results.'
,p_no_data_found_message => 'No data found.'
,p_max_rows_per_page => ''
,p_search_button_label => ''
,p_page_items_to_submit => ''
,p_sql_query => l_query
,p_status =>'AVAILABLE_FOR_OWNER'
,p_allow_report_saving =>'Y'
,p_allow_report_categories =>'Y'
,p_show_nulls_as =>'-'
,p_pagination_type =>'ROWS_X_TO_Y'
,p_pagination_display_pos =>'BOTTOM_RIGHT'
,p_show_finder_drop_down =>'Y'
,p_show_display_row_count =>'Y'
,p_show_search_bar =>'Y'
,p_show_search_textbox =>'Y'
,p_show_actions_menu =>'Y'
,p_report_list_mode =>'TABS'
,p_show_detail_link =>'Y'
,p_show_select_columns =>'Y'
,p_show_filter =>'Y'
,p_show_sort =>'Y'
,p_show_control_break =>'Y'
,p_show_highlight =>'Y'
,p_show_computation =>'Y'
,p_show_aggregate =>'Y'
,p_show_chart =>'Y'
,p_show_calendar =>'Y'
,p_show_flashback =>'Y'
,p_show_reset =>'Y'
,p_show_download =>'Y'
,p_download_formats =>'CSV'
,p_allow_exclude_null_values =>'Y'
,p_allow_hide_extra_columns =>'Y'
,p_owner =>v('APP_USER')
);
wwv_flow_api.create_worksheet_column(p_id => wwv_flow_id.next_val
,p_flow_id => v('APP_ID')
,p_page_id => l_page_id
,p_worksheet_id => l_worksheet_id
,p_db_column_name => 'empno'
,p_display_order => 1
,p_group_id => null
,p_column_identifier => 'A'
,p_column_label => 'EMPNO'
,p_report_label => 'EMPNO'
,p_sync_form_label =>'Y'
,p_display_in_default_rpt =>'Y'
,p_is_sortable =>'Y'
,p_allow_sorting =>'Y'
,p_allow_filtering =>'Y'
,p_allow_ctrl_breaks =>'Y'
,p_allow_aggregations =>'Y'
,p_allow_computations =>'Y'
,p_allow_charting =>'Y'
,p_others_may_edit =>'Y'
,p_others_may_view =>'Y'
,p_column_type =>'NUMBER'
,p_display_as =>'TEXT'
,p_display_text_as =>'ESCAPE_SC'
,p_heading_alignment =>'CENTER'
,p_column_alignment =>'LEFT'
,p_rpt_distinct_lov =>'Y'
,p_rpt_show_filter_lov =>'D'
,p_rpt_filter_date_ranges =>'ALL'
,p_help_text =>'The employee''s unique number'
);
wwv_flow_api.create_worksheet_column(p_id => wwv_flow_id.next_val
,p_flow_id => v('APP_ID')
,p_page_id => l_page_id
,p_worksheet_id => l_worksheet_id
,p_db_column_name => 'ENAME'
,p_display_order => 2
,p_group_id => null
,p_column_identifier => 'B'
,p_column_label => 'ENAME'
,p_report_label => 'ENAME'
,p_sync_form_label =>'Y'
,p_display_in_default_rpt =>'Y'
,p_is_sortable =>'Y'
,p_allow_sorting =>'Y'
,p_allow_filtering =>'Y'
,p_allow_ctrl_breaks =>'Y'
,p_allow_aggregations =>'Y'
,p_allow_computations =>'Y'
,p_allow_charting =>'Y'
,p_others_may_edit =>'Y'
,p_others_may_view =>'Y'
,p_column_type =>'STRING'
,p_display_as =>'TEXT'
,p_display_text_as =>'ESCAPE_SC'
,p_heading_alignment =>'CENTER'
,p_column_alignment =>'LEFT'
,p_rpt_distinct_lov =>'Y'
,p_rpt_show_filter_lov =>'D'
,p_rpt_filter_date_ranges =>'ALL'
,p_help_text =>'The employee''s name'
);
wwv_flow_api.create_worksheet_column(p_id => wwv_flow_id.next_val
,p_flow_id => v('APP_ID')
,p_page_id => l_page_id
,p_worksheet_id => l_worksheet_id
,p_db_column_name => 'SAL'
,p_display_order => 3
,p_group_id => null
,p_column_identifier => 'C'
,p_column_label => 'SAL'
,p_report_label => 'SAL'
,p_sync_form_label =>'Y'
,p_display_in_default_rpt =>'Y'
,p_is_sortable =>'Y'
,p_allow_sorting =>'Y'
,p_allow_filtering =>'Y'
,p_allow_ctrl_breaks =>'Y'
,p_allow_aggregations =>'Y'
,p_allow_computations =>'Y'
,p_allow_charting =>'Y'
,p_others_may_edit =>'Y'
,p_others_may_view =>'Y'
,p_column_type => 'NUMBER'
,p_display_as =>'TEXT'
,p_display_text_as =>'ESCAPE_SC'
,p_heading_alignment =>'CENTER'
,p_column_alignment =>'LEFT'
,p_rpt_distinct_lov =>'Y'
,p_rpt_show_filter_lov =>'D'
,p_rpt_filter_date_ranges =>'ALL'
,p_help_text =>'The employee''s salary'
);
--
wwv_flow_api.create_worksheet_rpt(p_id => wwv_flow_id.next_val
,p_flow_id => wwv_flow.g_flow_id
,p_page_id => l_page_id
,p_worksheet_id => l_worksheet_id
,p_session_id => null
,p_base_report_id => null
,p_application_user => 'APXWS_DEFAULT'
,p_report_seq =>10
,p_status =>'PUBLIC'
,p_category_id =>null
,p_is_default =>'Y'
,p_display_rows =>15
,p_report_columns =>l_report_columns
,p_flashback_enabled =>'N'
,p_calendar_display_column =>''
);
--
end;

Wednesday, 15 October 2008

SQL Developer Data Modeler: nice but not complete

I have been playing around with the Oracle SQL Developer Data Modeling tool (OSDM). My first impression was that it is a real nice product. It looks good and just like Tobias Arnhold blogged it is really easy to generate a diagram from DDL files. Also, an import from a Designer Repository works like a charm.

However, since I am a big fan of the Designer Entity Relationship Diagrammer (in combination with Headstart Utilities you get the best data models), I am not easily satisfied. In OSDM, building a entity relation model from scratch (painting it on the screen) is not a user friendly task. Creating a new entity is easy, but adding new attributes is a tough job. You need to press the plus button for every attribute and it is not possible to "tab" through the fields. Specifying a datatype for the attribute requires you to go through 2 popup windows. Can you imagine that you record 20 entities with 25 attributes like this?

I also struggled with the "Engineering" part. Although it exactly does what it is supposed to do (make a physical data model which allows you to generate ddl), I was a lit disapointed about the applied naming conventions. I am used to model the entities in singular and get the tables in plural. Furthermore, the entity abbreviation is used in primary keys and foreign keys. I noticed that there are options to supply naming standards in OSDM, but I did not get them to work properly.

Conclusion: if you want to make a nice model of an existing data model, OSDM is perfect. Oracle Designer has very poor functionality to make a nice picture or to export a diagram.
If you need to create a serious new data model, Oracle Designer is still the best choice. If it is not possible to use Designer (license wise) you could make the tables with SQL Developer and reverse engineer it with OSDM.

Let's wait and see how Oracle SQL Developer Data Modeler evolves. I must say that I feel very positive about the future of OSDM: the development team is right on top of the supplied feedback (I wish the helpdesks where this fast ;-)


Tuesday, 14 October 2008

Apex 3.2 still not out?

I wonder how long it will take before Apex 3.2 will be available. I would expect that it should be somewhere short after OOW.
The preview that Dimitri Gielis and Marc Sewtz offers us, makes me curious of what the forms2apex tool can offer us (read the interesting comments). A lot of Oracle and Java developers that deal with Oracle Forms customers will find it interesting, since I think there is still no "real" alternative for good old forms . . .


Reading "Pro Oracle Application Express"

Last week I received the "Pro Oracle Application Express" book by John Scott and Scott Spendolini. Currently I am reading it (bit by bit) and although I already know quit some things about Apex, the book is really good. It has really handy tips and tricks and it also has a nice, brief explanation of "standard" Oracle technology as Virtual Private Database.
I am almost halfway the book, but I already can recommend the book for everyone that wants to do more with Apex.