Alison Holloway's Blog

There are a couple of ways to protect the standard iSQL*Plus URL. One way is to use the default setup for the DBA URL and extend this to also protect the standard URL. The other way is to create a new role which protects just the standard URL. You will need to do this setup manually, and here's the instructions on how to do it.

I've written this using a Windows install, but the process is the same for other operating systems. Just change the % to $, and switch the to / in the paths.

Protect both the standard and DBA URLs using the webDba role

The webDba role is already configured in an Oracle database install to protect the DBA URL using basic authentication. To extend this same authentication to include the iSQL*Plus standard URL, perform the following steps: 

1. Edit %ORACLE_HOME%oc4jj2eeoc4j_applicationsapplicationsisqlplusisqlplusWEB-INFweb.xml to add another <url-pattern> element:
   

   <url-pattern>*</url-pattern>

   Your <security-constraint> element should now look like this:

   <security-constraint>
         <web-resource-collection>            <web-resource-name>dba.uix</web-resource-name>
               <description>
               </description>
               <url-pattern>/dba.uix</url-pattern>
               <url-pattern>/dbA.uix</url-pattern>
               <url-pattern>/dBa.uix</url-pattern>
               <url-pattern>/dBA.uix</url-pattern>
               <url-pattern>/Dba.uix</url-pattern>
               <url-pattern>/DbA.uix</url-pattern>
               <url-pattern>/DBa.uix</url-pattern>
               <url-pattern>/DBA.uix</url-pattern>
               <url-pattern>*</url-pattern>
            </web-resource-collection>
            <web-resource-collection> <web-resource-name>DynamicDba</web-resource-name>
               <description>
               </description>
               <url-pattern>/dba/dynamic</url-pattern>
            </web-resource-collection>
            <auth-constraint>
               <role-name>webDba</role-name>
            </auth-constraint>
            <user-data-constraint>
               <transport-guarantee>NONE</transport-guarantee>
            </user-data-constraint>
       </security-constraint>

2. From the directory %ORACLE_HOME%oc4jj2eeisqlplusapplication-deploymentsisqlplus, run the following commands:  
   

   set ORACLE_HOME=<your_oracle_home>
   set JAVA_HOME=%ORACLE_HOME%jdk
   cd %ORACLE_HOME%oc4jj2eeisqlplusapplication-deploymentsisqlplus

   Change <your_oracle_home> to the location of your Oracle Home, for example, C:oracleprogramsora10g.

3. Use JAZN to add a user and assign it a role to access the iSQL*Plus URLs. This is the same as setting up the DBA URL described in the SQL*Plus documentation.   
   

   %JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -adduser "iSQL*Plus DBA" plusdba plusdba
   

   %JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -grantrole webDba "iSQL*Plus DBA" plusdba

   If you want to use the JAZN shell, here's the equivalent commands:

     %JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -shell 

   JAZN>adduser "iSQL*Plus DBA" plusdba plusdba
   JAZN>grantrole webDba "iSQL*Plus DBA" plusdba
   JAZN>exit 
   

   A little handy hint that I discovered while setting this up. If you use the JAZN shell, you need to exit the shell before any changes take place. I had a lot of frustration figuring out this procedure until I worked out this little feature!

   I didn't have to restart iSQL*Plus for this to take effect, but I did have to exit the JAZN shell.

4. Test this has now been set up. Open a browser window and enter the iSQL*Plus URL:

http://<;machine_name>:5560/isqlplus

You should see an authentication dialog displayed. Enter plusdba/plusdba as your login. The iSQL*Plus login screen should be displayed. Close your browser (to remove the basic authentication), and do the same to test the iSQL*Plus DBA login:

http://<;machine_name>:5560/isqlplus/dba

Protect  the standard URL using a new role, and the DBA URL using the webDba role

The other way of setting  up basic authentication for the iSQL*Plus standard URL, is to have two roles, one for the DBA URL, and one for the standard URL. So you can have one login for the DBA URL, which can also access the standard URL, and another login that can only access the standard URL.

As before, we reuse the webDba role which is already configured in an Oracle database install to protect the DBA URL. Then we  create a new role to protect the iSQL*Plus standard URL. Here are the steps you need to perform:

