Jason Dentler

This is a continuation of my posts about auditing with triggers.

The Problem

My previous examples have used system_user as the source of user information in the audit log, which required you to impersonate users down to the database level, that is to open the connection in the context of the application user, not some shared generic account. There are several reasons why this is not a good idea:

1. You either don’t have per-user credentials appropriate to create SQL server accounts (typical in public-facing web applications), or are unable to do so for various reasons ranging from security concerns to account management workload.
2. When each user authenticates with their own credentials, this effectively disables connection pooling. Especially in high-volume web scenarios, this will kill performance and put a strain on server resources.
3. I believe the loss of connection pooling will also burn through your license pool with per-user licensed SQL servers. I may be mistaken on this point, as I haven’t worked with a per-user licensed SQL server for the better part of a decade.
4. If you are unwilling to use SQL server authentication, you will most likely have to set up Kerberos constrained delegation so that your database server trusts your web server to authenticate users. This is a royal PITA to implement.

The Trouble With Triggers

As far as input inside a trigger, you only have access to the data being manipulated, system functions, and very little else. You can’t use SELECT statements in triggers either, at least not against normal tables and views.

SQL Context Info

Starting with MS SQL Server 2000, you can associate up to 128 bytes of data with the current connection using the SET CONTEXT_INFO command. Starting with SQL 2005, you can use the CONTEXT_INFO function to retrieve this data. This data is available everywhere, including triggers.

If you use connection pooling (and you probably are), this data is not reset when the connection is reused.

To set your context info data, use the SET CONTEXT_INFO command like so:

DECLARE @Ctx varbinary(128)
SELECT @Ctx = CONVERT(varbinary(128), 'My context data goes here.')
SET CONTEXT_INFO @Ctx

SET CONTEXT_INFO can only be used with variables and constants, meaning you can’t CONVERT data types or concatenate strings or any other calculation in the same command.

To retrieve the context data, use the CONTEXT_INFO function:

DECLARE @CtxData varchar(128)
SELECT @CtxData = CONVERT(VarChar(128), CONTEXT_INFO())
PRINT @CtxData

This will output ‘My context data goes here.\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0’ etc. Note – The \0 is ASCII character 0 (NULL). Why? We’re converting the string to fixed-length binary and then converting it back.

Know Your Roots

There was a time when programmers weren’t protected from the fact that strings are just chunks of memory. Just like programmers in the old days, you have two options: Pascal strings or C strings. You can stuff a length byte at the beginning of your context as in Pascal, or you can search for the null termination of your string as in the C language.

Also, because nchar and nvarchar are UNICODE UCS-2 strings, they use two bytes (of your 128 maximum) per character. char and varchar only use one byte per character, but the character set is limited. It’s a trade-off.

How does this help me?

We can use context info data to get information from our application in to our audit triggers. For example, we can get the current user name from our application or evem some identifying information about the location in our application, such as the name of the controller and action that triggered the event. Just be conscious of the 128 byte limitation. It’s probably better to store an id for more information such as the id of the user account, or an id that references some lookup table with application locations.

The Code, Sir.

We will override the GetConntion() method of DriverConnectionProvider so that every NHibernate connection will have the current username stored in the context.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate.Connection;
using System.Data;

namespace NHibernate.Connection
{
    public class ContextDriverConnectionProvider : DriverConnectionProvider
    {

        public override System.Data.IDbConnection GetConnection()
        {
            var conn = base.GetConnection();
            SetContext(conn);
            return conn;
        }

        private const string CONTEXT_SQL = "declare @Length tinyint\ndeclare @Ctx varbinary(128)\nselect @Length = len(@username)\nselect @Ctx = convert(binary(1), @Length) + convert(varbinary(127), @username)\nset context_info @Ctx";

        protected virtual void SetContext(IDbConnection conn)
        {
            IDbCommand cmd = conn.CreateCommand();
            IDbDataParameter param = cmd.CreateParameter();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = CONTEXT_SQL;

            param.ParameterName = "@username";
            param.DbType = DbType.AnsiString;
            param.Size = 127;
            param.Value = System.Environmnt.UserName;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
        }

    }
}

This will run the following SQL code when NHibernate opens a SQL connection. Note that @username is a variable defined as a parameter on our IDbCommand. Also, I chose to use Pascal strings with the length stored in the first byte.

