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