1. Edit %ORACLE_HOME%oc4jj2eeoc4j_applicationsapplicationsisqlplusisqlplusWEB-INFweb.xml to add the another <security-constraint> element:   

   <security-constraint>
            <web-resource-collection> <web-resource-name>login.uix</web-resource-name>
               <description>
               </description>
               <url-pattern>*</url-pattern>
            </web-resource-collection>
            <auth-constraint>
               <role-name>webUser</role-name>
               <role-name>webDba</role-name>
            </auth-constraint>
            <user-data-constraint> <transport-guarantee>NONE</transport-guarantee>
            </user-data-constraint>
       </security-constraint>

   And add another <security-role> element:

    <security-role>
       <description>Privileged access to iSQL*Plus URL.</description>
       <role-name>webUser</role-name>
  </security-role>

2. From the directory %ORACLE_HOME%oc4jj2eeisqlplusapplication-deploymentsisqlplus, run the following commands: 

set ORACLE_HOME=C:ora102
set JAVA_HOME=%ORACLE_HOME%jdk
cd %ORACLE_HOME%oc4jj2eeisqlplusapplication-deploymentsisqlplus

3. Add a the role webUser to the "iSQL*Plus DBA" realm.

   %JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -addrole "iSQL*Plus DBA" webUser  
   

   Add a user to the "iSQL*Plus DBA" realm.

   %JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -adduser "iSQL*Plus DBA" plususer plususer

   Grant the role webUser to the plususer user. 

   %JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -grantrole webUser "iSQL*Plus DBA" plususer

   If you want to use the JAZN shell, here's the equivalent commands: 

   %JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -shell

   JAZN>adduser "iSQL*Plus DBA" plususer plususer
   JAZN>addrole "iSQL*Plus DBA" webUser
   JAZN>grantrole webUser "iSQL*Plus DBA" plususer
   JAZN>exit  
   
   I didn't have to restart iSQL*Plus for this to take effect, but I did have to exit the JAZN shell.

4. Test this has now been set up. Open a browser window and enter the iSQL*Plus URL:

http://<;machine_name>:5560/isqlplus


You should see an authentication dialog displayed. Enter plususer/plususer as your login. The iSQL*Plus login screen should be displayed. Close your browser (to remove the basic authentication), and do the same to test the iSQL*Plus DBA login:

http://<;machine_name>:5560/isqlplus/dba

The user you have just created, plususer, should be able to login to standard iSQL*Plus URL, but should not have access to the iSQL*Plus DBA URL.

When you create a user to access the iSQL*Plus DBA URL (as described in the SQL*Plus User's Guide and Reference), you should also grant the webUser role so it can access the standard iSQL*Plus URL. 

Some Handy Commands

Just to add to this topic, I thought I'd add a few extra commands, to save you some typing, that you might need while setting all this up.

To stop iSQL*Plus:

%ORACLE_HOME%binisqlplusctl stop

To start iSQL*Plus:

%ORACLE_HOME%binisqlplusctl start

To remove the user plususer  using JAZN:

%JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -remuser "iSQL*Plus User" plususer

To list the users using JAZN:

%JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -listusers

To list the  realms using JAZN:

%JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -listrealms

To create the user plusdba in the "iSQL*Plus DBA" realm using JAZN:

%JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -adduser "iSQL*Plus DBA" plusdba plusdba

To grant the role webDba to the plusdba user using JAZN:

%JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -grantrole webDba "iSQL*Plus DBA" plusdba

To grant the role webUser to the plusdba user using JAZN:

%JAVA_HOME%binjava -Djava.security.properties=%ORACLE_HOME%oc4jj2eehomeconfigjazn.security.props -jar %ORACLE_HOME%oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -grantrole webUser "iSQL*Plus DBA" plusdba

It's easy to get confused as to how to specificy your Oracle database connection string, and there's a handy new feature in Oracle 10g that makes this a whole lot easier. So here's a little rundown of the three ways to connect to Oracle databases. You can use the:

• tnsnames.ora file
• Full connection string
• Easy connect string

tnsnames.ora File

The tnsnames.ora file is a client side file that maps an alias used by client programs to a database service. It is used to connect to a non-default database. Here you have to have an entry in the tnsnames.ora file, and reference the alias to that entry in your connection code.

PHP code:

oci_connect($un, $pw, 'MYDB');

tnsnames.ora entry

MYDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = mymachine.mydomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB.AU.ORACLE.COM)) )

Full Connection String

The full connection string does not require the use of a tnsnames.ora file. You need to enter the full connection string when you connect to the database in your code.

PHP code:

oci_connect($un, $pw, '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mymachine.mydomain)(PORT=1521)) (CONNECT_DATA=(SERVER=DEDICATED) (SERVICE_NAME = MYDB)))');

Easy Connect String

