Skip navigation.

Feed aggregator

The UKOUG Apps14 Conference

Duncan Davies - Mon, 2014-12-15 08:00

Cedar had a strong presence at the recent Apps14 conference in the ACC, Liverpool. It is supposed to be the largest applications conference in Europe with over 800 attendees, and we were particularly interested in the PeopleSoft and Apps Innovation streams.

This year we’d decided not to have a stand (as the conference is mixed with the other applications and the database/tech community, it means the exhibition stands are much more expensive than at the PeopleSoft-only Roadshow earlier in the year, while the attendance of the PeopleSoft community is much lower) but we did support the conference with four of our team and three speaking slots.

It was nice to see a different city, although I’m pleased to see that it’s back in Birmingham next year as that is a lot more central for everyone. This is the view from my hotel room out over Albert Docks (the ACC is out to the left).

01 - Sunrise at Apps14

In terms of our speakers, our GP guru Alex spoke about Global Payroll upgrades to v9.2 (as Cedar has helped several clients either complete their move to v9.2 or with an upgrade in progress at the moment):

02 - Alex and Global Payroll 9.2

Several of us also took part in the Oracle Usability Feedback session. We have <ahem> quite strong opinions on how a UX should be so it was really interesting to be part of the process. We can’t talk about the product that was being tested, but it looks really nice. I can’t wait until it hits GA. Here’s a picture of Simon giving it a thorough test with the lovely Rhonda (the screen has been intentionally blurred):

03 - Simon on UX and Usability testing

Cedar’s tech guru Neville also spoke on a couple of topics. He covered PeopleSoft Selective Adoption / PeopleSoft Update Manager in one session (joint with Hays, who are using some of our upgraders at the moment on their massive upgrade – HCM, Fins, CRM, Portal, ELM, all at the same time!). He also spoke about Oracle Secure Enterprise Search (joint with Allen & Overy, who we helped to upgrade to 9.2 earlier in the year).

04 - Neville and PeopleSoft Selective Adoption

The evening in between the two PeopleSoft days was pretty fun too. It started off with a familiar looking (for Liverpool, especially) band called ‘The Cheatles’ (pic below) and then many from the PeopleSoft community sat down for ‘off the record’ chat over a decent meal.

05 - The Cheatles


SQL Server 2014 : sortie du livre "Développer et administrer pour la performance"

Yann Neuhaus - Sun, 2014-12-14 23:50

Un billet en cette fin d'année qui approche à grand pas pour vous annoncer la sortie prochaine (fin décembre 2014) du livre SQL Server 2014 : Développer et administrer pour la performance en français et auquel j'ai eu l'immense plaisir de participer avec Frédéric Brouard (alias SQLPro - MVP SQL Server), Nicolas Souquet (alias Elsuket - MVP SQL Server) et Christian Soutou

Ce livre est destiné aussi bien aux développeurs qu'aux administrateurs débutants ou confirmés soucieux de la performance et couvre un ensemble complet de domaines (l'administration, le développement, la sécurité ou encore la haute disponibilité).

Bonne lecture !

 

 

 

GOLDENDATE HA MAA RAC ACFS XAG

Michael Dinh - Sun, 2014-12-14 17:39

Purpose is to demonstrate how to create HA for Bi-Directional Replication Goldengate installed on ACFS with RAC cluster using XAG.

XAG simplifies the process since there are no requirements to create action scripts.

Please review REFERENCE section for versions used in test case and versions requirements.

Goldengate is installed on ACFS for simplicity; otherwise, at a minimum the following directories br, dirchk, dirdat, dirtmp will need to be on shared storage with symbolic links if installed on local storage. Keyword is minimum until you find out more directories are required.

Role separation was a huge PITA and do not attempt to perform chmod -R 775 /u01 as it will break since the setuid get unset.
Even with chmod 6751 oracle may prove to be ineffective and relink was done.

# id grid
uid=54322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),493(vboxsf),54323(asmadmin),54324(asmdba)

# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),493(vboxsf),54324(asmdba)

# id gguser
uid=54323(gguser) gid=54321(oinstall) groups=54321(oinstall),54322(dba),493(vboxsf)

$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x. 1 grid oinstall 209914519 Dec  7 20:57 /u01/app/11.2.0.4/grid/bin/oracle

Last, DO NOT stop Goldengate using ggsci. For cluster-aware Goldengate, use $XAG_HOME/bin/agctl.

The configuration shown is for one cluster only and the same would have to be performed on the other clusters.

AS ROOT, CONFIGURE GOLDENGATE VIP (lax-ggate1-vip)
1. Determine the network number

# crsctl stat res -p|grep -ie .network -ie subnet|grep -ie name -ie ora_subnet
NAME=ora.net1.network
USR_ORA_SUBNET=192.168.56.0

2. Create GoldenGate VIP with naming Convention: LAX (closest airport code to data center), ggate1 (for Goldengate on network number 1), vip
Note IP address provided is 192.168.56.41

# appvipcfg create -network=1 -ip=192.168.56.41 -vipname=lax-ggate1-vip -user=root -group=oinstall
Production Copyright 2007, 2008, Oracle.All rights reserved
2014-12-14 10:58:41: Creating Resource Type
2014-12-14 10:58:41: Executing /u01/app/11.2.0.4/grid/bin/crsctl add type app.appvip_net1.type -basetype ora.cluster_vip_net1.type -file /u01/app/11.2.0.4/grid/crs/template/appvip.type
2014-12-14 10:58:41: Executing cmd: /u01/app/11.2.0.4/grid/bin/crsctl add type app.appvip_net1.type -basetype ora.cluster_vip_net1.type -file /u01/app/11.2.0.4/grid/crs/template/appvip.type
2014-12-14 10:58:42: Create the Resource
2014-12-14 10:58:42: Executing /u01/app/11.2.0.4/grid/bin/crsctl add resource lax-ggate1-vip -type app.appvip_net1.type -attr "USR_ORA_VIP=192.168.56.41,START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network),STOP_DEPENDENCIES=hard(ora.net1.network),ACL='owner:root:rwx,pgrp:root:r-x,other::r--,group:oinstall:r-x,user:root:r-x',HOSTING_MEMBERS=rac01.localdomain,APPSVIP_FAILBACK="
2014-12-14 10:58:42: Executing cmd: /u01/app/11.2.0.4/grid/bin/crsctl add resource lax-ggate1-vip -type app.appvip_net1.type -attr "USR_ORA_VIP=192.168.56.41,START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network),STOP_DEPENDENCIES=hard(ora.net1.network),ACL='owner:root:rwx,pgrp:root:r-x,other::r--,group:oinstall:r-x,user:root:r-x',HOSTING_MEMBERS=rac01.localdomain,APPSVIP_FAILBACK="

3. Set permission for users to start/stop VIP

crsctl setperm resource lax-ggate1-vip -u user:oracle:r-x
# crsctl setperm resource lax-ggate1-vip -u user:grid:r-x
# crsctl setperm resource lax-ggate1-vip -u user:gguser:r-x
# crsctl start resource lax-ggate1-vip
CRS-2672: Attempting to start 'lax-ggate1-vip' on 'rac02'
CRS-2676: Start of 'lax-ggate1-vip' on 'rac02' succeeded

RETRIEVE INFORMATIONS TO CREATE GOLDENGATE AGENT
–nodes

$ olsnodes
rac01
rac02

–filesystems

$ crsctl stat res -w "TYPE = ora.acfs.type" -p|grep '^NAME'
NAME=ora.dg_acfs.vg_acfs.acfs
NAME=ora.dg_acfs.vg_acfs.acfs

–databases

$ crsctl stat res -w "TYPE = ora.database.type"
NAME=ora.emu.db
TYPE=ora.database.type
TARGET=ONLINE         , ONLINE
STATE=ONLINE on rac01, ONLINE on rac02

– extracts and replicats

