Blog ala Vidar

SQL, AppFrame and other cool technologies

Tag Archives: Security

Microsoft Security Essentials

A couple of days ago Microsoft released MSE (Microsoft Security Essentials). This is a FREE anti virus and malware application. I have been using the beta version for several months now, and I’m very happy with it. It auto updates, scans your computer and checks everything you download from the internet without bugging you with “You need to update…”, “Your computer was just scanned for viruses” etc. It’s very light weight so it doesn’t steel your computers performance like F-Secure and other AVs.

I was curious about if this actually was as other AVs, so I found a couple of viruses and downloaded them. Surely, right after I was finished downloading them, MSE said that it had found viruses and that it was going to clean my computer. Two minutes later it said it had cleaned my computer. Awesome! So, what are you waiting for? Uninstall your slow AVs and install MSE. Remember that even though you’ve got AV, you still need to think logically when surfing!


I should probably call this FREE PORN. That way everyone would have read it, but anyways.. Although this is a bit of a boring subject, this will only take two minutes, and it can save you many hours afterwards! The topic is security. I’ve gathered some security actions after watching a couple of TechEd Sessions from Steve Riley about Security. All my bullet points are COST FREE as in no money spent, and almost no time spent implementing. Then, why don’t you already do it? I don’t care, but please start doing it.


  • Anti-malware
    This includes virus, spyware, adware+++. Vista comes with Windows Defender, but AVG FREE is a good alternative.
  • Be skeptical!
    Never give your password to ANYONE. This includes your administrator! If I really want/need (for testing purposes) to use your account, I can reset your password to something.
    Never click on anything that you don’t know the results of. For example, lately I’ve gotten many links from a contact on MSN. Typically “Try this cool website:”. I have of course changed the host so you won’t click on it 😉 The reason I got suspect was that this person is Norwegian. Why should she start a conversation with an English sentence and a link? And why is she offline? She’s clicked on one of those links herself, added her msn address and password to the site, and there you go. This also goes to e-mails. If for some reason spam gets through our very tough spam-filter, be skeptical! For the record, I’ve blocked this person from MSN. When she’s stopped clicking on stupid links, I’ll unblock her.
  • Do not run as administrator
    Most common applications does not have any problems running as normal user
  • Ensure Microsoft update is running frequently
  • Firewall
    Both XP and Vista comes with built-in firewalls. DO NOT DISABLE THEM! If you’ve got apple, throw it in the garbage and buy a real computer.
  • Lock your computer when you leave it
    THIS COST YOU about 14 calories, but believe me, you want to loose these 14 calories. Have you ever thought about how easy it is to for example go in to the Omega HQ office at for example lunch time, go in to an empty office and play with your computer? Many of you leave your computer when you’re logged on to MarketMaker, logged in with management studio to our SQL Servers and maybe remote desktop. Now, use one minute to think about what the intruder could do, just because you want to save your 14 calories. This is the easiest security action to take, less time consuming and it’s totally cost free, and also, it’s the most efficient!
  • Uninstall unused applications
    This will not only increase security, it will also increase your computers performance!
  • Use a good password
    Your name is not a good password. Neither is the one you’ve used for 10 years. Use a passphrase and change it every now and then. For example: ILoveEasySecurityActions! It’s easy to remember, it’s secure because of the length, the use of lower and uppercase, and it’s got special signs (!).


  • Anti-malware
    For filestores
  • Firewall
    All windows servers comes with built in firewalls. Leave them on, and also install a hardware firewall on your network to protect you.
  • Microsoft Update
  • Reduse the amount of admins
    The more people that have access, the more likely the server is to be exploited!
  • Uninstall unused applications
    SQL Servers should NOT run ANYTHING ELSE than SQL Server. Same goes to web-servers, DNS servers etc!

Omega’s security conecpt

Wouldn’t it be a great world if everyone had access to everything, but only used the things they should be using? Well, unfortunately humans do things they are not supposed to do. Either it’s because of curiosity, or stupidity. Because of this we have a security concept in Omega built on both SQL server security and our own AppFrame Security. Today I’m going to go into details about how this works.

