How secure SQL Server is? (1 Viewer)

KitaYama

Well-known member
Local time
Today, 23:20
Joined
Jan 6, 2022
Messages
1,489
If you cannot trust the user to not manipulate the data against company rules, then why give them permission at all.
I really didn't expect this.
Before I answer that, let me ask you something. As a database developer, you create a FE, with strict rules, with a lot of codes in beforeupdate to restrict input data, hide the tables, use accde instead of accdb and a hundred more other steps to be sure the input data is correct. Why? If you don't trust your user, why do you allow him accessing the database.
It's not a matter of trust. It's a matter of you, as a developer, need the data being manipulated as you want. Not as the user want.

In a new order registration form, the Date field is locked and its default value is set to Now().
Why? because you don't want the user (by mistake) input a date earlier than today.
But if this user can access the tables via HIS database, he may do this mistake. And you can do nothing to prevent him.

Can you see where I'm going?
 
Last edited:

KitaYama

Well-known member
Local time
Today, 23:20
Joined
Jan 6, 2022
Messages
1,489
@theDBguy
If anyone is allowed to create a database and access the data, what's the use of your FE?
you trust them. Then allow them build their own FEs.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:20
Joined
Oct 29, 2018
Messages
21,357
I really didn't expect this.
Before I answer that, let me ask you something. As a database developer, you create a FE, with strict rules, with a lot of codes in beforeupdate to restrict input data, hide the tables, use accde instead of accdb and a hundred more other steps to be sure the input data is correct. Why? If you don't trust your user, why do you allow him accessing the database.
It's not a matter of trust. It's a matter of you, as a developer, need the data being manipulated as you want. Not as the user want.

In a new order registration form, the Date field is locked and its default value is set to Now().
Why? because you don't want the user (by mistake) input a date earlier than today.
But if this user can access the tables via HIS database, he may do this mistake. And you can do nothing to prevent him.

Can you see where I'm going?
@theDBguy
If anyone is allowed to create a database and access the data, what's the use of your FE?
you trust them. Then allow them build their own FEs.
No offense intended by any of my posts, so please keep that in mind.

What you are mentioning now are "data validation" rules. It has nothing to do with any "malicious" intent of users. If a user doesn't like my FE, and they want to create their own, then they can do so. Why would I stop them? Heck, I do it myself. I create my own FE to databases I don't own, because I know how to get what I want from them. My company trusts me not to destroy their data.

What I think you were talking about earlier was preventing untrustworthy users to change the data in an unauthorized way, which is why I said that you either keep them out of the database or turn them over to HR.

When I create FEs, I don't spend much time "locking it down." I trust the company to enforce other security measures to prevent those users from getting to their sensitive data or punishing them if they do. Other than that, I only worry about data validity and integrity. At times when that fails, then it must be the user's fault, and again, the HR department should take care of it (after their supervisor, of course).

Hope that makes sense...
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:20
Joined
Feb 28, 2001
Messages
26,999
The problem isn't that you trust someone to access the data in specified ways. You already expressed that trust by enabling that class of operations. Your problem is that you have procedures that embody site or company rules that must be followed and you don't want that user to step outside the procedural rules. That is why you create an FE and eventually turn it into a .ACCDE file and etc. etc.

The FE is to guide users, some of whom are thumb-fingered idiots in training (sadly, not good enough to be certified thumb-fingered idiots), through the vagaries of proper procedures. The FE is actually a physical implementation of the second part of Murphy's Laws. Everyone remembers "If it CAN go wrong, it WILL go wrong" but nobody remembers the second part, "... so build it so that it can't go wrong." The FE is the way of guiding someone through procedures so that they CAN'T go wrong. It isn't always about security. Sometimes it is about proper operation and user guidance. Dotting the i's and crossing the t's.

Rule violators cannot be stopped by simple permissions and FE guidelines very easily. If there is a way you can catch the rule violators, you can bring it up to the person whose database it is and have that person remove the rule violator from the list of users. Or from the list of employees who still have a job. Some things are NOT within the JOB role of a DBA or DB designer. Employee discipline is not one of those roles.

When we create a tightly structured front-end with all sorts of bells and whistles - and restrictions - we are within our "swim lane." When some hair-brained schmuck starts to violate rules about usage, s/he has swum outside of his/her lane. At that point, the question becomes "Is it YOUR job to impose discipline?" If this program is important to your company or site to have been implemented, important enough to do extensive backups and important enough to authorize creation of restrictive front-ends, it should be important enough for the management to publish detailed rules about using the application. AND it should be important enough to include a discussion of reasons WHY someone should stay within THEIR assigned "swim lane" when using it. That discussion usage should include a statement of potential penalties. Such discussion is not within YOUR "swim lane."

