Prev: How to implement Audit/Alert for Row level access?
Next: Disable and Rebuild or Drop and recreate Index
From: Neil Jones on 5 May 2008 21:09
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
From: Tom van Stiphout on 6 May 2008 01:08
On Mon, 05 May 2008 21:09:00 -0400, Neil Jones
<castellan2004-atnews(a)yahoo.com> wrote:

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
From: Neil Jones on 6 May 2008 03:03
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
> <castellan2004-atnews(a)yahoo.com> wrote:
>
> 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
From: Erland Sommarskog on 6 May 2008 17:53
Neil Jones (castellan2004-atnews(a)yahoo.com) 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(a)sommarskog.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
From: Tom van Stiphout on 6 May 2008 09:43
On Tue, 06 May 2008 03:03:22 -0400, Neil Jones
<castellan2004-atnews(a)yahoo.com> wrote:

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 ImportantCustomerRecFlag = 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
>> <castellan2004-atnews(a)yahoo.com> wrote:
>>
>> 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
 |  Next  |  Last
Pages: 1 2
Prev: How to implement Audit/Alert for Row level access?
Next: Disable and Rebuild or Drop and recreate Index