We separate the security into two sections, SQL and AppFrame. First, let’s have a look at how SQL security is built up.

SQL Security

Login – The account you are using when authenticating to the server
User – The account in the database which is linked up to your login, to specify which databases you’ve got access to.
Roles – A database “group” which is used for adding permissions. Instead of adding permissions to ALL (maybe a couple of thousand) users, you add them to a role and add permissions to this role.

We got one role, called af_user. All users should be a member of this role. This role got “SELECT *” permissions to all views, EXECUTE permissions on stored procedures and functions. When it comes to tables, we have a special concept here. We give af_user only access to the PrimKey and Timestamp fields on the tables. This is because PrimKey is the PRIMARY KEY on the table. Timestamp is the row-version field, so the application knows if someone else has updated the row since you last picked it up. af_user also got full INSERT, UPDATE and DELETE permissions to the tables. This is actually why we are using views (tbv, tbx). The user got full access to the view. When he’s trying to update data from the view, it will update the table. When access tries to update (or delete) a record, it uses a property called “unique table”. Here you specify which table should be updated. So, access actually uses the table, and not the view, when running updates (delete, insert etc). Therefore, the user needs access to SELECT PrimKey and Timestamp in the table.


Recordsource: SELECT * FROM stbv_System_Groups

You try to change the Description of the Administrators group in the Omega Domain. Access will then run something like this query to the server:

UPDATE stbl_System_Groups
    SET Description='Immortal users'
    WHERE PrimKey='C35F02E6-BA01-4DFD-A697-D4A2A6B8744A'
      AND Timestamp='0x000000000BB2BAC5'

Notice it is using stbl, and not stbv, even though you’re using stbv in the recordsource. This is because access reads the view and sets the unique table property itself if it’s not set by the developer.

AppFrame Security

In AppFrame we got both MasterGroups and Groups. The difference between these is that Groups are domain dependant. MasterGroup is not. So, what is a domain? Well, it’s used to separate data into areas where they belong. For example, in Statoil they use one domain for each project. Snohvit got one domain, OrmenLange got one domain, KEP2010 got one etc. This is because the people working with Snohvit doesn’t care about what’s happening at Langeled. So, therefore we got Groups which are domain dependant. But, we also got (Master) Groups which are not domain dependant. Typical groups like Administrators, CMS RO and CMS RW. Master Groups is like a template for Groups. Take “Administrators” as an example. We add all permissions (I’ll come back to this subject) we need as administrator (mainly SUID on all tables), and then assign it to domains. What actually happens when you assign a Master Group to domains is that you make a new group in that domain, based on the Master Groups permissions. If a user is member of the Master Group, he will automatically be a member of the Groups which “belongs” to the Master Group. If you want one user to not be an Administrator in all domains, only Snohvit, you add him to the Group in the Snohvit domain, and not in the Master Group. Smart? Well, be careful! If you delete a member of the Master Group, you will also delete that member from the Groups which has this Master Groups as a parent.

You might have noticed, we use both tbv and tbx as table views.

Tbv’s are used to get data from a table in your current domain.
Tbx’ are used to get data from a table in ALL domains you’ve got access to.

Table Rows Permissions

In each Group we got a set of Table Rows Permissions. This table contains the following fields:

Domain – to specify which domain this permission set belongs to.
GroupRef – The PrimKey for the group this permission set belongs to.
TableID – The table name this permission set belongs to.
PGrant – Which permissions the group got on this table. S for SELECT, U for UPDATE, I for INSERT and D for DELETE. Min: S, max: SUID
Criteria1, Criteria2, Criteria1Revoke, Criteria2Revoke – I’ll get back to these later.
MasterTRRef – Defines if this record has a Master Table Row. If it does, this group is controlled by a Master Group.

All our tbv’s should look something like this:

	FROM dbo.stbl_System_Groups (NOLOCK)
			FROM sviw_System_MyPermissionsCurrentDomain (NOLOCK)
			WHERE ISNULL(Domain, '') = stbl_System_Groups.Domain
			  AND TableID = 'stbl_System_Groups')

