August 29, 2005 at 4:49 am
I currently have over 100 customers using a software package - vb front and SQL Server 2000 back. The databases at the moment have no security on them - the only think stopping my customers from looking (or destroying) the data is their lack of knowledge. Some of my customers have discovered linking SQL data to Access Databases or importing data to Excel.
I don't have a problem with customers having read only permisions via odbc to excel or access to most of the tables, however there are a few tables where I don't want anyone to be able to read/write apart from withing my app.
I was wondering if the Application Role was the way to go. And if this is the way do I remove all permissiond on the database and only allow access via this role?
I could do with a crash course in MSSQL Server Security - anyone know of any good articles that could help?
Regards
Martin.
August 30, 2005 at 4:15 am
You could use an application role, but even with that, if they know the sa password, they could use that to connect ot the database adn do whatever they want. Does your app install SQL Server or are these existing SQL Server installs? Who controls the security on the Sql Server Box? Is there even an sa password on the SQL Servers?
Best practices are to only allow dbo's to access the base tables and allow users read access to views and to use stored procedures to access and modifiy data and only give application users execute access to the sp's. Then you control how data is modified within the sp's.
Here is a good article on security on this site: http://www.sqlservercentral.com/columnists/bknight/10securingyoursqlserver.asp
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 30, 2005 at 7:36 am
80% of the time we will install MSDE to run our app, however the other 20% will have SQL Server already installed.
Would installing a new instance be an option. Would that give us full control over the passwords etc ?
August 30, 2005 at 8:55 am
If they allow you to install a new instance that only you have control over that would work, but who is then responsible for performance monitoring, backup and reocery, etc... If you were installing the app at my location I would have to have sa access to the server.
By default SQL Server has no password for sa, so as part of the install you need to specify this password or change it after the install. Whenever I deal with an MSDE app I immediately try to login as sa with a blank password.
If you use views and stored procedures and do not grant access to the base tables you should be all set. If you have to use ad hoc/dynamic sql you should have an app role that is the only thing granted rights to the base tables. The first thing to check is that the Public role is not granted rights on base tables. IT sounds as though you have given Public rights to all the base tables, or in the places using Access and Excel to query the tables they know enough about SQL Server to give themselves the rights and if they have the sa password or sa privileges you can't stop them.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 30, 2005 at 9:44 am
I suggest using an application role for the vb apps...this allows the users to only access the db via the application. I'd revoke direct access to all objects except those objects that the application needs...SPs, views, FNs...these objects would then be granted to the app role only. Users would then be restricted from pulling up the data in MS Access. Users connecting to the db via MS Access can cause a performance hit.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply