Analyze This

In the good old days you analyzed a table using a statement along the lines of: -

ANALYZE TABLE tablename COMPUTE STATISTICS;

With the advent of Oracle 8i the DBMS_STATS utility was introduced to gather object statistics. This is Oracles preffered method of gathering the information.

Using it the equivalent of the above analyze statement would be: -

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SYSADM',TABNAME=>'tablename',CASCADE=>TRUE);

This should be executed after the table you need to analyze has been populated.

To cut back on the time it takes to execute, you can limit the number of rows it uses in its stats gathering by adding estimate_percent => nn (where nn is a number between 1 and 100) this will only use the given percentage of the total rows in the table when performing its anaysis.

e.g.

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SYSADM',TABNAME=>'tablename',CASCADE=>TRUE, estimate_percent => 20);

Will only use 20% of the tables rows. The higher the percentage, the better the analysis, but the longer it will take. When used Oracle randomly selects the rows it uses from the table to make them more representative.

N.B. This statement doesn't work from Oracle Developer (I've not tried it from Toad). As such execute it from SQL Plus instead.

Listing a tables Indexes

select index_name
from dba_indexes
where table_name=<table_name>

Useful SQL Plus Settings

On occaisions when you're working at an Oracle site with no access to sensible SQL development tools, such as Toad or Oracle Developer (available as a free download from the Oracle website, but no good if you can't download or install it), you are forced to use that most heinous of applications SQL Plus. To make life a bit more palatable, try using the following settings to brighten up its drab and dreary demeanor.

/* SQL Plus parameters required for each session */
select name from sys.v_$database;
alter session set current_schema=SYSADM;
set timing on;
set sqlprompt dbasename> ;
set linesize 150;
set null empty;
set pagesize 500;
set pause on;
set space 1;
set time on;
set trim on;
set wrap off;
set sqlnumber on;
set describe line on;

Enjoy !!!!

Specifying the report directory in PeopleSoft

To change the directory that Process Monitor tries to open report output in PeopleSoft (logs, trace files etc.) navigate to the Report Node definition page: -

Home > PeopleTools > Process Scheduler > Report Nodes

From here you can specify the URL, URI Port and URI Resource used to open report output in PeopleSoft.

Changing the PS, Application Server Process Scheduler Password

With a vanilla PeopleSoft installation, one of the first things you should do is change the password for the adminstrator account PS from the default. This post details what you need to do to accomplish this and offers a number of hints and tips along the way.

Backup First

Before making these changes in production I would advise taking a full backup of the system first. And making sure that the backup was successful.

Chosing a new PS Password

As this account gives you carte blanche access to the PeopleSoft system, care should be taken when changing it to ensure that the password chosen is as strong as possible. The new password should: -

• Use a minimum of 7 characters and a maximum of 8 characters in length.


• Use only mixed case alpha and numeric characters.


• Avoid common names, words, and anything obvious, such as your Operator ID. It should also be significantly different from your most recent passwords.

For further suggestions regarding password selection please refer to my previous article The Art of Creating a Password.

Changing the Password

• To change the password you need to: -


• Change the PS Password through the web;


• Change the Application Server password using PSADMIN;


• Change the Process Scheduler Passwor using PSADMIN.

Changing the PS Password

Log in to PeopleSoft through the web.

Navigate to: -

Home > PeopleTools > Security > User Profiles > User Profiles

And change the password for PS.

Note that in some installations a seperate PSAPPS user account is used by the Application Server and Process Scheduler. To detemine if this is so, have a look at their configuration files psappsrv.cfg and psprcs.cfg respectively. These will contain the ID and password used to connect to PeopleSoft. If PSAPPS is used then you should change it in the same manner as you changed PS.

Change the Application Server Password

There are two ways you can change the App Server password. You could directly edit the configuration file, named above, or use PSADMIN. As direct editing can cause problems if you encrypt it, I would reccoment you use PSADMIN.

Configure the Application Server

First select Application Server from the main menu to got to the Application Server Administration menu.

Then select Administer a domain and pick the domain you want to change.

Now select configure this domain and answer 'y' when it says that 'this option will shutdown the domain do you want to continue' and 'y' when it asks if you want to change any config values.

The first thing it will ask is if you want to change any startup values, answer 'y' to this.

Continue to press enter untill you get to UserPswd then enter you new PS or PSAPPS password as apropriate.

When it asks if you want to encrypt this password answer 'y'. This will mask it in the config file and make it less easy to hack.

Then keep hitting return to take the default values until configuration is complete.

Restart the Application Server

To restart the saerver with the new password, go to the Domain Administration menu and select 'Boot this domain'.

Change the Process Scheduler Password

Configure the Process Scheduler

First select Process Scheduler from the main menu to got to the Process Scheduler Administration menu.

Select 'Configure a Process Scheduler' and choose the database you want to change from the list displayed.

When told 'This option will shutdown the process scheduler. Do you want to continue' answer 'y'.

As with the Application server the Startup section will appear early on. When asked, enter 'y' when asked 'Do you want to change any values'.

When UserPswd appears enter 'y' to change it and enter the new PS or PSAPPS password.

Again I would advise you to encrypt this password, so enter 'y' when asked.

Keep hitting return to take the default values until configuration is complete.

Restart the Process Scheduler

Got to the Process Scheduler Administration screen and select 'Start a Process Scheduler Server'.

Select the apropriate database from the list displayed.

SQL to find Rules and associated Application Engine Sections

Get the AE Section name from the Rule ID.

select AE_SECTION
from PS_TL_RULE_DEFN
where TL_RULE_ID = 'XXX'

Get the Rule ID from the AE Section name.

select TL_RULE_ID
from PS_TL_RULE_DEFN
where AE_SECTION = 'XXX'

Nationality in PeopleSoft

I was recently asked how you could store Nationality in PeopleSoft. Some companies use birth country to hold an employees nationality, which may or may not be the same, but PeopleSoft now allows you to hold this information under Citizenship.

