IT-eye

There are several known methods to get DBA or other potential disastrous system rights due to bugs in the database. Keeping a rigid patching regime and restricting the access rights to packages (Remove those execute to publics! ) goes a long way to secure your Oracle database against these problems. Even with a good security setup misuse is always a possibility either from a new bug not yet known to yourself or a disgruntled employee. Auditing can help you catch these unknowns.

Oracle supplies several auditing mechanisms to fulfill your needs. For this entry I am interested in catching rogue GRANT DBA TO USER statements. These could be done by the appropriate users, system & sys, or via bugs in database packages such as dbms_metadata.

To catch a GRANT DBA or other potential disastrous system grant Oracle has the AUDIT statement. To enable the use of the audit statement the system parameter AUDIT_TRAIL must be set to the value DB,OS or XM(10G + ) . An AUDIT SYSTEM GRANT will then audit all system grants done in the database with the BY USER a specific user can be defined.

I am looking for potential misuse of database packages most of which are run by SYS. The AUDIT statement will, by default, not audit anything done by SYS. To enable SYS auditing the system parameter AUDIT_SYS_OPERATIONS must be TRUE and the AUDIT_TRAIL parameter must contain OS or XML. Being forced to write your entire audit trail to the OS is a good thing. If you where able to write to the DB the SYS user could just erase the SYSTEM.AUD$ table containing all the auditing records.

Having set all the above mentioned parameters doing a GRANT DBA TO EMIEL as sys will give me the following XML record.

<AuditRecord>
  <Audit_Type>4</Audit_Type>
  <Session_Id>4294967295</Session_Id>
  <StatementId>28</StatementId>
  <EntryId>27</EntryId>
  <Extended_Timestamp>2006-02-27T15:50:15.024000</Extended_Timestamp>
  <DB_User>/</DB_User>
  <Ext_Name>ITEYE\ebolwidt</Ext_Name>
  <OS_User>ITEYE\ebolwidt</OS_User>
  <Userhost>ITEYE\LAPTOP_xxx</Userhost>
  <OS_Process>3468:4036</OS_Process>
  <Instance_Number>0</Instance_Number>
  <Returncode>0</Returncode>
  <OS_Privilege>SYSDBA</OS_Privilege>
  <Sql_Text>grant dba to emiel</Sql_Text>
</AuditRecord>

This record will be located in a file per connection in the directory identified by the AUDIT_FILE_DEST parameter. To prevent a disgruntled employee, including the DBA’s, from erasing the audit records from the OS it’s a good idea to restrict access to this a directory to the security employee.

If the AUDIT_TRAIL is set to DB all statements can be found in the DBA_AUDIT_STATEMENT view. Remember that this has the potential misue of a deletion from the SYSTEM.AUD$ table to conceal actions.

Having setup the auditing of all system grants in the database it is imperative that a dedicated person is also looking at the generated audit trails! This person should not be a DBA to the system that is audited.

If you live in a country which is not US or your regional settings are different (in my case I use dutch settings), JDeveloper 10.1.3 recognizes this and a few things such as error messages in ADF are set to your region (if they support the language).

This not a real problem, but it also sets the action bindings in the data control Palette to that language. Still this would not have to be a problem, but in projects I work at we use the default US settings, thus english. Now if I drag for instance a ‘Commit’ to a page, in my case it’s translated to ‘Vastleggen’, and the event is called ‘event_Vastleggen’. Of course I could manually change this to english again in the definition file, but it’s quite annoying to do that all the time for most actions (create, delete etc), plus you manually need to change the generated code in the jsp for instance.

To solve this you could change your regional settings, but that’s not such a good idea. You’d better tell what JDeveloper should use. Find the following file: [PATH_TO_JDEV]/jdev/bin/jdev.conf and at the end add:

AddVMOption -Djbo.default.language=en
AddVMOption -Djbo.default.country=US


In this case set to the default US english settings. This also answers how you could change the default language to something else. You need to restart JDeveloper once done this.

Finally if you wish to use different settings in your application than the default used in JDeveloper, you could set the same properties in the application module configuration.

Sun has released Java SE 6, codename Mustang last week. It contains several new features on several subjects, like webservices, UI and development productivity. On Roger Brinkley’s Blog you can find links to blogs each covering a new feature of Mustang.

Imagine the following situation: you want to store data by ’stacking’ records for trend analysis but mutations in your source data are far too frequent to fit in your schedule. This article describes a possible solution where you don’t need a traditional type 2 / stacking program to insert the historical data correctly by checking on a row-by-row basis.

When making an analysis about how long your employees have been working on a certain location for example you might get source data like this:

Table: Employee_Audit

