Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
abissus
Starting Member
3 Posts |
 Posted - 2004-08-02 : 14:28:51
|
| Common SQL Server security procedures suggest that you should use Windows Authentication mode for having users log in. It also suggests you should limit access to the tables through views and stored procedures. Now considering that both of these are in place, how do you get around the problem where Access lets you create a new ADP/ADE based off existing data? You can hide the database window in an ADP/ADE that you create, but if the user can get the server and database name (from the existing ADP/ADE file), they can log in using windows authentication and see all the stored procedures that you have granted them execute permissions too. Of course you can use the WITH ENCRYPTION option to hide the code, but how do you protect against malicious users who may poke around in the stored procedures? Are there any ways to keep Access from listing all the Stored Procedures or at least executing them from an unauthorized ADP/ADE file? |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-08-02 : 16:46:16
|
| When I create a ADE I go to the startup and de-select all the check boxes this will prevent them from seeing or re createing the connection.As an Added precaution I do not give full versions of access to users that do not need them. (runtime only besides its free) JimUsers <> Logic |
 |
|
|
abissus
Starting Member
3 Posts |
Posted - 2004-08-02 : 17:36:47
|
| Jim,I assume you are talking about the Startup options to disallow full menu/toolbars and hide the database window? This is a good security precaution and I have done this.My concern is the creation of a new database. When you go to create a new database in Access you are allowed to create an ADP with existing data at which point you are prompted for SQL Server connection info. The only security you have at this point (with a windows authenticated user) is that the user doesn't know the server name and database. However, both of these are attainable by looking at the current application ADE file in a text or hex editor. Granted, it will take a sophisticated user to figure this out, but its still there and a DBs security shouldn’t have to depend on keeping the server name and database hidden.I can hide and lock down almost everything except those stored procedures. Is there not some nice switch somewhere that I can flip to hide these from all but users of the db_ddladmin role? It would save me the trouble of having to encrypt all of them too. |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-08-03 : 07:53:23
|
| Access Runtime does not permit creation of an ADP/ADE.For users with full MS Access Sometimes I create a new database with no tables only the nessisary functions and views for a given department. This way a static backup is easy to restore if someone gets into it and screws it up.Proper table layouts and relationships in the base DB and imposed table security should take care of the rest. ENCRYPTION is a two edged sword it limits you as well as them.As an added precaution I also track critical data alterations in a seperate database updated by triggers when/who.If they manage to get around all this make them your assistant. LOLJimUsers <> Logic |
|
|
|
|
|
|
SQL Server security hole through Access ADP?