How to inject a sp_set_session_context for ALL repository objects.

[michaelswells]

I have an application that uses the [SQL()] attribute in the Repository layer

[SQL("ProgramGetMany")]
IList<Program> GetManyAsync(int programId)

I am now trying to implement Row Level Security (RLS) and I would [IMO] need to prefix all calls with

Execute sp_set_session_context N'username', @ntId;

Would I need to change the original to:

[SQL("Execute sp_set_session_context N'username', @ntId; execute ProgramGetMany")]
IList<Program> GetManyAsync(int programId, string ntId) // not sure if I need to add the 2nd parameter to ALL repo calls throughout the service layer

or is there a simpler solution?

• Dotnet version: net6
• Database: SQL Server 2017

[jonwagner]

Since you are setting the context for the session, it sounds like the best place would be to set the user when you get the connection.

Maybe something like:

interface IDbSession {
   [SQL("sp_set_session_context")]
   void setSessionContext(string attribute, string value);
}

public IDbConnection GetDbSession(string username) {
     var sc = Connection().As<IDbSession>();
     sc.setSessionContext("username", username);
     return sc as IDbConnection;
}

// somewhere else
i = GetDbSession(username).As<MyInterface>();

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.