Blocking Oracle Reports Sensitive URLs

The following Oracle Reports rwservlet servlet urls should be available only from the server(s) that host the report server.

• /reports/rwservlet/showenv
• /reports/rwservlet/showmap
• /reports/rwservlet/showjobs
• /reports/rwservlet/getjobid7?server=myrep

To secure the above urls, the following Location directives should be inserted in apache configuration file reports_ohs.conf.

########### Apache configuration to block Oracle Reports rwservlet ###########
# New Locations:
<Location /reports/rwservlet/[sS][hH][oO][wW][mM][aA][pP]*>
    Order deny,allow
    Deny from all
    Allow from localhost 
</Location>
<Location /reports/rwservlet/[gG][eE][tT][sS][eE][rR][vV][eE][rR][iI][nN][fF][oO]*>
    Order deny,allow
    Deny from all
    Allow from localhost 
</Location>
<Location /reports/rwservlet/[sS][hH][oO][wW][jJ][oO][bB][sS]*>
    Order deny,allow
    Deny from all
    Allow from localhost 
</Location>
<Location /reports/[rR][wW][sS][eE][rR][vV][lL][eE][tT][sS][hH][oO][wW][jJ][oO][bB][sS]*>
    Order deny,allow
    Deny from all
    Allow from localhost 
</Location>
<Location /reports/rwservlet/[sS][hH][oO][wW][mM][yY][jJ][oO][bB][sS]*>
    Order deny,allow
    Deny from all
    Allow from localhost 
</Location>
<Location /reports/[rR][wW][sS][eE][rR][vV][lL][eE][tT][sS][hH][oO][wW][mM][yY][jJ][oO][bB][sS]*>
    Order deny,allow
    Deny from all
    Allow from localhost 
</Location>
<Location /reports/rwservlet/[sS][hH][oO][wW][eE][nN][vV]*>
    Order deny,allow
    Deny from all
    Allow from localhost 
</Location>
<Location /reports/rwservlet/[hH][eE][lL][pP]*>
    Order deny,allow
    Deny from all
    Allow from localhost 
</Location>
<Location /reports/[rR][wW][sS][eE][rR][vV][lL][eE][tT][hH][eE][lL][pP]*>
    Order deny,allow
    Deny from all
    Allow from localhost 
</Location>
###################################

Security Considerations for Java Web Applications

1. Protecting against data awareness. 
   Problem: Users become "aware" of system processes and data (such as primary keys) by reading application URLs.  When this happens, users can start Copying and Pasting URLs to execute actions.
   Remedy: Avoid GET requests that include data on the URL.  Always use POST requests. Also, on every request, check is user has access to the requested page, action, data and mode (edit/view)
   
   
2. Prevent SQL Injection attacks. 
   Problem: Read here for an Overview of SQL injection attacks
   Remedy: Always use parameters when sending SQL commands from code. Never concatenate sql strings.
   
   
3. Ensure that Form submissions come from the current user (1).
   Problem: Read here for an Overview of CSRF attacks
   Remedy: Use CSRF tokens to ensure that form submissions come from the same session. At post of form data, check that CSRF token is the same that was specified when form was rendered for input. This can be done via a csrf token hidden variable.
   
   
4. Ensure that Form submissions come from the current user (2).
   Scenario:  User "A" logs in, and access a form input page.  He/She fills in the form, but does not submit it. Then user "B", on the same browser presses Ctrl+N, opens a new window and logs in.   Now both windows in browser have user "B" as the username.  If form that was filled in from user "A" is submitted, the system will log user B as the submitter.
   Remedy: On every page render, store the username in a javascript variable.  Add javascript call to the focus event of browser windows that reads the username stored in the username cookie and compares it with the the username in a javascript variable.  If the usernames do not match, it means that the username has changed from another window. Note: this method is possible since cookies update on the client (browser) when changed even if no server interaction takes place.
   
   
5. Protecting against Back/Forward buttons
   Scenario:  User "A" logs in, and access a high privileged page, then logs out. Then user "B", a limited user, logs in on same browser. If user "B" presses multiple times the back button of browser, he/she will be looking at the privileged page user "A" was looking before loging out.
   Remedy: The application's login process should set the current username in a cookie and the session. Then, on every page rendered by the application an onload javascript calls a servlet which checks that there is a current user in the session, and that the username of the session is the same as the username sent by the AJAX call. If the usernames don't match, or there is no username in the session, redirect page to login page of application.
   
   