declare @Length tinyint
declare @Ctx varbinary(128)
select @Length = len(@username)
select @Ctx = convert(binary(1), @Length) + convert(varbinary(127), @username)
set context_info @Ctx

We’ll alter our trigger code to get the username from the context instead of system_user.

set nocount on
declare @Username varchar(127)
declare @Length tinyint
declare @Ctx varbinary(128)
select @Ctx = CONTEXT_INFO()
select @Length = convert(tinyint, substring(@Ctx, 1, 1))
select @Username = convert(varchar(127), substring(@Ctx, 2, 1 + @Length))
if (@Username is null) select @Username = SYSTEM_USER

Security Implications

I am not a security expert. I am certainly not a SQL security expert. However, I believe this method is secure for most scenarios. In my opinion, this is at least as secure as auditing through NHibernate interceptors and events.

Here’s a few ways to circumvent the system, and how you can prevent that from happening:

• Manipulate the audit logs directly. Obviously, in any audit scenario, you should set permission on your audit tables to make them select and insert only.
• Alter the context_info data directly. A user overwrites their own context_info with that of another user, effectively blaming their actions on someone else. This can be done through a SQL injection attack some other security breach. If you go on vacation with the front door open, don’t be surprised when your house is robbed.
• Fail to set context_info. Again, this requires some other security breach. You may also change your trigger code to ROLLBACK TRANSACTION and RAISERROR when context_info is not set. This will force a rollback of the transaction (or in the case of no explicit transaction, the SQL statement that caused the trigger), abort the remainder of the SQL batch, and report an error. The approach requires everyone, including your DBA, to set an appropriate context_info before altering any audited data.

This is certainly my worst blog post this year, but don’t worry. I can do worse.

- Jason

In my December 23rd post, I showed you how to generate audit tables and triggers from your NHibernate mappings. Since then, I had an amazing conversation with Fabio Maulo, NHibernate project owner, where among other things, he showed me a much better way to accomplish the same task.

In my last post, we used the NHibernate mapping as source data to create our script by hand. With this version, we’re going to take it a step further. When you add an IAuxilaryDatabaseObject to the mappings, it will be included in the ddl output of SchemaExport. These have no effect outside of SchemaExport. It’s a simple way to include additional items in your database setup scripts.

In our main program, we set up some additional columns to hold our audit data – user, timestamp, and action. These columns will be added to each audit table. Then, we use TriggerStrategy to add auxiliary database objects to our configuration.

        static void AddAuditing(Configuration cfg)
        {
            var columns = new List<auditcolumn>();
            columns.Add(new AuditColumn() {
                Name = "AuditUser",
                SqlType = "sysname",
                IsNullable = false,
                ValueFunction = delegate(TriggerActions actions)
                {
                    return "system_user";
                }
            });

            columns.Add(new AuditColumn()
            {
                Name = "AuditTimestamp",
                Value = new SimpleValue() { TypeName = NHibernate.NHibernateUtil.DateTime.Name },
                IsNullable = false,
                IncludeInPrimaryKey = true,
                ValueFunction = delegate(TriggerActions actions)
                {
                    return "getdate()";
                }
            });

            columns.Add(new AuditColumn()
            {
                Name = "AuditOperation",
                Value = new SimpleValue() { TypeName = NHibernate.NHibernateUtil.AnsiChar.Name },
                Length = 1,
                IsNullable = false,
                ValueFunction = delegate(TriggerActions actions)
                {
                    switch (actions)
                    {
                        case TriggerActions.Insert:
                            return "'I'";
                        case TriggerActions.Update:
                            return "'U'";
                        case TriggerActions.Delete:
                            return "'D'";
                        default:
                            throw new ApplicationException("Triggers handling multiple event types (INSERT, UPDATE, DELETE) are unsupported. ");
                    }
                }
            });

            new NHibernate.Audit.TriggerStrategy(
                new NHibernate.Audit.AuditTableBuilder(),
                new NHibernate.Audit.MsSqlTriggerBuilder(),
                columns).Configure(cfg);
	}

Trigger strategy simply hands off the work of audit table builder and trigger builder.

Audit table builder creates an audit table object for each table already in your mapping. It is also responsible for calculating the name of each audit table. Yes, the naming should probably be pulled in to a separate class, but oh well.

