It greatly pleases me to announce, HAL0003!

24

November 23, 2016 by Kenneth Fisher

I while back as part of a T-SQL Tuesday post I created HAL0001, then later HAL0002 with a long-term goal of building up a database version of HAL9000. Well, at the rate I’m going I may have to pass this on to my kids (or even grandkids). But in the mean time here is HAL0003.

  • HAL0001 randomly stopped you from making DDL changes.
  • HAL0002 stopped you from using NOLOCK in code.
  • HAL0003 will not let you touch a given table (DiscoveryOne as it happens) and will disable your login and kill your connection if you try.

 
Just as a side note: Please, please, please! Never put any of these in production. They are meant as a joke only.

-- Create a database to put SPs that use EXECUTE AS OWNER and TRUSTWORTHY
-- https://sqlstudies.com/2014/02/26/impersonating-a-server-level-permissions/
CREATE DATABASE SPs;
GO
-- Make it trustworthy
ALTER DATABASE SPs SET TRUSTWORTHY ON;
GO
USE SPs;
GO
-- Change the owner to a sysadmin (sa was just convenient)
EXEC sp_changedbowner 'sa';
GO
CREATE PROCEDURE KillUserAndConnection
WITH EXECUTE AS OWNER
AS
BEGIN
	DECLARE @sql nvarchar(2000);
	DECLARE @error nvarchar(2000);
	-- Disable the current user
	SET @sql = N'ALTER LOGIN ' + QUOTENAME(ORIGINAL_LOGIN()) + N' DISABLE';
	-- Raise a sever error.  This will disconnect the current connection.
	SET @error = N'I''m sorry Dave.  ' + ORIGINAL_LOGIN() + ' misbehaved.  I had to get rid of them.' ;
	EXEC sp_executesql @sql;
	RAISERROR (@error, 25, 1) WITH LOG;
END
GO
-- Make it so anyone can connect to this DB and run this SP.
GRANT EXECUTE ON KillUserAndConnection TO public;
GRANT CONNECT TO public;
GO

USE Test;
GO
-- Create the all important table DiscoveryOne
CREATE TABLE DiscoveryOne (
	Id INT NOT NULL IDENTITY(1,1),
	Memory BIGINT NOT NULL,
	CPUCount BIGINT NOT NULL,
	DiskSpaceInMB BIGINT NOT NULL
	);
GO

-- Here it is! HAL0003!
CREATE TRIGGER HAL0003
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE,
    CREATE_FUNCTION, ALTER_FUNCTION,
    CREATE_VIEW, ALTER_VIEW,
    CREATE_TRIGGER, ALTER_TRIGGER,
    CREATE_TABLE, ALTER_TABLE
AS
    -- Make sure that this doesn't affect itself
    IF EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)') != 'HAL0003'
    BEGIN
	-- Make sure the command isn't touching DiscoveryOne in any way.
        IF EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') LIKE '%DiscoveryOne%'
        BEGIN
		-- If it did, get rid of them.  With extreem prediduce.
		EXEC SPs.dbo.KillUserAndConnection;
        END
    END
GO

Now let’s make sure that HAL0003 works correctly!

-- Create a login and give it ddladmin permissions on the DB where DiscoveryOne exists.
CREATE LOGIN SQLTest WITH PASSWORD = 'SQLTest';
GO
USE Test;
GO
CREATE USER SQLTest FROM LOGIN SQLTest;
GO
EXEC sp_addrolemember 'db_ddladmin','SQLTest';
GO

And in another connection log in as SQLTest and run this:

USE Test;
GO
ALTER TABLE DiscoveryOne ADD Test INT;
GO

hal0003

And one last time. The HALs are meant as a joke. Some of the code in them is interesting and could make a good template for something else, but I wouldn’t ever put them in production as is.

24 thoughts on “It greatly pleases me to announce, HAL0003!

  1. SqlBarbarian says:

    Awesome. I love it. Thanks for sharing this

  2. John Ness says:

    Looking at the code, the table will protect itself even from the ‘sa’ account or anybody with the ‘sysadmin’ role. Nasty. But dropping the table has no effect. Wouldn’t the best solution be to fully protect the table from any modification and have a backdoor in the sproc if the user is ‘sa’ or has the ‘sysadmin’ role?

  3. Pete Danes says:

    This is actually not a bad idea. I’ve been messing with something where I want to be absolutely sure that nobody, not even me, accesses a table directly. I have several tables that I am unable to link via normal relationships, due to complexity of construction, but I want to be certain that modifications follow a certain set of rules, i.e, adding a record in one table automatically generates records in several other tables, and a record may not be deleted unless all associated records are deleted as well – essentially, replicating the relationship construct for this particular conglomertate of association requirements.

    • I think you’d find a regular trigger would be good enough. This specifically stops you from altering the table but not from DML commands πŸ™‚

      • Pete Danes says:

        You’re probably right, but I don’t have that much experience writing triggers yet. This occurred to me more as a way to smack my own fingers if I forget and start poking around where I shouldn’t. But more usefully, I should pull out my thumb and get up to speed on triggers. There are several other places where I could use them, but I’ve been putting it off, as something I’ll study when I have more time, which of course never happens.

        • Two important things to remember when working with DML triggers
          1) Your command won’t end till the trigger does. The slower your trigger the slower the access to the table.
          2) Don’t assume that you will always be updating a single row of data. Code as if you’d just run a 100k row update/delete/insert whatever.

  4. Pete Danes says:

    Can’t I code the trigger to reject multiple rows? This particular setup, I do not anticipate ever adding more than one record at a time. In fact, I can almost certainly state that such an attempt would be a coding error right up front. I’m also writing the app code, and in fact am the entire show for this project – soup to nuts.

    • All I can tell you is that 90%+ of every problem I’ve seen with a trigger is because it was assumed that only one row was altered at a time. You could reject anything more than one row by checking inserted & deleted for counts > 1 but I wouldn’t do it. Remember it isn’t just your application code that’s going to hit these triggers. It’s everything you do to the table even if it’s an ad-hoc update you need to run for some reason.

      • Pete Danes says:

        Hm, maybe. Although possibly I could allow multiple-row updates, and specifically reject more than one-row adds. It sounds from your description like the problems were due to the trigger writer not anticipating a heavy load, rather than intentionally rejecting it. If the trigger were to immediately kick out an explicit error on receiving more than one row, it should be obvious what’s happening. And I could always temporarily disable the trigger if I found myself in some weird situation that called for multiple adds. I can’t imagine such a scenario now – I’ve gone to considerable effort to ensure that exactly that never happens, because orphan records were a pretty fair problem here, but I suppose it’s possible.

  5. Rudy Panigas says:

    Love the HAL idea πŸ™‚ Too bad we can’t make SQL Server do text to speech like in SQL 7.0. Then you can have the system talk like HAL too.. lol

  6. Maxim says:

    Nice one. How about killing a user session that sends a certain SELECT statement? Let’s say I have two huge tables in my database. There’s a 1:n relationship between them but some user tries to cross-join the tables without including this relationship in the query. So the server receives something like this:

    SELECT * FROM BigTable1
    CROSS JOIN BigTable2
    WHERE

    Because the tables are really big, this query can eventually flood my tempdb, and at this moment this user will get disconnected. However, he will take along a few innocent users who will also get ‘tempdb is full’ errors if their queries are sent before the server finishes rolling back the transaction.

    Any idea how we could kill this session before it fills up tempdb?

    Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,614 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: