It greatly pleases me to announce, HAL0003!
27November 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
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.
SQL Studies 
Awesome. I love it. Thanks for sharing this
Thanks 🙂 It was fun to write. Now I just need to come up with an idea for HAL0004.
Usage of a cursor is punished by a randomized Trump quote? 🙂
Funny but too much like HAL0002 🙂
Fair enough. Then what about adding the user to the Kiddie Pool for those unworthy. This would be a IO governed pool with IO max 5, and cpu throttled to max 5.
Oh that’s not bad. I’ll give that some thought and add it to the list.
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?
Well, let’s remember that this is joke code. But other than that the only way a sysadmin would be able to modify the table with this code in place would be to disable the trigger, modify the table, then re-enable the trigger.
I understand this code seems more like entertainment, but in some environment’s it may have some use. I have worked on some systems where I recorded who made the changes as there were applications that built and destroyed objects all the time. Yes, it was a risky trigger. i was able however to make a login that was immune to the trigger and use that to break glass in case of emergency.
Well, worst case you could disable the trigger before making any needed changes. Generally though I would think you could just not grant permissions to make the changes to anyone but that one login.
‘See, the first baseman’s name is Hu, but it sounds like ‘Who’, like ‘Who is it?’, and…’
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 🙂
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.
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.
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.
*nod* you could certainly create an INSERT trigger that rejects anything where SELECT COUNT(1) FROM inserted > 1. Still not sure it would be a good idea though.
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
That would be awesome 🙂 It’s ok though. By the time I get into the 7-8000 range I’m sure they will have a voice synthesizer built in.
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.
That would be cool but unfortunately there are no triggers (to my knowledge) on SELECT statements. You might be able to do something with a DDL in tempdb or with resource governor.
. 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.
[…] 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. […]
[…] fun scripts: HAL0001, HAL0002, HAL0003, and […]