Hide System Tables When Connecting To SQL Server (1 Viewer)

[raziel3]

How do you prevent the user from seeing the system tables when using the link table wizard in Access.

Is it a Server side setting or MS Access?

 

[GPGeorge]

Perhaps, if this question is asked in the context of an end user, and not the developer, it would be better not to allow the user that level of access to tables at all. Perhaps it would be a better strategy to provide the accdb already linked to the appropriate database and tables. And, if the end user is going to make changes at this fundamental level, it would be safer to provide a VBA procedure to relink backends under your control via that procedure.

 

[Pat Hartman]

I agree with George. At best, the only option should be to refresh the links or locate a new BE. I include a custom form for this so I don't give the user the option to even use the linked tables manager.

But, to answer the actual question, in your BE, view the navigation pane settings and uncheck the show hidden/ show system checkboxes.

 

[Isaac]

Perhaps

The understatement of the day. Yes ... as a developer, you want end users to only use, not design, your database!

 

[Pat Hartman]

OOPs, This was a SQL Server question. Even worse. Users have absolutely no business being able to control what tables they link to in SQL Server. PERIOD. It is bad enough with an ACE BE where you occasionally have to link to a different BE or move the BE to a different server.

 

[raziel3]

I did some testing. I set up passwords and credentials for the SQL sever so in order to link to the server, even if the user finds the dns file, they will need a password to login.

The question, Does access store this password anywhere? Can a user 'hack' and find it?

 

[Pat Hartman]

Does access store this password anywhere?

Only if you tell it to. It is easily viewable. No hack required. So no one stores the password.

But the question remains, why does the user need to link to SQL Server from the Access app?

 

[raziel3]

The user does not need to but they still can click the link table wizard.

This what I'm try to prevent. I want to disable the link table/external data button.

 

[Isaac]

There are several ways you should be hiding all that stuff from your users. Accde is one of them, other ones are in the Options

or really get fancy and do this:

Disabling the Access X “close”

Hi guys I am trying to not allow users to close the application using the Access X button. I have an public variable called fcanIclose as Boolean here is the code: Private Sub Form_Unload(Cancel As Integer) If fcanIclose = False Then Cancel = True MsgBox "You cannot close using tis button.You...

www.access-programmers.co.uk

 

[GPGeorge]

The user does not need to but they still can click the link table wizard.

This what I'm try to prevent. I want to disable the link table/external data button.

In addition to creating an accde and other steps, why not create a custom ribbon that doesn't include those functions you don't want available?

 

[Pat Hartman]

The user does not need to but they still can click the link table wizard.

You need to lock down your app a little better to prevent the user from getting to the link table wizard.

Also, if the user doesn't know his password to the server, he can't open an empty database and just link from there.

There is no way for you to shut the user down from getting to the link table manager if he opens a new database. your IT people might be able to but that might cause you a problem also.

 

[raziel3]

why not create a custom ribbon that doesn't include those functions you don't want available?

Of course I didn't even think of that . Thanks @GPGeorge

This also helped.

https://www.access-programmers.co.uk/forums/threads/change-custom-ribbon-with-password.295785/

 

[Pat Hartman]

As I said, locking down your application, while good practice, will not actually solve the problem if you are worried that the users are smart enough to create a new, empty Access app and just try to link to the SQL Server tables.

 

[raziel3]

As I said, locking down your application, while good practice, will not actually solve the problem if you are worried that the users are smart enough to create a new, empty Access app and just try to link to the SQL Server tables.

Yes, you made a point that I didn't even consider. Someone can open up a blank access file and try to link to the server. How do you normally deal with that?

That is what I was thinking in the op. Can I hide all system tables on the server side and have only dbo.*** available to view when linking weather it be Access, Excel, Oracle etc.

 

[GPGeorge]

Yes, you made a point that I didn't even consider. Someone can open up a blank access file and try to link to the server. How do you normally deal with that?

You could break their fingers so they can't use a keyboard.

Seriously, the problem of users "exploring" on their own is one that really can't be entirely addressed from the developer side. It's partially a training and HR problem as well. If you work in an organization where people are encouraged and incentivized to act responsibly, you're less likely to have problems related to undisciplined exploration.

If, on the other hand, you know that rogue actors are going to try to exploit loopholes and get to data they should not see, Access is probably not the most appropriate tool.

 

[Pat Hartman]

Can I hide all system tables on the server side and have only dbo.***

What is your obsession with hiding the system tables? The users can't do any damage with those. They are read only. You should be more afraid of your "too smart for their own good" users attempting to link to the application tables and do something "clever" with them.

One way to handle this is to not use Windows authentication but instead to create a separate account for each user. Use the user's own login ID but use a different password. You can use code to generate a password that you can reconstruct based on user info plus some known characters. Then, the app logs on when the user opens the app and uses the reconstructed password. That means that if the user tries to help himself to the server via a new Access app, he doesn't have the password he needs.

I don't recommend a single login because that obscures the network traffic. The DBA would never be able to track what is going on by user if all the traffic has the same ID.

This doesn't solve your problem with the application though since you logged in to the server with the read/write credentials. You have to do a better job of locking down the app. You also need to make it clear that unauthorized access to the server is a firing offense. No second chances because it can't happen accidentally. Management has to make that clear and get behind the concept.

If you go with the one password that only you and the DBA know, you need to arrange a schedule where you change the password frequently.

 

[raziel3]

What is your obsession with hiding the system tables? The users can't do any damage with those.

Well one time, I linked to a clocking machine system table and changed my credentials to admin and I was able to make changes to the clock in/out data.

The IT Department controls were sub-par at best.

 

[Pat Hartman]

The IT Department were morons.

Some people can't be helped. It isn't your job to save them but you should have been fired for even trying to change company data. Why was SSMS or even the full version of Access installed on a machine not used as a workstation?

 

[raziel3]

This was done with Access, and even though I signaled to them to secure the database properly, they didn't even try. And I'm not even a db specialist. I'm mediocre at best.

That's my obsession with locking down my db frontend.

 

[KitaYama]

One way to handle this is to not use Windows authentication but instead to create a separate account for each user. Use the user's own login ID but use a different password. You can use code to generate a password that you can reconstruct based on user info plus some known characters. Then, the app logs on when the user opens the app and uses the reconstructed password.

A question if you don’t mind.
I assume after reconstruction of the password, you should relink the tables too. Am I correct?

If yes, then the password can be extracted from An external database by reading the connect string of linked tables. Isn’t that so?

Thanks.