This is one Oracle 10g feature that I use daily. As I constantly connect to so many different databases in my day, this has saved me so much time as I don't have to configure anything, just know the machine name and the database alias and I'm off.

The easy connect string does not require the use of a tnsnames.ora file, and is an abbreviated version of the full connection string. you must have the Oracle 10g client-side libraries to use the easy connect string.

PHP code:

oci_connect($un, $pw, '//mymachine.mydomain:port/MYDB');

Introduction

In November 2005 I attended the PHP Conference in Frankfurt. At that conference I did a demonstration which showed how to integrate PHP Web Services using Oracle BPEL, and the Oracle JDeveloper PHP Extension. I have written up that demo for others to get an overview of what I talked about and showed to the conference attendees.

If you have PHP web services you want to integrate into a new web service, or business process, then BPEL is your answer. BPEL lets you orchestrate your disparate web services into new web services. You can even use the BPEL web services as part of another BEPL web service. The beauty of web services is that they can be in a variety of languages, so you can reuse your PHP web services and ones in other languages.

This article gives an overview of the tools you could use to create BPEL processes:

• Oracle JDeveloper 10g PHP Extension - an extension to Oracle JDeveloper 10g to create and edit PHP scripts, with syntax highlighting and sample code to create connections to an Oracle database.
• Oracle BPEL Designer - an extension to Oracle JDeveloper 10g to graphically create and orchestrate BPEL processes.
• Oracle BPEL Console - a web service to start, stop, monitor and debug BPEL processes.
• Oracle BPEL Worklist - a web service to interract with BPEL processes that require user intervention.

These tools help you to quickly get started creating BPEL processes to incorporate your PHP web services into new business processes.

Let's suppose you have a departmental application to approve employee leave requests. A PHP developer has written this application as a web service and managers log into the application to manage the leave requests. The employee leave request table contains a row with the identifier of 1004, which is a leave request for an employee, CJONES. You can see this leave request in the PHP web service used by department managers to approve employee leave requests.

Figure 1, PHP Web Service Consumer Interface

This is a standalone application that you now want to incorporate into a new company-wide business process as a web service. You can use BPEL to do this. Let's now run through the Oracle BPEL and PHP tools.

Oracle JDeveloper 10g PHP Extension

This PHP web service was written using the Oracle JDeveloper 10g PHP Extension. This extension was written by Oracle and gives you, amongst other things, PHP code syntax highlighting, and some snippets of code that help you get started creating connections to an Oracle Database. Here's a look at the PHP Extension in action.

Figure 2, Oracle JDeveloper 10g PHP Extension

The code snippets shown in the Component Palette are included in the PHP Extension and enable to to quickly create database connections and run SQL statements. The Structure pane includes a list of PHP functions and their respective variables.

Although the PHP Extension doesn't give you PHP debugging, it may be useful if you want to write or edit PHP files while creating a BPEL application.

To create a BPEL process which will incorporate this PHP web service, you would use the Oracle BPEL Designer.

Oracle BPEL Designer

The Oracle BPEL Designer is another extension to Oracle JDeveloper 10g. I would suggest installing the Oracle BPEL Designer (Oracle JDeveloper 10g and the BPEL Designer Extension) first, and then add the PHP Extension. There is also an Oracle BPEL Designer extension available for Eclipse, although this doesn't include the Oracle-specific features.

Here's a look at the BPEL Designer interface.

Figure 3, Oracle BPEL Designer Interface

The BPEL Designer gives you a graphical tool to create and orchestrate BPEL processes. For example, you can click and drag in PartnerLinks (links to WSDL files for web services), User Tasks (interraction by a user), add decision making elements such as While loops, Waits, Switches, and throw errors.

The BPEL process shown here includes a Partner Link to a database adapter, called DBRead in Figure 3. This enables you to make direct calls to an Oracle Database to query and update data. This BPEL process queries the database using the leave request ID and returns the row associated with that ID. The BPEL process then manipulates the results using XSLT and XQuery functions to format the query results, and create new variables required by the process.

There is also some decision making in the process. In this case, there is a switch statement which enables different actions to be performed depending on whether the leave request is approved, denied, or an error is generated.

If the employee leave request is approved, the BPEL process will use the PHP web service, called PHPUpdateDB in Figure 3, to delete the row from the departmental table. The BPEL process can then update the main human resources database with the employee leave request information using another web service, or a direct database update using a database adapter.

When the BPEL process is ready to deploy, the BPEL Designer will generate all the required deployment information and deploy it to the BPEL Process Manager Server.

