BuyLog In
Ticket T566541
Visible to All Users

Auditing data access in XAF

created 7 years ago

Hi,

Can I please have your advice on the following? In one of our XAF applications we need to track which user has accessed what data. I know the Audit Trail module in XAF logs changes to records, but I need to know who read the record, not who modified it. This in order to comply with regulations similar to HIPAA in the USA, where we have to be able to identify users who accessed information without a good reason. Only a limited set of tables with sensitive information needs to be monitored and I'm concerned about performance as this could be a massive amount of logging data.

I can see several options:

  1. Log SELECT statements using the  SQL Server Audit feature. This doesn't include the XAF user names and is only available on the Enterprise edition of SQL Server.
  2. Use third-party tools like ApexSQL Audit - Expensive and still doesn't log the XAF user name
  3. Include logic in the XAF application - affects performance and possibly requires changes to the DevExpress source code(?)
  4. Customize DevExpress Middle Tier Security (how?)

I would appreciate your thoughts on this.

regards,
Michiel

Comments (1)
DevExpress Support Team 7 years ago

    Hi Michiel,

    Thank you for your question. We need additional time to discuss it. Please bear with us. We will get back to you as soon as possible.

    Sign in to comment on this post

    Answers approved by DevExpress Support

    created 7 years ago (modified 7 years ago)

    Hello Michiel,

    >>1-2
    If your produce XAF WinForms apps, technically you can create users on the SQL server and then provide user-specific connection strings for them. However, this option will be difficult to maintain + I guess the license cost is a problem too.
    UPDATED:
    Are you using Windows Authentication/Integrated Security (each application user accesses the database on behalf of its Windows account) or SQL Server Authentication (i.e. use a shared SQL Server login in all connection strings)? I am clarifying, because in the first case you can know user names: http://www.intstrings.com/ramivemula/articles/understanding-different-ways-to-extract-logged-in-userlogin-in-sql-server-using-tsql/
    The use of Windows authentication on SQL Server is also considered a best practice —it enables centralized management of SQL Server principals (as per https://docs.microsoft.com/en-us/sql/relational-databases/security/microsoft-sql-and-the-gdpr-requirements). You may find the AuthenticationActiveDirectory and Deployment > Deployment Tutorial > Database Security References articles in the XAF documentation or https://msdn.microsoft.com/en-us/library/bsz5788z.aspx and similar articles in public community resources helpful to learn more about configuring your apps this way.

    I also wanted to comment on the Enterprise edition of SQL Server, because I was browsing http://www.sqlservercentral.com/blogs/matthew-mcgiffen-dba/2017/09/26/auditing-data-access-in-sql-server-for-gdpr-compliance/ and came across this note: "Available in SQL Server 2008 Enterprise edition, Server level auditing came to Standard edition in 2012 and all features are available in all editions from 2016 SP1". The https://www.logbinder.com/Blog/?p=d1f5efd3-d0a8-4c3e-82a5-6c8fc7e7f672 and https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine articles confirm this too.
    While it seems that there can be custom-tailored solutions without SQL Server Audit (e.g., https://stackoverflow.com/questions/1515598/anyway-to-create-a-sql-server-ddl-trigger-for-select-statements), they are generally not recommended due to performance and security, at least based on what I learnt from related community resources. So, I suggest you take another moment to evaluate the built-in SQL Server Audit features, which are recommended in the GDPR guidelines I mentioned above. Since we do not provide any specialized means for this scenario at the XAF level, it is possible that these standard solutions for SQL Server and other database engines may be safer and easier to implement for you. Anyway, please keep us informed of your results, because this topic will be valued by the XAF community too. Thanks in advance.

    >>3-4
    Would you please elaborate more on your current application schema? Am I correct that you are developing a WinForms app that connects to the application server? The more information on how your app is developed we have, the more technical options we can suggest to you. Based on the current description, it seems that the Task-Based Help > How to: Enable Logging in the Application Server solution is the most appropriate for the middle-tier Application Server configuration. Take special note of the 'LoadObjects' entry in the resultant log file, which is what you need. This mechanism allows you to implement a custom logger to avoid any performance issues. For instance, instead of writing on every request, you can introduce a local cache that will be flushed down on a timer or after exceeding a certain size to reduce network or other overhead. You should be able to use the SecuritySystem.CurrentUser property to obtain an object that represents the current user.

    Here I also want to list other solutions that I considered for your scenario:
    How to log the SQL queries made by XPO and Cross-Platform Core Libraries > DevExpress.Xpo.DB > DataStoreLogger  - create a logger at the low XPO level.
    Logging in the Application Server (WCF Service) and Logging in the Application Server - create a WcfSecuredDataServer class descendant and override its LoadObjects method directly.
    All approaches will require you to parse queries data (e.g., ObjectStubsQuery) if you need to know exactly which data tables and columns were involved in the reading data operation.
    I am afraid that some APIs mentioned in my answer are internal and undocumented, so we do not provide guidelines on their usage. That said, you will need to research the source code of our XAF, XPO and Data libraries on your own to learn more about their usage.

      Show previous comments (2)
      Dennis Garavsky (DevExpress) 7 years ago

        Hello Michiel,

        >>1. Log SELECT statements using the  SQL Server Audit feature. This doesn't include the XAF user names and is only available on the Enterprise edition of SQL Server.
        …
        >>Our application is a XAF application with both a Win and a Web version. Currently both platforms connect directlyto the SQL database using the standard XPO layer.
        Are you using Windows Authentication/Integrated Security (each application user accesses the database on behalf of its Windows account) or SQL Server Authentication (i.e. use a shared SQL Server login in all connection strings)? I am clarifying, because in the first case you can know user names: http://www.intstrings.com/ramivemula/articles/understanding-different-ways-to-extract-logged-in-userlogin-in-sql-server-using-tsql/
        The use of Windows authentication on SQL Server is also considered a best practice —it enables centralized management of SQL Server principals (as per https://docs.microsoft.com/en-us/sql/relational-databases/security/microsoft-sql-and-the-gdpr-requirements). You may find the AuthenticationActiveDirectory and Deployment > Deployment Tutorial > Database Security References articles in the XAF documentation or https://msdn.microsoft.com/en-us/library/bsz5788z.aspx and similar articles in public community resources helpful to learn more about configuring your apps this way.

        I also wanted to comment on the Enterprise edition of SQL Server, because I was browsing http://www.sqlservercentral.com/blogs/matthew-mcgiffen-dba/2017/09/26/auditing-data-access-in-sql-server-for-gdpr-compliance/ and came across this note: "Available in SQL Server 2008 Enterprise edition, Server level auditing came to Standard edition in 2012 and all features are available in all editions from 2016 SP1". The https://www.logbinder.com/Blog/?p=d1f5efd3-d0a8-4c3e-82a5-6c8fc7e7f672 and https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine articles confirm this too.
        While it seems that there can be custom-tailored solutions without SQL Server Audit (e.g., https://stackoverflow.com/questions/1515598/anyway-to-create-a-sql-server-ddl-trigger-for-select-statements), they are generally not recommended due to performance and security, at least based on what I learnt from related community resources. So, I suggest you take another moment to evaluate the built-in SQL Server Audit features, which are recommended in the GDPR guidelines I mentioned above. Since we do not provide any specialized means for this scenario at the XAF level, it is possible that these standard solutions for SQL Server and other database engines may be safer and easier to implement for you. Anyway, please keep us informed of your results, because this topic will be valued by the XAF community too. Thanks in advance.

        MB MB
        Michiel Blotwijk 7 years ago

          Hi Dennis,
          I found an alternative solution for this particular application, so I'm fine for now. Nevertheless I would like to ask you to put this issue on your development roadmap. On 25/5/2018 the GDPR will come into force in the EU, which has huge consequences for any application that sotres personal data. For sensitve personal data it can in some cases be mandatory to log not only who changed data, but also who accessed it. It would be really helpful if DevExpress could enable that in XAF.
          regards,
          Michiel

          Dennis Garavsky (DevExpress) 7 years ago

            Hello Michiel,

            Thanks for your update.
            As for our plans, we have not planned this for any specific release yet. That is primarily because we are not sure of the value of resolving this problem at the XAF level, especially taking into account the availability of existing solutions on other layers (e.g., SQL Server). This problem also appears to be a general programming issue not directly related to DevExpress, which exists regardless of XAF and XPO; e.g., for any LOB application accessing a database and storing personal data there (see that paper from Microsoft).
            However, we are still interested in learning more about typical solutions our users would use for this task as well as their experience with them (e.g., costs, difficulties), because this may be an opportunity for us to improve this situation. So, if you describe your alternative solution for other XAF users here and continue posting updates as you go, we would greatly appreciate it.

            Sign in to comment on this post

            Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

            Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.