I understand and deeply appreciate your dilemma. I know that you are "all in" on this project and as a result, it is important to you. Been there, done that, wore out the T-shirt. But this is a case where you need to understand and appreciate your role in the larger scheme of things. This is a case of alternation of attention, of "not seeing the forest for the trees" with respect to corporate operations.

IF you were the corporate boss, you could punish someone in some way. But are you that boss? (If so, why are you wasting time building something that a subordinate could have built?) Leave the boss to do his/her job and you just keep on enthusiastically doing yours. You aren't operating in a total vacuum, so why act as though you are?

EDIT: theDBguy and I are on the same page. But he DID say it shorter than I did.
 

KitaYama

Well-known member
Local time
Today, 23:20
Joined
Jan 6, 2022
Messages
1,489
@theDBguy Of course that makes sense.

More than malicious intent I was thinking to unintended mistakes.
But, seriously, SQL server was and is designed to be used for organization with a lot of users. So in an organization with several hunderd or several thousands users, can you trust them all? I say No. They are paid to use the designed FE to input - edit - insert data via a trusted FE.
Now if anyone of them is allowed to access the tables, ..., I say it's a disaster.

We have 39 users in our company for a database. Based on season and the amount of job, this number changes between 20 to 50 users.
Most of them are part time workers that are employed to do a specific routine with a FE. and almost all of them don't know what a sql server is.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:20
Joined
Oct 29, 2018
Messages
21,357
@theDBguy Of course that makes sense.

More than malicious intent I was thinking to unintended mistakes.
But, seriously, SQL server was and is designed to be used for organization with a lot of users. So in an organization with several hunderd or several thousands users, can you trust them all? I say No. They are paid to use the designed FE to input - edit - insert data via a trusted FE.
Now if anyone of them is allowed to access the tables, ..., I say it's a disaster.

We have 39 users in our company for a database. Based on season and the amount of job, this number changes between 20 to 50 users.
Most of them are part time workers that are employed to do a specific routine with a FE. and almost all of them don't know what a sql server is.
Hi. Glad to hear we're on the same page. My company makes employees go through a lot of security checks before getting hired. They also go through a lot of checks and training before gaining access to a database, and each database has their own sets of security check requirements. Preventing "unintended mistakes" is doable, but not totally foolproof. Users will always find a way to get around things to make their job easier for them. You simply handle them as they happen (to prevent the same mistakes from happening again in the future).

The way I look at it is, I can only do so much within my power. So, I don't usually worry about things outside my control.

Good luck with your project.
 

KitaYama

Well-known member
Local time
Today, 23:20
Joined
Jan 6, 2022
Messages
1,489
@The_Doc_Man I understand your and @theDBguy 's point.
As my first post on this thread says, it's not my problem. It simply didn't make sense that any user can access the server. So I asked the question.
It was simply because I thought I'm missing something. But as @GPGeorge explained, it's been a problem for a long time.
The whole point of the question was something to learn. not to solve a problem.

Thanks for taking your time and explaining. It's always good to read your posts.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,970
Reading it all, seems nothing can be done. Pat Hartman's solution seems interesting, but I can't figure it out how she did it.
You use SQL Server authentication. This is more work for the DBA because he has to define the users. One suggestion for "calculating" a password is to use the first and last letters of the login & the number of characters in the last name & the other letters of the login. You can make some letters caps and intersperse special characters even. The point is that you never tell the user what his password is to SQL Server. So if he tries to connect using his normal credentials, he can't get in. You don't need to store the password, you need to calculate it each time he logs in and link to the BE with his credentials each time the db gets opened. You can make a little app for the DBA to calculate the password so he doesn't have to remember how. He just has to remember to use your app. You can use a link to Active Directory to get to the user's Last Name or whatever other information you want to draw the data your calculation uses.

Each database should use a different password so you will need several variations on creating a password from the info you have available.
 

KitaYama