GGSCI (rac02.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     ELAX        00:00:00      00:00:02
EXTRACT     RUNNING     PLAX_DEN    00:00:00      00:00:03
REPLICAT    RUNNING     RDEN_LAX    00:00:00      00:00:06

AS GRID, CONFIGURE GOLDENGATE AGENT (lax_ggate)
Options for –instance_type is source|target|dual and I am interpreting dual is bi-directional.

$XAG_HOME/bin/agctl add goldengate lax_ggate \
--gg_home /acfsmount/ggs112 \
--instance_type dual \
--nodes rac01,rac02 \
--vip_name lax-ggate1-vip \
--filesystems ora.dg_acfs.vg_acfs.acfs \
--databases ora.emu.db \
--oracle_home /u01/app/oracle/product/11.2.0.4/db_1 \
--monitor_extracts ELAX,PLAX_DEN \
--critical_extracts ELAX,PLAX_DEN \
--monitor_replicats RDEN_LAX \
--critical_replicats RDEN_LAX

VERIFY RESULTS

$ $XAG_HOME/bin/agctl config goldengate lax_ggate
GoldenGate location is: /acfsmount/ggs112
GoldenGate instance type is: dual
Configured to run on Nodes: rac01 rac02
ORACLE_HOME location is: /u01/app/oracle/product/11.2.0.4/db_1
Databases needed: ora.emu.db
File System resources needed: ora.dg_acfs.vg_acfs.acfs
Extracts to monitor: ELAX,PLAX_DEN
Replicats to monitor: RDEN_LAX
Critical extracts: ELAX,PLAX_DEN
Critical replicats: RDEN_LAX
Autostart on DataGuard role transition to PRIMARY: no
Autostart JAgent: no

START GOLDENDATE

$XAG_HOME/bin/agctl start goldengate lax_ggate

CHECK GOLDENGATE STATUS

$XAG_HOME/bin/agctl status goldengate lax_ggate
Goldengate  instance 'lax_ggate' INTERMEDIATE on rac02

$XAG_HOME/bin/agctl status goldengate lax_ggate
Goldengate  instance 'lax_ggate' is running on rac02
$ crsstat

Resource Name                            Resource Type  Target       State        Node            State Details
---------------------------------------- -------------- ------------ ------------ --------------- ---------------
lax-ggate1-vip                           appvip_net1    ONLINE       ONLINE       rac02
ora.DATA2.dg                             diskgroup      ONLINE       ONLINE       rac01
ora.DATA2.dg                             diskgroup      ONLINE       ONLINE       rac02
ora.DG_ACFS.dg                           diskgroup      ONLINE       ONLINE       rac01
ora.DG_ACFS.dg                           diskgroup      ONLINE       ONLINE       rac02
ora.LISTENER.lsnr                        Listener       ONLINE       ONLINE       rac01
ora.LISTENER.lsnr                        Listener       ONLINE       ONLINE       rac02
ora.LISTENER_SCAN1.lsnr                  SCAN Listener  ONLINE       ONLINE       rac01
ora.LISTENER_SCAN2.lsnr                  SCAN Listener  ONLINE       ONLINE       rac02
ora.LISTENER_SCAN3.lsnr                  SCAN Listener  ONLINE       ONLINE       rac02
ora.asm                                  ASM            ONLINE       ONLINE       rac01           Started
ora.asm                                  ASM            ONLINE       ONLINE       rac02           Started
ora.cvu                                  cvu            ONLINE       ONLINE       rac02
ora.dg_acfs.vg_acfs.acfs                 acfs           ONLINE       ONLINE       rac01           mounted on /acfsmount
ora.dg_acfs.vg_acfs.acfs                 acfs           ONLINE       ONLINE       rac02           mounted on /acfsmount
ora.emu.db                               database       ONLINE       ONLINE       rac01           Open
ora.emu.db                               database       ONLINE       ONLINE       rac02           Open
ora.gsd                                  Gbl Svc Daemon OFFLINE      OFFLINE
ora.gsd                                  Gbl Svc Daemon OFFLINE      OFFLINE
ora.net1.network                         Network (VIP)  ONLINE       ONLINE       rac01
ora.net1.network                         Network (VIP)  ONLINE       ONLINE       rac02
ora.oc4j                                 OC4J           ONLINE       ONLINE       rac02
ora.ons                                  Ora Notif Svc  ONLINE       ONLINE       rac01
ora.ons                                  Ora Notif Svc  ONLINE       ONLINE       rac02
ora.rac01.vip                            Cluster VIP    ONLINE       ONLINE       rac01
ora.rac02.vip                            Cluster VIP    ONLINE       ONLINE       rac02
ora.registry.acfs                        registry       ONLINE       ONLINE       rac01
ora.registry.acfs                        registry       ONLINE       ONLINE       rac02
ora.scan1.vip                            SCAN VIP       ONLINE       ONLINE       rac01
ora.scan2.vip                            SCAN VIP       ONLINE       ONLINE       rac02
ora.scan3.vip                            SCAN VIP       ONLINE       ONLINE       rac02
xag.lax_ggate.goldengate                 goldengate     ONLINE       ONLINE       rac02

CONNECT TO THE CORRECT NODE FOR GOLDENGATE

$ su - gguser
Password:
0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
!!!!! PLEASE CONNECT TO NODE WHERE GOLDENGATE IS RUNNING !!!!!
!!!!!                                                    !!!!!
--+ Goldengate  instance 'lax_ggate' is running on rac02 +--
!!!!!                                                    !!!!!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[gguser@rac01:/home/gguser]
$ ssh rac02
Last login: Sun Dec 14 13:23:48 2014 from rac01
1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~ GOLDENGATE IS RUNNING ON THIS NODE - GOOD TO GO    ~~~~~
--+ Goldengate  instance 'lax_ggate' is running on rac02 +--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[gguser@rac02:/home/gguser]
$

.bash_profile
# User specific environment and startup programs
ogg=`/u01/app/grid/xag/bin/agctl status goldengate lax_ggate`
/u01/app/grid/xag/bin/agctl status goldengate lax_ggate|grep -ic `hostname -f`
if [ "$?" != "0" ]; then
  clear
  echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
  echo "!!!!! PLEASE CONNECT TO NODE WHERE GOLDENGATE IS RUNNING !!!!!"
  echo "!!!!!                                                    !!!!!"
  echo "--+ $ogg +--"
  echo "!!!!!                                                    !!!!!"
  echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
else
  clear
  echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
  echo "~~~~~ GOLDENGATE IS RUNNING ON THIS NODE - GOOD TO GO    ~~~~~"
  echo "--+ $ogg +--"
  echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
fi
umask 0002

RELOCATE GOLDENGATE TO DIFFERENT NODE

[grid@rac01:+ASM1:/home/grid]
$ crsstat


Resource Name                            Resource Type  Target       State        Node            State Details
---------------------------------------- -------------- ------------ ------------ --------------- ---------------
lax-ggate1-vip                           appvip_net1    ONLINE       ONLINE       rac02
ora.DATA2.dg                             diskgroup      ONLINE       ONLINE       rac01
ora.DATA2.dg                             diskgroup      ONLINE       ONLINE       rac02
ora.DG_ACFS.dg                           diskgroup      ONLINE       ONLINE       rac01
ora.DG_ACFS.dg                           diskgroup      ONLINE       ONLINE       rac02
ora.LISTENER.lsnr                        Listener       ONLINE       ONLINE       rac01
ora.LISTENER.lsnr                        Listener       ONLINE       ONLINE       rac02
ora.LISTENER_SCAN1.lsnr                  SCAN Listener  ONLINE       ONLINE       rac01
ora.LISTENER_SCAN2.lsnr                  SCAN Listener  ONLINE       ONLINE       rac02
ora.LISTENER_SCAN3.lsnr                  SCAN Listener  ONLINE       ONLINE       rac02
ora.asm                                  ASM            ONLINE       ONLINE       rac01           Started
ora.asm                                  ASM            ONLINE       ONLINE       rac02           Started
ora.cvu                                  cvu            ONLINE       ONLINE       rac02
ora.dg_acfs.vg_acfs.acfs                 acfs           ONLINE       ONLINE       rac01           mounted on /acfsmount
ora.dg_acfs.vg_acfs.acfs                 acfs           ONLINE       ONLINE       rac02           mounted on /acfsmount
ora.emu.db                               database       ONLINE       ONLINE       rac01           Open
ora.emu.db                               database       ONLINE       ONLINE       rac02           Open
ora.gsd                                  Gbl Svc Daemon OFFLINE      OFFLINE
ora.gsd                                  Gbl Svc Daemon OFFLINE      OFFLINE
ora.net1.network                         Network (VIP)  ONLINE       ONLINE       rac01
ora.net1.network                         Network (VIP)  ONLINE       ONLINE       rac02
ora.oc4j                                 OC4J           ONLINE       ONLINE       rac02
ora.ons                                  Ora Notif Svc  ONLINE       ONLINE       rac01
ora.ons                                  Ora Notif Svc  ONLINE       ONLINE       rac02
ora.rac01.vip                            Cluster VIP    ONLINE       ONLINE       rac01
ora.rac02.vip                            Cluster VIP    ONLINE       ONLINE       rac02
ora.registry.acfs                        registry       ONLINE       ONLINE       rac01
ora.registry.acfs                        registry       ONLINE       ONLINE       rac02
ora.scan1.vip                            SCAN VIP       ONLINE       ONLINE       rac01
ora.scan2.vip                            SCAN VIP       ONLINE       ONLINE       rac02
ora.scan3.vip                            SCAN VIP       ONLINE       ONLINE       rac02
xag.lax_ggate.goldengate                 goldengate     ONLINE       ONLINE       rac02

[grid@rac01:+ASM1:/home/grid]
$ $XAG_HOME/bin/agctl relocate goldengate lax_ggate --node rac01
[grid@rac01:+ASM1:/home/grid]
$ crsstat


Resource Name                            Resource Type  Target       State        Node            State Details
---------------------------------------- -------------- ------------ ------------ --------------- ---------------
lax-ggate1-vip                           appvip_net1    ONLINE       ONLINE       rac01
ora.DATA2.dg                             diskgroup      ONLINE       ONLINE       rac01
ora.DATA2.dg                             diskgroup      ONLINE       ONLINE       rac02
ora.DG_ACFS.dg                           diskgroup      ONLINE       ONLINE       rac01
ora.DG_ACFS.dg                           diskgroup      ONLINE       ONLINE       rac02
ora.LISTENER.lsnr                        Listener       ONLINE       ONLINE       rac01
ora.LISTENER.lsnr                        Listener       ONLINE       ONLINE       rac02
ora.LISTENER_SCAN1.lsnr                  SCAN Listener  ONLINE       ONLINE       rac01
ora.LISTENER_SCAN2.lsnr                  SCAN Listener  ONLINE       ONLINE       rac02
ora.LISTENER_SCAN3.lsnr                  SCAN Listener  ONLINE       ONLINE       rac02
ora.asm                                  ASM            ONLINE       ONLINE       rac01           Started
ora.asm                                  ASM            ONLINE       ONLINE       rac02           Started
ora.cvu                                  cvu            ONLINE       ONLINE       rac02
ora.dg_acfs.vg_acfs.acfs                 acfs           ONLINE       ONLINE       rac01           mounted on /acfsmount
ora.dg_acfs.vg_acfs.acfs                 acfs           ONLINE       ONLINE       rac02           mounted on /acfsmount
ora.emu.db                               database       ONLINE       ONLINE       rac01           Open
ora.emu.db                               database       ONLINE       ONLINE       rac02           Open
ora.gsd                                  Gbl Svc Daemon OFFLINE      OFFLINE
ora.gsd                                  Gbl Svc Daemon OFFLINE      OFFLINE
ora.net1.network                         Network (VIP)  ONLINE       ONLINE       rac01
ora.net1.network                         Network (VIP)  ONLINE       ONLINE       rac02
ora.oc4j                                 OC4J           ONLINE       ONLINE       rac02
ora.ons                                  Ora Notif Svc  ONLINE       ONLINE       rac01
ora.ons                                  Ora Notif Svc  ONLINE       ONLINE       rac02
ora.rac01.vip                            Cluster VIP    ONLINE       ONLINE       rac01
ora.rac02.vip                            Cluster VIP    ONLINE       ONLINE       rac02
ora.registry.acfs                        registry       ONLINE       ONLINE       rac01
ora.registry.acfs                        registry       ONLINE       ONLINE       rac02
ora.scan1.vip                            SCAN VIP       ONLINE       ONLINE       rac01
ora.scan2.vip                            SCAN VIP       ONLINE       ONLINE       rac02
ora.scan3.vip                            SCAN VIP       ONLINE       ONLINE       rac02
xag.lax_ggate.goldengate                 goldengate     ONLINE       INTERMEDIATE rac01           ER(s) not running : ELAX,ELAX,RDEN_LAX

[grid@rac01:+ASM1:/home/grid]
$ crsstat


Resource Name                            Resource Type  Target       State        Node            State Details
---------------------------------------- -------------- ------------ ------------ --------------- ---------------
lax-ggate1-vip                           appvip_net1    ONLINE       ONLINE       rac01
ora.DATA2.dg                             diskgroup      ONLINE       ONLINE       rac01
ora.DATA2.dg                             diskgroup      ONLINE       ONLINE       rac02
ora.DG_ACFS.dg                           diskgroup      ONLINE       ONLINE       rac01
ora.DG_ACFS.dg                           diskgroup      ONLINE       ONLINE       rac02
ora.LISTENER.lsnr                        Listener       ONLINE       ONLINE       rac01
ora.LISTENER.lsnr                        Listener       ONLINE       ONLINE       rac02
ora.LISTENER_SCAN1.lsnr                  SCAN Listener  ONLINE       ONLINE       rac01
ora.LISTENER_SCAN2.lsnr                  SCAN Listener  ONLINE       ONLINE       rac02
ora.LISTENER_SCAN3.lsnr                  SCAN Listener  ONLINE       ONLINE       rac02
ora.asm                                  ASM            ONLINE       ONLINE       rac01           Started
ora.asm                                  ASM            ONLINE       ONLINE       rac02           Started
ora.cvu                                  cvu            ONLINE       ONLINE       rac02
ora.dg_acfs.vg_acfs.acfs                 acfs           ONLINE       ONLINE       rac01           mounted on /acfsmount
ora.dg_acfs.vg_acfs.acfs                 acfs           ONLINE       ONLINE       rac02           mounted on /acfsmount
ora.emu.db                               database       ONLINE       ONLINE       rac01           Open
ora.emu.db                               database       ONLINE       ONLINE       rac02           Open
ora.gsd                                  Gbl Svc Daemon OFFLINE      OFFLINE
ora.gsd                                  Gbl Svc Daemon OFFLINE      OFFLINE
ora.net1.network                         Network (VIP)  ONLINE       ONLINE       rac01
ora.net1.network                         Network (VIP)  ONLINE       ONLINE       rac02
ora.oc4j                                 OC4J           ONLINE       ONLINE       rac02
ora.ons                                  Ora Notif Svc  ONLINE       ONLINE       rac01
ora.ons                                  Ora Notif Svc  ONLINE       ONLINE       rac02
ora.rac01.vip                            Cluster VIP    ONLINE       ONLINE       rac01
ora.rac02.vip                            Cluster VIP    ONLINE       ONLINE       rac02
ora.registry.acfs                        registry       ONLINE       ONLINE       rac01
ora.registry.acfs                        registry       ONLINE       ONLINE       rac02
ora.scan1.vip                            SCAN VIP       ONLINE       ONLINE       rac01
ora.scan2.vip                            SCAN VIP       ONLINE       ONLINE       rac02
ora.scan3.vip                            SCAN VIP       ONLINE       ONLINE       rac02
xag.lax_ggate.goldengate                 goldengate     ONLINE       ONLINE       rac01

STOP GOLDENDATE

[grid@rac01:+ASM1:/home/grid]
$ $XAG_HOME/bin/agctl stop goldengate lax_ggate
[grid@rac01:+ASM1:/home/grid]

DISCOVERY
It’s easy to know where everything when you are the architect, but what happens when you are not?
How to find the Goldengate VIP and is it in DNS?

$ crsctl stat res lax-ggate1-vip -p|grep USR_ORA_VIP
GEN_USR_ORA_VIP=
USR_ORA_VIP=192.168.56.41

$ nslookup 192.168.56.41
Server:         127.0.0.1
Address:        127.0.0.1#53

41.56.168.192.in-addr.arpa      name = lax-ggate1-vip.localdomain.

$ crsctl stat res -w "TYPE = ora.acfs.type" -p|grep '^MOUNT'
MOUNTPOINT_PATH=/acfsmount
MOUNTPOINT_PATH=/acfsmount

REFERENCE:

Oracle GoldenGate Best Practices: 
Configuring Oracle GoldenGate with Oracle Grid Infrastructure Bundled Agents (XAG) - (Document 1527310.1) 

Oracle Clusterware: 

http://oracle.com/goto/clusterware

Oracle Grid Infrastructure (Bundled) Agents: 

http://www.oracle.com/technetwork/database/database-technologies/clusterware/downloads/index.html

Oracle Grid Infrastructure Agents Version 5.1
11.2.0.3 and later 
12.1.0.1 and later 

http://www.oracle.com/technetwork/database/database-technologies/clusterware/downloads/ogiba-2189738.pdf

SRDC - Data to Collect on GoldenGate Issues Related to XAG (Doc ID 1913048.1)	

Patch 16762459: ORACLE GOLDENGATE V11.2.1.0.7 FOR ORACLE 11G

[gguser@angel:/u01/app/ggs112]
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.7 16934304 OGGCORE_11.2.1.0.7_PLATFORMS_130709.1600.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 07:04:28

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (angel.local) 1> dblogin userid ggadmin, password ggadmin
Successfully logged into database.

GGSCI (angel.local) 2> versions
Operating System:
Linux
Version #1 SMP Fri Feb 22 18:16:18 PST 2013, Release 2.6.39-400.17.1.el6uek.x86_64
Node: angel.local
Machine: x86_64

Database:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Partner Webcast – Oracle AppAdvantage powered by Oracle Fusion Middleware

Modern organizations require applications to seamlessly extend, integrate, become more agile, and embrace new business imperatives, including social, mobile, cloud, and big data. ...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Implementing a Database Authentication Scheme in APEX

The Anti-Kyte - Sun, 2014-12-14 12:26

The following tangential opening was written especially for Scott Wesley in the hope that he’ll be minded to point out any errors in what follows. The same applies to Jeff Kemp ( although I don’t know if he’s into the AFL).
Unlike me, both of these guys are APEX experts.

Football. It’s a term that means different things to different people.
To a European, it’s most likely to be a reference to good old Association Football ( or Soccer).
To an American, it’s more likely to be the Grid-iron game.
A New Zealander will probably immediately think of Rugby Union.
An Australian ? Well, it’s probably a fair bet that they’ll think of Aussie Rules Football.

On the face of it, the rules appear rather arcane to an outsider. 18-a-side teams kicking, catching and punching something that resembles a Rugby ball around a pitch that resembles a cricket oval. Then there is the scoring system.
“Nice Behind”, to an AFL player is more likely to be taken as a compliment of their skill at the game than an appreciation of their anatomy.

Then again, it’s easy to scoff at any sport with which you are unfamiliar.
For example, Rugby could be characterised as 30 people chasing after an egg. Occasionally, they all stop and half of them go into some strange kind of group hug. I wonder if the backs ever get paranoid because they think the forwards are talking about them ?

As for soccer, even afficionados will acknowledge that there’s something a bit odd about a game where 22 millionares spend lots of time chasing after one ball…when they’re not rolling around in apparent agony after appearing to trip over an earth worm. I mean, the ball isn’t that expensive, surely they can afford one each ?

The point of all of this ? Well, what is considered to be obscure, eccentric, or just plain odd often depends on the perspective of the observer.

Take APEX authentication schemes for example.
Whilst not the default, Database Authentication is a scheme that is readily available. However, there doesn’t seem to be much written on this subject.

In contrast, there is a fair bit out there about APEX Custom Authentication. A lot of it would appear to re-enforce the idea that implementing security by hand is fraught with difficulty.
Just one example can be seen here.

If we were to approach this topic from the perspective of looking to migrate an elderly Oracle Forms application – where each user has their own database account – to APEX, we might be attracted to the idea of a Database Authentication Scheme and want to find out more.

What follows is my adventure through setting up such an Authentication Scheme.
Specifically, I’m going to cover :

  • Creating an APEX Database Authentication Scheme
  • Default behaviour
  • Adding a Verification Function to restrict access to a sub-set of Database Users
  • The vexed question of password resets

Why use Database Authentication

The Oracle documentation states :

“Database Account Credentials is a good choice if having one database account for each named user of your application is feasible and account maintenance using database tools meets your needs.”

If we’re migrating an application from Oracle Forms, then chances are that this is what we’re doing now, so a Database Authentication Scheme should save us a fair bit of work.
The other major advantage is that utilising the Database’s built-in User and Security management means that we don’t have to try and re-invent the wheel.
So, the objective here is to implement Authentication in our new Application without having to :

  • Create and maintain extra tables
  • Write lots of extra code
  • Figure out a secure way of storing passwords
The Application

Firing up my trusty XE 11g installation, I’ll be using a simple APEX application that consist of a standard login page and, initially at least, a Home Page with two read only fields in an HTML Region called WHOAMI.
These are :

  • Application User – the APP_USER that I’m connected to APEX as
  • Database User – the actual user connected to the database

For the P1_APPLICATION_USER, the Source Type is Item (application or page item name).
The source value is APP_USER.
For the P1_DATABASE_USER, the Source Type is set to SQL Query(return single value).
The source value is simply the query :

select user from dual
A Note on the Design

In this example, I’ve taken the approach that the code required to implement this functionality is included in the parsing schema ( HR in this case). As a consequence, the privileges required to execute this code are also granted to the parsing schema.
I’ve done this for the purposes of clarity.
Careful consideration needs to be given to this design decision if you’re planning to implement it in a “proper” production environment.

Creating a Database Authentication Scheme

After navigating to the Application in Application Builder, rather than do anything to the Application itself, we need to create a Shared Component…

db_auth1

The type of component we want is an Authentication Scheme.

NOTE – Authentication Scheme – controls login to the Application.
Authorisation Scheme – governs which bits of the Application the user can see…once they’re connected.

Anyway, in the Security Region, select Authentication Scheme :

db_auth2

…and then hit the Create button…

db_auth3

We want to create a scheme “Based on a pre-configured scheme in the gallery” …

db_auth4

In the next screen :

Name : HR_DB
Scheme Type : Database Accounts

db_auth5

And finally, we click the Create Authentication Scheme button and…

db_auth6

We can see from this that HR_DB is now the Authentication Scheme currently being used by any Application in the Workspace.

Anyway, now to test it.

To this point, I haven’t setup any users for this application.

So, Can I log in as a user that does exist in the database ?
Well, I have a user called MIKE :

select 1
from dba_users
where username = 'MIKE'
/

         1
----------
         1

SQL> 

So, if I now run my application and try to connect using my database credentials…

run_app2

… I can connect using my database credentials.

db_auth_ok1

It’s worth noting that, despite this, the actual database connection from APEX is as the ANONYMOUS user.
If you’re using the APEX Listener instead of the Embedded PL/SQL Gateway (the default in XE), then it’ll probably be APEX_PUBLIC_USER.

So, in order to login to my application, you now have to be a database user.
All the messy password encryption stuff is handled by Oracle and I can now get on with polishing my finely crafted APEX Application….or so you might think.

Just consider this :

run_app_system

…also let’s you connect :

We're not fussy, we'll let anyone in !

We’re not fussy, we’ll let anyone in !

Now, my imaginary Forms application – remember, that’s the one I want to migrate to APEX – may be sitting on a Database Instance with a number of other Applications. So, how do I restrict access to my application to a subset of the users in the database ?
Time for a bit of a re-think then…

The verify function

What we need is a means of identifying a database user as an Application user.
At this point it may well be worth revisiting the role of database roles in APEX applications.
Hang on, you’re thinking, last time you said they were pretty much useless in APEX.
Well, bear with me.

Roles as Privileges, sort of

What we’re going to do here is to simply create an empty role and assign it to a database user :

create role hr_user
/

grant hr_user to mike
/

We now have some means of determining which database users are our application users :

select 1
from dba_role_privs
where granted_role = 'HR_USER'
and grantee = 'MIKE'
/
The function

Now all we need is a function that checks to see if the user attempting to login has this role granted to them.
It’s worth bearing in mind here that, for a function based on the above statement, select privileges on DBA_ROLE_PRIVS is required.

To start with I’m going to grant the privilege to HR :

grant select on sys.dba_role_privs to hr
/

and then I’m going to create the function in the HR schema :

create or replace function is_hr_user_fn
    return boolean
is
--
-- Is this user a database user with privileges to access the APEX Application ?
-- NOTE - the owner of this function requires SELECT privilege on DBA_ROLE_PRIVS
--
    l_dummy pls_integer;
begin
    select 1 
    into l_dummy 
    from sys.dba_role_privs
    where granted_role = 'HR_USER'
    and grantee = apex_040200.v('APP_USER');
    
    return true;
exception
    when no_data_found then
        raise_application_error('-20000', 'You are not an application user');
end;
/ 

You’ll note that the references to both DBA_ROLE_PRIVS and the V function are done directly on the objects themselves rather than through their public synonyms.
In many cases, but especially where security is concerned, it’s usually a good idea to make sure that you’re referencing the object that you intend rather than relying on a synonym.

If you want to see an example of how public synonyms can be changed to point to objects other than those originally intended, then have a look here.

Now we need to tell our Authentication scheme to use this function as the Verify Function.
In the Application Builder, go back to the Shared Components screen then select Authentication Schemes.

Now click on the pencil icon next to HR_DB – Current :

If you want to be a bit more discerning...

If you want to be a bit more discerning…

In the Session Not Valid section, there is a field called Verify Function Name.
In here, simply enter the name of our function – i.e. is_hr_user_fn :

...add a Verify Function

…add a Verify Function

And save the changes.

So, we should now be able to connect as MIKE, but not any other database user.

Connecting as MIKE works as before. However, for SYSTEM, the results are slighty different :

Your name's not down, you're not coming in !

Your name’s not down, you’re not coming in !

As we can see, the Application Error raised by the function is displayed. If you hit the OK button, you’ll then be returned to the Login Page.

The Principle of Least Privilege

In case your not familiar with the term, it basically boils down to the principle that access to an application should be restricted to the minimum level required for a user, application or program to function.
Have a look here for a proper explanation.

It’s probably worth noting that, implementing this approach to Authentication means that, in order to create a new application user, all that’s required is the following :

create user plugger identified by pwd
/

grant hr_user to plugger
/

In case you’re wondering, Plugger is the nickname of a certain Tony Lockett who, apparently, was a pretty good Aussie Rules player in his time.

Anyway, as you can see, our new user requires no system privileges, not even CREATE SESSION. They simply need to be granted the role so that they can be identified as an application user.

Whilst were on the subject of least privilege, you might consider that it is by no means necessary for the parsing schema of an APEX application to have CREATE SESSION priviliges, or indeed, to even be the owner of the application’s database objects.
This applies irrespective of the Authentication Scheme being used.

We now have a robust and efficient Authentication Scheme. There is however, one rather thorny issue that we still need to consider.

Changing Passwords

Whilst we now have a mechanism for authenticating users through their database accounts, unless we give them the facility to change their passwords before they expire, we’ll be storing up a significant amount of admin for the poor, hard-pressed DBA.

The venerable Forms Application we’re migrating was written in the days prior to SSO becoming prevalent and authentication is still managed entirely within the database. Remember, the whole point of chosing Database Authentication is so that we minimise the amount of effort required to migrate this application onto APEX in terms of re-coding the Application’s Security Model.

This is where things get a bit tricky.
Whilst our users are authenticating as themselves, they are actually connecting to the database as ANONYMOUS or APEX_PUBLIC_USER.

Therefore, we need a procedure in a schema with ALTER USER privileges to change passwords from within the APEX application.

So, how do we provide this functionality in our application.

Danger ! Assumption Imminent !

As I’m all too aware ( often through bitter experience), Assumption is the Mother of all cock-ups.
Therefore, the assumption I’m about to make here requires careful explanation.

Here goes then…

I’m assuming that I can safely call a stored procedure from within APEX, passing a user password in clear text.

Clear text ! I hear you cry, Have you gone mad ?
Well, possibly. On the other hand a trawl through of the APEX documentation reveals that there are a few package members in the APEX packages themselves where this takes place.

These are :

Further research reveals that, certainly in the latest versions of APEX, there do not appear to be any exploits available to compromise these procedures. The most recent one I found was for APEX 3.1, an example of which can be seen on the Red Database Security site.

As well as giving the user the ability to change their password at any time, we also want to check immeadiately after the user connects and find out whether their password is near to expiry. If so, then we need to re-direct them to a password change page.
What was Jeff saying about scary code ?

Anyway, the steps to build this functionality are, in order :

  1. Create a Change Password Procedure to be called from the application
  2. Create a Change Password Page where the user can change their password ( and which will call the procedure)
  3. Create a branch in the Application to re-direct a user to the Change Password Page if their password is due to expire
Allowable characters in the password

As we’re going to have to change the password by executing an ALTER USER command from within a PL/SQL procedure, we’re going to have to use dynamic SQL. Critically, we’re not going to be able to use bind variables for this command because it’s a DDL statement.
In order to ensure that the resulting procedure is not vulnerable to SQL Injection, we’re going to have to make sure that passwords do not contain the single quote (‘) character.

To do this, we’re going to create a profile for our application users which includes a password verify function and assign it to them.

So, the Password Verify Function, which needs to be created in the SYS schema, looks like this :

create or replace function new_hr_verify_fn
(
    username varchar2,
    password varchar2,
    old_password varchar2
)
    return boolean 
is 
--
-- A very simple password verify function. In this case. all we're interested in
-- is that the password should not contain a quote (') character.
-- NOTE : this function is purely to illustrate this sole restriction.
-- A proper password verification function would be rather more extensive.
--
begin
    if instr(password, q'[']') > 0 then
        raise_application_error(-20000, q'[Password cannot contain a "'"]');
    end if;
    return true;
end;
/

A quick test of this function shows that it works as expected :

set serveroutput on size unlimited
declare
--
-- Script to test the password verify function
--
    type typ_passwords is table of varchar2(100)
        index by pls_integer;
        
    tbl_passwords typ_passwords;
    
    lc_old_password constant varchar2(50) := 'DUMMY';
    
    l_dummy boolean;
begin
    tbl_passwords(1) := q'[Hawthorn Top O' the heap!]';
    tbl_passwords(2) := 'Tony||chr(39)||or 1=1||chr(39)';
    tbl_passwords(3) := q'[Tony '; select * from dba_users --]';
    tbl_passwords(4) := 'beware men with funny shaped balls';
    for i in 1..tbl_passwords.count loop
        begin
            l_dummy := new_hr_verify_fn
                (
                    username => user,
                    password => tbl_passwords(i),
                    old_password => lc_old_password
                );
            dbms_output.put_line('Test '||i||' Password '||tbl_passwords(i)||' is allowed');
        exception when others then
            dbms_output.put_line('Test '||(i)||' ERROR : '||sqlerrm);
        end;
    end loop;
end;
/

Run this as we get…

SQL> @test_verify
Test 1 ERROR : ORA-20000: Password cannot contain a "'"
Test 2 Password Tony||chr(39)||or 1=1||chr(39) is allowed
Test 3 ERROR : ORA-20000: Password cannot contain a "'"
Test 4 Password beware men with funny shaped balls is allowed

PL/SQL procedure successfully completed.

Note that, although the string containing “chr(39)” is allowed, because there is no way to concatenate a quote into the entry string, this is treated as a collection of characters rather than a call to the CHR function.
Incidentally 39 is the ASCII code for a single quote.

Also note that this particular password verify function has been kept simple deliberately for the purposes of clarity.
Something rather more complex is likely to be in place in a real-life production scenario.

The profile then, looks like this :

create profile hr_default limit
    failed_login_attempts 10
    password_life_time 30
    password_reuse_time 1800
    password_reuse_max 60
    password_lock_time 1
    password_grace_time 7
    password_verify_function new_hr_verify_fn
/

Finally, we’re going to assign the profile to PLUGGER :

alter user plugger profile hr_default
/
The Change Password Procedure

Once again, this procedure is being created in the HR schema. It will be used to ultimately issue the ALTER USER command to change the passwords. Therefore, we need to grant the ALTER USER privilege to HR :

grant alter user to hr
/

As this procedure also needs to reference DBA_USERS, we’ll need to grant SELECT on that too.

grant select on sys.dba_users to hr
/

When writing this procedure, paranoia is the watchword. Objects need to be referenced directly, rather than via synonyms and any user input needs to be sanitised before we plug it into the dynamic SQL statement we need to run.
The result might look something like this :

create or replace procedure change_apex_user_pwd_pr
(
    i_old_pwd in varchar2,
    i_new_pwd in varchar2
)
is
--
-- Procedure to change the password for a user of the NEW_HR APEX application
-- The old password is required, as well as the new one because, if we're
-- using a verify function in the profile the user is assigned to, the
-- old password must be specified in the ALTER USER statement.
--
    l_user sys.dba_users.username%type;
    
    lc_apex_user constant sys.dba_users.username%type := 'ANONYMOUS';
    
    l_dummy pls_integer;
    cursor c_validate_user( cp_user sys.dba_users.username%type)
    is
        select 1
        from sys.dba_users usr
        inner join sys.dba_role_privs rol
            on rol.grantee = usr.username
        where usr.username = cp_user;
begin
    --
    -- Make sure that the parameter values have been specified
    --
    if i_new_pwd is null or i_old_pwd is null then
        raise_application_error(-20000, 'Both the Old Password and the New Password must be specified');
    end if;
    --
    -- Sanitize the user input parameters to prevent SQL Injection.
    -- This boils down to rejecting strings that contain a "'"
    --
    if instr(i_old_pwd, q'[']') > 0 
        or instr(i_new_pwd, q'[']') > 0
    then
        raise_application_error(-20001, 'Passwords must not contain the '||CHR(39)||' character.');
    end if;
    --
    -- Additionally, check that the password does not exceed the maximum length
    -- allowed ( 50 in 11g)
    --
    if length( i_old_pwd) > 50 
        or length( i_new_pwd) > 50
    then
        raise_application_error(-20002, 'Passwords must not exceed 50 characters in length.');
    end if;
    --
    -- Now validate that the user is indeed 
    --  (a) calling the function from APEX
    --  (b) exists in the database
    --  (c) is a user of this application
    -- ...also check that the username does not contain a quote character
    -- to guard against a Blind Injection.
    --
    l_user := apex_040200.v('APP_USER');
    if l_user is null 
        or user != lc_apex_user
    then
        raise_application_error(-20003, 'This function can only be called from APEX');
    end if;
    open c_validate_user( l_user);
    fetch c_validate_user into l_dummy;
    if c_validate_user%notfound then
        close c_validate_user;
        raise_application_error(-20004, 'This user is not a NEW_HR Application user');
    end if;
    close c_validate_user;
    --
    -- Now change the password. REPLACE clause is required in case the
    -- user's default profile has a password verify function specified
    --
    execute immediate 'alter user '||l_user||' identified by '||i_new_pwd||' replace '||i_old_pwd;
end;
/

In the procedure itself, we’re taking a number of precautions :

  • Values for both parameters must be supplied
  • The input parameter values must not exceed 50 characters – the maximum length of an 11g password
  • The input parameter values must not contain a single quote character
  • The user currently connected to the database is the Apex user ( in my case ANONYMOUS)
  • A call to the V function for the application user returns a value
  • The application user we’re changing is indeed a valid user of the NEW_HR Apex application – and a database user
  • references to any database objects are done directly and not via synonyms

Hopefully, that’s enough paranoia to prevent the procedure being misused.
Once again, we can use a test harness to check the parameter tests at least :

set serveroutput on size unlimited
declare
--
-- test for the change_apex_user_pwd_pr procedure.
-- Note all of these tests should fail as we're running from SQL*Plus and
-- are not connected as ANONYMOUS.
-- 
    type rec_params is record
    (
        old_pwd varchar2(100),
        new_pwd varchar2(100)
    );
    type typ_params is table of rec_params index by pls_integer;
    tbl_params typ_params;
begin
    -- populate the test parameter array
    
    -- Test 1 - missing old password value
    tbl_params(1).old_pwd := null;
    tbl_params(1).new_pwd := 'Boring';
    
    -- Test 2 - missing new password value
    tbl_params(2).old_pwd := 'Boring';
    tbl_params(2).new_pwd := null;
    
    -- Test 3 - old password contains a quote
    tbl_params(3).old_pwd := q'[I'm a silly password]';
    tbl_params(3).new_pwd := 'sensible';
    
    -- Test 4 - new password contains a quote
    tbl_params(4).old_pwd := 'sensible';
    tbl_params(4).new_pwd := q'[Who's sensible now ?]';
    
    -- Test 5 - old password > 50 characters
    tbl_params(5).old_pwd := 'just leaning on the keyboard until i have printed over 50 characters zzzzz';
    tbl_params(5).new_pwd := 'short_and_to_the_point';
    
    -- Test 6 - new password > 50 characters
    tbl_params(6).old_pwd := 'short_and_to_the_point';
    tbl_params(6).new_pwd := 'just leaning on the keyboard until i have printed over 50 characters zzzzz';
    
    -- Test 7 - parameters are valid but we're not connected through APEX...
    
    tbl_params(7).old_pwd := 'Valid_pwd';
    tbl_params(7).new_pwd := 'anotherboringpassword';
    
    --
    -- Execute the tests
    --
    for i in 1..tbl_params.count loop
        begin
            change_apex_user_pwd_pr
            (
                i_old_pwd => tbl_params(i).old_pwd,
                i_new_pwd => tbl_params(i).new_pwd
            );
            dbms_output.put_line('Test '||i||' - Somthing has gone wrong - no error !');
        exception
            when others then
                dbms_output.put_line('Test '||i||' Error : '||sqlerrm);
        end;
    end loop;
