What are the recommended security measures to put in place?
Hi Experts,
We have a database of employees which includes a lot of personal info, like names, addresses, phone numbers, ss etc..
Now the manager would like to enforce security in place that not everyone with access to the FE application should have access to all this.
In addition, he would like certain info to be hidden altogether, like SS should only get displayed last 4 digits..
Another point of concern is that someone can potentially create a report/query, export to an Excel file and copy that to a flash drive...how can this be prevented?!
Since we currently have our data stored in SQL (2005) and our FE application is Access ADP (2003), I was wondering what kind of security are available for those platforms, and what is recommended?
PS. Would also want to know, since these software are no longer supported by Microsoft, does it mean its a breach of security to continue using them (specially cyber security), meaning they are more prone for undesired people to get access?
Thanks in advance.
We have a database of employees which includes a lot of personal info, like names, addresses, phone numbers, ss etc..
Now the manager would like to enforce security in place that not everyone with access to the FE application should have access to all this.
In addition, he would like certain info to be hidden altogether, like SS should only get displayed last 4 digits..
Another point of concern is that someone can potentially create a report/query, export to an Excel file and copy that to a flash drive...how can this be prevented?!
Since we currently have our data stored in SQL (2005) and our FE application is Access ADP (2003), I was wondering what kind of security are available for those platforms, and what is recommended?
PS. Would also want to know, since these software are no longer supported by Microsoft, does it mean its a breach of security to continue using them (specially cyber security), meaning they are more prone for undesired people to get access?
Thanks in advance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all experts for your opinions on security.
And SPECIAL thanks to Bitsqeezer for his in-depth course of SQL/Access security modeling!
I really needed to represent the manager with a variety of options, so thanks again for providing it.
And SPECIAL thanks to Bitsqeezer for his in-depth course of SQL/Access security modeling!
I really needed to represent the manager with a variety of options, so thanks again for providing it.
ASKER
Just a question (if you dont mind).
Meanwhile while we are still operating in unsafe mode..
We have an Access DB linked to SQL where users can create their own queries (its linked with user/pwd that only allows viewing), Is there a way from preventing users from exporting to Excel?
Also how can I prevent them from selecting/copying records like Cntrl+C Cntrl+V keys or from top menus (without disabling the ability to create new queries, update existing ones and using some built in menus like the sort a-z or find menus)?
Thanks,
Ben
Meanwhile while we are still operating in unsafe mode..
We have an Access DB linked to SQL where users can create their own queries (its linked with user/pwd that only allows viewing), Is there a way from preventing users from exporting to Excel?
Also how can I prevent them from selecting/copying records like Cntrl+C Cntrl+V keys or from top menus (without disabling the ability to create new queries, update existing ones and using some built in menus like the sort a-z or find menus)?
Thanks,
Ben
Hi Ben,
if you allow creating own queries the result is a query window of Access. This is not a form so there are no events which you could handle (although some are possible with some dirty tricks) so you can't do anything to stop the user from copying/pasting the results to anywhere else including Excel. That's also the case with forms, only difference is that you can disable CTRL-C or CTRL-A with keyboard events.
Moreover if the user has username/password he can do anything also in Excel directly to download data using the query editor of Excel.
I would not allow users to create queries on their own or give them the possibility to access tables directly. Creating all kind of queries means you can get data out of the database where you not thought of. For example, a history table could be used to find out which user has worked on which data how long, how often, or if he were at office at a specific date and so on.
Cheers,
Christian
if you allow creating own queries the result is a query window of Access. This is not a form so there are no events which you could handle (although some are possible with some dirty tricks) so you can't do anything to stop the user from copying/pasting the results to anywhere else including Excel. That's also the case with forms, only difference is that you can disable CTRL-C or CTRL-A with keyboard events.
Moreover if the user has username/password he can do anything also in Excel directly to download data using the query editor of Excel.
I would not allow users to create queries on their own or give them the possibility to access tables directly. Creating all kind of queries means you can get data out of the database where you not thought of. For example, a history table could be used to find out which user has worked on which data how long, how often, or if he were at office at a specific date and so on.
Cheers,
Christian
You should also make sure your IT policy states that these kinds of behaviors are not allowed. And have all employees sign it.
Although it may be convenient to allow people to create their own queries, it is generally a bad idea if the data is sensitive. If you want to give them some flexibility, you should build your own query generator that limits what they have access to.
ASKER
Thanks Experts,
The problem is that I will have to find a way to restrict things slowly piece by piece, while replacing each thing with alternative way of accomplishing it, so since some users/managers currently have the ability to create their own queries, I will have to do something like Pat stated
Thanks,
Ben
Moreover if the user has username/password..I agree with all you wrote above re security, however it will take some time till I can get to apply all that, therefore we're basically looking what can be done momentarily, and w/o changing too much the existing application/functionality.
The problem is that I will have to find a way to restrict things slowly piece by piece, while replacing each thing with alternative way of accomplishing it, so since some users/managers currently have the ability to create their own queries, I will have to do something like Pat stated
If you want to give them some flexibility, you should build your own query generator that limits what they have access toDo you know of a freeware/shareware utility that offers that capability?
Thanks,
Ben
No. Not many people develop for Access. I would probably start by changing their passwords and having the Access appliction log into SQL Server for them. Then for the people who create their own queries, I would give them a second account to be used for creating queries because they will need to know their password in order to do their own linking. Then I would create views and only allow their accounts to access those views rather than the tables themselves.. This is a fairly minor change to the app but it will require some work to come up with an algorithm you and the DBA can use to generate passwords from the userID's. An alternative is to use the same password for everyone. So JoeK signs in and in the Access app, you have a hard coded password that the user doesn't know but that you use for everyone. This is less desirable since Access databases are too easy to crack and you would need to store the value in a table or property somewhere.
ASKER
Hi Pat,
Let me clarify the situation here a bit more..
We have two Access files, one is an ADP FE application which is used for data entry and for pre-defined reports, and the other is is a MDB that is used for users who are familiar with designing queries, for the first file I understand from what Bitsqeezer wrote above that since its an ADP that you must provide the connection properties before opening it, and its not possible to use your method, which means first open the file, prompt for user input and then assign connection string.
While for the second file, all what users have is a file with tables linked to them and under queries they see all their created queries (which are by now a few hundred or probably close to a thousand..), therefore I dont see where I have to deal with their passwords, and from my experience, no one knows their user name and passwords, as these are all saved in the DNS file which they never got to see it or ever dealt with them..
Thanks,
Ben
..and having the Access appliction log into SQL Server for them
..ecause they will need to know their password in order to do their own linking
Let me clarify the situation here a bit more..
We have two Access files, one is an ADP FE application which is used for data entry and for pre-defined reports, and the other is is a MDB that is used for users who are familiar with designing queries, for the first file I understand from what Bitsqeezer wrote above that since its an ADP that you must provide the connection properties before opening it, and its not possible to use your method, which means first open the file, prompt for user input and then assign connection string.
While for the second file, all what users have is a file with tables linked to them and under queries they see all their created queries (which are by now a few hundred or probably close to a thousand..), therefore I dont see where I have to deal with their passwords, and from my experience, no one knows their user name and passwords, as these are all saved in the DNS file which they never got to see it or ever dealt with them..
Thanks,
Ben
ASKER
Hi Experts,
When you have a chance please look at the following
https://www.experts-exchange.com/questions/29022658/How-to-create-and-use-encrypted-columns-in-SQL-2005.html
Thanks,
Ben
When you have a chance please look at the following
https://www.experts-exchange.com/questions/29022658/How-to-create-and-use-encrypted-columns-in-SQL-2005.html
Thanks,
Ben
Ben,
Sounds like you are not in a position to change anything.
If you are giving people an empty .mdb with linked tables, then you must have saved the userID and password when you created the .mdb so everyone is using the same credentials. That means that the DBA has no idea who is doing what. Hopefully, the credentials do't allow updating and the linked tables don't contain sensitive data. Otherwise, since you "can't" change anything, you can't secure the data.
Sounds like you are not in a position to change anything.
If you are giving people an empty .mdb with linked tables, then you must have saved the userID and password when you created the .mdb so everyone is using the same credentials. That means that the DBA has no idea who is doing what. Hopefully, the credentials do't allow updating and the linked tables don't contain sensitive data. Otherwise, since you "can't" change anything, you can't secure the data.
Hi Ben,
if someone can use an "open" MDB with linked resources he can read out the connection string which is in the properties (or displayed if you move the mouse over the link) and then use that connection string in any other application like Excel. He don't need to know what's in the DSN as Access also doesn't "know" it. As long as the DSN is OK the user can access everything which is allowed with the DSN (a reason why I would never provide a DSN on the user's computer).
Cheers,
Christian
if someone can use an "open" MDB with linked resources he can read out the connection string which is in the properties (or displayed if you move the mouse over the link) and then use that connection string in any other application like Excel. He don't need to know what's in the DSN as Access also doesn't "know" it. As long as the DSN is OK the user can access everything which is allowed with the DSN (a reason why I would never provide a DSN on the user's computer).
Cheers,
Christian
ASKER
Hi Experts,
@Pat,
Please let me correct something..I'm not currently giving them anything, they already had all these ever since..the question is now how I can remove things w/o causing a big turmoil..and the business should continue running. (this is why I stated above, will have to work piece by piece, and as you saw in my other thread I'm looking to start with encrypting some data, which is what some of you suggested above.
@Bit,
PS. I do see under MySysbjects table the password being displayed [ask me why..], and if you have a solution for that let me know, (I will open a new thread so you can be rewarded..)
Thanks,
Ben
@Pat,
Sounds like you are not in a position to change anything.
If you are giving people an empty .mdb..
Please let me correct something..I'm not currently giving them anything, they already had all these ever since..the question is now how I can remove things w/o causing a big turmoil..and the business should continue running. (this is why I stated above, will have to work piece by piece, and as you saw in my other thread I'm looking to start with encrypting some data, which is what some of you suggested above.
@Bit,
..he can read out the connection string which is in the properties (or displayed if you move the mouse over the link)Right, the DB name and user name gets displayed then, however I dont see the password getting displayed, and w/o a password I dont see how any connection can be accomplished?
PS. I do see under MySysbjects table the password being displayed [ask me why..], and if you have a solution for that let me know, (I will open a new thread so you can be rewarded..)
Thanks,
Ben
Hi Ben,
no, I don't have an idea about that issue with MSysObjects, sorry.
But what I meant above is that you've said you use a DSN so you save the credentials on Windows level. So the same DSN can also be used by any other application including such simple as VBScript to get a connection.
Cheers,
Christian
no, I don't have an idea about that issue with MSysObjects, sorry.
But what I meant above is that you've said you use a DSN so you save the credentials on Windows level. So the same DSN can also be used by any other application including such simple as VBScript to get a connection.
Cheers,
Christian
https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security#a-namebesta-best-practices