MS Access: Hiding liked table information from power query or other external data fetching tool without authorization

[adewale4favour]

Hi Guys,

Hope we are all keeping safe.....

I discovered a major problem with my Access Application and I have been trying to fix it, anyone with tips, will really appreciate it.

I have an access application running from a server with the FE distributed on users table. One very important table linked to every FE is the UserID information. This is where all user Authorization Information is, including UserName Password, Credit Card details(Verification purpose) and other important information.

What I actually discovered is that, when anyone uses EXCEL's POWER query to fetch information from any of the tables, the whole information include very sensitive ones, like the password, username, credit-card details, everything just comes up and this information can be exported to Excel or used.

What I have tried to do was to use the VBA deploying the Access TableDef Object, I noticed that, though the table is hidden by the code, it doesn't stop Excel Power Query or other external data fetching tool from accessing it.

I think there will be a way around this, I have been trying to figure this out, this is not good for any Database Application by any means, as anyone could use this to fetch any sensitive information from the Application.

Anyone with tips to fix this please.

 

[sonic8]

This is where all user Authorization Information is, including UserName Password, Credit Card details(Verification purpose) and other important information.

You should never store passwords in clear text in a table. Store a hash value of the password instead.
Do you really have to store credit card information at all? If yes, you should think about encrypting the data. If encryption is not possible easily, a workaround would be to give the users only permission to query data from a view on that table, which doesn't include sensitive information or only shows each user his own sensitive data but not that of others.

 

[Gasman]

Why not search this site, @isladogs has several posts about hiding tables etc

 

[isladogs]

@Gasman
The OP had already PM'd me (and 5 others) earlier. I said to post a forum thread so everyone could contribute/benefit.

@adewale4favour
My first two points have already been covered by @sonic8
a) If you must use login passwords, make sure they are encrypted. See https://www.isladogs.co.uk/password-login/index.html
b) Access is not secure enough for sensitive data such as credit card information. Your post demonstrates that perfectly

As you have found out, even using the dbHiddenObject attribute to make the tables 'deep hidden' within Access does not prevent PowerQuery being able to read the data. You could try applying a strong password to the database and/or using code to prevent the tables being exported to Excel

Whatever solution you use, someone who knows what they are doing would still be able to circumvent that if they had sufficient time & motivation.
Obtaining credit card data might well provide sufficient motivation

If you want to explore adding as much security as possible, have a look at my security challenge:

Encrypted Split No Strings

This example app shows how an Access database can be made reasonably secure against hackers whilst still allowing full functionality to authorised users.

www.isladogs.co.uk

 

[sonic8]

I have an access application running from a server

This made me believe you are using a SQL Server Backend. If this isn't the case, my advice re. permissions is not feasible.

 

[adewale4favour]

This made me believe you are using a SQL Server Backend. If this isn't the case, my advice re. permissions is not feasible.

yes SQL

 

[adewale4favour]

@Gasman
The OP had already PM'd me (and 5 others) earlier. I said to post a forum thread so everyone could contribute/benefit.

@adewale4favour
My first two points have already been covered by @sonic8
a) If you must use login passwords, make sure they are encrypted. See https://www.isladogs.co.uk/password-login/index.html
b) Access is not secure enough for sensitive data such as credit card information. Your post demonstrates that perfectly

As you have found out, even using the dbHiddenObject attribute to make the tables 'deep hidden' within Access does not prevent PowerQuery being able to read the data. You could try applying a strong password to the database and/or using code to prevent the tables being exported to Excel

Whatever solution you use, someone who knows what they are doing would still be able to circumvent that if they had sufficient time & motivation.
Obtaining credit card data might well provide sufficient motivation

If you want to explore adding as much security as possible, have a look at my security challenge:

Encrypted Split No Strings

This example app shows how an Access database can be made reasonably secure against hackers whilst still allowing full functionality to authorised users.

www.isladogs.co.uk

 

[adewale4favour]