end;
/

Running this gives us :

SQL> @change_pwd_test
Test 1 Error : ORA-20000: Both the Old Password and the New Password must be specified
Test 2 Error : ORA-20000: Both the Old Password and the New Password must be specified
Test 3 Error : ORA-20001: Passwords must not contain the ' character.
Test 4 Error : ORA-20001: Passwords must not contain the ' character.
Test 5 Error : ORA-20002: Passwords must not exceed 50 characters in length.
Test 6 Error : ORA-20002: Passwords must not exceed 50 characters in length.
Test 7 Error : ORA-20003: This function can only be called from APEX

PL/SQL procedure successfully completed.

SQL> 

To test the rest of the function, we will of course, need to be connected via APEX.

The Change Password Page

Now we come to the page we will be using to call the procedure we’ve just created.
The page will have :

  • a password field for the application user to enter their current password
  • a password field for the application user to enter their new password
  • and another one for them to re-type it
  • some validation that the new password and confirm password matches
  • a button to call the change password procedure
  • a field to present a message to the user after the password change call

Sounds simple (dangerous) enough…

In Application Builder hit the Create Page button…

select Blank Page ….

In the Page Attributes…

Page Alias : change_db_pwd

change_pwd1

In the Page Name …

Name : Change My Password
HTML Region1 : change password

