Monday 17 September 2012

IIS: IE doesn't auto authenticate

I've been doing a little web development work recently with MVC and have come across a (very) minor issue when deploying this to my test server.

Essentially, I've deployed to the application to the server but when I access it via the IP Address, I get prompted for my windows credentials. However, when accessing it via server name I am not prompted and I get the (much better) user experience of auto authentication.

It appears that the issue is not with IIS or my MVC app but with my browser and the way it handles different URL patterns. When accessing it via server name (eg HTTP//Servername/App) the browser "knows" the site is internal so automatically authenticates whereas the IP address (HTTP//1.1.1.1/App) could be external and so throws the prompt.

Its explained here in David Wang's blog.

T-SQL: Allow low privileged user to Truncate table

The problem with TRUNCATE is that it is a DDL statement rather than the regulation DELETE statement which is DML. As such, its not captured by using the db_datewriter database role and so on the face of it, you're left with two options:

1) Add the user to db_ddladmin role (or db_owner)
2) Grant ALTER table permissions to the table(s) in question

I'm not really a fan of either of these approaches as the first gives far too much control to the user and I try to avoid giving object level permissions if possible.

An alternative is to make use of the EXECUTE AS clause and wrap your TRUNCATE statement within a stored procedure and grant permissions on that instead.

-- create a test database 
CREATE DATABASE TruncateTest
GO

USE TruncateTest
GO

-- create a low privilege user to test with
CREATE USER NoRights WITHOUT LOGIN
GO

-- create a table to test against
CREATE TABLE dbo.TestData (Id INT IDENTITY(1,1))
GO
-- add some dummy data
INSERT INTO dbo.TestData DEFAULT VALUES
GO 20
-- check the data is ok
SELECT COUNT(*) FROM dbo.TestData
GO

-- impersonate the test user
EXECUTE AS USER = 'NoRights'
GO
-- try to issue the truncate command
TRUNCATE TABLE dbo.TestData
GO

Msg 1088, LEVEL 16, State 7, Line 1
Cannot find the object "TestData" because it does not exist or you do not have permissions.
-- switch back to my superuser
REVERT
GO

-- create a wrapper procedure to truncate the table
CREATE PROCEDURE dbo.TruncateTestData
AS
TRUNCATE TABLE
dbo.TestData
GO

-- grant execute to the test user
GRANT EXECUTE ON dbo.TruncateTestData TO NoRights
GO

-- impersonate the test user
EXECUTE AS USER = 'NoRights'
GO
-- execute the procedure
EXEC dbo.TruncateTestData
GO

Msg 1088, Level 16, State 7, Procedure TruncateTestData, Line 4
Cannot find the object "TestData" because it does not exist or you do not have permissions.
-- switch back to my superuser
REVERT
GO

-- modify the procedure to execute in the context of the owner (dbo)
ALTER PROCEDURE dbo.TruncateTestData
WITH EXECUTE AS OWNER
AS
TRUNCATE TABLE
dbo.TestData
GO

-- impersonate the test user
EXECUTE AS USER = 'NoRights'
GO
-- execute the procedure
EXEC dbo.TruncateTestData
GO

-- switch back to the superuser
REVERT
GO

-- check the data
SELECT COUNT(*) FROM dbo.TestData
GO

-- tidy up
DROP DATABASE TruncateTest
GO
/* add this crazy stuff in so i can use syntax highlighter