sviw_System_MyPermissions_CurrentDomain will return ALL your permissions in your current domain. Your current domain is saved in the field CurrentDomain in stbl_System_Users. This query will look for records with TableID = ‘stbl_System_Groups’ and make sure it only return records where stbl_System_Groups.Domain is like your current domain. This is how the SELECT works.


Now, let’s move on to UPDATE, INSERT and DELETE. These actions are handled by triggers. stbl_System_Groups_ITrig is the INSERT trigger for this table. In this trigger you’ll find:

IF @@RowCount = 0 RETURN
IF IS_MEMBER('db_owner') = 1 GOTO ContinueTransaction

		FROM dbo.sviw_System_MyPermissions MyP
		JOIN Inserted
			ON ISNULL(MyP.Domain, '') = ISNULL(Inserted.Domain, '')
		WHERE MyP.TableID='stbl_System_Groups'
		  AND MyP.PGrant LIKE '%I%') GOTO ContinueTransaction

    RAISERROR ('---- No insert permissions on table: stbl_System_Groups ----',18,1) ROLLBACK TRANSACTION


What happens here is that it first checks if there’s any records “thrown” at the trigger. If not, it quits the trigger. Then it checks if you are a member of the builtin db_owner database role. If you are, you should have access to the table, so no need in checking AppFrame permissions. If you are not a member of this role, it will continue, to check if you got records with INSERT permissions on this table. If you do, you continue to ContinueTransaction, if not, you get an error saying “—- No insert permissions on table: stbl_System_Groups —-“. 18 is the severity level of this message. 18 is the highest normal (mortal) users are allowed to use. This will cause an MsgBox to show in access. If this message is raised, the trigger will rollback all changes.


I said I would come back to Criteria1, Criteria2 etc later. Now it is “later”. Till now I’ve only told you about table level security. Now, let’s discuss ROW level security. As a part of AppFrame Security, you’ve got the privilege of using row level security. This is actually one of the main reasons we’re not only using SQL security. MSSQL are not supporting row level security. They said they would in 2005, then they said they would in 2008. Still no support for it though! In other words, we got something more fancy than Microsoft 🙂 Anyhow. This means that you can specify WHICH rows you want a user to be able to see, add, update or remove by the use of a filter. This is actually what Criteria1 and Criteria2 are used for. Here you specify the filter. Which fields to filter on is desided in stbl_System_DBTables. This table contains the following fields (I’ve removed the fields you do not need to worry about):

  • ObjectName – The table name
  • CField1 – The first field to have a filter on if using row level security. This uses LIKE
  • CField2 – The second field to have a filter on if using row level security. This uses LIKE
  • CField1Revoke – (bit) Set this to 1 if you’d like CField1 to use “NOT LIKE” instead of “LIKE”.
  • CField2Revoke – (bit) Set this to 1 if you’d like CField2 to use “NOT LIKE” instead of “LIKE”.
  • PredecessorTable – If you have parent-child tables, your child table may have its parent table in this field. This way the child table will inherit the parent’s permissions.
  • NoDataLog – (bit) Skip INSERT to stbl_System_DataLog in the triggers
  • ExtendedDataLogging – (bit) If this is set, the trigger will log every change to all fields in stbl_System_DataLog
  • StandardSecurity – (bit) This table has tbv (and tbx) and standard triggers.
  • BypassSecurityForDBO – (bit) This adds the following line to the triggers: IF IS_MEMBER(‘db_owner’) = 1 GOTO ContinueTransaction
  • DenyafUserUpdatePermissions – (bit) This makes ApplyPermissions REMOVE UPDATE, INSERT and DELETE permissions from af_user on the table. Do NOT use this unless you’re only using SPs on this table!
  • GrantAccessForClusteredIndex – (bit) This makes ApplyPermissions also add SELECT permissions on all fields that are in the clustered index on the table, in addition to PrimKey and Timestamp. The reason is that when you use Access on SQL 2005, it will use the clustered index, instead of the primary key, for SELECT when updating.

To change the settings in DBTables, open Query Analyzer, rightclick the table you want to change and choose “Security (Views and Triggers)”