change_pwd2

In Tab Options…

Tab Options : Use an existing tab set and create a new tab within the existing tab set
New Tab Label : Change Password

change_pwd3

…and hit Finish.

Now Edit the Page.

Create a new field with an Item Type of Password :

old_pwd1

In the Display Position and Name screen,

Item Name : PX_OLD_PWD (where X is the number of the page you’re editing).

old_pwd2

In the Item Attributes Screen :

Label : Current Password
Field Width : 50

old_pwd3

In the Settings Screen –

Value Required : Yes
Submit when Enter pressed : No

old_pwd4

In the Source Screen :

Source Used : Always, replacing any existing session state

Hopefully, the APEX5 Graphical Page Designer will result in fewer screenshots being required in future !

Hopefully, the APEX5 Graphical Page Designer will result in fewer screenshots being required in future !

And hit Create Item.

Now create two further fields with the same properties except :

PX_NEW_PWD has a label of New Password
PX_CONFIRM_PWD has a label of Confirm New Password

Next, we create a Display Only field called PX_MESSAGE.
We’ll use this to provide feedback to the user.
We define this with no label so that it doesn’t show up on the screen, until it’s populated.

Now we’ve got all of the fields on the page the next step is to create the Change Password button :

Accept the defaults for Button Region and Button Position.