Audit table uses NHibernate’s table object internally to create a duplicate of each table, plus the additional audit columns, then exposes the drop/create scripts for this table object as an IAuxilaryDatabaseObject. I’ve ommitted some of the name-related static below.

using System.Collections.Generic;
using NHibernate.Mapping;

namespace NHibernate.Mapping
{

    public class AuditTable : AbstractAuxiliaryDatabaseObject
    {

        public AuditTable(string Name, Table DataTable, IEnumerable<AuditColumn> AuditColumns)
        {
            dataTable = DataTable;
            auditTable = new Table();
            auditColumns = AuditColumns;
            BuildAuditTable(Name);
        }

        protected readonly Table dataTable;
        protected readonly Table auditTable;
        protected readonly IEnumerable<AuditColumn> auditColumns;

        protected virtual void BuildAuditTable(string Name)
        {
            auditTable.Catalog = dataTable.Catalog;
            auditTable.Schema = dataTable.GetQuotedSchema();
            auditTable.Name = Name;

            foreach (Column column in dataTable.ColumnIterator)
                CopyColumn(column);

            CopyPrimaryKey(dataTable);

            AddAuditColumns();
        }

        protected virtual void CopyColumn(Column column)
        {
            auditTable.AddColumn((Column)column.Clone());
        }

        protected virtual void CopyPrimaryKey(Table dataTable)
        {
            if (dataTable.PrimaryKey != null)
            {
                var pk = new PrimaryKey();
                auditTable.PrimaryKey = pk;
                foreach (Column c in dataTable.PrimaryKey.ColumnIterator)
                {
                    Column auditTableColumn = auditTable.ColumnIterator.WithName(c.Name);
                    pk.AddColumn(auditTableColumn);
                }
            }
            else
            {
                throw new System.NotSupportedException("Tables without primary keys are not supported.");
            }
        }

        protected virtual void AddAuditColumns()
        {
            foreach (AuditColumn source in auditColumns)
            {
                Column dest = (Column)source.Clone();
                auditTable.AddColumn(dest);
                if (source.IncludeInPrimaryKey)
                    auditTable.PrimaryKey.AddColumn(dest);
            }
        }

        public override string SqlCreateString(NHibernate.Dialect.Dialect dialect, NHibernate.Engine.IMapping p, string defaultCatalog, string defaultSchema)
        {
            return auditTable.SqlCreateString(dialect, p, defaultCatalog, defaultSchema);
        }

        public override string SqlDropString(NHibernate.Dialect.Dialect dialect, string defaultCatalog, string defaultSchema)
        {
            return auditTable.SqlDropString(dialect, defaultCatalog, defaultSchema);
        }
    }

}

That handles our audit tables. Now let’s look at the triggers.

Abstract trigger builder builds insert, update, and delete triggers for each table. You should override BuildTriggerBody to customize the body of your trigger. I’ve created MsSqlTriggerBuilder to output my preferred trigger code.

NHibernate dialects have no concept of triggers and lack the required strings to build a DBMS-specific CREATE TRIGGER statements. Instead, you specify a trigger type inherited from AbstractTrigger specific to your database to build the CREATE TRIGGER statement. I’ve included MsSqlTrigger.

That’s all folks. You can grab the code from my SVN repository at http://basiclyeverything.googlecode.com/svn/trunk/AuditLogExample/

Since I’m still using the system_user function to get the current user, you will still need to impersonate the user all the way down to the database level. I’ve found a way around this, and I will show it to you next time. Until then, Happy New Year.

Last week, Fabio was showing me the Parallel Models pattern and explaining some of the work he’s doing with it. It is a VERY powerful concept.

What I’m going to show you today is the poor-mans version. It certainly doesn’t qualify as parallel model, but it’s pretty good audit logging if you meet the prerequisites.

The Problem

You need some audit logs for your current application, but you’re behind. In fact, you started the project already a month behind schedule. This needs to be quick and reliable. It’d be great if it didn’t intrude on the application, because you’ve got enough rattling around in your head already.

It sounds like you want SQL trigger audit logging. By the way, let’s say you’re using MS SQL Server. Let’s also say your application impersonates the user all the way down to the database level. That means your connection string says integrated security=SSPI or your dba enjoys the extra work of maintaining a sql login for each user. Those are prerequisites for this approach.

