SQL permissions - security concerns

[vvmichal]

Could you please elaborate more on SQL permissions? The need for SELECT permission is a significant security concern, and I don't understand why this isn't discussed more.

[ckadluba]

Hi @vvmichal!

As stated in the documentation here, SELECT is necessary for the batched mode of the sink because it uses SqlBulkCopy. It seems that SqlBulkCopy requires SELECT permission to work.

Form a security perspective I would recommend to use a separate database which is used exclusively for logging. When setting things up like this, the permissions for the identity (user) used by the MSSQL sink to access the DB can be scoped only to that DB and there would be no risk that the SELECT permission leads to any data which could be read by the logging app which is not log data. Even if no separate DB is used for logging, the SELECT permission can also be set exclusively on the log table (or tables). This will ensure that no other tables can be read and minimize the risk of any undesired data visibility to the logging app. This patterns is also recommended by our documentation.

[vlm---]

Just to add one more solution in the mix: we are using row level security based on Windows accounts to prevent users from reading other user's logs written from desktop app with integrated auth.

[ckadluba]

@vlm--- that sounds interesting. Do you do this based on a column that contains the application name/id that created the logs? Can you share some details of your solution?

[vlm---]

There's a custom column with account name, provided by Enrich.WithEnvironmentUserName() from the Serilog.Enrichers.Environment package. MSSQL sink then uses integrated security login and all users are members of a AD group with permissions to SELECT and INSERT. Row level security uses a security predicate function to filter only the rows for a current user determined with SUSER_SNAME() or USER_NAME() function. Pretty good starting examples are here.

[ckadluba]

Very nice.solution. Thank you for sharing this!

[vvmichal]

Thank you for your detailed response and for suggesting Row-Level Security (RLS). While RLS could work, I believe requiring each user to be authenticated through Windows or Active Directory (such as with integrated security) may not be ideal for many scenarios.

I still see the need for SELECT permissions as a security concern. While I understand that many may not emphasize this, I’d like to stress that this approach might not align with the best security practices in environments where access should be as minimal as possible.

Here are a few thoughts that come to mind as potential alternatives:

1. Encrypted logs: Although encryption doesn’t fully solve the issue, it adds an additional layer of security, which might be better than exposing SELECT permissions by default.

2. Bulk-less version with INSERT only: Could there be a version of the sink that avoids using SqlBulkCopy and allows for simple INSERT operations without needing SELECT? This could be a better fit for systems where SELECT is a security concern but bulk operations aren't critical.

3. Stored procedure for bulk operations: Would it be possible to handle the bulk insert logic inside a stored procedure that requires only EXECUTE permissions? This way, the user can still take advantage of bulk operations without requiring SELECT or direct INSERT on the table.

Thanks for your time! I’m looking forward to your thoughts.

[ckadluba]

2. Bulk-less version with INSERT only: Could there be a version of the sink that avoids using SqlBulkCopy and allows for simple INSERT operations without needing SELECT? This could be a better fit for systems where SELECT is a security concern but bulk operations aren't critical.

In fact you can already do that. If you set the sink option UseSqlBulkCopy to false, the sink will only use plain INSERT statements for writing log events and the SELECT permission should not be necessary.

[ckadluba]

I added now a bit to the documentation specifying under which circumstances the SELECT permission is not required.