Unauthorized Access into the Database Using the Excel Power Query

[adewale4favour]

Hi Guys,
I probably would't be the first person to notice this, it actually calls my curiosity, so I tried it and it worked. I developed Access Database with all the required security settings, both for the Front End (FE) and the Back End (BE). The users are all required to Login, before any access is given and based on their user level authorization.

I tried to use the Power Query resources in Excel to access the backend of the database, this is to fetch all users password, but the system returned an error, that the Database is password protected, which is fine. I now tried using the same query to access the Front End to fetch the Users Credentials and surprisingly, it worked. All user details, Name and Password, together with User Level Authorization was fetched.

Now my area of concern is this, if an intruder, wants to get user credentials to get information from the system, if the fellow adopt same means, then he/she will have access to the whole system, even the Administrative right.

Please guys, is there a way around protecting this from happening?

Regards
Michael

 

[CJ_London]

presumably your front end is using linked tables to the backend.

The usual solution is to not use linked tables. Instead use dao or ado recordsets created in code to connect to the backend and assign to the form/report recordset.

Depending on the level of security required you may only need to do this for the admin tables - however linked tables will contain the password in it's properties so anyone with a small amount of knowledge can discover the password.

And to go further, more sophisticated users can discover the password if you are using recordsets and have hardcoded the password in vba - even from a .accde - so you may have to obscure it here as well - you need to look at using a hash function.

No system is 100% secure. Access, being a file based system can still be made 95% secure, but will never be as secure as using sql server/mysql and the like which are server based systems.

You need to develop a security policy - what you want to protect, from whom and using what methods. For example do you need your login routines to be protected from sql injection? what is to stop a user downloading a report of all your customers/orders/whatever?

 

[Deleted member 147267]

I presume your user data is in the fe? Why isn't it in the be where it's password protected? Additional protection might be gained via encryption.
If your data is super sensitive, Access isn't the right tool.

 

[adewale4favour]

Many thanks CJ_London

This is actually educating. The FE is linked to the BE directly. I think your suggestion will be the best option.

If you have any other idea, kindly share.

Regards
Michael

 

[adewale4favour]

Thanks Micron

 

[theDBguy]

Hi Michael. As already mentioned, Access is not the "best" tool to secure your data. If it really has to be protected, then you would want to migrate it to a standard RDBMS like SQL Server or Oracle or MySQL, etc.

 

[adewale4favour]

Hi Michael. As already mentioned, Access is not the "best" tool to secure your data. If it really has to be protected, then you would want to migrate it to a standard RDBMS like SQL Server or Oracle or MySQL, etc.

I quite appreciate. Access might not be the best. The type of records I am dealing with require some high security level.
Many thanks friend.

Regards
Michael

 

[CJ_London]

If you have any other idea, kindly share.

There are other things you can do but they really needed to be done as part of your initial db design specification. To go back now probably means a significant rewrite of both the FE and the BE.

you might find this link of interest http://www.mendipdatasystems.co.uk/security-challenges/4594398127 - the site is owned by isladogs (Colin)

 

[adewale4favour]

There are other things you can do but they really needed to be done as part of your initial db design specification. To go back now probably means a significant rewrite of both the FE and the BE.

you might find this link of interest http://www.mendipdatasystems.co.uk/security-challenges/4594398127 - the site is owned by isladogs (Colin)

This is great! I will take time to study this and apply if still possible.
From the content I already read, beginning of the design would have being the best implementation stage.

Thanks a lot!

Regards
Michael