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

isladogs

MVP / VIP
Local time
Today, 06:08
Joined
Jan 14, 2017
Messages
18,261
I agree with that, though my answer was ambiguous as written. Better still don't store passwords at all
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:08
Joined
Feb 19, 2002
Messages
43,484
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

AWF VIP
Local time
Today, 07:08
Joined
Oct 27, 2015
Messages
999
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

Immoderate Moderator
Staff member
Local time
Today, 00:08
Joined
Feb 28, 2001
Messages
27,321
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

MVP / VIP
Local time
Today, 06:08
Joined
Jan 14, 2017
Messages
18,261
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

AWF VIP
Local time
Today, 07:08
Joined
Oct 27, 2015
Messages
999
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

MVP / VIP
Local time
Today, 06:08
Joined
Jan 14, 2017
Messages
18,261
Ah. it seems I misread post #5.
Can you spell out the SS permissions details that will solve this issue
 

sonic8

AWF VIP
Local time
Today, 07:08
Joined
Oct 27, 2015
Messages
999
Can you spell out the SS permissions details that will solve this issue
SQL:
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.
 

isladogs

MVP / VIP
Local time
Today, 06:08
Joined
Jan 14, 2017
Messages
18,261
Thanks to both of you. More importantly hopefully this will be useful to the OP
 

Users who are viewing this thread

Top Bottom