Home > Workforce Administration > Personal Information > Citizenship, Identification Data > Citizenship/Passport

Under citizenship you can hold country and details of a persons passport.  As an employee can have multiple  countries on this page it also caters for the possibility of dual nationality.

Associated Tables: PS_CITIZENSHIP and PS_CITIZEN_PSSPRT

Note that the same tables are also used to hold citizenship details for dependants.

Current XLAT Values for a Field

The following field selects current active values for a field from the XLAT table.

-- ---------------
-- Dump XLAT Table
-- ---------------
select XLAT.FIELDVALUE,
       XLAT.XLATLONGNAME,
       XLAT.XLATSHORTNAME
  from XLATTABLE_VW XLAT
 where XLAT.FIELDNAME    = 'EMPL_STATUS'
   and XLAT.EFFDT = (select max(XLAT1.EFFDT)
                       from XLATTABLE_VW XLAT1                    
                      where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                        and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                        and XLAT1.EFFDT <= SYSDATE)
   and XLAT.EFF_STATUS = 'A'

Finding The Current Database Name

Often you want to find out what the database is that you are logged in to. This can be useful when spooling the output of a SQL script to a log file or checking you are in the right environment before executing a script.

Oracle
In Oracle you can get the current database name by querying the table v_$database

i.e.

select name
 from sys.v_$database

SQL Server
The equivalent way of finding it in SQL Server is to use the function DB_Name.

e.g.

select DB_Name()

Finding a table name in SQL Server

To find a table name in SQL Server query the table sysobjects.

For example to list all PeopleSoft tables on the system, you could use: -

select *
from sysobjects
where name like 'PS_%'

PeopleSoft Payroll Interface Timeout Issue

I recently came across a problem where a vanilla payroll interface was failing due to a timeout occurring. The error message given was: -

13884 08.28.58 300.014 ErrorReturn-> -1 - Child program F:\psoft8\HR88\HR8DEV\CBLBINA\PIPINRUN REMOTEA/MICROSFT/HR8DEV/ASTOLDT/////0/people/%PSWD%/9aec1b63-2af2-11dc-86cf-cbed13adba68 (5568) did not complete in allowed time period (300 seconds). Killing process. (2,-1) At PI_INRUN.2EXPORT.GBL.default.1900-01-01.Step01.OnExecute PCPC:263 Statement:1

On investigation I found that the solution was to change two timeout values in two configuration tables.

psappsrv.cfg
[PSAPPSRV]
;=========================================================================
; Settings for PSAPPSRV
;=========================================================================

;-------------------------------------------------------------------------
; UBBGEN settings
Min Instances=1
Max Instances=3
Service Timeout=300

psprcs.cfg
[PSAPPSRV]
;=========================================================================
; Settings for PSAPPSRV
;=========================================================================

;-------------------------------------------------------------------------
; UBBGEN settings
Min Instances=1
Max Instances=3
Service Timeout=300

In both instances I changed the timeout value from 300 to 0 (no limit) before using PSADMIN to stop and restart the Application Server and Process Scheduler. The net result was that the interface ran to a successful completion when re-run.

Huzzah!!!

PeopleSoft Payroll Interface SQL

/****************************************/
/*                                      */
/* Dump the Payroll Interface Run Table */
/*                                      */
/****************************************/
select *
from PS_PI_RUN_TBL
order by PI_RUN_NUM desc

/*************************/
/*                       */
/* Dump PI Partic table. */
/*                       */
/*************************/
select *
from PS_PI_PARTIC

/*************************************/
/*                                   */
/* Dump PI Partic table, latest run. */
/*                                   */
/*************************************/
select PIP.*
from PS_PI_RUN_TBL PUN,
     PS_PI_PARTIC  PIP
where PUN.PI_RUN_NUM = (select max(PUN1.PI_RUN_NUM)
                         from PS_PI_RUN_TBL PUN1)
 and PUN.PI_RUN_NUM = PIP.PI_RUN_NUM

/*******************************/
/*                             */
/* Dump PI PARTIC detail table */
/*                             */
/*******************************/
select *
from PS_PI_PARTIC_EXPT

/********************************************/
/*                                          */
/* Dump PI Partic detail table, latest run. */
/*                                          */
/********************************************/
select PIE.*
from PS_PI_RUN_TBL PUN,
     PS_PI_PARTIC_EXPT PIE
where PUN.PI_RUN_NUM = (select max(PUN1.PI_RUN_NUM)
                         from PS_PI_RUN_TBL PUN1)
 and PUN.PI_RUN_NUM = PIE.PI_RUN_NUM

/***************************************************/
/*                                                 */
/* Dump Pay Calendars associated with a particular */
/* Payroll Interface run.                          */
/*                                                 */
/***************************************************/
select RUN.PI_RUN_NUM,
     CAL.*
from PS_PI_RUN_TBL RUN,
     PS_PAY_CALENDAR CAL
where RUN.PI_RUN_NUM = 999
 and RUN.RUN_ID     = CAL.RUN_ID

/******************************/
/*                            */
/* Dump the PI Empl ID Table. */
/*                            */
/******************************/
select *
from PS_PI_EMPLID_TBL

/********************************************/
/*                                          */
/* List fields in a Payroll Interface File. */
/*                                          */
/********************************************/
select DEF.PI_FIELD_SEQ,
     DEF.PI_FIELD_ID,
     FLD.DESCR,
     FLD.PI_FIELD_LEN,
     FLD.PS_FIELD1_NM,
     DEF.PI_KEY_FIELD,
     DEF.PI_GROUP_ID
from PS_PI_DEFN_FIELD DEF,
     PS_PI_FIELD_TBL  FLD