Well-known member
Local time
Today, 23:20
Joined
Jan 6, 2022
Messages
1,489
You use SQL Server authentication. This is more work for the DBA because he has to define the users. One suggestion for "calculating" a password is to use the first and last letters of the login & the number of characters in the last name & the other letters of the login. You can make some letters caps and intersperse special characters even. The point is that you never tell the user what his password is to SQL Server. So if he tries to connect using his normal credentials, he can't get in. You don't need to store the password, you need to calculate it each time he logs in and link to the BE with his credentials each time the db gets opened. You can make a little app for the DBA to calculate the password so he doesn't have to remember how. He just has to remember to use your app. You can use a link to Active Directory to get to the user's Last Name or whatever other information you want to draw the data your calculation uses.
Pat, you're a genius. I had guessed how to create a user defined password. What I stumbled upon was how to use it in a FE, because all users are coping the same FE from a shared folder. I never thought of re-linking the db after each login. That section solved all questions I had. I'll test it on Monday and will be back if I need some help on it.

Million thanks.
 

isladogs

MVP / VIP
Local time
Today, 14:20
Joined
Jan 14, 2017
Messages
18,186
Or encrypt the passwords with a different cipher for each database . . .
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,970
Is that going to stop Charlie from opening Access and creating a new database that links to SQL Server and logging in.
 

isladogs

MVP / VIP
Local time
Today, 14:20
Joined
Jan 14, 2017
Messages
18,186
Is that going to stop Charlie from opening Access and creating a new database that links to SQL Server and logging in.
I meant if someone was going to use your system, then why not encrypt the passwords rather than ...
use the first and last letters of the login & the number of characters in the last name & the other letters of the login. You can make some letters caps and intersperse special characters even.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,970
I meant if someone was going to use your system, then why not encrypt the passwords rather than ...
We (the DBA and I) need a way to each calculate the password. The user doesn't ever get to know what his password is but I need to be able to calculate it without anyone actually typing it in. Normally, you would be given a default password that needed to be changed to whatever you want. But if you know what your password is, you can then open Access and create a new FE to link to SQL Server. That is what we didn't want the users to be able to do.
 

sonic8

AWF VIP
Local time
Today, 15:20
Joined
Oct 27, 2015
Messages
998
In a new order registration form, the Date field is locked and its default value is set to Now().
Why? because you don't want the user (by mistake) input a date earlier than today.
But if this user can access the tables via HIS database, he may do this mistake. And you can do nothing to prevent him.
There is a lot you can do to prevent users from entering invalid data.
Just one approach of several for the scenario you outlined above:
SQL:
ALTER TABLE dbo.Invoice
    ADD  DEFAULT (getdate()) FOR InvoiceDate
GO

ALTER TABLE dbo.Invoice
    ADD CONSTRAINT ChkInvoiceDate
        CHECK (InvoiceDate < GetDate());
GO

REVOKE UPDATE ON dbo.Invoice  FROM TestUser;
GO

GRANT UPDATE ON dbo.Invoice (List_All_Columns_Except_InvoiceDate) TO TestUser;
GO

If invalid data in your database is a real threat to your business, you must use the SQL Server permission system and server side data validation rules in constraints (example shown above), triggers, views, Stored Procedure, etc.
An Access any frontend application that runs on the user's local computer is insufficient protection for critical data.

If you are really concerned that genuine, benevolent users of your application rather enter data in linked tables in their own database instead of using your frontend, I would suggest you find out and fix what is wrong with your frontend application. No sane user will voluntarily do this, unless there is a serious problem with your application.


I've been in this type of discussion too many times and will probably not contribute here beyond this post.
I wrote some more on the matter in Authentication Mechanisms for Access + SQL-Server-Applications.
 

KitaYama

Well-known member
Local time
Today, 23:20
Joined
Jan 6, 2022
Messages
1,489
@sonic8
Although as you said you don't want to engage in this discussion because you've seen too many of them but :

1- I really appreciate your input.

