none
Alerts for anonymous access on sensitive data RRS feed

  • Question

  • Hello Team,

    We have a requirement wherein we have a security table of users and their schema access in database.

    We need to set up an alert to the database admin if any user tries to access any schema other than the one which he is allocated.

    Is there any way by which we can achieve this?

    Monday, June 3, 2019 3:16 AM

All replies

  • Did you issue DENY  on the schema to to the user that is NOT authorized  to access it ?

    https://stackoverflow.com/questions/53006547/azure-sql-database-auditing-for-dml-ddl-oprations


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 3, 2019 4:11 AM
  • Hello Uri,

    Currently we have a mapping table like:

    Name      Schemas

    y            Confidnetial,dbo

    X            highlyconfidential,restricted,confidential,dbo

    They have grant access to read (no deny)

    So in case if y resource tries to access restricted schema ,the database admin should get an alert stating that

    y resource is illegally trying to access sensitive data

    Monday, June 3, 2019 4:31 AM
  • But why not just issue DENY  on that schema?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 3, 2019 5:52 AM
  • Hello uri,

    Even if we deny him or do not give the resource any access: still if the resource queries the error would be same:

    you do not have permission to access the schema.

    So in those scenarios we want a alert to be triggered.

    So that we can reach out to those resources and confirm the reason for them trying to even access that data?

    This is just a security set up.

    Hope this clarifies the issue.

    Monday, June 3, 2019 6:31 AM
  • Hi Nandan,

    Are you aware of or have you considered data discovery & classification (link)?

    Data discovery & classification (currently in preview) provides advanced capabilities built into Azure SQL Database for discovering, classifying, labeling, and protecting the sensitive data in your databases. Discovering and classifying your utmost sensitive data (business/financial, healthcare, personal data, etc.) can play a pivotal role in your organizational Information protection stature. It can serve as infrastructure for:

    • Various security scenarios, such as monitoring (auditing) and alerting on anomalous access to sensitive data.
    • Controlling access to, and hardening the security of, databases containing highly sensitive data.
    • Helping meet data privacy standards and regulatory compliance requirements.

    See for more information: Azure SQL Database and SQL Data Warehouse data discovery & classification (link)

    This functionality is built-in to the Azure SQL Database service and is the exact same as what you are attempting to achieve. There is a process of classifying your database by labeling the target columns. You will need to map your definitions to the predefined labels available for classification. Once that is completed, auditing actions can be configured to generate the required alert.

    Please let us know if you have additional questions. If not, please mark as answered if this provides you a solution.

    Thursday, June 6, 2019 10:33 PM
    Moderator