where DEF.PI_FILE_ID = 'XXX'
 and DEF.EFFDT        = (select max(DEF1.EFFDT)
                           from PS_PI_DEFN_FIELD DEF1
                          where DEF.PI_FILE_ID = DEF1.PI_FILE_ID
                            and DEF1.EFFDT    <= SYSDATE)    and DEF.PI_SYSTEM_ID = FLD.PI_SYSTEM_ID    and DEF.PI_FIELD_ID  = FLD.PI_FIELD_ID    and FLD.EFFDT        = (select max(FLD1.EFFDT)                              from PS_PI_FIELD_TBL FLD1                             where FLD.PI_SYSTEM_ID = FLD1.PI_SYSTEM_ID                               and FLD.PI_FIELD_ID  = FLD1.PI_FIELD_ID                               and FLD1.EFFDT      <= SYSDATE)

/***************************************************/ 
/*                                                 */
/* List interface files that contain a given Field */
/*                                                 */
/***************************************************/
select DEF.PI_FILE_ID,
        DEF.PI_FIELD_SEQ,
        DEF.PI_FIELD_ID,
        FLD.DESCR,
        FLD.PI_FIELD_LEN,
        FLD.PS_FIELD1_NM, 
        DEF.PI_KEY_FIELD,
        DEF.PI_GROUP_ID
   from PS_PI_DEFN_FIELD DEF,
        PS_PI_FIELD_TBL  FLD
  where DEF.PI_SYSTEM_ID = 'CHR'
    and DEF.PI_FIELD_ID IN ('E001')
    and DEF.EFFDT        = (select max(DEF1.EFFDT)
                              from PS_PI_DEFN_FIELD DEF1
                             where DEF.PI_FILE_ID = DEF1.PI_FILE_ID
                               and DEF1.EFFDT    <= SYSDATE)
    and DEF.PI_SYSTEM_ID = FLD.PI_SYSTEM_ID
    and DEF.PI_FIELD_ID  = FLD.PI_FIELD_ID
    and FLD.EFFDT        = (select max(FLD1.EFFDT)
                              from PS_PI_FIELD_TBL FLD1
                             where FLD.PI_SYSTEM_ID = FLD1.PI_SYSTEM_ID
                               and FLD.PI_FIELD_ID  = FLD1.PI_FIELD_ID
                               and FLD1.EFFDT      <= SYSDATE) 

Pizza Pie

After completing my two posts on password security (The Art of Creating a Password and the imaginatively titled The Art of Creating a Password Part 2), I was inspired to go through my own passwords and make them as strong as possible. Eventually I arrived at the one I use to login to the Dominos Pizza website for my occaisional fix of Pepperoni and Green Peppers, Mushrooms, Olives, Chives. Logging in to my account I attempted to change my existing password, only to be presented with an error message along the lines of you couldn't change your details once an order had been started. E-mailing Dominos technical support I was later told that the only way to change it was to get them to delete my account so that I could create it from scratch with a new password.

My suspicion is that Dominos not only sponsor the Simpsons, they also hired Homer to design their website.

Doh!!!

The Art of Creating a Password Part 2

In my last post (cue trumpet solo) I described some techniques you can use to come up with a strong password. A strong password, as opposed to its weedy cousin the weak password who gets sand in his face when kicked to the ground, is very difficult for a hacker to crack and so ideal to protect your valuables on-line.

Unfortunately your man of steel password could be as safe as a woman of kleenex if your PC becomes infected by a keylogger. Not someone who cuts down trees and skips and jumps, a key logger is a piece of software that sits on your PC and transmits every keystroke you enter back to the criminal who wrote it. So as you smuggly enter your 15 character super password, with more special characters than an episode of Heroes, a vilain with an even smugger smile on their face could be receiving it on the other side of the world.

First and foremost, you should protect yourself from spyware and malware in general to the best of your ability (see my post Tinker, Taylor, Soldier, Spyware for details of how to do so). However, even with the best protection in the world, there is still the chance that something slips under your radar. So what do you do?

The solution to this problem is to use a password manager. A password manager can sidestep keylogging by storing all your passwords in an encrypted file on your PC then entering them via a virtual keyboard. As the keyboard is never used the key logger wouldn't get a sniff of the details you enter. Hurrah !!!

Reccomendation

A few years ago I spent a significant amount of time burning the midnight oil and researching password managers on-line, reading reviews, downloading and using trial versions and ending up with rings round my eyes so I looked like a panda. Finally I settled on a product from Siber Systems called Roboform.

Features

The features that sold me on Roboform initially and still have me using the product today are:

• Security - Roboform stores your login details in an encrypted file using 256 bit encryption using AES, Blowfish, RC6, 3-DES or 1-DES algorithms. The Pentagon may be able to crack it, but your average hacker probably wont.

• Single Click Web Page Entry - Roboform has the ability to login to a website with a single click, and to poplulate personal information fields, such as address and phone numbers, again with a single click. It can also fill in card details, warning you when it does so. Again this information is encrypted on your PC and again it wont fall foul of a keylogger.

• Multi-Browser support - A Roboform plugin works with Firefox and IE. For unsupported browsers you can still use it by drag and dropping your information into the relevant fields on the webpage.

• Safenotes - Another feature of Roboform is the safenote. A safenote is a free format note that is encrypted using the same algorith as everything else. This means, for example, if there was sensitive information you wished to keep on your PC you could enter it into a safenote to stop it being hacked.

• One password to rule them all - Roboform has one master password which it uses as the encryption key for the data you store. As such you only need to remember a single password to access all your others. This password should be strong and I would reccomend you follow the techniques I previously outlined in The Art Of Creating A Password. If you just need to remember one password, make it a good one.

• Automatic password generation - One useful utility included in Roboform is the facility to automatically generate a password. This allows you to specify the length of password you want and the type of characters you want to include (alpha, numeric, special). Press generate and a password is randomly generated to your specification. As you can imagine this tool can provide you with some very strong passwords. As you don't need to remember them you can generate the best possible password that meets the constraints of the account you are logging in to, thus giving you the maximum possible password security.

