Blog ala Vidar

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.

Example:

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:

SELECT *
	FROM dbo.stbl_System_Groups (NOLOCK)
	WHERE EXISTS (SELECT *
			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.

Triggers

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

IF EXISTS (SELECT MyP.*
		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

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

ContinueTransaction:
    SET NOCOUNT ON

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.

DBTables

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)”