What’s a trigger?

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
- CREATE TRIGGER, MSDN

If tables are the data structures in your database and stored procedures are the methods, then triggers are the event handlers. We’re concerned with three specific events – INSERT, UPDATE, and DELETE. We want to log each time those events happen to rows in our tables.

Trigger auditing has been around since at least the 1990s – since before SQL Server was even worth a damn. That’s MSSQL 6.5 folks. It’s sturdy and well-proven, although poorly implemented triggers will bring your server to a screeching halt. Fair warning – mine may be one of them.

We want to know what the data was before the change, what the data is now, who changed it, and when.

When you insert records in a table, the insert trigger for that table executes. The same goes for update and delete.

Triggers have two imaginary tables to help us with this task – inserted and deleted. Inside a trigger, they mostly work like real tables. Inserted always has the new data. Deleted always has the old data (from before the change.) So, in the case of an INSERT trigger, deleted is empty. For a DELETE trigger, inserted is always empty, since there is no new data with a deletion. For an UPDATE trigger, they both have data.

How does this help us? Well, suppose we have want to audit a table called Person. Let’s make a table called Audit_Person with the exact same columns as Person. When we delete data from Person, our DELETE trigger on Person will run. Inside our trigger, the deleted table has all of the rows that were just deleted from Person. We can copy that data to our audit table so we have a record of it by doing INSERT INTO Audit_Person SELECT * FROM deleted. Since the columns match up exactly, it works.

Wait a minute though. We need to know who deleted that data and when. Oh, and we need some way to indicate the different events.

Lucky for us, there’s some easy functions in SQL. system_user will give us the login of the current user – either a sql account name or a windows account name. Of course, GetDate() will tell us the current time and we’ll just write that to the table.

Let’s go ahead and write out a full trigger.

CREATE TRIGGER Person_onInsert ON Person FOR INSERT AS
SET NOCOUNT ON
INSERT INTO Audit_Person SELECT *, system_user, getdate(), 'I' FROM Inserted

Oh yeah, we need to add some fields in Audit_Person to hold the extra stuff. system_user is a custom sql type called sysname. It’s really nvarchar(256). Of course, getdate() is a datetime. Finally, we’re passing a capital letter I (for Inserted) as the last field, so we’ll need to make that a char(1) type.

The SET NOCOUNT ON command is there to suppress the extra (X rows affected) messages that SQL spits out. You’ll know what I’m talking about if you’ve ever clicked on the Messages tab after running a query.

So, that’s the audit logging. Now we just need to create an audit table and 3 triggers for each table in your application.

What does that get us?

Suppose we set up the audit table and all 3 triggers, then ran these 5 SQL commands:

INSERT INTO [Person] VALUES (NEWID(), 'Jason 1')
INSERT INTO [Person] VALUES (NEWID(), 'Jason 2')
INSERT INTO [Person] VALUES (NEWID(), 'Jason 3')
UPDATE Person SET Name = 'Jason 4'
DELETE FROM Person 

Our Audit_Person table would have these rows: (click the picture for 100% size)

Being Lazy

You’ve got 200 tables? Well, you’d better get to typing then.

OK. Just like you can generate a SQL script using NHibernate.Tool.hbm2ddl.SchemaExport, we can generate a script to build our audit tables and triggers from the mappings.

Now, we’re not going to parse out the XML in the hbm files. We’re going to read the proposed table schema – what SchemaExport will give you – directly from the NHibernate configuration object. This is the same way SchemaExport does it’s magic.

Here’s the code:

using System.IO;
using NHibernate.Cfg;
using NHibernate.Mapping;
using NHibernate.AdoNet.Util;

namespace AuditLogExample
{

    public class AuditScripter
    {

        public const string USER_DEFAULT = "system_user";
        public const string TIMESTAMP_DEFAULT = "getdate()";

        private enum Operations
        {
            Insert,
            Update,
            Delete
        }

        public AuditScripter(Configuration Configuration)
        {
            cfg = Configuration;
            mapping = cfg.BuildMapping();
            dialect = NHibernate.Dialect.Dialect.GetDialect(cfg.Properties);
            formatter = (NHibernate.Util.PropertiesHelper.GetBoolean(NHibernate.Cfg.Environment.FormatSql, cfg.Properties, true) ? FormatStyle.Ddl : FormatStyle.None).Formatter;
        }