All in all I've found Roboform an excellent tool that definitely makes my A-List. There are other features available which you can check out on their website at http://www.roboform.com/. My advice would be to download it, try it and then buy it.

The Art of Creating a Password

One area of security that hackers regularly exploit is your password. Most people struggle to remember long and complex passwords and so resort to either using short and simple ones, which are easy to crack, or more complex ones that they end up writing it on a postit note generally found attached to their computer monitor.

The Ten Password Commandments

1. Don't use repeated characters (e.g. aaaaaaa), sequences (123456 or abcdefg) or adjacent keys on your keyboard (e.g. qwerty) .
   
2. Don't use words found in the dictionary
   
3. Chose a password that is easy to remember to avoid your having to resort to variations of the post-it trick. Note that if you do resort to writing it down then do so in a secure fashion. Consider splitting it up and storing the fragments in different places. Store the written password using an easy to remember code such as adding an offset to the characters in it or reversing it.
   
4. Don't share a password between accounts. If a hacker cracks it for one they can use it for any others you've used it for. You may also inadvertently use it on a bogus website where it will be stored and systematically tried against your legitimate acounts.
   
5. Incorporate as many keyboard characters on the keyboard as possible (if possible include upper and lower case letters, numbers and special characters). The fewer you use the longer it needs to be to have the same strength.
   
6. Your password should be as long as possible (preferably 14 characters or more long but 8 as a minimum).
7. It should not the same as your user name, any part of your (or family members) name, birthday or other personal information. Hackers will try variations of these if they can get hold of this information.
8. Don't store it on the internet or on a network. Don't store it on your PC unless it is encrypted. If a hacker gets at the file containing them then they can use them.
9. Change your password regularly. The stronger the password you use the less frequently you need to change it.
10. Don't just use simple letter substitution on a weak password. P@$$W0rd may look strong but password cracking software will often incorporate this kind of letter substitution.

The third and sixth of these criteria would at first glance appear to be mutually exclusive. Most people struggle to remember a 5 or 6 character password, without traversing the heady realms of passwords that contain ten or more.

Johnny Mnemonic

One way around this is to base your password on mnemonic phrase, saying or the lyrics of a song that you remember. Take the first letter of each word in your key phrase, et voilla an instant long password that is easy to remember. Marvelous.

For example take the old Nancy Sinatra song "These Boots are Made for Walking". Taking the first 12 words you get "These boots are made for walking and thats just what they'll do". Taking the first letter of each word gives you the letters "TBAMFWATJWTD" which to all intents and purposes looks pretty random.

Substitute

To widen the range of characters used in your password you can now apply the rule that you use and upper case letter for the first letter of each word on your phrase that is greater than two characters long and lower case for the rest. You now get "TBAMFWATJWTd", not a huge improvement in this example but hey you can always choose a new key phrase. To widen the range of characters even further, consider substituting special characters for certain letters. For example: -

• '@' for 'a'
  
• '8' instead of 'B'
  
• left bracket '(' instead of 'C'
  
• '!' for an 'i' (invert ! and you get i)
  
• zero for 'O' 
  
• '$' instead of 'S' 
  
• '+' for 't'. 
  

Your password would now be "+8AMFWA+JW+d".

The Specials

Finally add one or more additional special characters to the mix at predetermined points in your password. For example you could always enter "~" as the third character of your password and "#" your last.. As long as you're consistent you should be able to remember as you key it in. Adding these in would give you "T8A~MFWATJWTd#" which is pretty strong. Just don't go humming the tune to yourself as you're entering it.

Remember that if you don't use a strong password, then one of these days these hackers are gonna walk all over you.

Just What the Daktari Ordered

Apple has released Safari 3.02 this week.  This new version of their beta Windows browser, includes a number of stability and security fixes. 

So far I've been quite impressed with  Safari. Loading times for web pages are as fast as a Gazelle in flight and its interface as uncluttered as a water hole with a Lion in residence. Watch this space, it'll soon have other Hippo like pieces of browsing bloatware in its sights.

Tinker, Tailor, Soldier, Spyware

Combating the Insidious Threat to Your Privacy posed by Spyware

What Is Spyware?

Spyware is any software that uses your Internet connection in the background without your knowledge or explicit permission.

It has the ability to: -

• Read cookies
• Scan files on your hard drive
• Monitor your keystrokes
• Install other Spyware programs
• Alter your default home page
• Send information back to its creator

This can result in information theft of sensitive information such as PIN numbers and passwords, reduce the performance of your PC and cause it to become unstable (it is thought that Spyware is the cause of up to 50% of PC crashes). Spyware can also take over your PC using it as a Zombie for such illicit activity as denial of service attacks or propagating spam.

In general Spyware is produced by a group of people for profit whereas viruses were produced by an individual for kudos.

Network performance can also be adversely affected by a Spyware attack. For a business the act of tracking down and removing the offending software will lead to disruption and loss of productivity.

Types of Spyware

Spyware generally fall into one of two categories; Adware, or Malware.

Adware
Adware is generally benign and is usually produced by advertising companies. Adware generally generates annoying on-screen advertisements (normally pop-ups).

The worst forms hijack links on websites and take you to destinations of your choice. The reason for this is that by artificially inflating the amount of traffic to their websites, your friendly neighbourhood hijacker can command higher advertising revenues. This can be worrying if you've got a child who surfs the net as the destinations routed can be anywhere on the web and pornography companies do make use of Adware in this manner.

Adware uses cookies to hold details of your browsing habits which are periodically sent to the marketers.

Although not directly compromising your system in the way that the far more more malign Malware does (see below), such software can cause it to become unstable and have a detrimental effect on its performance.

Malware
Malware is an abbreviation of malicious software and is often written to harm your system, much in the way that a virus can. Other forms use key logging to send details of your typing to the perpetrators. This has implications of identity theft and other criminal acts being perpetrated on you. A hacker potentially could use these programs to get your credit or bank card details.

