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

[sonic8]

yes SQL

This is good news for you, as it means you can implement my suggestions to improve security.

As to all the other contributors in this thread discussing the mechanics and implications of hidden tables in Access: Your discussion is not very relevant to this case, as the tables are not in Access but in SQL Server.

 

[The_Doc_Man]

I don't know if it helps but this is one of the few cases I ever found (while with the U.S.Navy) where a 1:1 table made sense, where the "public" or "less sensitive" data was in a table with only minor security, but the "private" or "more sensitive" data was in a different table with far more stringent security settings. The two tables were related in a 1:1 manner since they had the same PK. Having the tables in SQL server would seem to facilitate such a solution. I would emphasize that this is probably one of only two reasons to ever use a 1:1 table relationship.

 

[isladogs]

This is good news for you, as it means you can implement my suggestions to improve security.

Hi Philipp
We both agree that hashing/encrypting the data is worthwhile whether the tables are in Access or SQL Server.

Perhaps I'm being dense but which of your suggestions would help improve security specifically in relation to linked SQL tables in this instance?
The end user is running Excel PowerQuery on deep hidden linked Access tables which in this case happen to be from SQL Server.
But what difference does that make? Am I missing something here?

 

[sonic8]

which of your suggestions would help improve security specifically in relation to linked SQL tables in this instance?

[...]give the users only permission to query data from a view on that table, which doesn't include sensitive information

SQL Server's permission system enables the developer to implement the above in a secure way, even if queried from PowerQuery or any other tool, while Access does not.

 

[isladogs]

Ah. it seems I misread post #5.
Can you spell out the SS permissions details that will solve this issue

 

[sonic8]

Can you spell out the SS permissions details that will solve this issue

REVOKE ALL ON TableWithSensitiveData FROM OrdinaryUserAccount;
GRANT SELECT ON ViewWithoutSensitiveData TO OrdinaryUserAccount;

This is just the tip of the iceberg. One would need to spend some time to learn SQL Server's permission system.

 

[GPGeorge]

You can review the topic in this AUG User Group Presentation: Building a Secure Access and SQL Server Application, by Anders Ebro

 

[isladogs]

Thanks to both of you. More importantly hopefully this will be useful to the OP