Oh yes: http://www.myfoxdetroit.com/story/23118642/bra-sizes-of-female-detroit-cops-mistakenly-emailer-to-officers#ixzz2bs9wF2qy
dinsdag 13 augustus 2013
donderdag 30 mei 2013
Launch clientside executables from webapplication
Some time ago I wrote a post "A method to execute external programs via APEX", here DBMS_SCHEDULER was used to execute commands on the (database)server. Recently we had to find a way to "launch clientside executables via APEX". On the web various solutions are described using ActiveX (via shell.application), but for some reason I wasn't able to get that running. To be clear about our ambition: we want to launch a program, add some parameters, on the clientmachine that is viewing our webpage. We thus want to leave the browser sandbox.
The path we took was inspired by the following hyperlink. In a nutshell; we add an URI handler to the Windows registry so that hyperlinks of the form
The before mentioned hyperlink uses Inno Setup, to build an installation program that makes the registry modifications for us. The beauty of Inno Setup is that de-installation of our registry modification is very easy (via default add/remove). I left the default setting pretty standard and added the following lines to the new setup file:
Myhyperlinktextlaunch the windows notepad program on the client with argument <<text>>. Adding an URI handler requires us to make a modification to the windows registry: we need to tell Windows that hyperlinks of the form "webrep:<<text>>" launch notepad with argument <<text>>.
The before mentioned hyperlink uses Inno Setup, to build an installation program that makes the registry modifications for us. The beauty of Inno Setup is that de-installation of our registry modification is very easy (via default add/remove). I left the default setting pretty standard and added the following lines to the new setup file:
; Script generated by the Inno Setup Script Wizard.
[Files]
Source: "files\launch.cmd"; DestDir: "{app}"
Source: "files\demo.html"; DestDir: "{app}"
[Registry]
Root: HKCR; Subkey: webrep; ValueName: URL Protocol; ValueType: string; Flags: uninsdeletekey
Root: HKCR; Subkey: webrep; ValueType: string; ValueData: URL:webrep Protocol; Flags: uninsdeletekey
Root: HKCR; Subkey: webrep\DefaultIcon; ValueType: string; ValueData: {app}\launch.cmd,0; Flags: uninsdeletekey
Root: HKCR; Subkey: webrep\shell\open\command; ValueType: string; ValueData: "{app}\launch.cmd %1"; Flags: uninsdeletekey
After compilation of the setup file, Inno Setup gives us setup.exe. This executable does 2 thing. It copies the required files (that are included in the installer) to a setup-user-defined directory and makes the required modifications to the registry.
The contents of launch.cmd are as follow:
@ECHO OFF set parameter=%1 ECHO. ECHO Don't close this window. ECHO it will close automatically when notepad is closed. ECHO. cmd /c "c:\Windows\System32\notepad.exe %parameter:~7%"The contents of demo.html are as follow:
<html> <head> <title>D E M O</title> </head> <body> <a href="webrep:mynewfile.txt">Click me!</a> </body> </html>Clicking the hyperlink will cause notepad to lauch with a mynewfile.txt. Making this work in APEX is quite straightforward.
woensdag 13 maart 2013
You can never change who you are, or can you?
In PL/SQL statements are parsed as the current connected schema. One can find out what user is parsing a statement by typing (for instance)
select user from dual;This is usefull in all kinds of debugging, auditing etc. statements. You cannot really change your identity, for security reasons ofcourse. However one can parse a statement using rights and privileges of another user. This is nothing new, Oracle APEX does it all the time, as descibed here. Basically one uses
sys.dbms_sys_sqlpackage to parse the statement using the rights of another user. However, one never becomes that user. This is obvious in APEX. For instance when a trigger writes the USER to an audit column (depending on the Listerner configuration) on sees APEX_PUBLIC_USER. Maybe the trigger is adapted to APEX and uses v('APP_USER'), but that is not what I want to demonstrate. Inspired by this pdf, I wrote some code that allows one to be like Mystique, a chameleon:
create procedure run_statement_as_user(p_statement in clob,
p_user in dba_users.username%type)
authid definer is
l_ret_val number;
l_current_user dba_users.username%type := user;
l_ddl varchar2(128);
--
e_not_allowed exception;
begin
-- here a list of users one can 'change into'
-- be carefull, not to list any users that have DBA like
-- rights or privileges.
--
if p_user not in ('<MYUSER>') then raise e_not_allowed; end if;
--
-- switch user to desired p_user
--
l_ret_val := sys.kupp$proc.disable_multiprocess;
sys.kupp$proc.change_user(upper(p_user));
--
-- switch to the user schema
--
l_ddl := 'alter session set current_schema ='||upper(p_user);
execute immediate l_ddl;
--
-- Next parse the statement using rights of the p_user
--
declare
l_cursor number;
l_ret_val_c number;
uid dba_users.user_id%type;
begin
l_ret_val_c := sys.dbms_sql.open_cursor;
--
select user_id
into uid
from dba_users
where username = user;
--
sys.dbms_sys_sql.parse_as_user( l_ret_val_c
, p_statement
, dbms_sql.native
, uid);
--
l_cursor := sys.dbms_sql.execute(l_ret_val_c);
--
sys.dbms_sql.close_cursor(l_ret_val_c);
end;
--
-- Done, all remains is switching back to the original user.
--
-- switch back to original user
sys.kupp$proc.change_user(l_current_user);
l_ret_val := sys.kupp$proc.enable_multiprocess;
-- switch back to schema
l_ddl := 'alter session set current_schema = '||l_current_user;
execute immediate l_ddl;
--
exception
when e_not_allowed then raise;
when others then
sys.kupp$proc.change_user(l_current_user);
l_ret_val := sys.kupp$proc.enable_multiprocess;
l_ddl := 'alter session set current_schema = '||l_current_user;
execute immediate l_ddl;
raise;
end run_statement_as_user;
When the above procedure is compiled in SYS schema any other user that is given executable grant can execute statemens AS IF p_user . So for instance, when connected to the database as user JOHN (who is given executable rights ofcourse):
declare
l_statement varchar2(128):= ' begin
insert into
mytable ( dbuser )
values ( user );
commit;
end;';
l_user varchar2(64) := 'RONALD';
begin
sys.run_statement_as_user(l_statement, l_user);
end;
results in the insertion of one record in the RONALD.MYTABLE table, with dbuser having the value RONALD instead of JOHN, the latter is not even required to have any grants on RONALD.MYTABLE.
maandag 11 februari 2013
Upgrading APEX in the pre-built Oracle VM Virtualbox
I really like Oracle Virtualbox, especially the pre-built VM's from Oracle. For those not acquainted: it does pretty much the same as VMware. One can download Virtualbox from here, and the pre-built VM's from here.
Personally I like: `Database App Development VM': it delivers a pre-configured Oracle EE database, with APEX and APEX listener pre-installed. Importing a pre-built VM is very easy. If setup with NAT port forwarding, one can use the VM as if it were a true service on the host. To do that: start the VM in headless-mode (left-shift + click on start), so that you don't get a terminal. For a how-to of various network configuration-options click here.
For the following it is convenient to start the VM with a terminal / display. Remember that all passwords on the pre-build VM are `oracle' (without the quotes).
When the pre-built VM is up and running, ofcourse one wants to start APEX. However the version of APEX on the pre-build VM is 4.2, whilst the latest is 4.2.1. So we are in need of a patch. The pre-build VM is configured with APEX listener in standalone mode. Patching APEX is relatively straightforward, however we also need to update the images directory of APEX and this can be a bit of a puzzle. This is how I did it:
1. Start the VM, logon as user oracle/oracle and download the APEX 4.2.1 patch from Oracle (click here). Unzip the file in your favorite folder.
2. Stop the APEX listener using the command:
6. Goto /home/oracle/listener and type the command:
7. When done, press CTRL + c. This will kill the listener, so we have to restart it, this time avoiding hangup when we close the connection:
APEX 4.2.1 should come up.
Personally I like: `Database App Development VM': it delivers a pre-configured Oracle EE database, with APEX and APEX listener pre-installed. Importing a pre-built VM is very easy. If setup with NAT port forwarding, one can use the VM as if it were a true service on the host. To do that: start the VM in headless-mode (left-shift + click on start), so that you don't get a terminal. For a how-to of various network configuration-options click here.
For the following it is convenient to start the VM with a terminal / display. Remember that all passwords on the pre-build VM are `oracle' (without the quotes).
When the pre-built VM is up and running, ofcourse one wants to start APEX. However the version of APEX on the pre-build VM is 4.2, whilst the latest is 4.2.1. So we are in need of a patch. The pre-build VM is configured with APEX listener in standalone mode. Patching APEX is relatively straightforward, however we also need to update the images directory of APEX and this can be a bit of a puzzle. This is how I did it:
1. Start the VM, logon as user oracle/oracle and download the APEX 4.2.1 patch from Oracle (click here). Unzip the file in your favorite folder.
2. Stop the APEX listener using the command:
ps -ef | grep apex | grep -v apexlistener | grep -v grep | cut -c9-15 | xargs kill -93. Patch APEX, from your download folder, on the database using the following command:
sqlplus sys/oracle@orcl as sysdba @apxpatch.sql4. Remove the current configuration-settings of APEX listener using the following command:
rm -r /home/oracle/apex5. Copy the images directory to an oracle accessible folder, for instance: /home/oracle/images
6. Goto /home/oracle/listener and type the command:
java -jar apex.warBecause we removed the previous configuration file, one is required to setup the listener (password for APEX_PUBLIC_USER is `oracle', and SID is `orcl', leave all the rest default) and define where the images folder is located (/home/oracle/images). I kept port 8888 for APEX Listener;
7. When done, press CTRL + c. This will kill the listener, so we have to restart it, this time avoiding hangup when we close the connection:
nohup java -jar apex.war >/dev/null&8. Done. Close the terminal (not the VM) and test the setup by opening a browser and type:
http://localhost:8888/apex
APEX 4.2.1 should come up.
dinsdag 22 januari 2013
Using APEX send-mail, from outside APEX
Some time ago I was asked to write a piece of code that would use APEX send mail functionality, but without the APEX session (from a database job, if I recall correctly)
It turns out to be really simple, just set an APEX session, and make sure that the database-user is connected to an APEX workspace. The code is as follow:
It turns out to be really simple, just set an APEX session, and make sure that the database-user is connected to an APEX workspace. The code is as follow:
create or replace procedure my_send_mail( p_to in varchar2
, p_from in varchar2
, p_subject in varchar2
, p_message in varchar2
, p_apex_workspace in varchar2)
is
l_workspace_id number := null;
begin
--
-- This procedure is possibly called from outside an APEX session.
-- Take note: one is required to set-up an session, before APEX_MAIL.SEND
-- can be used. Make sure that the schema using this procedure is
-- connected to the workpace.
--
-- Ignoring the above will raise
-- ORA-20001: This procedure must be invoked from within an
-- application session
--
l_workspace_id :=
apex_util.find_security_group_id (p_workspace => p_apex_workspace);
--
apex_util.set_security_group_id (p_security_group_id => l_workspace_id);
--
apex_mail.send(
p_to => p_to,
p_from => p_from,
p_subj => p_subject,
p_body => p_message);
--
apex_mail.push_queue;
end my_send_mail;
/
Calling the above procedure will give satisfying results in any PL/SQL session, as long as the schema is connected to an APEX workspace.
Abonneren op:
Posts (Atom)