Securing the Network: 8

Securing The Network
A Post on Corporate Security Issues for the Non Technical

This post covers:
                Auditing
                Separation of Duties

Auditing
It is important that part of your security initiative involves auditing your systems. A lot of important information is contained in logs that are scattered around your servers and devices.

It is necessary to look at what devices produce logs that are important and need regular monitoring, and then ensure that you do monitor them. It will be beneficial to introduce some mechanism so that the logs are sent to you on a regular basis, rather than you having to go and get them each time.

You should set up a document that details all your important logs along with the schedule for checking and auditing them.

Separation of Duties
Am important part of corporate security is Separation of Duties. This basically means that no one individual should be able to control a process from beginning to end.

Separation of Duties allows for checks to be made by a different individual which helps eliminate mistakes and minimises the risks of fraud.

"How much easier it is to be critical than to be correct." - Benjamin Disraeli

Securing the Network: 7

Securing The Network
A Post on Corporate Security Issues for the Non Technical

This post covers:
                Workstations
                Laptops / Portable Devices

Workstations
Employee workstations can be the most difficult device to secure properly. For a start the employee has unrestricted physical access to the computer and (hopefully) restricted access to the network.

As much data and information as possible should actually be stored on the server with limited facilities for the employee to download and copy the data via his computer.

Ideally technologies such as server based profiles, Active Directory, Terminal Services and SMS (Systems Management Server) should be used to lock down employee access as much as possible without restricting them to the point of severe inconvenience.

Features of the operating system that the user does not need on a day to day basis, such as access to the Command Prompt on Windows, should be locked down and access restricted.

Users should never logon to their computers with the Administrator or Root account. See the sub-section on Least Privilege.

Laptops / Portable Devices
Data that is installed on a device that is going to be used in the field, must be encrypted. Under Windows a superb solution is Truecrypt.

Truecrypt allows you to set up a ‘container’ in which the contents are heavily encrypted, an encryption key must be entered every time the computer is turned on. This ensures that if the device is lost, you data will remain secure.

"What we say is important for in most cases the mouth speaks what the heart is full of." - Jim Beggs

Security Primer: Authentication

Authentication
Authentication is the act of confirming that someone is who they say they are. From the perspective of computer or network security the device needs to be able to cross reference the data that is input as the data that is expected in order to be able to allow access to controlled resources.

Authentication comes before, and is different to Authorisation. Once you are authenticated with a system, you can then be Authorised to access agreed system resources. Access criteria is the crux of Authorisation.

There are generally thought to be three ways to authenticate:

        Something a person knows
        Something a person has
        Something a person is


Something a person knows:
Password, Pass Phrase or Pin Number Etc.

Something a person has:
ID Card, Security Token, Mobile Phone Etc.

Something a person is:
Fingerprint, DNA, Retina Scan, Voice Scan Etc.

"Know the true value of time; snatch, seize, and enjoy every moment of it. No idleness; no laziness; no procrastination; never put off till tomorrow what you can do today." - Lord Chesterfield

Securing the Network: 6

Securing The Network
A Post on Corporate Security Issues for the Non Technical

This post covers:
                Wireless Networks

Wireless Networks
Wireless networks are a major potential security breach. The following are some ideas on what you can do to minimize your exposure.

Change Your SSID
A SSID is the public name of your wireless network. SSID stands for Service Set IDentifier. Many people leave this set to the factory default, which may be LINKSYS or 3COM or similar. Change the SSID to something that describes your own network, this will at least ensure that people do not accidentally connect to your network instead of their own.

TECH NOTE: AP (Access Point): This is transmitter / receiver which connects your wireless network to you LAN (Local Area Network).

Turn off the Access Point Beacon
When you have setup your wireless network there is no further need for your AP to transmit it’s beacon that basically says ‘I AM LINKSYS. I AM HERE’. So within the administration software or webpage that you use to administer your AP, turn off the beacon. This will make your wireless network invisible to somebody who is just scouting around. If they know you have a network already or if they know the SSID they can still see and/or connect to you.

Restrict Access to specific MAC Addresses
Each network card within a computer contains a Mac Address that is (to all intents and purposes) unique. With some AP’s you can restrict access to your wireless network to computers of a known MAC Address. The procedures differ for each AP and some do not even support this, but if your AP does support this it is worth pursuing. This assumes that you do not regularly have new computers needing to connect to your network. Also be aware that valid MAC Addresses can be sniffed from your network and the attacker can spoof his MAC Address so that it looks like yours ...