In the Button Attributes Page :

Button Name : change_pwd_btn
Label : Change Password

cpwd_btn1

Then just hit Create Button.

Finally, we need to add a Dynamic Action to validate that the values in PX_NEW_PWD and PX_CONFIRM_PWD are not null and identical, and then to call the Procedure.

NOTE – I daresay any APEX experts reading this may have a better way of doing this !

So, Create a Dynamic Action.

In the Identification Page :

Name : change_pwd_da

da1

In the When Page :
Action : Click
Selection Type : Button
Button : CHANGE_PWD_BTN

da2

In the True Action Page :

Action : Execute PL/SQL Code

The PL/SQL Code is as follows :

begin
    if nvl(:P6_NEW_PWD, 'x') != nvl(:P6_CONFIRM_PWD, 'y')
    then
       :P6_MESSAGE := 'Confirm Password does not match New Password.';
    else
        hr.change_apex_user_pwd_pr
        (
             i_old_pwd => :P6_OLD_PWD,
             i_new_pwd => :P6_NEW_PWD
        );
        :P6_MESSAGE := 'Your password has been changed';
    end if;
exception when others then
      :P6_MESSAGE := SQLERRM;
end;

Page Items to Submit : P6_OLD_PWD,P6_NEW_PWD,P6_CONFIRM_PWD,P6_MESSAGE
Page Items to Return : P6_MESSAGE

da3

Click Create Dynamic Action.

Now to test.
I’m connected as PLUGGER and I want to change my password.
So, I click on the Change Password Tab and I see :

cpwd_page1

If the new and confirm password fields don’t match, I get an error from the Dynamic Action itself, before it calls the procedure :

Someone's having a fat-finger moment

Someone’s having a fat-finger moment

If I try to enter a password that contains a single quote, I get :

We'll have none of those naughty quotes thank you very much.

We’ll have none of those naughty quotes thank you very much.

Finally, I manage to get it right and am rewarded with :

pwd_changed

Invoking the Change Password Programatically

All that remains now is for us to arrange for the user to be re-directed to the change password page when they connect and their password is near expiry.

The password expiry_date is available in the DBA_USERS view so we need to grant SELECT on this to HR :

grant select on sys.dba_users to hr
/

As I’m re-directing them to a page that belongs specifically to the current application, I’m going to put the re-direction in the application itself.
So, I’m going to add a Branch to the Home Page.
Once again we need to pause here for the APEX gurus to explain the proper way to do this !

Edit the Home Page and Create a Branch…

In Branch Attributes

Name : pwd_change_br
Branch Point : On Load : Before Header

br_cpw1

In Target

Page : the number of the Change Password Page ( 6 in my case)

In Branch Conditions

Condition Type : Exists( SQL query returns at least one row)
In Expression 1, enter the query :

select 1
from sys.dba_users
where username = apex_040200.v('APP_USER')
and expiry_date < trunc(sysdate) + 7

This will return 1 if the password is due to expire within the next 7 days.

br_cpw2

and hit Create Branch.

In order to test the branch, I’ve had a bit of a fiddle with the FIXED_DATE parameter [link to post] so that PLUGGER’s password is now due to expire in less than 7 days.

Now, when I login as plugger…

brtest1

…I go straight to the Change Password Page…

brtest2

Summary

What started off as a fairly short post about Database Authentication Schemes in APEX has grown quite a bit more than I intended.
I believe that the solution to password management, which I have outlined here, is secure.
Obviously, if anyone can spot any flaws in this, I (and anyone reading this), would find it immensly helpful if you could provide reasons/code as to why and how this approach could be exploited.
Whilst the Change Password functionality is something of an overhead in going down the Database Authentication route, the use of database roles, not to mention the RDBMS itself, does mean that this is an approach worth considering when porting older applications to APEX….or maybe it isn’t.
I wonder if there’s a passing Australian who’d like to share their opinion on this ?


Filed under: APEX, Oracle, PL/SQL, SQL Tagged: APEX Database Authentication Scheme, change password procedure, dba_role_privs, dba_users, password verify function, profile, verify function

Oracle lateral inline view, cursor expression and 12c implicit statement result

Yann Neuhaus - Sun, 2014-12-14 09:50

I'll present here 3 ways to run a query for each result of another query. Let's take an exemple: get all executions plan (select from dbms_xplan.display_cursor) for each of my queries (identified from v$sql). The 90's way was to run the first query, which generates the second queries into a spool file, and execute that file. Here are easier ways, some of them coming from 12c new features lateral join and implicit statement result.

Vendors as Traditional Revolutionaries

Michael Feldstein - Sun, 2014-12-14 09:40

