Showing posts with label Security. Show all posts
Showing posts with label Security. Show all posts

Tuesday, June 26, 2007

Using SET IDENTITY_INSERT as a normal database user in SQL Server 2005.

In earlier versions of Microsoft SQL Server it was impossible to grant "SET IDENTITY_INSERT" rights to a normal user. To have permission to do an identity_insert (even as part of a stored procedure) you needed to grant the user ddl admin rights which is quite a heavy permission to grant to a normal user.

Luckily this problem can be solved in SQL Server 2005 by using Certificates and code signing.

Let's set up a demo environment.

create database identity_insert_test
use identity_insert_test
exec sp_addlogin 'SimpleLogin', 'PasswordForSimpleLogin'
GO
exec sp_grantdbaccess 'SimpleLogin', 'SimpleLogin'
GO

if exists(select * from sys.objects where object_id = object_id('dbo.a')) 
   drop table dbo.a
create table dbo.a (keyfield int identity, somevalue varchar(10))
GO

if exists(select * from sys.objects where object_id = object_id('p_a')) 
  drop procedure p_a
go
create procedure p_a
as
set identity_insert dbo.a on
insert into dbo.a (keyfield, somevalue) values (2,'nice')
set identity_insert dbo.a off

return (0)
go

grant exec on p_a to SimpleLogin
go

When we run the stored procedure with the simple "no rights" login we quickly see the following error

Msg 1088, Level 16, State 11, Procedure p_a, Line 3
Cannot find the object "dbo.a" because it does not exist or you do not have permissions.
(Make sure you run the "revert;" part of the statement to revert to your login after the error.)

Execute as user = 'SimpleLogin'
exec p_a
revert;
GO

To fix this annoying problem we need to create a few more objects in the database. First of all we need a master key which will be used to encrypt the certificate. Then we create a certificate, a login based on the certificate and we grant the "ALTER ANY SCHEMA" permission to the login. And finally we will sign the stored procedure with the schema login.

CREATE MASTER KEY ENCRYPTION BY password = '**somethingspecial**';
CREATE CERTIFICATE SchemaCert WITH SUBJECT = 'Schema user Certificate'
CREATE USER SchemaCertUser FROM CERTIFICATE SchemaCert
GRANT ALTER ANY SCHEMA TO SchemaCertUser
ADD SIGNATURE TO p_a BY CERTIFICATE SchemaCert
GO

Now when we rerun the stored procedure as our simple login we see that everything works as expected.

Execute as user = 'SimpleLogin'
exec p_a
revert;
GO

The ability to sign stored procedures gives you the power to build a much more secure SQL Server Database system. For example you can sign stored procedures that use dynamic sql and remove the need to grant select rights on the underlying tables.

I have not (yet) benchmarked the performance impact of signing stored procedures, but I suspect the overhead will not be huge.

You can download the complete test script and try it out.

Wednesday, May 16, 2007

Fix the user/login sid mismatch when restoring a SQL Server Database on a different server.

When you take a backup of a SQL Server database one of the tables that is saved in the backup is called "sysusers". This table is used to link a server login to a database user. To do this, SQL Server links the "sid" field of the sysusers table to the "sid" field of the syslogins table in the master database.

select name, sid from sysusers
select name, sid from syslogins
When you restore the database backup on another SQL Server, the "sid" fields are out of sync and SQL Server does not fix this automatically. This is a big problem because your database users are pointing to logins that don't exist or worse, pointing to the wrong login. To fix this in a simple way you can run the following script:
   1:  DECLARE @username sysname
   2:   
   3:  SELECT top 1 @username = name 
   4:  FROM sysusers 
   5:  WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null 
   6:   
   7:   
   8:  WHILE @username is not null
   9:    BEGIN
  10:      IF @username='dbo'
  11:        BEGIN
  12:          PRINT N'mapped sa to the dbo user...'
  13:          EXEC sp_changedbowner 'sa' 
  14:        END
  15:      ELSE
  16:        BEGIN
  17:          PRINT N'mapped ' + @username + ' to the dbo user...'
  18:          EXEC sp_change_users_login 'update_one', @username, @username
  19:        END
  20:      SELECT @username = null
  21:      
  22:      SELECT TOP 1 @username = name 
  23:      FROM sysusers 
  24:      WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null 
  25:    END
The script simply tries to link the users to the logins based on the name, and it tries to make sa the default database user. Whew, problems fixed you would think... Not quite. You still have a bunch of users left in your database that are not linked to any logins. To remove this we can use the following script.
   1:  DECLARE @username sysname
   2:   
   3:  SELECT top 1 @username = su.name 
   4:  FROM sysusers su
   5:    left join master..syslogins sl
   6:    on su.name = sl.name
   7:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
   8:    and suser_sname(su.sid) is null  
   9:    and sl.name is null
  10:   
  11:  WHILE @username is not null
  12:    BEGIN
  13:      print N'Dropping user ' + @username + '...'
  14:      exec sp_dropuser @username
  15:   
  16:      SELECT @username = null
  17:      SELECT top 1 @username = su.name 
  18:      FROM sysusers su
  19:        left join master..syslogins sl
  20:        on su.name = sl.name
  21:      WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
  22:        and suser_sname(su.sid) is null  
  23:        and sl.name is null
  24:    END
