Entify Framework Inserts require Select permissions

Question

We use LINQ to Entities to write entries into an Audit database (SQL Server 2008). Since this is a dedicated Audit database, we only insert rows - we never read any rows, update or delete them from the auditing application.

The auditing application should use the principle of Least Privilege, so we don't wish to grant it more permissions than it needs. Since we never read any rows, we don't want to grant permissions to select from the database.

However, when we attempt to write the data, we get this error message:

The SELECT permission was denied on the object 'AuditEvent', database 'IdentifyAudit', schema 'dbo'.

The code is pretty standard EF code:

var auditEvent = new AuditEvent();
auditEvent.EventType = eventType;
auditEvent.Timestamp = timestamp;
auditEvent.UserName = userName;
auditEvent.ApplicationId = this.ApplicationId;

this.objectContext.AddToAuditEvents(auditEvent);
this.objectContext.SaveChanges();

Why do we need SELECT permission to write to the table, and more importantly: is there any way we can remove that requirement?

---

EDIT

SQL Profiler shows this statement being executed:

exec sp_executesql N'insert [dbo].[AuditEvent]([EventType], [Timestamp], [UserName], [ApplicationId])
values (@0, @1, @2, @3)
select [Id]
from [dbo].[AuditEvent]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 nvarchar(10),@1 datetimeoffset(7),@2 nvarchar(11),@3 nvarchar(36)',@0=N'UpdateUser',@1='2009-11-10 10:58:33.2814740 +01:00',@2=N'foo',@3=N'bar'

This explains why SELECT permissions are needed, because the operation returns the auto-generated ID of the inserted row.

The question now remains: I don't need to know the ID of the row I just inserted, so is there any way I can switch off this feature?

Answer 1

By default, after you add an entity to the ObjectContext and call SaveChanges the state of that object changes from Added to Unchanged and it is still tracked by ObjectContext. That is why EF needs that ID so it can be able to track changes on it.

Entity Keys and added objects:

1.The entity object is constructed. At this point the key properties all have default values, either null or 0.

2.The new object is added to the ObjectContext either by calling AddObject or one of the entity set-specific add methods in the context or by calling Add on a navigation property that returns an EntityCollection.

At this point, Object Services generates a temporary key, which is used to store the objects in the ObjectStateManager.

3.SaveChanges is called on the ObjectContext.

An INSERT statement is generated by Entity Services and is executed on the data source.

4.If the INSERT operation succeeds, server-generated values are written back to the ObjectStateEntry.

5.The ObjectStateEntry updates the object with the server-generated value.

6.When AcceptChanges is called on the ObjectStateEntry, a permanent EntityKey is computed using the new server-generated values.

So, as far as I know, it is not possible to switch of this feature from ObjectContext and I don't see any "nice" solution to this problem: One way you could avoid this is to use your own stored procedures to insert entity (if you can) (http://msdn.microsoft.com/en-us/library/bb399203.aspx).

Also, if there are no server generated ID's, I think that select query will not be executed (again, if you can change dbs, and if you want to bother with generation of id's).

Answer 2

However, this is an old question but for future maybe anybody will use. One way is to give select permission only to id fields.