DB Security Vs. Application

Feb 3, 2004

We are rolling out mssqlxpress to our development staff and would prefer to alter their permissions so that they can only make changes using mssqlxpress (since it integrates nicely with VSS).

So are choices are to uninstall SQL Enterprise Manager and SQL Query Analyzer, or more preferably, alter their NT Permissions so that they do not have the capability to change anything in the DB's.

My concern is that it seems we can only set their permissions from DB level and not the application, does that sound correct? Has anyone else had to deal with this predicament and is there an alternative to uninstalling the SQL Client tools?

View 1 Replies

Security- Application Roles

Jul 17, 2000

We are looking to make our applications as secure as possible. I am interested in how well Application Roles work to make security tighter.

Have you used Application Roles. If you have, I'd like to know if it helped provide better security or not and if it did, how was it implmented in you production environment. I already know how to get it set up, I'm just wondering if it's really worth the trouble.

Thanks!

View 2 Replies View Related

Designing Security In An Application

Apr 24, 2008

With my next application, I'm thinking about establishing a new security paradigm for my programs, with respect to SQL Server.

In all my previous applications, connections are established using SQL Server authentication. So, all my users may log-in under a single log-in, mapping to a single user in a database. My application then has to use its own security arrangement to determine who has access to what forms within the application. There are three major problems with this design: 1) the user name and password to connect to the SQL server have to be resident as strings somewhere in the application code (or ancillary files), 2) everyone connecting to the SQL Server "looks" the same, and 3) you have to give the broadest rights to everyone with the same login and then pare those rights down within the application itself.

By implementing multiple SQL Server Authentication logins I can mitigate problems 2) and 3), but the only way to eliminate problem 1) is to move to Windows Authentication.

Windows Authentication would allow me to resolve all three design constraints, but there is one problem that I see coming as a result.

If I use Windows Authentication, each user must have an independent login to the SQL Server. If I have an application that may have 4,000 to 6,000 users, does that means I have to have 4,000 to 6,000 logins set up on SQL Server?

Is that true?

I could, of course, generate a script to build all 4,000 to 6,000 users, but I am concerned about this.

Is this a "normal" arrangement that SQL Server has no problem-with?

View 8 Replies View Related

Yet Another Question On Application Security....

Sep 29, 2006

Ok, i have read a few articles regarding application security and it sems to me the Microsoft just doesn't get it.

As others have posted, Vendor packages are not normally controlled by the IT staff... they do what they do. When an application is used for mission critical data, it becomes important to lock down that data as best as it can be under the limits of that application. Yet what is often lacking in a vendor package is thought about what opening up the database means.

We are using a Vendor package that does not allow for application security to be set. In other words, it uses windows authentication to grant Database access. As many others here have noted, this opens the database to manipulation via any other tool the user has at their disposal.

It seems that the general reply here is that we should get the vendor to change their package to allow for Application level security. Good Luck!

I am not a DBA (must be obvious at this point) and i do not know all the what's up and coming, but generally as a system support person, i must deal within the environment i am given. Here's what I see as the environment I am working under, the problem and the preferred solution.

Application only allows windows authentication.

The users must be allowed full access to the database in order for the application to perform updates as needed.

Security to specific data by company is controlled within the application to determine what a specific user can change.

Granting security to a user ID allows them to access and change data at will through an alternate product (E.G. Enterprise manager, MS Access etc.) without regards to the security built in to the application.

SQL Server knows what ID is requesting data and knows what application is generating the request (I have seen screen prints of the list of who is logged into the database and right alongside the UserID is the Apllication Name).

If SQL server knows these two bits of info, why is it so difficult to allow SQL server to append the Application name onto the user ID and allow access to the DB only via this combination? The permissions can be set up with some combination of UserID/Application name and no special security, no messing with special application passwords/aliases, no pleading with Vendors to make changes and best of all.... satisfying SEC rules for locking down DBs.

Am i missing something? Does anyone know how i can accomplish this simple task without stressing out the DBAs?

Thanks for all your help and creative ideas!

Rob

View 21 Replies View Related

Security For Application Role

Sep 4, 2007

We have an application use Approle to read from database. If the client login to windows as administrator or a name that has the administrator rights, the application can get all data. If the client login to windows as a domain user that has limited rights, the application can't get all data.
I run profiler and found that it seems, when application use approle to access a database, the login name is the domain user that log into windows. Is there anybody know what type of right the window login name should have in order to get all data from a database?

Second question, when I log in to window as domainusername( username is not administrator, but has administrator rights). In the profiler, I can see the application use this domainusername access database. However, under sql server login node, I didn't find domainusername. Is this because, the domainusername belongs to buildinadministrator?

Thanks