ODBC - Security

[Adrianna]

I'm using Access to run VBA scripts in order to generate reports for my coworkers. I've been doing this all from my desk, but would like to be able to create a switchboard to allow them to create their own form driven queries and push button reports.
The issue is that I'm actually using an MS SQL 2000 (being converted to SQL 2005) database, so I require an ODBC in order to run my scripts through access. In order for coworkers to utilize the switchboard, they also need the ODBC.....which leads me to lack of security for my data. Although I can create logins and restrictions in Access, the mere fact that the have the ODBC gives them direct access to my repository.
Does anyone know of a way to get around this?

 

[FoFa]

Not true. Sql Server security can limit that.
First they need a DB login to SqlServer. This can be setup on either an individual basis, or via NT groups.
Create views to limit data by login.

 

[Adrianna]

Limiting access based on login is not an issue. The issue is when you create and ODBC, user's can develop their own interface to the ODBC, allowing them access to EVERYTHING.
I believe that I might be able to acheive the security I desire through the use of an ADP file instead of creating a shared MDB. I'm going to test it out, but if anyone has some insight, please let me know!

 

[SQL_Hell]

what login are your users using to set up their ODBC connections?

 

[Adrianna]

The ODBC would be set up with my user name and password and the adp would be set up the same. Either way I need to set up access for the user's to the SQL databse and the only way that I know how to do that is by either creating as Access database using an adp file or an mdb with an ODBC connection. The real issue is that I can't allow the user's direct access to the tables so I wanted to create a switchboard for them with access to the options that they actually need to have. Allow me to give them reports and forms accessing only the fields that they are authorized to access and modify.

 

[Adrianna]

FoFa
What utility would I use to establish these accounts on the SQL server? We're using MicroSoft Active Directory, so I would assume that I could still add users into a user group. Do you have any reference links on this?
A

 

[FoFa]

If the users do not know your userid/password, they will not be able to access the SQL Server. Of course if you setup an ODBC connection and store the password, they could use that to access the information.
BUT you could NOT store the password, and supply it in your CODE instead, and make sure they can't get to that.

 

[Adrianna]

FoFa,
OKay...I'm missing the obvious. I see where I can establish user accounts. I do need to make a group account for the office. Then I should be able to attach the ODBC using that userid.....etc
Not sure why I didn't think about that before. I guess now I have to determine....if they're a user of the database.....do they need to be a user on the server itiself as well?
A

Thanks!

 

[FoFa]

A SQL User does NOT need to be a user on the server itself.
You can even go as far as to setup NT Groups, and give that/those groups access to the SQL server, and you can control security via NT that way (using windows seucrity). You can setup DB roles in SQL, and assign users/groups to those roles. You may only give one role SELECT access only (read only), etc. Even if they did connect via ODBC and Excel (as an example) they are still limited to the security on the SQL server.

 

[Adrianna]

You're wonderful

THANKS.....setting that up as we speak! We're only using MS Access for our internal office reporting because it's easier to format things into other MS Office product then to publish them to a web page and then transfar them back down to a spreadsheet or other format.
THANK YOU!

 

[FoFa]

You could do the same thing using SQL servers DTS, create spreadsheets, word documents, files, etc. You can automate that to run say at night, have the fresh documents ready each day.