TECH NOTE: MAC (Media Access Control) Address: This is a unique identifier attached to most sorts of networking equipment and consists of two parts, the first part related to the manufacturer of the device and the second part is a serial number.

Change the Admin Password on your Access Point
This one goes without saying.

Implement Encryption
At a minimum, enable WEP. However if possible WPA should be setup and used. Use the maximum encryption length.

TECH NOTE: WEP (Wired Equivalent Privacy)
TECH NOTE: WPA (WI-FI Protected Access)

"Nature magically suits a man to his fortunes, by making them the fruit of his character." - Ralph Waldo Emerson

Securing the Network: 5

Securing The Network
A Post on Corporate Security Issues for the Non Technical

This post covers:
                Servers
                Service Packs / Updates
                
Servers
If somebody has physical access to your servers then all further security is completely compromised. Your servers should be located in a secure location, i.e. safe from theft, tampering, fire and flood and ideally accessed only remotely using tools such as Remote Desktop and VNC.

TECH NOTE: VNC (Virtual Network Computing): This is a desktop sharing system ideal for use when attempting to administer a computer that is located inconveniently.


Service Packs / Updates
As with anything else concerning security, the installation of Service Packs and Updates is a compromise.

Install them quickly when released and you may secure your servers from a current threat, however if you have not had time to test the updates then they may cause serious problems on your systems.

Personally I lean towards installing them quickly on machines that may be exposed to the external threat and taking my time on machines that are unlikely to be threatened.

"When you play, play hard; when you work, don't play at all." - Theodore Roosevelt

New Addition to My Collection !

Well, I didn’t realize that I had a collection of watches until my wife asked me to count them before deciding whether I *really* needed another one ... when the count surpassed a dozen I decided that I was in fact a collector, which means I have a need to add to my collection ...

This one is an MTM Blackhawk and it arrived today. This is an excellent watch. It’s solid, heavy and virtually indestructible, if you need a tough watch for outdoor use, I doubt this can be beaten. Here’s a picture.

I purchased the watch from nightgear.co.uk and received it the following day, how’s that for service !

Here’s some information I found that describes the background:

The US Special Forces wanted a new watch (one that was more rugged and reliable) about 18 years ago. They approached a company in California (MTM) and asked them to design a sort of Swiss Army Knife watch.

