ODBC Security

[lostexchange]

Hi, I need help from a SQL guru. We have a very important program that runs on a sql 2000 sp3 server. The application front end connects via ODBC to SQL. We have 4 areas that log into the same database and access different parts of the database for thier area. The auth is via SQL. Our problem is now we are are looking at running reports and we notice that via excel, crystal etc we can connect via odbc and see everything (and update anything) in the database no matter who we are logged in as. So for security reasons and data protection this is not good. There is sensitive data in there. We need people to only be able to see the area of the database that they can see while in the application. I am not sure I am making sense - I am not a sql guru. Is there a tool I can use that will limit what users can see via odbc when running reports.
Thanks for any help you can offer!

 

[ecobb]

You should be able to set your security permissions on the SQL server. For each account that accesses the database you can allow/deny what you want them to have access to.

 

[lostexchange]

We cannot do this as some of the tables are common to all areas but the data in the tables should only be visable to the area that entered it. Plus there are over 520 tables. And if they cannot preform a Select, Deleted etc query then they cannot use the application. I feel there isnt a solution except to seperate the 1 database into 4 databases - What a pain!

 

[ecobb]

You can drill down and get very detailed in your permission settings. You can specify wich tables they can and can't select/delete from on a per user/per table basis (User 1 can delete from table A but is denied delete rights with table B). Although, with 520 tables, that could turn into a nightmare.

 

[Corran007]

I would consider using roles to designate who can see what. this would allow you to set it all up for one "user", the role. Then you can assign users to that role. You may have to create specific accounts for each of those roles to access that you will use odbc with.

IT will be a pain on initial setup, but after its done, you can easly modify it.

 

[rachelgeoghegan]

I am having the same problem and alththough the security options work when accessing the SQL Server 2000 database from the software application (written in VB6) or from EM they do not work from external office applications.

For example I have created a user defined database role which only allows access to a no. of crucial stored proceedures & read only access to 1 table in the database - the idea being that anyone who uses the software must be a member of that role otherwise they can't even log on.

This works perfectly until you discover that when you take the 'Get external data' option in Ms Excel or Import Data in MS Access you can import data from tables in the database that the user doesn't even have read-only access to. I have even denied access to these tables and still the same.

There has to be a workaround to this as it is a huge breach of security.

Any comments/ ideas would be most appreciated.

A frustrated Irish developer!!!....