0

My use case is as follows:

A table in my database is accessible by application A, B, C, etc each of whom has their own database principal. We want to start limiting the principals that can access certain table in my database, i.e. we want it such that only application A has access to said table. The idea is to relegate access to the table such that B and C calls application A instead of going to the table directly.

One naive way is to remove the principal B and C's access to the table straight away, but this will cause an application outage on B and C. A better approach is to reimplement B and C to call A first before taking away their access.

The challenge is that B and C might be accessing the table via an opaque library. We have been slowly phasing out the opaque library in favor of calling application A, but we are not 100% sure that B and C no longer accesses the table.

I have been thinking that I could check the database audit log to check which principals have been accessing that table. I have been using Azure SQL Auditing feature in my project which gives me the stats on individual statement, including its query string.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-audit-log-format

However, the query string itself might be composed of JOINs of several tables, or the string is an EXEC of a stored procedure, i.e. the granularity of the audit is on the statement level. It doesn't tell me explicitly which tables are involved in a given statement.

Is there a way to effectively capture this information that table T has been used by principals A, B, C in the past X hours?

  • If you know B and C database principal, it would be easier to examine what queries are still running on those principals. Especially if you desire to completely redirect flow to A application. – Paweł Tajs Sep 20 at 10:23
  • @PawełTajs: actually I do know the principals. But instead of "get all queries by principal B", can I do "get all queries that requires table T by principal B"? – Aditya Santoso Sep 21 at 6:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.