To make using this code more practical I wrapped it in a stored procedure which you can install on the master database. You can download the script sp_fix_user_login_mismatch.sql or read the code below:
   1:  IF object_id('dbo.sp_fix_user_login_mismatch') IS NOT NULL
   2:    BEGIN
   3:      DROP PROCEDURE dbo.sp_fix_user_login_mismatch
   4:    END
   5:  GO
   6:  CREATE PROCEDURE dbo.sp_fix_user_login_mismatch
   7:  AS 
   8:   
   9:  DECLARE @username sysname
  10:   
  11:  -- Map all the database users to the logins that have the same name.
  12:  SELECT top 1 @username = su.name 
  13:  FROM sysusers su
  14:    inner join master..syslogins sl
  15:    on su.name = sl.name
  16:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
  17:    and suser_sname(su.sid) is null 
  18:   
  19:  WHILE @username is not null
  20:    BEGIN
  21:      IF @username='dbo'
  22:        BEGIN
  23:          PRINT N'mapped sa to the dbo user...'
  24:          EXEC sp_changedbowner 'sa' 
  25:        END
  26:      ELSE
  27:        BEGIN
  28:          PRINT N'mapped the login "' + @username + '" to the database user "' + @username + '"...'
  29:          EXEC sp_change_users_login 'update_one', @username, @username
  30:        END
  31:      SELECT @username = null
  32:      
  33:     SELECT top 1 @username = su.name 
  34:      FROM sysusers su
  35:        inner join master..syslogins sl
  36:        on su.name = sl.name
  37:      WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
  38:        and suser_sname(su.sid) is null 
  39:    END
  40:   
  41:  -- Remove all the database users that do not have a matching login.
  42:  SELECT top 1 @username = su.name 
  43:  FROM sysusers su
  44:    left join master..syslogins sl
  45:    on su.name = sl.name
  46:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
  47:    and suser_sname(su.sid) is null  
  48:    and sl.name is null
  49:   
  50:  WHILE @username is not null
  51:    BEGIN
  52:      print N'Dropping user ' + @username + '...'
  53:      exec sp_dropuser @username
  54:   
  55:      SELECT @username = null
  56:      SELECT top 1 @username = su.name 
  57:      FROM sysusers su
  58:        left join master..syslogins sl
  59:        on su.name = sl.name
  60:      WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
  61:        and suser_sname(su.sid) is null  
  62:        and sl.name is null
  63:    END
  64:  RETURN(0)
  65:  GO
I'm not a fan of giving you a procedure and leaving it to you to figure out if it is good code or not. So to unit test it I have used the following code
   1:   
   2:   
   3:  -- Test harness:
   4:  -- 
   5:  -- Create 3 logins to use as basis for our database users
   6:  -- Create a test database 
   7:  -- map the dbo owner to the fakesa login
   8:  -- add 2 users to the test database based on our 2 logins
   9:  -- backup the test database and drop it
  10:  -- drop fakesa and orphanedlogintest
  11:  -- restore the database and execute our fix procedure.
  12:  -- the user logintest should be remapped
  13:  -- the dbo user should be pointing to sa
  14:  -- the users fakesa and orphanedlogintest should be gone
  15:   
  16:   
  17:  set nocount on
  18:  create login fakesa WITH password = 'a D1ff1cult passw0rd'
  19:  create login logintest WITH password = 'a D1ff1cult passw0rd'
  20:  create login orphanedlogintest WITH password = 'a D1ff1cult passw0rd'
  21:  create database testloginproblems
  22:  GO
  23:  use testloginproblems
  24:  EXEC sp_changedbowner 'fakesa' 
  25:  exec sp_adduser 'logintest', 'logintest', 'db_datareader'
  26:  exec sp_adduser 'orphanedlogintest', 'orphanedlogintest', 'db_datareader'
  27:  use master
  28:  EXEC sp_addumpdevice 'disk', 'TestLoginProblemsData', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\TestLoginProblemsData.bak'
  29:  backup database testloginproblems TO TestLoginProblemsData
  30:  drop database testloginproblems
  31:  drop login fakesa
  32:  drop login logintest
  33:  drop login orphanedlogintest
  34:  create login logintest WITH password = 'a D1ff1cult passw0rd'
  35:  RESTORE DATABASE testloginproblems FROM TestLoginProblemsData
  36:   
  37:  use testloginproblems
  38:  print N''
  39:  SELECT su.name as 'list of problematic users'
  40:  FROM sysusers su
  41:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
  42:    and suser_sname(su.sid) is null 
  43:   
  44:  exec sp_fix_user_login_mismatch
  45:   
  46:  print N''
  47:  SELECT ul.uname as 'user name to check', su.name as 'database user', sl.name 'server login'
  48:  FROM (select uname = 'logintest' union all select uname = 'dbo' union all select uname = 'fakesa' union all select uname = 'orphanedlogintest') ul
  49:    left join sysusers su
  50:    on ul.uname = su.name
  51:    left join master..syslogins sl
  52:    on su.sid = sl.sid
  53:   
  54:  use master  
  55:  EXEC sp_dropdevice 'TestLoginProblemsData', 'delfile'
  56:  drop database testloginproblems
  57:  drop login logintest