iLAMP

Validating file system files is a crucial part of system security. However, without the help of an integrity-checking tool such as Tripwire, this can be a daunting task.


Tripwire makes it easy. It creates a cryptographically protected database of files and directories that you define, which you can use to periodically verify the state of the system to ensure no unwanted changes have occurred.

Tripwire is easy to use, but it may be a little time-consuming to set up. However, this additional setup time will offset the amount of time previously required to determine if problems exist on the system.

Many Linux vendors ship Tripwire, so you may be able to install RPM or DEB packages. Once installed, run the twinstall.sh script to generate the local and site keys used to protect your configuration, policy, and database files.

The default Tripwire policy file may generate a lot of missing file errors, and it may not cover everything you want to observe. You can use your favorite editor to change the policy to match your system and requirements. On Mandrake Linux, the policy file is /etc/tripwire/twpol.txt.

If you want to modify the policy file after creating the initial database, change the clear text copy (twpol.txt), and generate the new protected copy by using the following:

# twadmin --create-polfile --cfgfile /etc/tripwire/tw.cfg --site-keyfile /etc/tripwire/site.key /etc/tripwire/twpol.txt


After changing the policy file, initialize the database again using the following:

# tripwire --init

Finally, create a cronjob to execute the Tripwire check daily:

# tripwire --check

For more information, check out the Tripwire Web site.

By Arthur Fuller, Builder.com | Thursday, December 30 2004 3:22 PM

All developers make mistakes from time to time; sometimes this happens because we fail to build in obvious but unstated requirements.

Here's an example: Your database is up and running successfully but various errors in data entry and updating mandate a new requirement: add two columns (LastUpdated and UpdatedBy) to every table. There are hundreds of tables, so it's impractical to perform this task by hand.

This is clearly a chunk of reusable code, so you want to write it once and ensure that it can work on every database. (You might have to refine it slightly for each new database by, for example, changing the column names. But the idea is, you want a procedure to walk all the tables in a database and add one or more columns.)

It's easy to obtain the list of user tables:
SELECT Name FROM sysobjects WHERE Type = 'U' ORDER BY Name

The result set is more conveniently handled as a user-defined function that returns a table:
CREATE FUNCTION dbo.UserTables_fnt
()
RETURNS TABLE
AS
RETURN
(SELECT TOP 100 PERCENT name
FROM dbo.sysobjects
WHERE type = 'U')
ORDER BY name
)

Suppose that you want to add a column called LastUpdated (of type TimeStamp) to every table in the database. To add such a column to any given table, e.g., Customers, your command would look like this:
ALTER TABLE MyDB.dbo.Customers ADD LastUpdated TimeStamp NULL

Now you create a query (view, stored procedure, UDF) that manufactures the statements you need to accomplish your task:
SELECT
'ALTER TABLE NorthwindTest.dbo.[' + name + '] ADD LastUpdated TimeStamp NULL'
AS CommandText
FROM dbo.UserTables_fnt()

The results
Assuming that you make a copy of the Northwind sample database called NorthwindTest and run this code against it, the results look like this:
ALTER TABLE NorthwindTest.dbo.[Categories] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[CustomerCustomerDemo] ADD LastUpdated TimeStamp
NULL
ALTER TABLE NorthwindTest.dbo.[CustomerDemographics] ADD LastUpdated TimeStamp
NULL
ALTER TABLE NorthwindTest.dbo.[Customers] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[dtproperties] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Employees] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[EmployeeTerritories] ADD LastUpdated TimeStamp
NULL
ALTER TABLE NorthwindTest.dbo.[Order Details] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Orders] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Products] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Region] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Shippers] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Suppliers] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Territories] ADD LastUpdated TimeStamp NULL

I used brackets around the table names because they guard against a problematic table name: Order Details. In the absence of spaces, the parser doesn't care about the brackets; but in the presence of spaces, the generated SQL will cause an error.

You can deal with this result set in a variety of ways, including paste it into Query Analyzer and execute it, turn it into a stored procedure, or turn it into an updateable view. Given its one-off nature, I prefer the first choice.

I love writing code that writes code because then I don't have to do it--and it never misspells anything. You can extend this concept to perform just about any DML action that you could perform by hand.

If you're going to try this technique, I strongly encourage you to create a SELECT query first, which manufactures the desired DML, so you can inspect it and check its syntax before running it.

Arthur Fuller has been developing database applications for more than 20 years. He frequently works with Access ADPs, Microsoft SQL 2000, MySQL, and .NET.