[dba-SQLServer] SQL Server Field Data Security

[David Emerson]

Hi Stuart,

Re the SQL SA, that was what I was afraid of.  The SQL instance will be
maintained by the client.

The Access FE is an accde but could be distributed to hundreds of users so
storing the data in there would be a problem.  As we don't want everyone to
get a warning when the system is due to expire.  We also only want 1 person
to validate the activation code for everyones copy to be activated to the
new date.

The activation is not a single event.  It might be annually depending on
what payment option the client chooses so we need to be able to update the
expiry date inside their system.

Regards

David



-----Original Message-----
From: dba-SQLServer
<dba-sqlserver-bounces+newsgrps=dalyn.co.nz at databaseadvisors.com> On Behalf
Of Stuart McLachlan
Sent: Monday, June 5, 2023 3:53 PM
To: Discussion concerning MS SQL Server <dba-sqlserver at databaseadvisors.com>
Subject: Re: [dba-SQLServer] SQL Server Field Data Security

It depends on who maintains the SQL Server instance.
An SA will have full access to everything, so you need to prevent them from
making a meaningful change to the data.

Do they have access to an accdb or are you locking it down to an accde?

If it's only accde, you can encrypt/decrypt a field in the database and
validate when it opens.

If it's an accdb, it's a lot harder to protect your encryption/decrytion
routine ( maybe consider creating and distributing a DLL with the applicatin
that is called to validate the SQL Server
data.)




On 5 Jun 2023 at 15:06, David Emerson wrote:

> Hi Listers,
>
 > I have a couple of fields that I would like inaccessible to the 
> client's IT department that might have access to my SQL database.
> They store data that is used internally by my Access application to 
> determine whether the users should still be able to use the software.
> 
> I don't want to store the values in the access application itself as 
> the unlocking of the software should only be done once and apply to 
> all users (who will have their own copies of the FE).
> 
> My thought is that they would be in a separate table and I would 
> prevent anyone with SA or administrator roles from being able to look 
> at the table design and data (or at least not change the data) unless 
> it is me (but how do I prevent Administrator users from changing the 
> permissions?).
> 
> What is the best way of doing this?  Is there another method (similar 
> to Access Database Properties) where I could store the data in another 
> place in SQL instead?
> 
> Regards
> 
> David Emerson
> Dalyn Software Ltd
> Adelaide, Australia