In a post titled “The LMS for Traditional Revolutionaries,” Instructure’s VP of Research and Education for Canvas Jared Stein responded to my LMS rant with some numbers and some thoughts about the role of the vendor in encouraging progressive teaching practices. First, the numbers on the use of open education features in Canvas:

  • 3.8% of courses are “public”; you don’t need a login to see them.
  • 0.6% of courses are Creative Commons-licensed.
  • 4.0% of assignments are URL submissions (suggesting that students are completing their assignments on their blogs or elsewhere on the open web).

On the one hand, as Jared acknowledges, these percentages are very low. On the other hand, as he points out, 4% of assignments is close to 250,000 assignments, which is non-trivial as an absolute number. And all of this raises the question: What is the role of the vendor in promoting progressive educational practices?

Let’s take the best-case scenario. Suppose you’re a good person and a thoughtful educator who happens to work for a vendor at the moment. (For those of you who don’t know him, Jared enjoys just such a reputation, having spent a number of years as an excellent academic ed tech blogger and practitioner before joining Instructure.) What can you do? What is your role? On the one hand, you will get criticized by educators who want more and faster change for being too conventional. I certainly have leveled that sort of criticism at vendors before. And maybe those criticisms will sting particularly hard if you were one of those educators yourself before you joined the company (and maybe still are, in your heart of hearts). On the other hand, you are likely to be criticized as arrogant, high-handed, and unwilling to listen to your customers if you put yourself in the position of lecturing to educators (or, at worst, bullying them) about what you, as a vendor, define as best teaching practices. I certainly have leveled this sort of criticism as well.

So what’s a vendor to do? Jared writes,

These [open education features] are just a few examples of capabilities in Canvas that we believe add flexibility and encourage different approaches to teaching and learning. I recognize that sharing this data is a little risky; some may use it to argue that Canvas shouldn’t worry so much about the small percentage of educators who may take advantage of these fringe capabilities. After all, won’t teachers who are actually invested in open educational practices just eschew the LMS for their own platforms anyway?

Focusing only on “users like us” and ignoring the others may work in the short-term, but for long-term success you have to build bridges, not walls.

To help education improve itself for all teachers and learners we have to try to connect with those teachers who aren’t comfortable with radical shifts in pedagogy or technology. We believe that the best way to encourage positive change in educational practices across the broad landscape of content areas, learning objectives, and teaching philosophies is by providing tools that are easy-to-use, flexible, and comfortable to the majority of teachers and learners. The door to change must be open and the doorkeeper must be deposed.

Some of the ways we do this is by having an open community, engaging with people who disagree with us, and investing in the open platform aspect of Canvas. We need both traditionalists, critical pedagogues, progressive researchers, and open educators to contribute to Canvas.That doesn’t have to be done through pull requests or by building LTI apps or integrations, though that’s a brilliant way to build solutions that are right for your context. But by dialoging what works in teaching and learning and what doesn’t. By debating what technology is best for, and when it leads us away from our shared goals of teaching and learning better in an open and connected world.

Shorter Jared: We put capabilities to support progressive practices in our product in the hopes that our users will discover, adopt, and promote them, but it’s not our place to push our preferred educational practices on our customers.

In many cases—particularly with a platform that serves a large and heterogeneous swath of the campus community—that’s the best attitude you can get from your vendor. That’s the most they can do without rightly pissing off (more) people.

All of which brings me back to a single point: If you want better educational technology, then work to make sure that your colleagues in your campus community are asking for the things that you think would make educational technology better. If 40% rather than 4% of assignments created by your colleagues were on the open web, then learning platforms like LMSs would look and work differently. I guarantee it. Likewise, as long as most educators tend to use the technology to reproduce existing classroom practices, LMSs will look the same. I guarantee that too. And that’s not a vendor thing. That’s a software development thing. Community-developed open source learning platforms generally haven’t broken the mold, and the few that have tend to be the ones that you probably have never heard of because they don’t get adopted. They build what their community members ask for and what they think will attract other community members. So if you want better tech, then the best thing you can do to get it is to create demand for it among your colleagues.

The post Vendors as Traditional Revolutionaries appeared first on e-Literate.

Is Oracle 12c REST ready?

