How to secure the tables in SQL Server Express 2016
I would like to find out how to secure the tables in MS Express SqL Servers 2016, I’m totally shocked to what I saw today when a prospective client called me to showcase our software which also has both the MS Access Database (As Back End) and SQL Server as Back End. The client has a standard software off the shelf but can move around within tables in SQL Server 2014 freely and was able change setting from the same software, now because of what I saw I cannot supply my software with SQL Server Backend until I know how to protect the tables from intruders. It is now clear why he was calling for our software is because he massed the unprotected back end, thanks god, I have not yet started supplying the SQL Server as backend except MS Access 2016 which has the back end encrypt with a password.
(1) If in Ms Access, we can encrypt the backend through a password how can we do the same with MS Express SQL Server 2016???????
(2) If the there are ways of protecting the tables, then doesn’t that affect the users and re-linking the tables, though with Ms Access the re-link table manager has no problem with that, now how are about the MS SQL Server 2016????????
Regards
Chris
(1) If in Ms Access, we can encrypt the backend through a password how can we do the same with MS Express SQL Server 2016???????
(2) If the there are ways of protecting the tables, then doesn’t that affect the users and re-linking the tables, though with Ms Access the re-link table manager has no problem with that, now how are about the MS SQL Server 2016????????
Regards
Chris
The appropriate concept would be using application roles. Which is not really possible using DAO / linked tables.
On the other hand, this kind of "protection" is unnecessary. Cause it is natural that an admin can access the backend. (1) does not protect your data or application from capable hackers. It only "protects" it from curious users.
The first step is define your security targets. Then we can give you some concrete advice.
On the other hand, this kind of "protection" is unnecessary. Cause it is natural that an admin can access the backend. (1) does not protect your data or application from capable hackers. It only "protects" it from curious users.
The first step is define your security targets. Then we can give you some concrete advice.
As Anders said when you install the Database on their server then its just natural to have full control over everything...even high end ERP have everything unlocked for everyone with the right permissions to view just about everything...
The way i see it you can either opt for continuing with Access... or encrypt the data by using your own encrypting mechanism (of course this will have some impact on your database performance)
The way i see it you can either opt for continuing with Access... or encrypt the data by using your own encrypting mechanism (of course this will have some impact on your database performance)
As two people suggested above assigning Roles is likely the correct approach.
This will be highly dependent on the purpose of your code.
If your code initiates actions which requires full admin privileges, then your code will require a rethink.
Tip: Rather than allow direct access to your database, create an API layer on top of your database where people can only initiate high level API calls which completely abstract/hide underlying data.
This will be highly dependent on the purpose of your code.
If your code initiates actions which requires full admin privileges, then your code will require a rethink.
Tip: Rather than allow direct access to your database, create an API layer on top of your database where people can only initiate high level API calls which completely abstract/hide underlying data.
So in short we are saying it is not posible to hide the tables only or protect them like the way we do it in Ms access , yes or no???????
If we apply login laye still the administrator is still free to do anything , I understand why I saw some clearly naked tables today in SQL Server 2014.
Regards
Chris
If we apply login laye still the administrator is still free to do anything , I understand why I saw some clearly naked tables today in SQL Server 2014.
Regards
Chris
Well unless i missed something ...this is the case...the Tables on SQL are naked....so as i said the only way to "protect" them is to obfuscate the data...maybe along with some meaningless table/fields names
I must ask...why you are concerned about your data...in the end a database is only useful if it has data and the data are always coming from the client...you could have the "best" database in terms of design ...but unless someone fills it with the data ...its as good as nothing...
To take it one step further...if you really want to protect "your" database then the only solution would be a cloud based SQL so that the clients would have access to their data only by your application...but i am not sure if they agree on this and it comes with great responsibility on your part to ensure the data are always available and protected
I must ask...why you are concerned about your data...in the end a database is only useful if it has data and the data are always coming from the client...you could have the "best" database in terms of design ...but unless someone fills it with the data ...its as good as nothing...
To take it one step further...if you really want to protect "your" database then the only solution would be a cloud based SQL so that the clients would have access to their data only by your application...but i am not sure if they agree on this and it comes with great responsibility on your part to ensure the data are always available and protected
So in short we are saying it is not posible to hide the tables only or protect them like the way we do it in Ms access , yes or no???????As there is no way in Access to protect tables, this question makes no sense. The only protection in Access is using a database password, which is from the viewpoint of security only obfuscation. It is not a security measure.
Thus:
The first step is define your security targets.What do you like to secure against what threats? Define the security targets. Post them as bullet list. Then we can discuss it and give you concrete advice. But don't think, that security is something which can be implemented easily or on the fly. Such a kind of measures are not security.
A "secure" Access application using SQL Server means that users get role controlled access to the necessary data. Keyword is data, not table. This means, you don't link tables, but only views. These views are crafted to provide only the necessary amount of data for your application. Thus it is not a security issue, when a user can access those views (without your application).
Okay so in short we cannot use a password like the way we do with Ms Access because it work very well there, yes I know the data alone does not make sence without an application. But again the reason for my migration to sql was based on security and multi user concurent, so in this case it appears the gain for the table to SQL is simply is just maybe multi user gain only, for security I do not see any safety on the tables or database objects here.
(1) For someone to copy a database you have to have full administrators rights which is an obvious case for all administrators.
Anyway tough issue
Regards
Chris
(1) For someone to copy a database you have to have full administrators rights which is an obvious case for all administrators.
Anyway tough issue
Regards
Chris
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
From a user perspective, you CAN secure the data.
As a business owner, I wouldn't consider a system where I can not get access to the data. Why are you opposed to system admins being able to view the data?
User access to data (Select,insert,update,dele
As a business owner, I wouldn't consider a system where I can not get access to the data. Why are you opposed to system admins being able to view the data?
User access to data (Select,insert,update,dele
Again:
Using descriptions like "cannot use a password like the way we do with Ms Access" make also no sense, cause this is an possible implementation for one or more security targets. But it is not possible for us to say which ones were the reason for choosing this implementation.
Example:
Target: Users should not access tables.
Solution: Don't grant DML permissions to users. Users get only DML permissions on views and EXECUTE on procedures.
The first step is define your security targets.Just talking about "safety" is not worth the time. You cannot implement security without exactly defining what you want to do, before you do it.
Using descriptions like "cannot use a password like the way we do with Ms Access" make also no sense, cause this is an possible implementation for one or more security targets. But it is not possible for us to say which ones were the reason for choosing this implementation.
Example:
Target: Users should not access tables.
Solution: Don't grant DML permissions to users. Users get only DML permissions on views and EXECUTE on procedures.
Thanks slightwv (䄆 Netminder)
That makes sense now
Regards
Chris
That makes sense now
Regards
Chris
>>Why are you opposed to system admins being able to view the data?
Easy: Separation of Duties and Least Privilege
It is a fairly common practice these days.
Easy: Separation of Duties and Least Privilege
It is a fairly common practice these days.
This can be done at the schema level or the table level, or even down to the column level,and can be done quite complex or quite simple depending on your needs.
But you should understand that when the database is on their server, they can make changes, by signing in as a system administrator,and I don't think there is any way to prevent that. Of course only their system administrators will be able to do that.
What method of sign in do you support in your application? Are users signing in through Windows AD, or are you creating users for them?
Finally, what exactly are you trying to protect? Obviously the average user of your database should not be able to make changes to the backend, but usually it is the intellectual property (code) of the frontend an access developer wants to protect.