2-There's a lot of tests that a FE may goes through before saving a new record. The date I explained was just an example because I didn't want to go through details. There are a lot more (and maybe rare) tests that our FE does and MAYBE is impossible or hard to achieve with server permission system.
For example we receive the order for manufacturing the same product from 5 different customers. So before allowing the user to save a new order, there's a very complex check that is done to be sure CustmoerFK is selected correctly. It involves checking
a- previous orders from the same customer,
b- the type of received OrderNo (each company has its own mix of numbers and digits),
c- the order quantity comparing to previous quantities of the same product for the same customer, (they normally order in same lot)
d- Seiban (which is an almost standard No. and most companies here use it as an identification of the project they'll plan to use the product. There's no equivalent word in English, and I've never seen this in other countries),
e- and some other checks.
If this test fails the user is asked to double check if the CustomerFK (combobox) is selected correctly. The user either confirms the selection or corrects the input data, and only then a new record is saved.
I really don't know how you do this test with SQL Server permission system or stored procedure and how you send a confirmation message to user to be sure if the data is OK even if it's different with what is expected. It means a different order from what is expected is possible, but it needs the user's confirmation.
That's only a part of the checks. Our FE has more checks to do with Deliveries, Shipping Addresses, Payments etc. that I don't believe it's possible to have the same result with what you explained above. For example we normally need 5 days for manufacturing 10,000 parts and users are not allowed to enter delivery less than today+5 days. In case the Customer Order Sheet says Emergency, a passcode must be issued by the production line manager and only by using this passcode then today+3 days delivery will be accepted by beforupdate event.

3- Even if everything is possible with what you explained above, I still don't think it's a good idea to allow someone create a linked table to SQL Server database even if the server is set to protect the data correctly. As I explained in my previous posts, intentionally or by mistake s/he may delete an order (without a correct check by vba to be sure) and no-one can prevent him (even sql rules) because his account in sql server is set to be able to Add-Edit-Delete data in tables.

4- Again Even if you are able to protect your sql server database perfectly, I think what @GPGeorge explained here is a lack of security level for sql server.

5- For now @Pat Hartman's suggestion covers the answer to my question (thanks to her). But I will read your link tonight in the way back home in train.

Again I really appreciate taking your time and replying.
 
Last edited:

sonic8

AWF VIP
Local time
Today, 15:20
Joined
Oct 27, 2015
Messages
998
As I explained in my previous posts, intentionally or by mistake s/he may delete an order (without a correct check by vba to be sure) and no-one can prevent him (even sql rules) because his account in sql server is set to be able to Add-Edit-Delete data in tables.
The Add-Edit-Delete permissions have not been imposed by an infallible godlike entity. They were configured by you or the DBA of the server. If you don't want a user to have delete permission then revoke that permission. Instead grant them permission to execute a stored procedure that deletes the record only after the necessary checks were successfully performed.

The same line of thinking can be applied to the objections you described in your paragraph "2".
 

GPGeorge

Grover Park George
Local time
Today, 07:20
Joined
Nov 25, 2004
Messages
1,775
@sonic8
Although as you said you don't want to engage in this discussion because you've seen too many of them but :

1- I really appreciate your input.

2-There's a lot of tests that a FE may goes through before saving a new record. The date I explained was just an example because I didn't want to go through details. There are a lot more (and maybe rare) tests that our FE does and MAYBE is impossible or hard to achieve with server permission system.
For example we receive the order for manufacturing the same product from 5 different customers. So before allowing the user to save a new order, there's a very complex check that is done to be sure CustmoerFK is selected correctly. It involves checking
a- previous orders from the same customer,
b- the type of received OrderNo (each company has its own mix of numbers and digits),
c- the order quantity comparing to previous quantities of the same product for the same customer, (they normally order in same lot)
d- Seiban (which is an almost standard No. and most companies here use it as an identification of the project they'll plan to use the product. There's no equivalent word in English, and I've never seen this in other countries),
e- and some other checks.
If this test fails the user is asked to double check if the CustomerFK (combobox) is selected correctly. The user either confirms the selection or corrects the input data, and only then a new record is saved.
I really don't know how you do this test with SQL Server permission system or stored procedure and how you send a confirmation message to user to be sure if the data is OK even if it's different with what is expected. It means a different order from what is expected is possible, but it needs the user's confirmation.
That's only a part of the checks. Our FE has more checks to do with Deliveries, Shipping Addresses, Payments etc. that I don't believe it's possible to have the same result with what you explained above. For example we normally need 5 days for manufacturing 10,000 parts and users are not allowed to enter delivery less than today+5 days. In case the Customer Order Sheet says Emergency, a passcode must be issued by the production line manager and only by using this passcode then today+3 days delivery will be accepted by beforupdate event.

3- Even if everything is possible with what you explained above, I still don't think it's a good idea to allow someone create a linked table to SQL Server database even if the server is set to protect the data correctly. As I explained in my previous posts, intentionally or by mistake s/he may delete an order (without a correct check by vba to be sure) and no-one can prevent him (even sql rules) because his account in sql server is set to be able to Add-Edit-Delete data in tables.

4- Again Even if you are able to protect your sql server database perfectly, I think what @GPGeorge explained here is a lack of security level for sql server.
5- For now @Pat Hartman's suggestion covers the answer to my question (thanks to her). But I will read your link tonight in the way back home in train.

Again I really appreciate taking your time and replying.
As Sonic8 explained, you and the DBA need to work together to implement the kinds of security that you need to protect your data from your colleagues who are not authorized to use it incorrectly. I think I posted a link to a recent presentation on basic SQL Server Security that covers many of the issues you and others have raised, specifically how you can limit users ability to change data, even their ability to SEE specific rows or columns in a table. If I didn't include that link before, here it is again.

And finally, my comment about an application role that would allow us to designate permissions on a database for a specific relational database application itself, not just an accdb was not intended to imply any lack of security level for SQL Server. It is highly secure when configured properly. It was intended as a comment on one way applications built with Access could be made more secure when connecting to SQL Server. I grant that's a subtle distinction, but important.
 

GPGeorge

Grover Park George
Local time
Today, 07:20
Joined
Nov 25, 2004
Messages
1,775
@sonic8
Although as you said you don't want to engage in this discussion because you've seen too many of them but :

1- I really appreciate your input.

2-There's a lot of tests that a FE may goes through before saving a new record. The date I explained was just an example because I didn't want to go through details. There are a lot more (and maybe rare) tests that our FE does and MAYBE is impossible or hard to achieve with server permission system.
For example we receive the order for manufacturing the same product from 5 different customers. So before allowing the user to save a new order, there's a very complex check that is done to be sure CustmoerFK is selected correctly. It involves checking
a- previous orders from the same customer,
b- the type of received OrderNo (each company has its own mix of numbers and digits),
c- the order quantity comparing to previous quantities of the same product for the same customer, (they normally order in same lot)
d- Seiban (which is an almost standard No. and most companies here use it as an identification of the project they'll plan to use the product. There's no equivalent word in English, and I've never seen this in other countries),
e- and some other checks.
If this test fails the user is asked to double check if the CustomerFK (combobox) is selected correctly. The user either confirms the selection or corrects the input data, and only then a new record is saved.
I really don't know how you do this test with SQL Server permission system or stored procedure and how you send a confirmation message to user to be sure if the data is OK even if it's different with what is expected. It means a different order from what is expected is possible, but it needs the user's confirmation.
That's only a part of the checks. Our FE has more checks to do with Deliveries, Shipping Addresses, Payments etc. that I don't believe it's possible to have the same result with what you explained above. For example we normally need 5 days for manufacturing 10,000 parts and users are not allowed to enter delivery less than today+5 days. In case the Customer Order Sheet says Emergency, a passcode must be issued by the production line manager and only by using this passcode then today+3 days delivery will be accepted by beforupdate event.

3- Even if everything is possible with what you explained above, I still don't think it's a good idea to allow someone create a linked table to SQL Server database even if the server is set to protect the data correctly. As I explained in my previous posts, intentionally or by mistake s/he may delete an order (without a correct check by vba to be sure) and no-one can prevent him (even sql rules) because his account in sql server is set to be able to Add-Edit-Delete data in tables.

4- Again Even if you are able to protect your sql server database perfectly, I think what @GPGeorge explained here is a lack of security level for sql server.

5- For now @Pat Hartman's suggestion covers the answer to my question (thanks to her). But I will read your link tonight in the way back home in train.

Again I really appreciate taking your time and replying.
I think you may be conflating two problems.

" As I explained in my previous posts, intentionally or by mistake s/he may delete an order (without a correct check by vba to be sure) and no-one can prevent him (even sql rules) because his account in sql server is set to be able to Add-Edit-Delete data in tables.

If you give users permission to modify data, you have to expect that they make mistakes using that data, or that they might decide to sabotage their employer by deleting all of the data in a database, or cover their criminal activity by modifying data. In other words, these kinds of problem have nothing at all to do with the technical aspects of security.

It matters not at all whether they use the Access interface to make mistakes, vandalize the database or steal, or whether they use another route to the data to make mistakes, vandalize the database or steal. That is NOT the source of the problem. The problem is "people", and that's a whole other world of problems to manage.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:20
Joined
Feb 28, 2001
Messages
26,999
The problem is "people", and that's a whole other world of problems to manage.

Yep, "people" is a dirty word though it IS bigger than four letters. For me the four-letter word is "user" and is spoken with appropriate vehemence. Or,... USER - can't live with them, don't have a job without them. What are you gonna do?
 

Users who are viewing this thread

Top Bottom