A Growing Threat

"Over the past three months, EarthLink and Webroot found more than 29.5 million instances of spyware. This figure equates to an average of nearly 28 spyware items per computer and demonstrates the broad proliferation of spyware." said Cobb. "While most spyware is Adware-related and relatively benign, it's disturbing that over 300,000 of the more serious System Monitors and Trojans were uncovered. This figure represents how real a threat identity theft or system corruption is for users."

Earthlink and Webroot

Spyware has been around for less time than viruses but is rapidly becoming a greater threat.

In a recent survey by Earthlink and Webroot an average of 28 pieces of spyware were found on infected PC's. Most were relatively benign Adware, but 300,000 cases of more serious Trojans and System Monitors were found (from 1,062,756 scans).

It is currently thought that 90% of all computers connected to the internet are infected.

The Legal Position on Spyware

While the legal position regarding viruses is pretty well laid out the legality of Spyware is not.

Direct advertising companies sometimes use the spurious argument that because a user agrees to the terms and conditions of a piece of software they download, which includes mention of dubious activities in the smallest of small print, they consent to having Spyware running on their machine. Furthermore they state that to legislate against these nefarious products would have a dire effect on the economy. I seem to remember similar arguments were used when the slave trade was abolished, which is ironic considering that the zombification of your PC by some Spyware constitutes a form of cyber slavery.

The legal position of Spyware in the United States is also murky, in some states it constitutes a criminal activity, in others it doesn’t.

How You Can Get Infected?

Spyware can be installed by clicking on a weblink, opening an attachment in an email or by the payload of a virus. In fact you only have to visit a website, known as drive-by installation, or view a HTML e-mail message to get infected.

Spyware can also piggyback on the back of a utility you install (such as a P2P file sharing system).

Symptoms of Spyware

As with any disease there a number of tell tale signs that you've probably got it. Some typical spyware symptoms include: -

• Your browser being hijacked i.e., it takes you to sites other than the ones you type in

• A repeated, or sudden change, of your browsers home page that you didn't make

• Your being inundated by a plague of pop-up ads

• New toolbars appearing in your browser you don't expect to see

• Unexpected new icons appearing in your system tray

