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

[isladogs]

I agree with that, though my answer was ambiguous as written. Better still don't store passwords at all

 

[Pat Hartman]

You don't see the hidden tables when you use Access to link to the BE, why should other apps be able to "see" the hidden tables. I know Access can't distinguish between a link from an application you want to link from and a link from an application you don't want to link from but it can identify hidden tables and just not show them in ANY link dialog. That lets the developer control which apps get to link and which don't because the developer can unhide the linked table to enable linking or link using VBA.

We are only talking about SHOWING the hidden tables in the linking dialog. I agree, if you know the name of the table, it can't really be hidden. But the users using the Power app won't know the table name and if they can't see it in the list, they can't use it. You might want to change the table name to something that will obscure its actual usage. You can do this without changing the app if that is easier. Just rename the table. Then create a query and save it as the name of the old table. This trick shows you why you can't have both a query and a table with the same name.

 

[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