BPEL Process Manager

The BPEL Process Manager is a J2EE application which allows you to start, stop, monitor, debug, and kill BPEL processes. You can monitor the state of all the BPEL processes and drill down to individual SOAP messages. This is useful for debugging processes while you're developing them, as well as monitoring them when they have been deployed.

The BPEL Process Manager can be deployed to a standalone OC4J instance (which is the default deployment option and included in the BPEL Process Manager), or to another J2EE compliant server.

When the BPEL process is deployed, use the BPEL Work Console to start and stop, monitor, and debug BPEL processes.

BPEL Work Console

The BPEL Work Console is used to start, monitor and debug BPEL processes. The BPEL Work Console is a web service and sends a SOAP message to the BPEL process. Using the LeaveRequestID of 1004 and sending the process an XML message will start the process.

Figure 4, Starting a BPEL process using the Oracle BPEL Console Interface

The BPEL process verifies that the LeaveRequestID exists by using a Database PartnerLink (web service) to select a row from the table and retrieve the contents of that row. This is a direct database query and does not use the PHP web service.

When the BPEL process is started, the BPEL Console displays a screen with options to visually track the flow, audit it using XML, or to debug it.

Figure 5, BPEL process monitoring options using the Oracle BPEL Console Interface

The Visual Flow is a very easy way to see what state the process is in, what variables have been passed, what decisions have been made, and what errors may have been generated. In this case, the BPEL process has halted and is waiting for manual intervention at the receiveUpdate... User Task.

Figure 6, BPEL process waiting for user input using the Oracle BPEL Console Interface

You could write an application yourself which would allow you to interract with the BPEL process (web service), or you can use the Oracle BPEL Worklist. The BPEL Worklist is a supplied web service which enables you to interract with BPEL processes that require user intervention.

The BPEL Worklist shows a list of the User Tasks assigned to a manager. In this case, it is the approval of the employee leave request.

Figure 7, Acquiring a task using the BPEL Worklist Interface

The task must be acquired by the manager before any action or decision can be made on it. Once the task is acquired, the manager can approve, reject or escalate the task to a supervisor.

The escalation heirarchy is managed by JAZN (Oracle's implementation of Java Authentication and Authorization Service (JAAS)) and can be either set up using the JAZN interface, or connected to an LDAP or SSO Server.

Figure 8, Approving a task using the BPEL Worklist Interface

When the task is approved, the BEPL process receives a message from the Worklist web service and continues processing. The BPEL process continues through its decision making and processing, assigning variables to whatever is required using simple transformations, or XSLT.

Figure 9, BPEL process execution completed using the Oracle BPEL Console Interface

You can click on any of the events in the process to see the SOAP messages being generated For example, the result of invoking the GetLeaveDetails Partner Link generates the following SOAP message.

Figure 10, DBRead Database Adapter SOAP messages displayed using the Oracle BPEL Console Interface

The SOAP message displays the input and output variables of the message. You can see that the input variable is lrequest=1004, and the output variables are employeeId=CJONES, id=1004, leaveTypeId=7 and noOfDays=4. This message represents a request to the database Partner Link to retrieve the row with the leave request identifier of 1004.

Now that the BPEL process has finished, and the database updated to remove the leave request with an ID of 1004, logging into the PHP web service shows that the database has been updated. The leave request with an ID of 1004 is no longer displayed.

Figure 10, PHP Web Service Interface showing ID 1004 removed from the database

This is a very simple BPEL process and PHP web service, but it shows that you can use BPEL to integrate existing PHP web services with other web services to create new business processes.

Further Information

For further information on BPEL and using PHP with Oracle, go to the following URLs.

Oracle BPEL Process Manager

http://otn.oracle.com/bpel

Service-Oriented Architecture Technology Center

http://otn.oracle.com/webservices

PHP Developer Center

http://otn.oracle.com/php

Zend Core for Oracle

http://www.oracle.com/technology/tech/php/zendcore

Oracle JDeveloper 10g PHP Extension

http://www.oracle.com/technology/products/jdev/htdocs/partners/addins/exchange/php

Chris Jones and I recently travelled to Sydney's first LinuxWorld, and delivered a three hour training course, with colleague Aslam Edah-Tally. We wrote a small guide on installing Oracle XE, Zend Core for Oracle, and using the new PHP OCI8 driver to connect to Oracle. You may find some technical tips in here that you won't find anywhere else, so here's a link to this little gem of a document.

PHPfest Tutorial: Oracle Database 10g Express Edition and Zend Core for Oracle