6. Protecting against Session information spillover
   Problem: This refers to the possibility of session data from a previous session be present in the next session opened in the same browser window.
   Remedy: The application should have a log out process, and a link to execute this process clearly visible on every page.  Make sure that Logout servlet invalidates session, and starts a new one. In java, call request.getSession(true). Also any login cookies should be cleared.
   
   
7. Cross-site Scripting (XSS)
   Problem: Read here for Cross-site Scripting (XSS) attacks
   Remedy: In short: never trust user's input.  Input must be validated against strict rules.  Output should be html-escaped. More here: http://www.veracode.com/security/xss and at the OWASP related page.

Much Needed Crystal Reports Improvements

1. Upgrade code
   Currently, code is VB6.  Should be upgraded to .NET to accept nullable parameter variables.
2. Text Definition Files.
   RPT files are currently in binary format. This excludes report generation using code generation tools, and fixing Crystal Report nuances by editing the definition file.
3. Modify Order Of Parameters on Prompt Screen.
   You define Parameter A and then you define Parameter B, and setup your selection formulas. Then, you decide that Parameter B should come before Parameter A on the parameters prompt screen that crystal shows before printing the report. You're in trouble. There is currently no way to modify the order of the parameters prompt. You will have to remove references of Parameter A from all formulas and then remove the Parameter. This will bring Parameter B first. Then you add Parameter A again and setup again all formulas you removed.

Restoring an SQL Server Database that is in use

Often we have to restore a database in sql server but that is in use.  The script below enables restore of database even if it is use. 


Adjust for your needs and use at your own risk.

USE [master]
GO
if exists (select * from sysdatabases where name='MY_DATABASE') begin
print 'taking database MY_DATABASE to single mode'
ALTER DATABASE MY_DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
end
GO
if exists (select * from sysdatabases where name='MY_DATABASE')
begin
print 'drop MY_DATABASE'
DROP DATABASE MY_DATABASE
end
go
USE [master]
GO
RESTORE DATABASE MY_DATABASE FROM
DISK = N'C:\MSSQL\Backup\MY_DATABASE_backup.bak' WITH FILE = 1,
MOVE N'DataTemplateSQL_dat' TO N'c:\MSSQL\DATA\MY_DATABASE.mdf',
MOVE N'DataTemplateSQL_log' TO N'c:\MSSQL\DATA\MY_DATABASE.LDF',
NOUNLOAD, STATS = 10
GO
use MY_DATABASE
go

Encrypting WebLogic passwords for WLST scripts

WLST scripts can be utilized to start, stop and restart managed web logic servers. These scripts require that a connection is established to the local or remote node manager and usually the password is included as plain text inside the script.
 To eliminate this security risk, node manager passwords can be encrypted to the file system, as per instructions at page: http://docs.oracle.com/cd/E15523_01/web.1111/e13813/reference.htm .  The python script that follows can be used to encrypt the node manager passwords. This script must be saved in %MW_HOME% directory and must be executed when passwords change.  Important note: The text in red below is the username and password of the WebLogic server. Before execution of the script, they must be changed to the actual values, and cleared after the execution.  Copy the code below and paste in a new file createUserConfig.py, remember that you have to change the username and password as explain above.

"""
This script stores username AND password of node manager in 
encrypted files.
To run it, execute: %MW_HOME%\wlserver_10.3\server\bin\setWLSEnv.cmd
%JAVA_HOME%\bin\java weblogic.WLST
wls:/offline> execfile('D:\oracle\Middleware\createUserConfig.py')
"""
import os
ucfile=os.environ['MW_HOME'] + '\\userconfigNM.secure'
uckey=os.environ['MW_HOME'] + '\\userconfigNM.key' 

nmConnect('weblogic', 'welcome1', 'localhost','5556','ClassicDomain')
storeUserConfig(userConfigFile=ucfile, userKeyFile=uckey,nm='true')
nmDisconnect()

nmConnect(userConfigFile=ucfile, userKeyFile=uckey, domainName='ClassicDomain', port='5556')

print '################################################################'
print '****** REMEMBER TO CLEAR THE username and password from line 11 '
print '################################################################'

Two Primary Key design principles

During my years of database design, I found 2 things that always make my life easier when it comes to primary keys:

1. I recommend to always use Surrogate (otherwise known as "dummy") primary keys. Never assign a "meaningful" column to be the primary key of a table. On Oracle, these are implemented by using sequences and before-insert triggers. In Microsoft's world, SQL Server and MS Access have the auto increment columns. I was once involved in a project where the Id Number was the primary key of an an "Individual" table. The designers took the word of the business people that an individual's Id Number never changes, so they assigned this field to the primary key of the table. Then they built a system of 300 tables, and about 100 of those had a foreign key referencing the above table/field. And of course after a couple of years in production, a case came in where an individual had lost their ID Number and a new one was issued. This was a nightmare: We had to write oracle scripts that dropped the foreign keys, do the update and then re create the foreign keys.
   The rule is, as long as the the primary key column has some meaning to the user, then this column may change and databases do not generally like updates on primary key values. There are ways to do it, I know, but why go through the trouble? Just always use Surrogate keys and you will be free from issues like changing or removing records by primary keys.
   Another reason to use surrogate primary keys is if you have some kind of user interface framework that inserts/updates/deletes rows, usually these frameworks work by identifying rows by primary keys. In case you do not have surrogate/dummy keys in your tables, you user interface can seriously mess up your data. Consider this scenario:
   
   1. User requests to edit record where id number equals '1000'. The system finds it by issuing the appropriate sql where clause ( "WHERE ID_NUMBER = 1000") and shows it on the screen.
   2. The user updates the data on the screen, and then mistakenly enters '1001' in the id number text box. She then submits the changes
   3. The system will then update using where clause "where id number='1001'" since it was changed by the user on the screen, and mistakenly update record 1001 instead of 1000! If you thing this is far fetched, think again! It happened within the first couple of days of UAT for a system I was involved in migrating from Informix to Java 2EE/Oracle. We ended up using the Oracle ROWID instead of the primary key to identify records for the user interface. Probably the best solution to begin with, but had the database use surrogate primary keys then we would not have a problem since user interface users would never see or be able to change the primary key value.
2. I recommend to Never use composite primary keys. Even if it makes sense from a normalization point of view, I always use a a SINGLE sequence/auto increment column to be the primary key of the table. This helps greatly when mapping the database table to some kind of Object Relation Mapping tool. Also, if again you will have to use a User Interface framework to manage your rows, having a sigle primary key column helps to keep the where clauses that identify rows short and easy to read and maintain. As an example, consider the case where you have a many to many relationship between an INDIVIDUAL table and a VEHICLES table. In other words, an individual can own many vehicles, and a vehicle can be owned by many individuals.   Additionally, a vehicle cannot be owned by an individual on the same date.  If we use a composite key, we will end up with something like this:
   
   
   
   
   
   With the above design, in order to identify a record for selects updates and deletes, your WHERE clauses have to include all 3 fields that logically define a unique record: SELECT * FROM VEHICLE_OWNER WHERE VEH_ID=:1 and IND_ID=:2 and OWN_START_DATE=:3
   Alternatively, you can use a single surrogate column for the primary key. You can then add a unique constraint to the table on the 2 foreign keys and the OWN_START_DATE field. Here is the design:
   
   
   
   
   With the above design, your WHERE clauses are kept short and maintanable:   SELECT * FROM VEHICLE_OWNER WHERE VEH_OWNER_ID=:1
   In addition, if in the future you introduce another table which needs a foreign key to VEHICLE_OWNER, you can easily extend your design since you only have a single field as the primary key. You simply add the VEH_OWNER_ID to that new table and create the foreign key. Had you use composite fields as the primary key, then you would have to drop the composite primary key, add a new column, populate values and create a single field primary key.

The Single Responsibility Principle (SRP)

Simply put: A class should have one and only one task to perform. Or another way to put it: A class should have one and only one reason to exist. Object Mentor says it (and describes it) best at http://www.objectmentor.com/resources/articles/srp.pdf

The single responsibility principle is one of those things that sounds too theoretical. How can a class have just a single task to do? What if my system has 1000 tasks to be performed, do I have to create 1000 classes? Well, YES :-). And I am here to tell you that this principle works, in practice.

What did I gain by following this principle?

1. Improved the maintainability of my code: Since a class does only one thing, I can touch it w/o breaking any code that does other things. Simple :-)
2. Improved the extensibility of my code:

And the drawbacks?

1. The so called 'Class Explosion'. Your application may end up with too many classes to manage.
2. Yes, I can change code in classes without worrying too much about affecting other code, but first I have to find the code I need to change. And with myriad of classes it can get tricky to pinpoint what you want to change