        private readonly NHibernate.Dialect.Dialect dialect;
        private readonly Configuration cfg;
        private readonly NHibernate.Engine.IMapping mapping;
        private readonly IFormatter formatter;

        public void Execute(System.IO.TextWriter script)
        {
            var mappings = cfg.CreateMappings(dialect);
            ScriptObjectsForTables(mappings, script);
        }

        private void ScriptObjectsForTables(Mappings mappings, TextWriter script)
        {
            var tables = mappings.IterateTables;
            var auditTableBuilder = new AuditTableBuilder();
            foreach (Table table in tables)
            {
                if (table.IsPhysicalTable)
                {
                    var auditTable = auditTableBuilder.BuildAuditTable(table);
                    ScriptObjectsForTable(table, auditTable, script);
                }
            }
        }

        private void ScriptObjectsForTable(Table dataTable, Table auditTable, TextWriter script)
        {
            ScriptAuditTable(auditTable, script);
            ScriptInsertTrigger(dataTable, auditTable, script);
            ScriptUpdateTrigger(dataTable, auditTable, script);
            ScriptDeleteTrigger(dataTable, auditTable, script);
        }

        private void ScriptAuditTable(Table auditTable, TextWriter script)
        {
            string defaultCatalog = NHibernate.Util.PropertiesHelper.GetString(NHibernate.Cfg.Environment.DefaultCatalog, cfg.Properties, null);
            string defaultSchema = NHibernate.Util.PropertiesHelper.GetString(NHibernate.Cfg.Environment.DefaultSchema, cfg.Properties, null);

            string sqlCreateString = auditTable.SqlCreateString(dialect, mapping, defaultCatalog, defaultSchema);

            script.WriteLine(formatter.Format(sqlCreateString));

            var comments = auditTable.SqlCommentStrings(dialect, defaultCatalog, defaultSchema);

            foreach (string line in comments)
                script.Write(formatter.Format(line));

            script.WriteLine(formatter.Format("go")); // create trigger statements must in their own batch
        }

        private void ScriptInsertTrigger(Table dataTable, Table auditTable, TextWriter script)
        {
            ScriptTrigger(string.Format("{0}_onInsert", dataTable.Name),
                "insert", "'I'", "inserted",
                dataTable, auditTable, script);
        }

        private void ScriptUpdateTrigger(Table dataTable, Table auditTable, TextWriter script)
        {
            ScriptTrigger(string.Format("{0}_onUpdate", dataTable.Name),
                "update", "'U'", "inserted",
                dataTable, auditTable, script);
        }

        private void ScriptDeleteTrigger(Table dataTable, Table auditTable, TextWriter script)
        {
            ScriptTrigger(string.Format("{0}_onDelete", dataTable.Name),
                "delete", "'D'", "deleted",
                dataTable, auditTable, script);
        }

        /// <summary>
        /// Scripts out a create trigger sql statement
        /// </summary>
        /// <param name="triggerName">The name of the trigger</param>
        /// <param name="triggerOperation">insert, update, or delete</param>
        /// <param name="auditOperationValue">Value to insert for AuditOperation field</param>
        /// <param name="triggerTable">The "built-in" trigger table with affected data - inserted or deleted</param>
        /// <param name="dataTable"></param>
        /// <param name="auditTable"></param>
        /// <param name="script"></param>
        private void ScriptTrigger(string triggerName, string triggerOperation,
            string auditOperationValue, string triggerTable,
            Table dataTable, Table auditTable, TextWriter script)
        {

            Table trigger; // Just to calculate the name of the trigger
            trigger = new Table(triggerName);
            trigger.Schema = dataTable.Schema;
            trigger.Catalog = dataTable.Catalog;
            trigger.IsQuoted = dataTable.IsQuoted;

            string dataTableName = dataTable.GetQualifiedName(dialect);
            string auditTableName = auditTable.GetQualifiedName(dialect);
            triggerName = trigger.GetQualifiedName(dialect);

            string triggerHeading;
            string triggerBody;

            triggerHeading = string.Format("create trigger {0} on {1} for {2} as", triggerName, dataTableName, triggerOperation);
            triggerBody = string.Format("insert into {0} select *, {1}, {2}, {3} from {4}", auditTableName, TIMESTAMP_DEFAULT, USER_DEFAULT, auditOperationValue, triggerTable);

            script.Write(formatter.Format(triggerHeading));
            script.Write(formatter.Format("set nocount on"));
            script.Write(formatter.Format(triggerBody));
            script.WriteLine(formatter.Format("go")); // create trigger statements must in their own batch

        }

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate.Mapping;

namespace AuditLogExample
{
    public class AuditTableBuilder
    {