In your datawarehouse you already have the following information (31-12-9999 means current record in this example):

In this simplified example you can update the target datawarehouse dimension table record by record from the source data but there are cases in which you get tens of thousands of records which have to be checked on their impact on the history one by one. In these cases the Oracle analytic functions can come in handy. You can see that the source data contains a set of records for employee ‘Roelant’ which become obsolete the moment you insert, because during the time of your last DWH upload this particular employee has worked on different locations. The first entry with audit date 09-02-2006 will be closed by the 2nd entry with audit date 14-02-2006 and so on.

Even worse: the date of the 2nd entry should be the (generated) end date for your first record in the DWH to ensure correct historical storage. In fact, this is what you want from your source data:

This actually reflects the correct situation and at any given moment in time you can tell on what location the employee has worked or has been working. The good part is: you can insert this set of data as a whole into your history. The only thing you need to take care of is ‘closing’ any existing records per employee in the datawarehouse (the 31-12-9999 records) because you can’t have two actual records co-existing per employee!

So how do you do it? By using the analytic functions! You can query your source data like this:

SELECT    Name,  Location,  Audit_Date AS Start_Date,
                   LEAD (Audit_Date,1) OVER (ORDER BY Name)-1 AS End_Date
FROM       Employee_Audit

You will now see that the end date is mostly filled with correct values. However, you are not done yet because the definitive end date (i.e. the current record which should have 31-12-9999 as value) is not right. In fact, it has the value of the next record from the table; another employee.

To counter this you should lookup the maximum date per employee and join it to your previous query and incorperate this in your end date calculation, like this:

SELECT  T1.NAME,  T1.LOCATION,  T1.AUDIT_DATE,
	(CASE
	   	WHEN T1.AUDIT_DATE = T2.MAX_DATE
		THEN TO_DATE('99991231', 'YYYYMMDD')
		ELSE LEAD(T1.audit_date,1) OVER (ORDER BY T1.name)-1
		END
	)   AS end_date
FROM       Employee_Audit T1,
	 (SELECT name, max(audit_date) as max_date FROM Employee_Audit GROUP BY name) T2
WHERE     T1.Name= T2.Name

And you’re done!

You can use this if you need to insert large sets of historical data at once. Additional analytic functions such as SUM OVER can be added to calculate running totals which can be used to update and insert ‘state information’ such as account balances and the like. Adding for instance the turnover per employee would be an easy task. You would just have to add/substract the running total with the balance information in your datawarehouse.

Today, I read on Yahoo! that they have released two interesting Ajax libraries: Yahoo! UI library and Yahoo! design pattern libraries.

The UI library consist out of several magic ajax components like drag-and-drop components, auto-completion, breadcrumbs e.a.. All the components in the Yahoo User Interface Library have been released as open source under a BSD license and are free for all uses. The layout of the components can be changed to your own fashion by just modifying the CSS file that comes with the components.

The Design pattern library contains design patterns for implementing common-known UI features to make you webpage more user-friendly. At the moment there are several design pattern on-line. Yahoo! is saying that they will bring up more design patterns in a monthly cycle. You can use the UI library and the design pattern independent of eachother.

Yahoo! has categorized the patterns and UI components in several categories like navigate, customize and explore data. Yahoo! also has openend a new UI blog.

With the release of the libraries Yahoo! is supporting webpage designers and developers to use AJAX to make their page more user-friendly and interactive for users.

Recently, I have made a demo application with EJB3.0 for an in-house presentation about the new EJB version. I used the OracleToplink-based implementation in the JDeveloper 10.1.3 EA release (so not the final release) and the OC4J 10.1.3 DP4 container for the run-time environment. The demo showed the attendance how you easily use EJB3.0 to manage your persistent model and access relational data. In this posting, I want to show how to model and manage a many-to-many relation between two tables with EJB3.0.

The case of my demo was set up by a Player entity, a Club entity and a ContractPeriod entity. The Player and the Club entity have a many-to-many relation with the association entity ContractPeriod. In other words, the data model contains all the information about which player has played/plays for which club at a specific moment of time.

I used Oracle Database 10g Express as the underlying database. The three tables are created with the following scripts:

CREATE TABLE  "CONTRACTPERIOD"
   (	"ID" NUMBER NOT NULL ENABLE,
	"PYR_ID" NUMBER NOT NULL ENABLE,
	"CUB_ID" NUMBER NOT NULL ENABLE,
	"START_DATE" DATE NOT NULL ENABLE,
	"END_DATE" DATE,
	 CONSTRAINT "CONTRACTPERIOD_PK" PRIMARY KEY ("ID") ENABLE,
	 CONSTRAINT "CONTRACTPERIOD_FK" FOREIGN KEY ("PYR_ID")
	  REFERENCES  "PLAYER" ("ID") ON DELETE CASCADE ENABLE,
	 CONSTRAINT "CONTRACTPERIOD_FK2" FOREIGN KEY ("CUB_ID")
	  REFERENCES  "CLUB" ("ID") ON DELETE CASCADE ENABLE
   )