I appreciate your ingenuity in laying these lines here. What I have tried to do in the immediate is hashing all the sensitive information, though this looks boring to get by, but till I can figure out a more stringent way to get this secured, hashing is implemented.

I am presently studying from one of your recommended post, once I am able to get somewhere with this, I will get back.

thanks.

 

[cheekybuddha]

Hashing is only really useful for passwords, since you never need to know a user's password - in fact, you should only ever hash passwords, never encrypt them.

However, you will need to encrypt data like credit card details, since you will need to decrypt them to use them. You will not be able to retrieve them from a hash.

However, there are a lot of other considerations when storing credit card details like PCI compliance, which is probably impossible using Access. You would be better off using your credit card service provider to store your client's card details and take payments via their API's.

 

[Pat Hartman]

Perhaps MS should change the way the ODBC driver for Jet/ACE works so that hidden tables are not exposed to the world. I'll send some feedback.

 

[Cotswold]

That's one of the most shouty thread titles I've seen in a while

 

[Gasman]

That's one of the most shouty thread titles I've seen in a while

O/P has posted a few like that.

Perhaps they thought they were not being heard?

 

[adewale4favour]

Perhaps MS should change the way the ODBC driver for Jet/ACE works so that hidden tables are not exposed to the world. I'll send some feedback.

I think MS needs to look at this, when VBA hides any of the Objects, that should not be visible to any type of query? If it must be available to anyone, then the developer would not implement such procedure on them.

 

[isladogs]

I think MS needs to look at this, when VBA hides any of the Objects, that should not be visible to any type of query? If it must be available to anyone, then the developer will not implement such procedure on them.

Do bear in mind that deep hidden tables can also be viewed in a query in Access providing you know the name of the table (which is easy to obtain):

SELECT * FROM tblDeepHide;

In other words, you should not rely on deep hiding tables alone. If you do use that approach, do so in conjunction with other methods such as those I mentioned in post #4. Start by encrypting the Access file with a password

 

[KitaYama]

Hidden is a visual state. The object still is there but can't be seen. If something exists you can access it one way or another.
In windows you can hide a folder, but still you can write the path in addressbar and see the contents.

You can even deep hide the tables in BE, but still you are able to create linked tables to them in FE.
Do you expect deep hidden tables in BE not being accessible from FE?

 

[isladogs]

Hidden is a visual state. The object still is there but can't be seen. If something exists you can access it one way or another.
In windows you can hide a folder, but still you can write the path in addressbar and see the contents.

You can even deep hide the tables in BE, but still you can create linked tables in FE.
Do you expect deep hidden tables in BE not be accessed from FE?

Just to clarify, unlike standard hidden tables, deep hidden tables can not be viewed from another database via the linked table manager.
However existing links still work if the table(s) are then deep hidden.

However, it is possible to link to deep hidden tables (including built in system tables) using VBA. In fact that is often VERY useful.

 

[KitaYama]

However, it is possible to link to deep hidden tables (including built in system tables) using VBA. In fact that is often VERY useful.

Yes, we can use VBA and that was why I said you can access a hidden object one way or another. Because it's still there.
I personally never use link table manager and use vba.
I think you still remember I needed to access BE's MsysObjects a while back in "Getting the count of tables in an external database" thread.
I wanted to link to hidden tables in BE.

 

[gemma-the-husky]

I would simply encrypt data you wish to be hidden. You can't store a hash for something you might need to decrypt on the fly, as it were.

A simple xor function would probably be sufficient.

Given a UK NI Number, such as AA123456D, if a simple reversible XOR operation produced a random string, you would struggle to reverse it to the plain text without knowing the precise XOR process that was used.

 

[isladogs]

I disagree. XOR is usually very easy to reverse using pattern matching. For passwords or even more importantly, credit card data, both encryption & hashing needs to be much stronger. Though as I've already mentioned, storing credit card info in Access isn't a wise decision

 

[cheekybuddha]

Though as I've already mentioned, encrypting passwords isn't a wise decision.