Over the next two years the company spent some time talking to NASA and MIT (Mass' Institute of Technology). Together they came up with a watch that would be one of the most accurate watches in the world.

They did all sorts of searches and tests to find the best materials for it (carbon fibre, diamond hardened quartz glass etc). The battery electro-magnetic recharging system originally existed as something developed for NASA (meant you could generate more efficient electricity in space via spacial object discharges). MIT played with it and got it into a stand which recharges the battery.

The way they designed the shielding around the battery meant that it would survive an EMP (electro-magnetic pulse) such as can be caused by a nuclear explosion or an EMP generator. This meant that the watches became virtually indestructible. They are water resistant to 300m. They can resist pressures of up to 15 atmospheres (which would have killed a human after about two and a half seconds).

They were issued (officially) to the Special Forces (Delta Force, Army Rangers, SWAT Teams, Navy Seals). After a year the civilian forces (FBI, DEA and CIA) wanted them and about six months after that the Army, Navy and USAF ordered them.

The British got hold of them a while ago and the SAS and SBS got them. Several Army, Navy and RAF officers got hold of them and they spread. Now even the Israeli Mossad use them now, along with the British MI5 and MI6 Intelligence Agencies.

I just need to see whether it is tough enough to survive the average day in my IT Department ...

"Help others get ahead. You will always stand taller with someone else on your shoulders." - Bob Moawad

Securing the Network: 4

Securing The Network
A Post on Corporate Security Issues for the Non Technical

This post covers:
                Routers
                The Administrator Account
                Resources

Routers
Your internet router should be setup so that it does not respond to an ICMP query from the Internet, i.e. disallow external pings.

In addition UPNP (Universal Plug and Play) should also be switched off.

Unless you desperately need access to remotely configure your router then you should also disable the remote access facility.

TECH NOTE: ICMP (Internet Control Message Protocol) Query: This is generally known as a ‘Ping’. One computer can Ping another as a way of saying ‘hello, are you there ?’. A reply is expected from the computer that receives the message.

TECH NOTE: UPNP (Universal Plug and Play): This is a set of protocols designed to simplify device configuration by attempting to automatically configure them for you.

The Administrator Account
The Administrator account on each server should be setup with a large and complex password and then disabled. Changing the name of the Administrator account will not fool a decent hacker, under Windows the Administrator account always has the ID of 500, even if you do choose to rename it to BilboBaggins or BartSimpson.

Each Administrator should then be given their own Admin account and password, no Admin should know the password for another Admins account. This ensures that you are able to Audit the Administrator level access to the servers and tie it down to a specific individual.

Resources
When considering the resources that you provide for your users you should look at them in the context of:

        Confidentiality
        Integrity
        Availability

The general rules to use when setting up access to resources are:

        Need to know
        Least Privilege

Need to Know
This applies to users and the information they need. There is nothing to be gained by passing on information to users regarding server and router IP addresses, DNS and DHCP if they do not need to be told these things to so their job.
Least Privilege
Basically what we are saying here is that users and employees should be given the lowest and most restrictive access possible, whilst still enabling them to do their job. It is easier to control the escalation of access rights than it is to try reducing them at a later date!

"For today and its blessings, I owe the world an attitude of gratitude." - Clarence E Hodges

Securing the Network: 3

Securing The Network
A Post on Corporate Security Issues for the Non Technical

This post covers:
                Used ID
                Passwords

User ID
Traditionally access to many computer systems has been via a ‘username’. Some examples are shown below:

Bilbo
Bilbobaggins
Bilbo.baggins

The problem with this of course is that an attacker can utilise employee information gained from many sources to guess the logons names to the computer system. If they know the logon name they are 50% of the way there to getting access.

Even worse, many people use the same ‘username’ as their email address, see below:

Bilbo@hobbit.com
Bilbobaggins@hobbit.com
Bilbo.baggins@hobbit.com

This means that an attacker has only to learn the name of an employee to have a good idea as to both their computer logon and their email address, or alternatively they only need the email address to learn an individuals computer logon and name.

TECH NOTE: Email SPAM: An additional problem with using name as an email address is the fact that some spammers now use code to churn out millions of emails to a domain name ie: hobbit.com using variations of peoples names. This in itself is a potentially massive problem.

My suggestion is that systems designed or re-engineered nowadays should use logons and email addresses that bear no relation to the name of the individual. For example:

M7071@hobbit.com

This may not be as simple or as intuitive as previous methods but it is a lot more secure, and anything we can do to secure ourselves that little bit more, is worth doing.

Passwords
User passwords should conform to the following criteria:

        Minimum Length, 9 Characters
        Combination of Letters, Numbers and Special Characters
        Mixed Case
        Does not form Proper Word

To ensure that the user remembers her password and does not stick it underneath the keyboard on a Post-It note, you may implement the following suggestions:

        Let the user choose her own password
        Build the password from a phrase, such as a line from a song.

The system should be setup so that after a given number of password attempts the account is locked, this helps prevent against Brute Force password attacks.

In addition the policy should be enforced so that passwords are changed at least twice a year, quarterly or more often would be better.

"If you're creative, if you can think independently, if you can articulate passion, if you can override the fear of being wrong, then your company needs you now more than it ever did. And now your company can no longer afford to pretend that isn't the case." - Hugh MacLeod

Securing the Network: 2

Securing The Network
A Post on Corporate Security Issues for the Non Technical

This post covers:
                Threats
                Physical Security
                Firewalls

Threats
The main threats that we face as a business if our network or computer or security systems are compromised, are listed below:

Data Loss
Data Theft
Identity Theft

The main ways in which these threats can be realised are listed below:

Malware / Trojans
Viruses
Pod Slurping
Social Engineering
Physical Destruction
Employee Dishonesty

Physical Security
Physical Security in the context of this document can be split into two areas, security of your building/office and security of your computers/servers.

The security of your building or office is covered in this document because if it is possible for somebody unauthorised to gain access to your building or office then the best computer security in the world will not help. They could steal your computer, plug a laptop into your network, put a tap on your phone, steal confidential information etc. etc.

Gaining access, even to a secure establishment can be as simple as ‘piggy backing’. This involves walking into a building close to a group of others, if this is done casually enough then you are extremely unlikely to be questioned. One way around this, typically used in high security installations, is the idea of a ‘man trap’, basically an enclosed ‘chicane’ type area which allows one person through at a time.

At the very least anybody visiting your establishment should be made to wear a ‘Visitors Badge’ displayed prominently, which should be handed in when leaving the premises.

Secondary entrances and Fire Exit’s should be kept closed and secured as far as possible.

Physical security of your computers and servers means paying particular attention to the CD/DVD Drives, USB Ports, Firewire Ports Etc. There items can all be used to apply Malware/Trojans/Viruses to a computer and in most cases can also be used to take data off the computer, and out of your control.

Any electrical device of value should be attached to a secure point via an armoured cable, available from many suppliers. Many desktop and laptop computers now have points that are designed to be used with the armoured cable and padlocks currently available.

Firewall
A Firewall is a device connected between your internal computer network and the external internet. A Firewall can either be software running on a computer or a dedicated hardware device.

The purpose of a Firewall is to stop undesirable access to the machines on your network and at the same time allow access and capabilities that you deem desirable.

A Firewall is not a guaranteed safeguard. Nevertheless it is an important item in your security portfolio. Without some sort of Firewall between you and the internet it is likely that your computer would be compromised within minutes.

"Life is too complicated not to be orderly." - Martha Stewart

Securing the Network: 1

This next series of posts is entitled:

Securing The Network
A Post on Corporate Security Issues for the Non Technical

This post covers:
                Introduction
                What is a Hacker
                The Security Triad
                The Laws of Security

Introduction
Security can be likened to Insurance. Most people and organisations never really consider it worthwhile until the worst happens. Today we live in an age where computer and network security has to be at the top of the CIO’s agenda. There is too much at stake for security to be an afterthought.

Unfortunately security and convenience/practicality are at opposite ends of the user experience. As such security will always be a compromise. To give a contemporary example: It is extremely easy to stop terrorists getting on commercial flights. Use airplanes for cargo only. Problem Solved. This however is not a practical solution and so a compromise between security and practicality is necessary.

This document looks at the computer and network security that is needed within the modern enterprise and explains in layman’s terms the policies, procedures and settings that are essential to ensure that if security is going to be compromised then somebody is going to have to work hard to do it.

This is not a comprehensive security document and it does not cover every eventuality. The suggestions it makes however, if followed, are likely to lead to more comprehensive security than what your competitors and most other companies have.

After all, you don’t always have to be able to run fast, as long as you run faster than the other person when you are both being chased by a bear …

What is a Hacker ?
You will see references to the word ‘Hacker’ throughout this document. I have used the word ‘Hacker’ as it should be used, not as it is often used in modern literature or in Hollywood films.

A Hacker is someone who likes to know, in depth, about a subject. Someone who is willing to study and tinker until they gain mastery of their craft. Generally accepted to be gifted Programmers or System Administrators they can I believe be categorised by their belief that if the knowledge is worth pursuing they won’t necessarily let laws or restrictions stop them. The average Hackers attitude is probably a bit ‘Grey’ as opposed to either ‘Black’ or ‘White’.

A Hacker , like anybody else can have either good or bad intentions, and be capable of either good, bad or indifferent acts. In this document you should understand the type of Hacker I am describing by the context of the paragraph in which they are mentioned.

The Security Triad
The three cornerstones of information security are:

Confidentiality
Integrity
Availability

Confidentiality is concerned with information being accessible to only the intended recipient. This may be documents, database information, emails or even instant messages.

Integrity is concerned with the fact that for information to be trusted we must know that it has only been modified by those who are authorised to do so. In addition the data must be 100% accurate.

Availability is making sure the information is available to the right person(s) when it is needed. Factors that affect this delivery of information such as incorrect permission settings or denial of service attacks are examples of how availability may be compromised.

TECH NOTE: Denial of Service Attack: This is when several hundreds or thousands of computers are commandeered by a Hacker and all set to send requests to a targeted website simultaneously. This can often cause the targeted website to crash or become otherwise unusable.

The concept of security in the enterprise involves considering and balancing these three concepts, every step of the way.

The Laws of Security
Client side security does not work.
You cannot securely exchange encryption keys without a shared piece of information.
Malicious code cannot be 100% protected against.
Any malicious code can be completely altered to bypass signature detection.
Firewalls cannot protect you 100% from attack.
Any Intrusion Detection System (IDS) can be evaded.
Secret cryptographic algorithms are not secure.
If you don’t have a key, you don’t have encryption, you have encoding.
Passwords cannot be securely stored on the client, without password protection.
For a system to be considered secure, it must undergo an independent security audit.
Security through obscurity does not work.

Source: INFOSEC Career Hacking, Syngress 2005.

"I want freedom for the full expression of my personality." - Mahatma Gandhi

SQL 101 7: SQL Background

This is the seventh and final post in my series designed to teach people the basics of SQL and it’s use in interrogating databases.

SQL Background Information
SQL is a declarative language, not a procedural language.  This effectively means that you tell the database what you want doing, not specifically how to actually do it. This is a similarity shared by several general fourth generation languages. Examples of procedural languages are Pascal, C++ and Basic, as well as old timers such as COBOL and FORTRAN.

The SQL Language consists of three parts:
                DDL:                Data Definition Language
                (Used to Design the tables and columns)
                DML:                Data Manipulation Language
                (Insert, Delete, Update & Query the database)
                DCL:                Data Control Language
                (Controls database security and permissions)

The DBA (Data Base Administrator) is concerned with the DDL & DCL, whereas the programmer or reasonably sophisticated end-user is responsible for the DML.

SQL differs from other procedural languages in that other languages handle data in FILES. A FILE is a container of data in which data is stored sequentially. There is a concept of last record and first record, this fits in well with the way files work in the real world with paper forms and filing cabinets and the like.

SQL however handles data in TABLES. A TABLE is a set, a set is a mathematical abstraction, sets are ordered and all of their members are of the same type. When you perform an operation on a set the operation happens all at once and to the entire membership. Tables can be permanent (base views) or virtual (views). An operation on an SQL table always returns a RESULTS TABLE.

Cardinality of a table
(The number of rows in a table, Zero or Greater)
Degree of a Table
(Number of columns in a table, One or Greater)

A foreign key is the column which links to another (primary) key in another table.

OR (When using an OR within the search string, the OR'd items must be bracketed for a successful search.)
NOT
AND
LIKE 'xxx%' %=wildcard of one or more characters, the like command is however CASE SENSITIVE.
IN
BETWEEN (= can be used for an exact match type search)

GROUP BY can be more than one field separated by a comma, both columns must also be in the actual columns displayed. DO NOT include a primary key in a GROUP BY command, as the primary key is unique. Fields other than the grouping fields cannot be in the display columns, as to do so would make zero sense.

NORMALISATION is the process where by a database designer attempts to minimise the amount of duplicate data stored within his database.

"If you have made mistakes, even serious ones, there is always another chance for you. What we call failure is not the falling down but the staying down." - Mary Pickford

SQL 101 6: DISTINCT & Functions

This is the sixth in my series of posts designed to teach people the basics of SQL and it’s use in interrogating databases.

The DISTINCT Option
This option removes duplicate rows from the Results Table. The result of the query will only include each distinct selected set of Columns, even though there may be many more such occurrences in the Table.   NOTE:  Only rows where ALL columns are the same will be removed.

One example of use is where you wish to query the stock file to see if you have ever sold a particular product, you only wish a row to be returned as confirmation, you do not need a row for every occurrence of the sold stock record. This query would use the DISTINCT option to return only the required data.

Calculated Columns
The value of a Column can have a calculation applied to it. This calculation can be in the form of a numeric calculation or a calculation based on the value of another Column.

Functions
It is possible that instead of displaying the contents of a Column in the Results Table, you can display the result of performing certain operations on the Column contents. The functions available are as follows:

AVG(COLUMN)
This function calculates the average value of the specified Column. The name of the Column to be averaged must be enclosed in parentheses.

SUM(COLUMN)
This function operates exactly as the average function, but produces the SUM of the selected Columns.

MIN(COLUMN)
This function operates exactly as the average function, but produces the minimum of the selected Columns.

MAX(COLUMN)
This function operates exactly as the average function, but produces the maximum of the selected Columns.

COUNT(*)
This function operates exactly as the average function, but produces a COUNT of the number of Columns that have met the selected criteria.

Next: SQL Background Information

"If you really do put a small value upon yourself, rest assured that the world will not raise your price." - Anonymous

SQL 101 5: ORDER and GROUP

This is the fifth in my series of posts designed to teach people the basics of SQL and it’s use in interrogating databases.

The Optional ORDER Clause
The ORDER clause provides an optional method of sorting the rows that have been returned to the Results Table. Please Note that using a sort can sometimes take longer for the query to execute, and require more memory on the Server. An example of the ORDER clause is as follows:

ORDER BY CUS_NAME, CUS_PRIMARYKEY

The order of sorting is determined by the order of the Columns within the ORDER section of the statement. The sort is carried out in ascending order of field value.

Below are some examples of queries with the optional ORDER clause in use.

SELECT SOH_NUMBER, SOH_DATE, SOH_NET, SOH_OWNER
FROM GUSER.F_SOHDR
WHERE (SOH_OWNER = 'JOHN CLARK' OR SOH_OWNER = 'PETER OAKDEN') AND SOH_NET > 3000
ORDER BY SOH_NET

SELECT SOH_NUMBER, SOH_DATE, SOH_NET, SOH_OWNER
FROM GUSER.F_SOHDR
WHERE (SOH_OWNER = 'JOHN CLARK' OR SOH_OWNER = 'PETER OAKDEN') AND SOH_NET > 3000
ORDER BY SOH_NET DESC


The Optional GROUP Clause
The GROUP clause is used to sort the output not as individual records, but as groups of records that have the same value in a specified field. This optional clause is only used when the query is applying one of the functions (AVERAGE, SUM, MIN, MAX or COUNT) to the groups.

The SELECT clause is limited to the Column that appears in the GROUP clause, with one or more functions of other Columns. For Example:

SELECT HIS_USER, COUNT(*)
FROM GUSER.F_SYSHISTORY
WHERE HIS_USER LIKE ‘%’
GROUP BY HIS_USER

This query returns a row containing the amount of system history records against each user.

Next: DISTINCT Option, Calculated Columns and Functions

"Don't join the book burners. Don't think you're going to conceal faults by concealing evidence that they ever existed. Don't be afraid to go in your library and read every book..." - Dwight D Eisenhower

SQL 101 4: The WHERE Clause

This is the fourth in my series of posts designed to teach people the basics of SQL and it’s use in interrogating databases.

The WHERE Clause
The WHERE clause is used to specify the conditions that must be met for the record to be selected. In the query, each record in the Table named in the FROM clause is read and tested for the condition specified in the WHERE clause. If the condition evaluates to True the Columns specified in the SELECT clause are included in the Results Table. Otherwise the record is not returned to the Results Table.

SELECT CUS_PRIMARYKEY,CUS_CODE
FROM GUSER.F_CUSTOMER
WHERE CUS_CODE LIKE '%'

This returns a Results Table containing all your Customers. (Of course the same could have been achieved in this case by eliminating the WHERE clause entirely.)

In its simplest form the WHERE clause consists of four parts: The word WHERE, a Columns name, an operator, and a value or another Column name. Several examples of a full SQL statement containing a simple WHERE clause are shown below:

SELECT SOH_NUMBER, SOH_DATE, SOH_NET, SOH_CUCODE
FROM GUSER.F_SOHDR
WHERE SOH_DATE BETWEEN ‘2004 MAR 01’ AND ‘2004 MAR 31’

SELECT SOH_NUMBER, SOH_DATE, SOH_NET, SOH_CUCODE
FROM GUSER.F_SOHDR
WHERE SOH_NET > 1000.00

SELECT CUS_CODE, CUS_NAME, CUS_BALANCE
FROM GUSER.F_CUSTOMER
WHERE CUS_NAME LIKE ‘Bathroom%’

SELECT CUS_CODE, CUS_NAME, CUS_BALANCE
FROM GUSER.F_CUSTOMER
WHERE CUS_NAME NOT LIKE ‘Bathroom%’

SELECT SOH_NUMBER, SOH_DATE, SOH_NET, SOH_OWNER
FROM GUSER.F_SOHDR
WHERE (SOH_OWNER = ‘JOHN CLARK’ OR SOH_OWNER = ‘PETER OAKDEN’) AND SOH_NET > 3000

Parentheses are used to alter the sequence in which conditions are evaluated. Normally conditions are evaluated multiplication/division, left to right, however parentheses cause the condition inside the parentheses to be evaluated first and to pass the results of that evaluation to the outer part of the condition (or the next outer set of parentheses if using nested parentheses)

An example of the use of parentheses, consider the following two expressions:

7 + 5 * 4        =        27
(7 + 5) * 4        =        48

The final part of the WHERE clause is the element that follows the operator. This can either be a data value, or the name of another column. If the data is alphanumeric, the data must be enclosed in quotation marks. Numeric data must not be enclosed in quotation marks. Dates are treated as alphanumeric fields and the form ‘01 JAN 06’ is commonly used.

Note: The ‘%’ symbol is a wildcard. As the query executes this is evaluated to mean ‘one or more characters’. Effectively allowing a simple ‘contains’ search. Another wildcard than can be used is the underscore character (_) , this represents one character only.

If using the wildcards then you cant use the equality operator (=), instead you must use the LIKE statement. See the examples earlier.

Tomorrow: The ORDER BY and GROUP BY Clauses.

"Good communication is as stimulating as black coffee and just as hard to sleep after." - Anne Morrow Lindbergh

SQL 101 3: SELECT and FROM

This is the third in my series of posts designed to teach people the basics of SQL and it’s use in interrogating databases.

The SELECT Clause
The SELECT clause is used to define the columns that are to be displayed in the Results Table for each record that satisfies the query conditions. If more than one Column is specified the field names must be separated with commas. If only one Column is specified, no separator is required. For example:

SELECT CUS_PRIMARYKEY,CUS_CODE
or
SELECT CUS_CODE


The FROM Clause
The FROM clause is used to define the Table from which the records are to be selected. The FROM clause normally follows the SELECT clause, and in order to keep the structure of the query clear, it can often be found entered on a separate line. For Example:

SELECT CUS_PRIMARYKEY,CUS_CODE
FROM GUSER.F_CUSTOMER

This means that the Columns Primary Key and Customer Code are to be retrieved from the table F_CUSTOMER. If more than one Table is to be used in a query (using a SQL ‘Join’ which is a technique for linking related tables),  the Table names must be separated by a comma. For Example:

SELECT CUS_PRIMARYKEY,CUS_CODE,SOH_NET
FROM GUSER.F_CUSTOMER, GUSER.F_SOHDR
WHERE

Tomorrow: The WHERE Clause.

"Most folks are about as happy as they make up their minds to be." - Abraham Lincoln

SQL 101 2: Relational Joins

This is the second in my series of posts designed to teach people the basics of SQL and it’s use in interrogating databases.

Relational Joins
Data can be linked to other data. A sales order is linked to it’s lines, it is also linked to a customer. These links are known as relational joins. When attempting to retrieve linked data you have to tell the computer what are the key fields that you want to use to establish the link.

The link is established through Primary and Foreign Keys. In my own databases I generally provide a second and arguably more intuitive link. More on this later.

Consider the following Example, which shows a simplified Sales Order Header Table, and a simplified Customer Table:

Table: GUSER.F_SOHDR
SOH_PRIMARYKEY                        Sales Order Primary Key
SOH_NUMBER                        Sales Order Number
SOH_DATE                                Date
SOH_DELIVDATE                        Delivery Date
SOH_CUCODE                        Customer Code
SOH_FK_CUSTOMER                Foreign Key to Customer Table

Table: GUSER.F_CUSTOMER
CUS_PRIMARYKEY                        Customer Primary Key
CUS_CODE                                Customer Code
CUS_NAME                                Customer Name

As you can see the customer name is not stored against the sales order header record. (There is a very valid reason for this and it concerns normalisation, see elsewhere in this document for an explanation of normalisation.)

Now if you wish to produce a SQL Query that shows all sales orders for the first week in March, that shows the order number, order date, customer code, customer name and net value, then you need to pull in the customer name from the customer table. This means that you have to apply a join in your query, and for this first example we are going to do the join via the sales order foreign key to customer (SOH_FK_CUSTOMER) and the customer primary key (CUS_PRIMARYKEY). The query looks like this:

SELECT SOH_NUMBER,SOH_DATE,SOH_CUCODE,CUS_NAME,SOH_NET
FROM GUSER.F_SOHDR,GUSER.F_CUSTOMER
WHERE SOH_FK_CUSTOMER = CUS_PRIMARYKEY
AND
SOH_DATE BETWEEN '2004 MAR 01' AND '2004 MAR 05'

The Important things to notice here are that we have had to specify in the FROM section the fact that we wish to pull in data from more than one table. The other thing we have had to do is specify that the way we want to link the sales order to the customer is through the Primary Key / Foreign Key mechanism, you can see this as the first part of the WHERE section.

As I mentioned earlier, within my own databases I generally provide a second way of linking the data between tables, through the use of a Common or Convenient code. In this example we could have used the Customer Code stored against the order (SOH_CUCODE) and the Customer Code stored in the customer table (CUS_CODE), to link the records. This is demonstrated in the example shown below, it returns exactly the same data as the first example.

SELECT SOH_NUMBER,SOH_DATE,SOH_CUCODE,CUS_NAME,SOH_NET
FROM GUSER.F_SOHDR,GUSER.F_CUSTOMER
WHERE SOH_CUCODE = CUS_CODE
AND
SOH_DATE BETWEEN '2004 MAR 01' AND '2004 MAR 05'

The only difference between the two examples is the in the first part of the WHERE section, where I have specified the column names used to join the two tables.

NOTE: If you specify more than one table in the FROM section of your query and do not specify how to link the tables in your WHERE section, then you end up with what is know as a Cartesian Product. (THIS IS A BAD THING). A Cartesian Product is mathematically a binary operation in which two objects are combined in an ‘everything in combination with everything’ fashion. This can cause a serious system slowdown, and will return nonsensical data.

An understanding of joins is essential for you to progress to more advanced SQL Queries, and to allow you to collate and interrogate data successfully.

"Temptation rarely comes in working hours. It is in their leisure time that men are made or marred." - W N Taylor

SQL 101 1: Intro / Overview

This is the first in a series of posts designed to teach somebody the basics of SQL. It is adapted from a training course I originally wrote around 10 years ago, revamped again in 2000 and then again in 2006. The syntax has been tested against Oracle but in the vast majority of cases should work fine against any version of SQL. Have Fun !

Introduction
Information is data organised in a way that is useful. What we give you within database software is Information, what we actually store is Data.

Obviously the data is not stored in a totally random fashion. The data is stored in a structured fashion, this structure is represented by Tables and Columns.

A Table is a collection of related data. For example the table F_CUSTOMER contains all the Customer data. The table F_SUPPLIER contains all the Supplier data. A column represents a particular portion of the data, for example a column within the customer table might be called CUS_NAME. This column will contain the customers name. You can liken this structure to a spreadsheet worksheet, where each row represents a single customer and each column represents a particular type of data stored. See this diagram for clarification.

For this tutorial we are going to be concentrating on using a SQL Query Tool to retrieve data from the database and present it to you on your screen.

Overview
‘SQL’ is an abbreviation for ‘Structured Query Language’. It provides a programming language for accessing selected records from a relational database. SQL often (and possibly incorrectly) pronounced ‘Sequel’ is actually pronounced ‘ess que ell’. This confusion stems from the fact that ‘SQL’ is the successor of a language called ‘Sequel’ developed by IBM in the late 1960’s.
For even the most simple of database inquiries there are three elements that you must communicate to the system to perform the inquiry:
You must specify the Table or Tables that you wish to search.
You must specify the Columns of data you wish to retrieve.
You must specify the search conditions for the data you require.
In SQL this is achieved using the SELECT ..  FROM .. WHERE ..  clauses.
The SELECT clause is to tell the system which Columns you want to select for the inquiry. The FROM clause is used to tell the system which Table contains the records which are to be selected. The WHERE clause is used to specify the conditions that must be met for a record to be selected.
These three clauses are all that is a necessary to perform a simple query from the SQLQuery Engine. The system reads the specified Tables(s) and checks each row against the condition, if the condition is met the selected records are returned into the Results Table.
Another feature of SQL is the ability to request not the contents of the selected fields, but a function of those contents, such as the average value, sum total, maximum or minimum value, or just a count of the number of records that meet the selection criteria.

"Aim at the sun, and you may not reach it; but your arrow will fly far higher than if aimed at an object on a level with yourself." - Joel Hawes