CREATE TABLE  "CLUB"
   (	"ID" NUMBER NOT NULL ENABLE,
	"NAME" VARCHAR2(100) NOT NULL ENABLE,
	"CITY" VARCHAR2(100) NOT NULL ENABLE,
	"COUNTRY" VARCHAR2(100) NOT NULL ENABLE,
	 CONSTRAINT "CLUB_PK" PRIMARY KEY ("ID") ENABLE,
	 CONSTRAINT "CLUB_UK1" UNIQUE ("NAME", "COUNTRY") ENABLE
   )

CREATE TABLE  "PLAYER"
   (	"ID" NUMBER NOT NULL ENABLE,
	"FIRSTNAME" VARCHAR2(30) NOT NULL ENABLE,
	"LASTNAME" VARCHAR2(30) NOT NULL ENABLE,
	"NATIONALITY" VARCHAR2(30) NOT NULL ENABLE,
	"BIRTHDATE" DATE NOT NULL ENABLE,
	 CONSTRAINT "PLAYERS_PK" PRIMARY KEY ("ID") ENABLE
   )

Using the wizard in JDeveloper I could easily generate three basic EJB3 entity POJOs representing the three tables in the database. The EA release of JDeveloper 10.1.3 did not facilitate an option to automatically generate code that define the relationships between your entities even the EJB diagram is not supported for EJB3.0 in JDeveloper EA 10.1.3 (also in the final release). Therefore, I have to do it all by myself. Thanks to the persistence annotations in EJB3.0 this was not much work. In the code-box below, I show the code fragments in each entity bean which implements the many-to-many relation between the Club and Player entities using ContractPeriod as the association table.

//Player
@Entity
@Table(name = "Player")
public class Player implements Serializable{
...
    private Collection clubs
    @ManyToMany(fetch=EAGER,targetEntity="nl.iteye.contractadmin.Club")
    @AssociationTable(table = @Table(name = "CONTRACTPERIOD")
                      ,
                      joinColumns = {@JoinColumn(name = "PYR_ID",
                referencedColumnName = "ID")}
                                        ,
                      inverseJoinColumns = {     @JoinColumn(name = "CUB_ID",
                referencedColumnName = "ID")
                                                                   } )
    public Collection getClubs() {
        return clubs;
    }

    public void setClubs(Collection<Club> clubs){
        this.clubs=clubs;
    }
...
}

//Club
@Entity
@Table(name = "CLUB")
public class Club implements Serializable{
...
    private Collection players
    @ManyToMany(mappedBy = "clubs", fetch = EAGER,  targetEntity="nl.iteye.contractadmin.Player")
    public Collection getPlayers() {
        return players;
    }

    public void setPlayers(Collection<Player> players){
        this.players=players;
    }
...
}

In the code-fragment above you can see that you only have to define the relationship properties at the owning side and by using mappedBy annotation you can refer to it on the inverse side. There is also no extra code needed in the ContractPeriod entity class, the AssociationTable annotationspecifies the use of the ContractPeriod entity as the association table for this relation

A few months ago I tried out JDeveloper 10.1.3 EA1 for JSF. As O/R layer I thought let’s try out the new Hibernate 3 (I always used 2 before then) with its unchecked exceptions.

So I configured a JDeveloper project to be ‘Hibernate’ enabled and tried out my first steps with JSF. Beside the new things in JSF, as soon as I tried to perform a query in Hibernate it crashed with a weird exception. First not knowing what was wrong, I looked over and over again to the configuration files. But really nothing was wrong, so I fell back to the classic Hibernate 2 and it worked fine. After some googling I learned Hibernate was using a different antlr.jar version than JDeveloper and apparently there were some differences in it. Back then I resigned my tryout as I had other things to do.

But now that the final release of JDeveloper 10.1.3 is out, I thought let’s try again. Unfortunately it still gives the same exception. This time I replaced the antlr.jar, which is located in: [DIRECTORY_JDEV]/toplink/jlib with the one provided by the Hibernate 3 distribution (in my distribution called: antlr-2.7.6rc1.jar, make sure to rename it to antlr.jar). It all works just fine now. You should first close JDeveloper to do this and I suggest you make a backup of the original file that comes with toplink.

At least you can continue your work now …