        public Table BuildAuditTable(Table dataTable)
        {
            Table auditTable = new Table(string.Format("Audit_{0}",dataTable.Name));
            auditTable.Catalog = dataTable.Catalog;
            auditTable.Schema = dataTable.Schema;
            auditTable.IsQuoted = dataTable.IsQuoted;

            foreach(Column column in dataTable.ColumnIterator)
                CopyColumn(column, auditTable);

            CopyPrimaryKey(dataTable, auditTable);
            AddAuditColumns(auditTable);

            return auditTable;
        }

        private void CopyColumn(Column sourceColumn, Table auditTable)
        {
            Column newColumn = (Column)sourceColumn.Clone();
            auditTable.AddColumn(newColumn);
        }

        private void CopyPrimaryKey(Table dataTable, Table auditTable)
        {
            var pk = new PrimaryKey();
            foreach (Column sourceColumn in dataTable.PrimaryKey.ColumnIterator)
            {
                pk.AddColumn(auditTable.Column(sourceColumn.Name));
            }
            auditTable.PrimaryKey = pk;
        }

        private void AddAuditColumns(Table auditTable)
        {

            Column ts = new Column("AuditTimestamp");
            ts.SqlType = "datetime";
            ts.IsNullable = false;
            ts.DefaultValue = AuditScripter.TIMESTAMP_DEFAULT;
            auditTable.AddColumn(ts);
            auditTable.PrimaryKey.AddColumn(ts);

            Column user = new Column("AuditUser");
            user.Length = 256; // system_user returns a sysname, which is really nvarchar(256)
            user.SqlType = "sysname";
            user.IsNullable = false;
            user.DefaultValue = AuditScripter.USER_DEFAULT;
            auditTable.AddColumn(user);
            auditTable.PrimaryKey.AddColumn(user);

            Column op = new Column("AuditOperation");
            op.SqlType = "char";
            op.Length = 1;
            op.IsNullable = false;
            auditTable.AddColumn(op);

        }

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate.Mapping;

namespace NHibernate.Mapping
{
    public static class TableExtensions
    {

        public static Column Column(this Table Table, string ColumnName)
        {
            return Table.ColumnIterator.Where(c => c.Name == ColumnName).FirstOrDefault();
        }

    }
}

I have a couple of gotchas and details to explain.

First, this works for me. I’m sure there are edge cases where everything goes horribly wrong. Please let me know when you find them.

Next, CREATE TRIGGER commands are VERY anti-social. They refuse to share a batch with any other statements. That’s why there’s a “go” between everything. In query analyzer (or the SSMS query window now), it starts a fresh batch.

If you know your SQL, this should look pretty simple. Pass an NHibernate configuration to the constructor of AuditScripter, then call Execute. The code loops through all of the physical tables in your configuration, first building a nearly-identical audit table, then creating the three triggers.

The primary key of the audit table has all the fields from the primary key of your data table, plus AuditUser and AuditTimestamp. A single user cannot alter the same record in two different ways at the exact same moment. In fact, I don’t think any two users could, but I’ll let it slide.

The code could probably be improved to automatically include some common indexes on the audit tables.

I’m sure this is the worst possible way to generate code, but I wanted something non-tool specific. I thought about T4 templates since everyone probably has Visual Studio, but this was quicker than figuring out how to get a full NHibernate configuration in to a T4 template.

Expanding the idea

You can definitely build on this idea to generate all sorts of repetitive code based on your NHibernate schema. For instance, if you’re unlucky enough to work in a shop with one of those stored-proc crazed database Nazis, this should help quite a bit. You can use this same idea to generate thousands of CRUD stored procedures in a few minutes.