• Keys that don’t work properly when pressed (e.g. the 'Tab' key doesn't work or is delayed when used on a Web form to move to the next field)

• Poor system performance when saving files or opening programs

• Random error messages appearing

Steps You Can Take To Avoid Infection

Take Care When Downloading Software
Be careful what software you download and install on your PC. Only download software from reputable sources.

Carefully read the End User License Agreement (EULA) of any software you plan to download. Often buried within the EULA will be a disclaimer stating something along the lines that

"...information about you and your browsing habits will be sent to the company's website."

Spyware mongers take your acceptance of this as giving them Carte Blanche to infect your PC, so be careful.

If the EULA is hard to find, or understand, or contains a clause like the one shown above, then seriously reconsider installing the software.

Be Wary of Gadgets
Use the active protection inherent in Anti-Spyware products to help protect your PC (see Anti-Spyware Products below).

With the advent of Windows Vista the use of Gadgets is becoming more and more prevalent. A gadget is an application that you can embed in a web page that delivers a piece of functionality to the user, such as showing the weather forecast for your area or displaying a digital clock on their webpage.

Quite often gadgets are created by hobbyists and delivered free of charge. Quite often they will also have a hidden payload of Spyware hidden in their code.

You can add gadgets to a website you administer or to a blog you post to. The revamped version of Google's homepage, iGoogle, also allows you to add gadgets.

In Windows Vista you can add gadgets to the windows sidebar. Unlike installing a gadget on a webpage where the Windows & Browser security will stop them accessing your file system, Vista gadgets have full access. That means that they can copy any embedded DLL, file or program to your hard drive. Vista gadgets execute with full system permissions and so can then execute those programs.

To quote the old saying, there's no such thing as a free lunch. In the case of gadgets the cost of your free lunch may end up being more than a Champagne and Caviar feast at the Ritz.

Take Care When Opening Attachments
If you receive an email with an attachment you are not expecting, the safest course of action is not to open it, and just delete it. If you later find that it was legitimate then you can always arrange to have it resent to you.

Ramp Up Your Browser Security
Ensure that the level of your browser security setting is sufficient to detect unauthorised downloads. In the case of Internet Explorer this should be set to at least 'Medium'.

This minimises the risk of drive-by downloads getting their teeth into your system.

Turn Off the Preview Pane in your Email Client
As previously mentioned the mere act of viewing HTML can cause Spyware to be installed. If you automatically view your current email in your email client this could potentially result in infection. To stop this from happening, turn off the preview pane. In MS Outlook on the View menu clicking Preview Pane toggles whether it is displayed or not.

Use a Browser Other Than Internet Explorer
The main way that Spyware is spread is through your web browser. A lot specifically target vulnerabilities in Internet Explorer, such as ActiveX, so one way of improving your security is to use an alternative web browser, such as Firefox, Opera or Safari. These browsers are not targeted as much as IE. Unfortunately a large number of websites only work using Internet Explorer so you may still need to use it. If this is the case make sure that you upgrade to at least IE7, security in this version was significantly improved.

Add Known Bad sites to IE's Restricted Sites Zone
If you are forced to use Internet Explorer it is worth considering blocking known bad websites. To avoid these you can install IE-SPYAD. This free program adds known bad sites to Internet Explorer's Restricted Sites Zone.

If you do use it then remember to update it on a regular basis to keep your list up to date.

Screen websites via a Browser Add-on

An alternative to blocking known bad sites via IE's restricted zone, which can be technically demanding, is to use a product such as Sitehound to alert you before you enter a known bad site.

Sitehound is a plug-in for Internet Explorer and Firefox. It works by using a list of known bad sites to check an entered URL against before you are directed there. The basic version of the product is free but requires you to manually update the bad site list. The pay for version does so automatically and also includes other features such as giving additional information about a suspect website.

Don't Click any Pop-Up Links
If a pop-up window appears don’t click any links within it. Doing so may cause the installation of Spyware on your computer. When a pop-up appears, close it by clicking the 'X' icon in its title bar.

Don't click on links in Spam
If you receive e-mail that claims to offer anti-spyware software, don't click on any of the links in it. Some of the Anti-Spyware products offered in spam actually install the spyware they claim to protect you from! If you want to install any Anti-Spyware products then a good place to start is with those listed later in this blog

Use Anti-Spyware Products
Use the active protection inherent in Anti-Spyware products to help protect your PC (see Anti-Spyware Products below).

Update Your System Regularly
Use Windows Update regularly to ensure that your operating system and web browser always has the latest patch or security update applied. Configure Windows Update to update automatically.

Use a Hosts File
A Hosts file allows you to specify an IP address that will be accessed when you enter a specific IP address in your web browser. By creating a file where the IP address for known malicious sites point back at your own computer, you can effectively make it impossible to visit them in the first place thus removing the chance of infection. Host files of this nature can be found on-line.

It is debatable however how effective this strategy is as Spyware can connect directly to the IP address, and thus circumvent this form of protection.

As an aside some Spyware modifies the host file as a means of redirecting you to sites of the authors choosing.

Consider Using a Program to Check Your E-Mail
As e-mail is one method that Spyware and viruses can infect your computer you could consider using a product to scan your e-mail for potential threats. One such product, Mail Washer Pro, is ostensibly a Spam removal tool but as the emails it screens for can also contain a virus or Spyware, it also provides another line of defence against infection. The preview pane it uses just shows the message in plain text so allows you to safely check your mail before you download it.

Detecting Spyware

Network and systems administrators can detect Spyware by: -

• Capturing and studying network transmissions for suspicious packets using a network analyser.

• Using the Netstat utility to monitor all ports. This is a TCP/IP application that reads network data structures. It can be used to find any suspicious ports open on your PC; they can then conduct a Web-based search on any suspect ports which may reveal the existence of Spyware.

There are also a number of third party products that can be used to scan your ports and provide a graphical interface (e.g. X-NetStat).

A more effective means of detecting Spyware is to use a dedicated Anti-Spyware application.

Anti-Spyware Applications
Some anti-virus products profess to also protect your computer against Spyware but the truth of the matter is that none of them do a very good job of it. To protect yourself properly from Spyware you should run a dedicated anti-Spyware product.

The pick of the crop of products at time of going to press are: -

Freeware

• Windows Defender
• Ad-aware
• Spybot Search and Destroy

Commercial Products

• Spyware Doctor - Spyware Doctor has consistently topped the ratings in reviews of anti-Spyware products and has a detection rate of around 97%. Another advantage of Spyware Doctor is that updates for new threats are made available for download within days (or in some cases hours) of the threat being identified. In the case of some well known products, it can be a week or more before countermeasures are made available.

As well as scanning for Spyware some products, generally the ones you have to pay for, also provide active defence to stop your PC getting infected in the first place. These active defences, although not 100% reliable, do provide some protection and are therefore recommended. The only downside to running active defence is you may find some degradation in performance.

One thing I would recommend is periodically scanning with more than one anti-Spyware tool. Even the best software won’t detect all current Spyware, so by scanning with more than one product you increase the chances of finding it. A combination of the current best commercial and the best of the free ones run on a regular basis will give you the best protection.

Keep Your Spyware Product Up-to-date
Update your Spyware signatures on a daily basis.

Scan Your PC for Spyware Often
You should perform a full Spyware Scan at least once or twice a week. In addition to this, if you have the option, configure your product to scan key areas on a daily basis, if possible on start-up.

Sometimes Spyware can mask itself during start-up making it difficult to detect and remove. To counter this, you should periodically run a full scan in Safe Mode. Safe Mode can be accessed by repeatedly pressing the F5 key (or on some systems F8) during the boot sequence.

False Alarms
Anti-Spyware programs use the following methods for detecting Spyware: -

• They contain a list of known Spyware which the use to compare against;

• They detect suspicious activity including Windows registry entries that are out of place, suspicious network connections and programs that exhibit suspicious behavior.

Sometimes they can falsely report a valid application as being Spyware. Always check any reports produced and make sure you don’t accidentally remove legitimate software.

Blocking Spyware Transmissions Using a Firewall
As previously mentioned, Spyware sends information back to its creator. Running a firewall, such as Zone Alarm, not only stops unwanted intrusions getting in, but can also stop unsolicited transmissions getting out.

By configuring your firewall to only give internet access to legitimate applications you run, you can deny any Spyware that has managed to evade your defences the ability to accomplish its objective.

Some Useful Web Sites

For further reading on the subject, check out the following websites.

• Spyware Warrior - Spyware Warrior lists free and pay for software. It also lists bogus spyware that, when installed, spy on you.

• Spywareinfo - this site has a number of spyware forums where experts in the area participate. As such it provides a good port of call should you have any questions.

• Malware Removal - a site with a number of Malware related forums. It also has some useful downloads that help you remove some specific Malware infections.

Conclusion

Today most individuals and organizations measures in place to deal with the threat posed by viruses; they should also invest in separate countermeasures to combat the rise of Spyware.

Failure to do some can harm your efficiency, reputation, productivity and ultimately, your financial wellbeing.

Roles, Permission Lists & Components

The following SQL identifies which roles/permission lists a user has to access a given component.

select RUSR.OPRID,
       RUSR.ROLENAME,
       RCL.CLASSID,
       MNU.MENUNAME,
       CMP.PNLGRPNAME
  from PSROLEUSER_VW RUSR,
       PSROLECLASS RCL,
       PS_PSACLMENU_VW2 MNU,
       PS_ACLCOMPONENT_V2 CMP
 where RUSR.ROLENAME  = RCL.ROLENAME
   and RUSR.OPRID     = 'oprid'
   and RCL.CLASSID    = MNU.CLASSID
   and MNU.MENUNAME   = CMP.MENUNAME
   and CMP.PNLGRPNAME = 'component'
 order by MNU.CLASSID

SQL to find out which roles have a particular permission list.

select distinct RCL.ROLENAME,
                RCL.CLASSID
           from PSROLEUSER_VW  RUSR,
                PSROLECLASS  RCL
          where RCL.CLASSID = 'TYALLPLS'
       order by RCL.ROLENAME

SQL to list users assigned to a particualr role.

select *
  from PSROLEUSER_VW RUSR
 where RUSR.ROLENAME = 'Rolename'

Current Job Row

I know I can enter the job effective dating code in my sleep, but the following, with a few amendments as needed, should save some time in the future.

select JOB.EMPLID, JOB.EMPL_RCD, JOB.EFFDT, JOB.EFFSEQ, JOB.ACTION, JOB.ACTION_REASON, JOB.EMPL_STATUS, JOB.BUSINESS_UNIT, JOB.COMPANY, JOB.PAYGROUP from PS_JOB JOB where JOB.EFFDT = (select max(JOB1.EFFDT) from PS_JOB JOB1 where JOB.EMPLID = JOB1.EMPLID and JOB.EMPL_RCD = JOB1.EMPL_RCD and JOB1.EFFDT <= SYSDATE) and JOB.EFFSEQ = (select max(JOB2.EFFSEQ) from PS_JOB JOB2 where JOB.EMPLID = JOB2.EMPLID and JOB.EMPL_RCD = JOB2.EMPL_RCD and JOB.EFFDT = JOB2.EFFDT)

Note: The above example is for Oracle, if using SQL Server change SYSDATE to GetDate().

Query Security Record SQL

The following SQL identifes which PeopleSoft records use a particular Query Security Record.

select RECNAME
from PSRECDEFN
where QRYSECRECNAME = 'query security record'

SQL to find a Field in PeopleSoft

This script lists all the tables that contain a given field in PeopleSoft. Just replace fieldname with the name of the field you want to find.

SELECT A.RECNAME,
B.SQLTABLENAME
FROM PSRECFIELD A,
PSRECDEFN B
WHERE A.FIELDNAME = 'fieldname'
AND A.RECNAME = B.RECNAME
AND B.RECTYPE = 0
AND B.RECNAME NOT LIKE 'AUDIT%'
AND B.RECNAME NOT LIKE '%TEO%'
AND B.RECNAME NOT LIKE '%AET'
AND B.RECNAME NOT LIKE '%TAO%'
AND B.RECNAME NOT LIKE '%PERS%'
AND B.RECNAME <> 'PSOPRALIAS'

How to Automatically Select Jobs on the Run Panel in PeopleSoft

To automatically select a job for running on the run panel in PeopleSoft add some PeopleCode to the RowInit event PRCSRQSTDLG_WRK.SELECT_FLAG to set this field.

e.g.

Evaluate %Component
When "COMPONENT_NAME_1"
SELECT_FLAG = "Y"
When "COMPONENT_NAME_2"
SELECT_FLAG = "N"
End-Evaluate;

Things You Do In Your Lunch Break - How to get the Ratio between two numbers in Excel

A while ago I was asked how to get the ratio between two numbers in Excel. One lunchbreak and two VBA functions later, this is what I came up with.

I ended up creating two functions. One returns the highest common denominator for two numbers HighDenom and the second, Ratio, returns a string of the ratio between two numbers (e.g. 5:1).

First off you need to add a couple of functions to your spreadsheet...

1. Open the workbook you're working on

2. Go into VBA (Alt+F11)

3. Insert a new module (Insert > Module)

4. Paste the following 2 functions into your module: -

Public Function HighDenom(intNum1 As Integer, intNum2 AsInteger) As Integer

' This procedure returns the Highest common denominator fortwo numbers.

If intNum1 > intNum2 Then
intMax = intNum1
Else intMax = intNum2
End If

For i = intMax To 1 Step -1
If (intNum1 / i = Int(intNum1 / i) And (intNum2 / i =Int(intNum2 / i))) Then
HighDenom = i
Exit Function
End If
Next

End Function

Public Function Ratio(intNum1 As Integer, intNum2 AsInteger) As String

' This procedure returns the Highest common denominator fortwo numbers.

intHD = HighDenom(intNum1, intNum2)
intDiv1 = intNum1 / intHDintDiv2 = intNum2 / intHD
Ratio = intDiv1 & ":" & intDiv2
End Function

5. Get out of VBA (Press Alt+Q)

6. Use the functions (They will appear in the Paste Function dialogbox (Shift+F3) under the "User Defined" category.

How to use %RECNAME_EDIT as a Prompt Table

Sometimes in PeopleSoft you want to be able to vary the contents of a drop down list. For example you may want to only give the user the choice of selecting departments for a particular division.

You could do this by having a controlling field that is a high level key in the lookup table. PeopleSoft’s use of Set IDs utilises this method to restrict values based on their Set ID.

On occasion though, you may need to be able to dynamically change your values on the fly. For example you may have a radio button on a page that you use to control the values displayed in a drop down list. One way of doing this is to use %RECNAME_EDIT functionality.

In this technique you specify a field as the prompt then set its value in PeopleCode to the name of the table you want to use as your prompt table.

First off set the Prompt Table for the field you want to display values for to %fieldname where fieldname is the name of the field you are going to set in your PeopleCode. For example PeopleSoft often use %RECNAME_EDIT. RECNAME_EDIT is a field in the DERIVED table.

Second, add your edit field to the page or pages that contain your dropdown field making it display only and invisible. For example if you used a prompt of %RECNAME_EDIT you could add RECNAME from the DERIVED record to the page.

Finally add some PeopleCode to set the value of this field to the actual name of the prompt table.

e.g.

If (condition) Then
DERIVED.RECNAME_EDIT = "A_VIEW_VW";
Else
DERIVED.RECNAME_EDIT = "B_VIEW_VW";
End-If;

This can be done in Page PeopleCode, Component PeopleCode or FieldChange PeopleCode on a controlling field.

Thanks to Kevin Gavaghan for giving me a heads up on this technique a while back.

Add to Google

One neat thing you can do with iGoogle is add a feed to a blog to your page. As a blogger, in order to facilitate this you need to add a button to your blog that the reader can press to add it.

To create this button on your blog: -

• Go to the following page http://www.google.co.uk/webmasters/add.html and enter your feed URL(e.g. http://feeds.feedburner.com/TheGeorgeLiteZone?format=xml) in the box.

• Press the Create HTML button.

• Copy the HTML generated.

• In Blogger add a new HTML/Javascript page element, or open an existing one.

• Paste in the HTML you copied.

Est voilla, a nifty little Add To Google button will appear as though by magic on your blog. Now anyone visiting it can add your blog to their iGoogle page. Fantastic.

Now to follow my own instructions to add a button to Coded Languages.

Query Tree Security SQL

Query Trees are assigned to a permission list in PeopleSoft under the Query tab of: -

PeopleTools > Security > Permissions and Roles > Permission Lists

Working out who's got access to what query trees can be a bit of a nightmare manually searching through each permission list in turn. To that end, the following piece of SQL is quite useful in establishing which roles have access to a particular query tree or trees.

select distinct ROL.ROLENAME,
ROL.CLASSID,
SAG.TREE_NAME
from PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG
where ROL.CLASSID = SAG.CLASSID
and SAG.TREE_NAME in (Query Tree List)

To see which query trees a particular user has access to, use: -

select distinct USR.ROLENAME,
ROL.CLASSID,
SAG.TREE_NAME
from PSROLEUSER_VW USR,
PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG
where USR.OPRID = 'Operator ID'
and USR.ROLENAME = ROL.ROLENAME
and ROL.CLASSID = SAG.CLASSID

To see which users have access to a particular query tree: -

select distinct ROL.ROLENAME,
OPR.OPRDEFNDESC
from PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG,
PSROLEUSER_VW USR,
PSOPRDEFN OPR
where ROL.CLASSID = SAG.CLASSID
and SAG.TREE_NAME = 'QRY_TMUK_ALL'
and ROL.ROLENAME = USR.ROLENAME
and USR.OPRID = OPR.OPRID
order by ROLENAME, OPR.OPRDEFNDESC

How To Add A Feed to your Blogger Blog

It's official, news aggregaters are great. They're a one stop place where you can check on a host of websites that are of interest to you, and quickly see updates that have been added.
Thus saving lots of time you can utilise by drinking cups of tea and randomly surfing other sites that aren't on your interest radar.

Later versions of browsers like IE and Firefox allow you to subscribe to a sites feeds from the browser itself and again get your info served up on a plate, with a couple of waffer thin mints on the side.

As such if you've got a blog yourself, then it's not a bad idea to give your readership (both of them) the opportunity to subscribe to your site by adding a link. The following simple bite sized instructions tell you how you can do this from blogger using Feedburner.
Go to your blog and copy its URL to the clipboard (Control+C is always a good bet for this).

1. Go to the Feedburner website at http://www.feedburner.com
   
2. In the shaded box under the imaginative heading "Start FeedBurning Now" paste your URL.
3. Click the Next>> button.
4. On the identify feed source page displayed select one of the sources displayed. As an RSS fan, I'm going with the RSS one for this example. For those of you who don't know their RSS from their elbow, RSS stands for Really Simple Syndication. No really, it does. You can also repeat the process for Atom later if you want.
5. Click Next>> again.
6. After tinkering with your feed description and address, or not if life's too short, do one of two things. Either, create an account by filling in a username, password and e-mail address; or login using an existing Feedburner account if you've already got one. If you've already got one you probably don't need to be reading these instructions, but hey.
7. Click Activate Feed>>
8. Click Skip directly to feed management. You can always refine your feed options later.
   
9. Click on the Publicize tab
10. Under Make it Easy to Subscribe click on the friendly graphic link (below the bullet point Chickletize your website).
11. Choose a feed icon (I always prefer the wee one)
12. Scroll to the bottom of the page
13. Under Use as a widget in select Blogger from the drop down list.
14. Press Go!
15. You are now directed to the Add Page Element page of your blog. If you have multiple blogs, select the one you are burning a feed for.
16. Press the Add Widget button.
17. Position your subscripew now widget where you want it, and press Save.

Congratulations, you've now got a feed on your blog. Have a cup of tea to celebrate.

Spooling Your Output

When writing SQL scripts in a production environment it's always a good idea to spool the fruits of your labor to a log file when it is run. It also gives you the opportunity to wear your "I Told You So" teeshirt when someone denies you've run something.

Selecting the database name from sys.v_$database also gives you the warm feeling that you've run your deletion script in a development environment and not accidentally against production when you review your log file. Alternatively it could give you a more localised warm feeling down your trouser leg if you find out it's the other way round !

The set pagesize 0 command formats your SQL output better so you get one continuous page rather than giving you reams of headers.

The set timing on statement gives you timings for the SQL being run. This gives future runners of the script an idea of how long the SQL takes, which can bu useful, especially when running it in production.

whenever SQLERROR exit failure;

spool "/filename"

set echo on;
set feedback on;
set verify on;
set pagesize 0;
set termout on;
set timing on;

select name from sys.v_$database;

/************************/
/* Insert your SQL here */
/************************/

spool off;

Set Current Schema

I always forget the syntax for setting the current schema in Oracle. Then again that's why the great Flying Spaghetti Monster created blogs.

alter session set current_schema = sysadm