Marcelo Ochoa - Sat, 2014-12-13 16:33
This post is a continuation of my previous post Is Oracle 11g REST Ready?, and the answer is yes.
Again the availability of the embedded JVM at the Oracle RDBMS allows us to run an implementation of the complete REST stack and application.To show how to implement a simple Hello World Application in REST I decided to use this time Jersey REST stack.With Oracle 12c we have the availability of two JDK (1.6 and 1.7) and to compile and run Jersey we have to change the default 1.6 and switch de RBMS to 1.7 JDK, follow this guide to do that, but remember that in a CDB/PDB environment switching the JDK means change the compatibility JDK on all PDB, here another good post on that topic, DB 12c update java to version 7.Once we have our RDBMS ready with JDK 1.7 we need Jersey compiled and ready to upload, here my steps:a.- Check JDK version:
    mochoa@localhost:~$ export JAVA_HOME=/usr/local/jdk1.7
    mochoa@localhost:~$ export PATH=$JAVA_HOME/bin:$PATH
    mochoa@localhost:~$ type java
    java is /usr/local/jdk1.7/bin/java
    mochoa@localhost:~$ java -version
    java version "1.7.0_55"
    Java(TM) SE Runtime Environment (build 1.7.0_55-b13)
    Java HotSpot(TM) 64-Bit Server VM (build 24.55-b03, mixed mode) b.- Check Maven:
    mochoa@localhost:~$ mvn -version
    Apache Maven 3.2.3 (33f8c3e1027c3ddde99d3cdebad2656a31e8fdf4; 2014-08-11T17:58:10-03:00)
    Maven home: /usr/local/apache-maven-3.2.3
    Java version: 1.7.0_55, vendor: Oracle Corporation
    Java home: /home/usr/local/jdk1.7.0_55/jre
    Default locale: en_US, platform encoding: UTF-8
    OS name: "linux", version: "3.13.0-40-generic", arch: "amd64", family: "unix"c.- Download and build Jersey using this guide Building and Testing Jersey, after a successful build of jersey all components will be located at our Maven local repository, on Linux is at $HOME/.m2/repositoryd.- Add a new container implementation for Oracle XMLDB Servlet, sources could be downloaded using this link, this new container implementation is a cloned version of jersey-servlet-core but downgrading Servlet 2.3 to 2.2 implemented by XMLDB, here the steps:mochoa@localhost:~/jdeveloper/mywork/jersey$ cd containers/
    mochoa@localhost:~/jdeveloper/mywork/jersey/containers$ tar xvfz /tmp/xdb-servlet.tar.gz
    mochoa@localhost:~/jdeveloper/mywork/jersey/containers$ cd xdb-servlet/
    mochoa@localhost:~/jdeveloper/mywork/jersey/containers/xdb-servlet$ mvn -Dmaven.test.skip=true clean install
    [INFO] Scanning for projects...
    [INFO]                                                                        
    [INFO] ------------------------------------------------------------------------
    [INFO] Building jersey-container-servlet-xdb 2.14-SNAPSHOT
    .... lot of stuff here ....
    [INFO] BUILD SUCCESS
    [INFO] ------------------------------------------------------------------------
    [INFO] Total time: 18.146 s
    [INFO] Finished at: 2014-12-13T17:07:21-03:00
    [INFO] Final Memory: 29M/351M
    [INFO] ------------------------------------------------------------------------e.- Create a new user into the RDBMS, this user will be used to contain all Jersey stack:SQL> select tablespace_name from dba_tablespaces;
    TABLESPACE_NAME
    ------------------------------
    SYSTEM
    SYSAUX
    TEMP
    USERS
    EXAMPLE
    SQL> create user jersey identified by jersey
         default tablespace users
         temporary tablespace temp
         quota unlimited on users;
    User created.
    SQL> grant connect,resource,create public synonym to jersey;
    Grant succeeded.f.- Upload all libraries to the RDBMS, the list of libraries build from Jersey sources and their dependency are:
    1. javax/servlet/servlet-api/2.2/servlet-api-2.2.jar (la versión instalada en la BD)
    2. javax/persistence/persistence-api/1.0/persistence-api-1.0.jar
    3. org/glassfish/hk2/external/javax.inject/2.4.0-b06/javax.inject-2.4.0-b06.jar
    4. org/glassfish/hk2/hk2-utils/2.4.0-b06/hk2-utils-2.4.0-b06.jar
    5. org/osgi/org.osgi.core/4.2.0/org.osgi.core-4.2.0.jar
    6. org/glassfish/hk2/osgi-resource-locator/1.0.1/osgi-resource-locator-1.0.1.jar
    7. org/glassfish/hk2/hk2-api/2.4.0-b06/hk2-api-2.4.0-b06.jar
    8. javax/ws/rs/javax.ws.rs-api/2.0.1/javax.ws.rs-api-2.0.1.jar
    9. org/glassfish/jersey/bundles/repackaged/jersey-guava/2.14-SNAPSHOT/jersey-guava-2.14-SNAPSHOT.jar
    10. javax/annotation/javax.annotation-api/1.2/javax.annotation-api-1.2.jar
    11. org/glassfish/jersey/core/jersey-common/2.14-SNAPSHOT/jersey-common-2.14-SNAPSHOT.jar
    12. org/glassfish/jersey/core/jersey-client/2.14-SNAPSHOT/jersey-client-2.14-SNAPSHOT.jar
    13. javax/validation/validation-api/1.1.0.Final/validation-api-1.1.0.Final.jar
    14. javassist/javassist/3.12.1.GA/javassist-3.12.1.GA.jar
    15. org/glassfish/hk2/external/aopalliance-repackaged/2.4.0-b06/aopalliance-repackaged-2.4.0-b06.jar
    16. org/glassfish/hk2/hk2-locator/2.4.0-b06/hk2-locator-2.4.0-b06.jar
    17. org/glassfish/jersey/core/jersey-server/2.14-SNAPSHOT/jersey-server-2.14-SNAPSHOT.jar
    library [1] should never be uploaded into RDBMS because is part of XMLDB implementation, so here the steps to upload [2]-[17] libraries:

    $ cd $HOME/.m2/repository % loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[2]
    Classes Loaded: 91
    Resources Loaded: 2
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 0
    Synonyms Created: 91
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[3]
    Classes Loaded: 6
    Resources Loaded: 3
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 0
    Synonyms Created: 6
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[4]
    Classes Loaded: 60
    Resources Loaded: 5
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 0
    Synonyms Created: 60
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[5]
    Some errors, but no resolving problems found.
    loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[6]
    Classes Loaded: 0
    Resources Loaded: 3
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 12
    Synonyms Created: 12
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[7]
    Classes Loaded: 0
    Resources Loaded: 3
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 153
    Synonyms Created: 153
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[8]
    Classes Loaded: 125
    Resources Loaded: 3
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 0
    Synonyms Created: 125
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[9]
    Classes Loaded: 1594
    Resources Loaded: 3
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 0
    Synonyms Created: 1594
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[10]
       ...Some errors not allowed in PDB, other classes OK....
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[11]
    Classes Loaded: 0
    Resources Loaded: 5
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 490
    Synonyms Created: 490
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[12]
    Classes Loaded: 99
    Resources Loaded: 4
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 0
    Synonyms Created: 99
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[13]
    Classes Loaded: 106
    Resources Loaded: 3
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 0
    Synonyms Created: 106
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[14]
    Classes Loaded: 366
    Resources Loaded: 3
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 0
    Synonyms Created: 366
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[15]
    Classes Loaded: 26
    Resources Loaded: 3
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 0
    Synonyms Created: 26
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[16]
    Classes Loaded: 0
    Resources Loaded: 4
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 92
    Synonyms Created: 92
    Errors: 0
    $ loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl ...[17]
    Classes Loaded: 0
    Resources Loaded: 16
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 652
    Synonyms Created: 652
    Errors: 0We can check above loadjava commands logged as jersey into the target database using (all queries must return empty):SQL> select dbms_java.longname(object_name) from all_objects where object_type = 'JAVA CLASS' AND status = 'INVALID'
    AND dbms_java.longname(object_name) like 'javax/persistence/%';
    SQL> select dbms_java.longname(object_name) from all_objects where object_type = 'JAVA CLASS' AND status = 'INVALID'
    AND dbms_java.longname(object_name) like 'javax/inject/%';
    SQL> select dbms_java.longname(object_name) from all_objects where object_type = 'JAVA CLASS' AND status = 'INVALID'
    AND dbms_java.longname(object_name) like 'javax/annotation/%';
    SQL> select dbms_java.longname(object_name) from all_objects where object_type = 'JAVA CLASS' AND status = 'INVALID'
    AND dbms_java.longname(object_name) like 'javax/validation/%';
    SQL> select dbms_java.longname(object_name) from all_objects where object_type = 'JAVA CLASS' AND status = 'INVALID'
    AND dbms_java.longname(object_name) like 'javax/ws/rs/%';
    SQL> select dbms_java.longname(object_name) from all_objects where object_type = 'JAVA CLASS' AND status = 'INVALID'
    AND dbms_java.longname(object_name) like 'jersey/%';
    SQL> select dbms_java.longname(object_name) from all_objects where object_type = 'JAVA CLASS' AND status = 'INVALID'
    AND dbms_java.longname(object_name) like 'org/%';g.- finally uploading our xdb-servlet container:
    loadjava -v -r -s -g PUBLIC -u jersey/jersey@pdborcl org/glassfish/jersey/containers/jersey-container-servlet-xdb/2.14-SNAPSHOT/jersey-container-servlet-xdb-2.14-SNAPSHOT.jar
    Classes Loaded: 39
    Resources Loaded: 4
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 0
    Synonyms Created: 39
    Errors: 0h.- at this point we have everything uploaded into the RDBMS, now we prepare XMLDB to run Java implemented Servlets.
    Enabling XMLDB HTTP access:
    SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);
    PL/SQL procedure successfully completed.
    SQL> alter system register;
    System altered.i.- By default XMLDB is configured with digest authentication, to change that we can download xdbconfig.xml file using ftp and updating the section and finally uploading again using ftp (require SYS user):
    <authentication>
        <allow-mechanism>basic</allow-mechanism>
    </authentication>j.- grants required for running Jersey Servlet, we are using JERSEY user here, for using other account similar grants are required directly or by creating a new role (recommend):
    SQL> exec dbms_java.grant_permission( 'JERSEY', 'SYS:java.lang.RuntimePermission', 'getClassLoader','');
    SQL> exec dbms_java.grant_permission( 'JERSEY', 'SYS:java.lang.RuntimePermission', 'accessDeclaredMembers', '' );
    SQL> exec dbms_java.grant_permission( 'JERSEY', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );
    SQL> exec dbms_java.grant_permission( 'JERSEY','SYS:java.util.logging.LoggingPermission', 'control', '' );k.- uploading a simple Hello World application from examples directory:
    mochoa@localhost:~/jdeveloper/mywork/jersey$ cd examples/helloworld
    mochoa@localhost:~/jdeveloper/mywork/jersey/examples/helloworld$ loadjava -r -v -u jersey/jersey@pdborcl target/classes/org/glassfish/jersey/examples/helloworld/HelloWorldResource.class
    arguments: '-u' 'jersey/***@pdborcl' '-r' '-v' 'target/classes/org/glassfish/jersey/examples/helloworld/HelloWorldResource.class'
    identical: org/glassfish/jersey/examples/helloworld/HelloWorldResource
    skipping : class org/glassfish/jersey/examples/helloworld/HelloWorldResource
    Classes Loaded: 0
    Resources Loaded: 0
    Sources Loaded: 0
    Published Interfaces: 0
    Classes generated: 0
    Classes skipped: 1
    Synonyms Created: 0
    Errors: 0l.- Registering Servlet into XMLDB Adapater (logged as SYS):SQL> DECLARE
        configxml SYS.XMLType;
    begin
     dbms_xdb.deleteServletMapping('JerseyServlet');
     dbms_xdb.deleteServlet('JerseyServlet');
     dbms_xdb.addServlet(name=>'JerseyServlet',language=>'Java',class=>'org.glassfish.jersey.servlet.ServletContainer',dispname=>'Jersey Servlet',schema=>'jersey');
    SELECT INSERTCHILDXML(xdburitype('/xdbconfig.xml').getXML(),'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet[servlet-name="JerseyServlet"]','init-param',
    XMLType('jersey.config.server.provider.classnamesorg.glassfish.jersey.examples.helloworld.HelloWorldResourceHello World Application'),'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') INTO configxml
    FROM DUAL;
     dbms_xdb.cfg_update(configxml);
     dbms_xdb.addServletSecRole(SERVNAME => 'JerseyServlet',ROLENAME => 'authenticatedUser',ROLELINK => 'authenticatedUser');
     dbms_xdb.addServletMapping('/jersey/*','JerseyServlet');
     commit;
    end;
    /m.- Finally test my App:mochoa@localhost:~/jdeveloper/mywork/jersey/examples/helloworld$ curl --basic --user jersey:jersey http://localhost:8080/jersey/helloworld
    Hello World!!And that's all, happy 12c REST world.Notes on security:
    1. As you can see when registering a Servlet we added ROLENAME => 'authenticatedUser',ROLELINK => 'authenticatedUser', this imply that a RDBMS user name and password is required for accessing to this Servlet, as in the example we have to provide jersey/jersey which was the owner of the Hello Wolrd app
    2. HTTP protocol sent user name and password encoded as Base 64 when using basic authentication schema, if we want to hide this information over the net when using plain HTTP protocol we have to move to HTTPS.
    3. If you install other Hello World application in a different schema, for example scott, is necessary to upload also the class ServletContainer, for example using loadjava -u scott/tiger@pdborcl org/glassfish/jersey/servlet/ServletContainer.class, and obviously our new application class, finally registering the Servlet changing the tag to <servlet-schema>scott</servlet-schema>
    4. Servlet which runs without authentication are registered using ROLENAME => 'PUBLIC',ROLELINK => 'PUBLIC'), but is NOT recommended and it requires anonymous account unlock and these grants:
    SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;User altered.SQL> exec dbms_java.grant_permission( 'ANONYMOUS', 'SYS:java.lang.RuntimePermission', 'getClassLoader',''));
    SQL> exec dbms_java.grant_permission( 'ANONYMOUS', 'SYS:java.lang.RuntimePermission', 'accessDeclaredMembers', '' );
    SQL> exec dbms_java.grant_permission( 'ANONYMOUS', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );
    SQL> exec dbms_java.grant_permission( 'ANONYMOUS','SYS:java.util.logging.LoggingPermission', 'control', '' );

    How To Set EAR Upload Size for Oracle Enterprise Manager

    Andrejus Baranovski - Sat, 2014-12-13 06:29
    By default, there is a limit for EAR upload size (~40 MB) in Enterprise Manager. If your ADF application is fairly big, you may face this limitation and will not be able to deploy through Enterprise Manager. Deployment for large EAR would work through WebLogic console, WLST script or directly from JDEV, however sometimes you may need to deploy through Enterprise Manager. I will describe in this post, how you could increase the limit - we are going to extract Enterprise Manager application EAR and change maximum file upload size specified in web.xml.

    In my case, the requirement was to be able to deploy ~47 MB EAR file:


    When trying to deploy this EAR through Enterprise Manager, I would get the error about maximum file upload size exceeded:


    You must navigate to the folder, where applications are deployed for your domain (alternatively, double check in WLS for EM application, where EAR file is stored). There must be one called em.ear, this is the deployment for Enterprise Manager application:


    Extract this EAR (make sure to create backup), you will find em.war file inside - we need this file:


    Extract em.war file now. Navigate to WEB-INF folder, you will find web.xml file there. Open web.xml file for editing:


    Search for org.apache.myfaces.trinidad.UPLOAD_MAX_DISK_SPACE parameter in web.xml and change its value to be large enough to accept the size of EAR file you want to deploy. I have set it to 50 MB = 52428800 bytes:


    Make sure to pack everything back, and keep original em.war and em.ear files structure:


    After all the are changes applied, large EAR file is accepted in Enterprise Manager and we can proceed with the deployment:

    Throw it away - Why you shouldn't keep your POC

    Robert Baillie - Sat, 2014-12-13 04:32
    "Proof of Concepts" are a vital part of many projects, particularly towards the beginning of the project lifecycle, or even in the pre-business case stages.They are crucial for ensuring that facts are gathered before some particularly risk decisions are made.  Technical or functional, they can address many different concerns and each one can be different, but they all have one thing in common.  They serve to answer questions.It can be tempting, whilst answering these questions to become attached to the code that you generate.I would strongly argue that you should almost never keep the code that you build during a POC.  Certainly not to put into a production system.I'd go so far as to say that planning to keep the code it is often damaging to the proof of concept; planning to throw the code away is liberating, more efficient and makes proof of concepts more effective by focussing the minds on the questions that require answers..Why do we set out on a proof of concept?The...

    Throw it away - Why you shouldn't keep your POC

    Rob Baillie - Sat, 2014-12-13 04:26

    "Proof of Concepts" are a vital part of many projects, particularly towards the beginning of the project lifecycle, or even in the pre-business case stages.

    They are crucial for ensuring that facts are gathered before some particularly risk decisions are made.  Technical or functional, they can address many different concerns and each one can be different, but they all have one thing in common.  They serve to answer questions.

    It can be tempting, whilst answering these questions to become attached to the code that you generate.

    I would strongly argue that you should almost never keep the code that you build during a POC.  Certainly not to put into a production system.

    I'd go so far as to say that planning to keep the code it is often damaging to the proof of concept; planning to throw the code away is liberating, more efficient and makes proof of concepts more effective by focussing the minds on the questions that require answers..

    Why do we set out on a proof of concept?

    The purpose of a proof of concept is to (by definition):

      * Prove:  Demonstrate the truth or existence of something by evidence or argument.
      * Concept: An idea, a plan or intention.

    In most cases, the concept being proven is a technical one.  For example:
      * Will this language be suitable for building x?
      * Can I embed x inside y and get them talking to each other?
      * If I put product x on infrastructure y will it basically stand up?

    They can also be functional, but the principles remain the same for both.

    It's hard to imagine a proof of concept that cannot be phrased as one or more questions.  In a lot of cases I'd suggest that there's only really one important question with a number of ancillary questions that are used to build a body of evidence.

    The implication of embarking on a proof of concept is that when you start you don't know the answer to the questions you're asking.  If you *do* already know the answers, then the POC is of no value to you.

    By extension, there is the implication that the questions posed require to be answered as soon as possible in order to support a decision.  If that's not the case then, again, the POC is probably not of value to you.

    As such, the only thing that the POC should aim to achieve is to answer the question posed and to do so as quickly as possible.

    This is quite different to what we set out to do in our normal software development process. 

    We normally know the answer to the main question we're asking (How do we functionally provide a solution to this problem / take advantage of this opportunity), and most of the time is spent focussed on building something that is solid, performs well and generally good enough to live in a production environment - in essence, not answering the question, but producing software.

    What process do we follow when embarking on a proof of concept?

    Since the aim of a POC is distinct from what we normally set out to achieve, the process for a POC is intrinsically different to that for the development of a production system.

    With the main question in mind, you often follow an almost scientific process.  You put forward a hypothesis, you set yourself tasks that are aimed at collecting evidence that will support or deny that hypothesis, you analyse the data, put forward a revised hypothesis and you start again.

    You keep going round in this routine until you feel you have an answer to the question and enough evidence to back that answer up.  It is an entirely exploratory process.

    Often, you will find that you spend days following avenues that don't lead anywhere, backtrack and reassess, following a zig-zag path through a minefield of wrong answers until you reach the end point.  In this kind of situation, the code you have produced is probably one of the most barnacle riddled messes you have every produced.

    But that's OK.  The reason for the POC wasn't to build a codebase, it was to provide an answer to a question and a body of evidence that supports that answer.

    To illustrate:

    Will this language be suitable for building x?

    You may need to check things like that you can build the right type of user interfaces, that APIs can be created, that there are ways of organising code that makes sense for the long term maintenance for the system.

    You probably don't need to build a completely functional UI, create a fully functioning API with solid error handling or define the full set of standards for implementing a production quality system in the given language.

    That said, if you were building a production system in the language you wouldn't dream of having in incomplete UI, or an API that doesn't handle errors completely or just knocking stuff together in an ad-hoc manner.

    Can I embed x inside y and get them talking to each other

    You will probably need to define a communication method and prove that it basically works.  Get something up and running that is at least reasonably functional in the "through the middle" test case.

    You probably don't need to develop an architecture that is clean with separation of concerns that means the systems are properly independant and backwards compatible with existing integrations. Or that all interactions are properly captured and that exceptional circumstances are dealt with correctly.

    That said, if you were building a production system, you'd need to ensure that you define the full layered architecture, understand the implications of lost messages, prove the level of chat that will occur between the systems.  On top of that you need to know that you don't impact pre-existing behaviour or APIs.

    If I put product x on infrastructure y will it basically stand up?

    You probably need to just get the software on there and run your automated tests.  Maybe you need to prove the performance and so you'll put together some ad-hoc performance scripts.

    You probably don't need to prove that your release mechanism is solid and repeatable, or ensure that your automated tests cover some of the peculiarities of the new infrastructure, or that you have a good set of long term performance test scripts that drop into your standard development and deployment process.

    That said, if you were building a production system, you'd need to know exactly how the deployments worked, fit it into your existing continuous delivery suite, performance test and analyse on an automated schedule.

    Production development and Proof of Concept development is not the same

    The point is, when you are building a production system you have to do a lot of leg-work; you know you can validate all the input being submitted in a form, or coming through an API - you just have to do it.

    You need to ensure that the functionality you're providing works in the majority of use-cases, and if you're working in a TDD environment then you will prove that by writing automated tests before you've even started creating that functionality.

    When you're building a proof of concept, not only should these tests be a lower priority, I would argue that they should be *no priority whatsoever*, unless they serve to test the concept that you're trying to prove.

    That is,  you're not usually trying to ensure that this piece of code works in all use-cases, but rather that this concept works in the general case with a degree of certainty that you can *extend* it to all cases.

    Ultimately, the important deliverable of a POC is proof that the concept works, or doesn't work; the exploration of ideas and the conclusion you come to; the journey of discovery and the destination of the answer to the question originally posed.

    That is intellectual currency, not software.  The important delivery of a production build is the software that is built.

    That is the fundamental difference, and why you should throw your code away.

    Paginated HTML is here and has been for some time ... I think!

    Tim Dexter - Fri, 2014-12-12 18:03

    We have a demo environment in my team and of course things get a little beaten up in there. Our go to, 'here's Publisher' report was looking really bad. Data was not returning or being rendered correctly on the five templates we have for it.
    So, I spent about a half hour cleaning up the report; getting things working again; clearing out the rubbish. I noticed that one of the layouts when rendered in HTML was repeatedly showing a header down the screen. Oh, I know where to get rid of that and off I click to the report properties to fix it. But what is this I see? Is it? Can it be? Are my tired old eyes deceiving me?

    Yes, Dexter, you see that right, 'View Paginated'! I nervously changed the value to 'true' and went back to the HTML output.
    Holy Amaze Balls Batman, paginated HTML, the holy grail of HTML rendered reports, the Mount Everest of ... no, thats too easy, the K2 of html output ... its fan-bloody-tastic! Can you tell Im excited? I was immediately on messenger to Leslie (doc writer extraordinaire) 


    Obviously not quite as big a deal in the sane, real world outside of my head. 'Oh yeah, we have that now ...' Leslie is so calm and collected, however, she does like Maroon 5 but, we overlook that :)

    I command you 11.1.1.6+'ers to go find the property and turn it on right now and bask in the glory that is, 'paginated html.!'
    I cannot stop clicking back and forth and then to the end and then all the way back to the beginning. Its fantastic!

    Just look at those icons, just click em, you know you want to!

    Categories: BI & Warehousing

    AZORA – Arizona Oracle User Group meeting January 20th

    Bobby Durrett's DBA Blog - Fri, 2014-12-12 16:25

    AZORA is planning a meeting January 20th.  Here is the link to RSVP: url

    Hope to see you there. :)

    – Bobby

    Categories: DBA Blogs

    Aliases with sdsql and simpler CTAS

    Kris Rice - Fri, 2014-12-12 14:29
    First, we just put up a new build of sdsql.  Go get it or the last thing here will not work.   SQL is a great and verbose language so there's many ways to shorten what we have to type.  As simple as a view or saving a script to call later with @/path/to/sessions.sql  SDSQL is taking it a step further and we added aliases to the tool.  Almost as if right on queue, John asked if we could add them.

    What is SDSQL ?

    Kris Rice - Fri, 2014-12-12 14:29
      SQL Developer is now up to version 4.1 and has had many additions over the years to beef up the sqlplus compatibility.  This is used today by millions of users adding up to millions if not billions of hours in the tool doing their work.  That means our support of core sqlplus has to be very full featured.  One idea we kicked around for a while but never had the time to do was to make our

    SDSQL's flux capacitor

    Kris Rice - Fri, 2014-12-12 14:29
      Writing sql or any code is an iterative process.  Most of the time that means to see what you have done say 5 minutes ago means how big is your undo buffer or better is if you are in SQL Developer there's a full blown history.  If you are in sqlplus, you are basically out of luck. History   SDSQL has built in history and between sessions.  We are still jiggling where it stores the history so

    Getting DDL for objects with sdsql

    Kris Rice - Fri, 2014-12-12 14:29
    Getting ddl out for any object is quite simple.  You can just call dbms_metadata with something nice and easy like select dbms_metata.get_ddl('TABLE','EMP') from dual; SQL> select dbms_metadata.get_ddl('TABLE','EMP') from dual; DBMS_METADATA.GET_DDL('TABLE','EMP') -------------------------------------------------------------------------------- CREATE TABLE "KLRICE"."EMP" ( "EMPNO" NUMBER

    SQL Developer 4.1 EA1 is out

    Kris Rice - Fri, 2014-12-12 14:29
    SQL Developer 4.1 is out for tire kicking.  There's a lot of new things in there as well as some great enhancements.  There's a nice shiny new Instance Monitor to get an overview of what's going on.   Keep in mind this is in flux from a UI stance and the final may be quite different that what you see today.  There's tons of data here and mostly all things have drill downs. Also, we took the

    Notable updates of SUSE Linux Enterprise 12

    Chris Foot - Fri, 2014-12-12 13:03

    Transcript

    Hi, welcome to RDX! Using SUSE Linux Enterprise Server to manage your workstations, servers and mainframes? SUSE recently released a few updates to the solution, dubbed Linux Enterprise Server 12, that professionals should take note of.

    For one thing, SUSE addressed the problem with Unix’s GNU Bourne Again Shell, also known as the “Shellshock” bug. This is a key fix, as it disallows hackers from placing malicious code onto servers through remote computers.

    As far as disaster recovery capabilities are concerned, Linux Enterprise Server 12 is equipped with snapshot and full-system rollback features. These two functions enable users to revert back to the original configuration of a system if it happens to fail.

    Want a team of professionals that can help you capitalize on these updates? Look no further than RDX’s Linux team – thanks for watching!

    The post Notable updates of SUSE Linux Enterprise 12 appeared first on Remote DBA Experts.

    SDSQL - Editing Anyone?

    Barry McGillin - Fri, 2014-12-12 12:05
    Since we dropped our beta out of SQLDeveloper 4.1 and announced SDSQL, we've been busy getting some of the new things out to users.  We support SQL*plus editing straight out of the box, but one thing that was always annoying was the time when you make a mistake and can't fix it to you have finished typing to go back and add a line like this.


    This was always the way as console editors didn't let you move around, the best you could hope for on the command line was a decent line editor and anything above was printed to the screen and not accessible unless through commands like you see here in the images about..

    Well, not any more.  In SDSQL we've taken a look at several things like history, aliases and colors and we've now added a separate multiline console editor which allows you to walk up and down your buffer and make all the changes you want before executing?  Sounds normal, right? So, thats what we did.  Have a look and tell us what you think.