How to implement Audit/Alert for Row level access?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Neil Jones
    #1

    How to implement Audit/Alert for Row level access?

    Hello,

    We have a large database of our customers. Some of the important
    customers records are flagged and can be accessed only by applications
    designed for upper management. The management would like to know if
    anyone else is trying or successfully accessed the flagged customers.

    Is there anyway to send an email alert when a normal user/sysadmin tries
    to access the flagged customers in the table?

    Thank you in advance for any help.

    NJ
    Tags: None
    • Tom van Stiphout
      #2
      Re: How to implement Audit/Alert for Row level access?

      On Mon, 05 May 2008 21:09:00 -0400, Neil Jones
      <castellan200 4-atnews@yahoo.co mwrote:

      You may have a logic error in your question, because if your assertion
      "can be accessed only by applications designed for upper management"
      is true, then obviously no one else has successfully accessed the
      flagged customers.

      -Tom.

      >Hello,
      >
      >We have a large database of our customers. Some of the important
      >customers records are flagged and can be accessed only by applications
      >designed for upper management. The management would like to know if
      >anyone else is trying or successfully accessed the flagged customers.
      >
      >Is there anyway to send an email alert when a normal user/sysadmin tries
      >to access the flagged customers in the table?
      >
      >Thank you in advance for any help.
      >
      >NJ

        Comment

        • Neil Jones
          #3
          Re: How to implement Audit/Alert for Row level access?

          Ok. Well, actually anyone user can access the data in the table. We
          would like to monitor the access to the flagged customers.

          NJ

          Tom van Stiphout wrote:
          On Mon, 05 May 2008 21:09:00 -0400, Neil Jones
          <castellan200 4-atnews@yahoo.co mwrote:
          >
          You may have a logic error in your question, because if your assertion
          "can be accessed only by applications designed for upper management"
          is true, then obviously no one else has successfully accessed the
          flagged customers.
          >
          -Tom.
          >
          >
          >Hello,
          >>
          >We have a large database of our customers. Some of the important
          >customers records are flagged and can be accessed only by applications
          >designed for upper management. The management would like to know if
          >anyone else is trying or successfully accessed the flagged customers.
          >>
          >Is there anyway to send an email alert when a normal user/sysadmin tries
          >to access the flagged customers in the table?
          >>
          >Thank you in advance for any help.
          >>
          >NJ

            Comment

            • Tom van Stiphout
              #4
              Re: How to implement Audit/Alert for Row level access?

              On Tue, 06 May 2008 03:03:22 -0400, Neil Jones
              <castellan200 4-atnews@yahoo.co mwrote:

              Just checking.

              You can apply SQL Server security to revoke access to that table, then
              create two views:
              select * from SecretTable
              and give access to the Mgmt group only
              and:
              select * from SecretTable
              where ImportantCustom erRecFlag = 0
              and give access to Everyone.

              -Tom.

              >Ok. Well, actually anyone user can access the data in the table. We
              >would like to monitor the access to the flagged customers.
              >
              >NJ
              >
              >Tom van Stiphout wrote:
              >On Mon, 05 May 2008 21:09:00 -0400, Neil Jones
              ><castellan20 04-atnews@yahoo.co mwrote:
              >>
              >You may have a logic error in your question, because if your assertion
              >"can be accessed only by applications designed for upper management"
              >is true, then obviously no one else has successfully accessed the
              >flagged customers.
              >>
              >-Tom.
              >>
              >>
              >>Hello,
              >>>
              >>We have a large database of our customers. Some of the important
              >>customers records are flagged and can be accessed only by applications
              >>designed for upper management. The management would like to know if
              >>anyone else is trying or successfully accessed the flagged customers.
              >>>
              >>Is there anyway to send an email alert when a normal user/sysadmin tries
              >>to access the flagged customers in the table?
              >>>
              >>Thank you in advance for any help.
              >>>
              >>NJ

                Comment

                • --CELKO--
                  #5
                  Re: How to implement Audit/Alert for Row level access?

                  >The management would like to know if anyone else is trying or successfully accessed the flagged customers. <<

                  This is a HIPAA requirement and it is best done with third party
                  software that has been certified. Conventional tools based off the
                  log files will show changes, but not access by user.

                    Comment

                    • Lennart
                      #6
                      Re: How to implement Audit/Alert for Row level access?

                      On May 6, 5:54 pm, --CELKO-- <jcelko...@eart hlink.netwrote:
                      The management would like to know if anyone else is trying or successfully accessed the flagged customers. <<
                      >
                      This is a HIPAA requirement and it is best done with third party
                      software that has been certified. Conventional tools based off the
                      log files will show changes, but not access by user.
                      Joe, you don't happen to have a list of such software?


                      /Lennart

                        Comment

                        • Erland Sommarskog
                          #7
                          Re: How to implement Audit/Alert for Row level access?

                          Neil Jones (castellan2004-atnews@yahoo.co m) writes:
                          We have a large database of our customers. Some of the important
                          customers records are flagged and can be accessed only by applications
                          designed for upper management. The management would like to know if
                          anyone else is trying or successfully accessed the flagged customers.
                          >
                          Is there anyway to send an email alert when a normal user/sysadmin tries
                          to access the flagged customers in the table?
                          >
                          Thank you in advance for any help.
                          In SQL 2000 and SQL 2005 there is very little support for this in
                          the product. You can set up a trace to caputre access to these tables.
                          You would then need a program that reads these trace files and checks
                          them for violation.

                          If you can afford to rework your applications, you can revoke access to
                          the underlying tables, and expose the customers through views. All
                          customers in one view, and another view with only non-flagged customers.
                          Only upper management would have permission to the former view.

                          In SQL 2008, currently in beta, there is a new auditing feature that I
                          think could meets your needs to some extent. But you would still have
                          to monitor the auditing log for violations on your own.


                          --
                          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                          Books Online for SQL Server 2005 at
                          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                          Books Online for SQL Server 2000 at
                          http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                            Comment

                            • --CELKO--
                              #8
                              Re: How to implement Audit/Alert for Row level access?

                              >Joe, you don't happen to have a list of such software? <<

                              Craig Mullins likes NEON, so I'd start there.

                                Comment

                                Previous template Next
                                Working...