QOD 9 Dec 03 - Users
-
I have built necessary logic in to my application to have a separate User Table for the application. I have only one user to log in to SQL server and the individual users have separate User Name in the application. The SQL-server-user-name is hard-coded in the application, and on log-in the Application-User-Name is entered by the users. The password is tested against what is there in the table and permissios are verified for each form and report from another table.
Any body have any comments on this ? Will this affect the performance of SQL-Server.
-
Not adversly. The answer to the question is technically right from a pure SQL point of view, but not from an application point of view. You're very rarely going to want to use SQL authentication for each user in an application, generally your're going to use some sort of connection string that has all the rights the app needs and then configure security for the app yourself. In most applications you have a ton of biz-logic stuff that SQL can't do anyway, so you need access to only be possible through the app.
+ anyone ever heard of any sales guy ever using Query Analyser? It would be like an amoeba driving a car!
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
<
+ anyone ever heard of any sales guy ever using Query Analyser? It would be like an amoeba driving a car!
>
I've worked at places where they've let trained (or untrained sometimes) end users use QA or tools like QA (depending on the backend DB) directly access the database for reporting....
--woody
C. Woody Butler, MCP
C. Woody Butler, MCP
cwbutler@cwoodybutler.com -
We have a very distributed app (individual companies hit a shared database hosted in our office - the database is a trading platform for work). We actually DO have individual logins in SQL for each "user". The logins are automatically created by an admin app we wrote as companies add subscriptions or (sadly) quit paying each month. So we do exactly as the question asks, and give each company a view of the data based on their login. Since we control the logins, we configure them such that the first 3 letters of each one is unique and in some cases the Stored Procedures filters the data based on the login without the need for a view. No user has direct table access. It works well for us and the performance seems fine with over a hundred users accessing the SQL server with a VB6 app using ADO over the internet. It's about 15 Gig with 155 users now and has been working well for 2 years.
What kinds of things make you say that individual logins are bad? Just the admin overhead of having to setup individual logins and passwords?
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither -
Can someone first please explain this row-level security concept and how it works. Is it an auto-filter that is controlled by some system stored procedure?
Thanks,
Johnny
-
I hope Yukon overcomes some of the weaknesses of SQL Server. Since I work on both Oracle and SQL Server, my initial response before reading the questions was an Oracle solution that would assign permissions on login.
My .02 dollars on this solution....To enforce row level security I would create schema level views for each Department Manager, or one view that would have a condition that checks the current user name against a Department Manager -> Department ID lookup table.
"Keep Your Stick On the Ice" ..Red Green -
quote:
I've worked at places where they've let trained (or untrained sometimes) end users use QA or tools like QA (depending on the backend DB) directly access the database for reporting....scary! Our sales guys phone the help desk to read their mail out to them
quote:
Can someone first please explain this row-level security concept and how it works. Is it an auto-filter that is controlled by some system stored procedure?It doesn't. SQL server has no row-level security. Instead you deny the table and grant a view which only sees particular rows. Of course if the field the query looks at changes in the underlying table the row will appear or dissappear, which makes this method more than a bit pants.
As a rule always control row access in the application. Then rules in the application can alter when to changes row availiability.
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
This is something our organization needs to develop a strategy around, and I'm a bit confused. Most of our front end apps are web based, and pass parameters to stored procedures. It seems to me that the easiest way for us to implement row level filtering is to pass the user id as a parameter, and have the stored procedure look up their permissions on a user table and add appropriate filters. Is there a better approach I just don't know about. Is the row level security referred to in this QOD anything more than creating a parameterized view? Does anyone know of a good write up of this topic I could read?
Thanks for any guidence you might provide.
-
At my former company, we had an Application Service Provider-style time and billing app. We designed that so that the application itself relied on the appropriate views to determine what users saw what data. Given the potentially sensitive nature of the data (at one point, I think a partner of ours on some project was using this as, as well as us), limiting access was critical.
R David Francis
R David Francis -
Shoot the database designer!
These questions just get worse over time. They mention, "Districts" and then have "DepartmentID". Nice.
Furthermore, it is clunky and silly to implement database row level security, even in views for just such a thing. We do this continually through the application level, where it makes more sense and is ten times easier to maintain and handle.
Furthermore, we never deal with users; we deal with GROUPS. Add another column to that table for a group assignment and you're ten times better off. Then you aren't assigning them to a district, but to a security grouping that you can use for other things. For instance what happens when you have a regional that wishes to straddle districts? Hmmm.
In many of our apps we have tables that assign appropriate security settings to the users - sometimes by module, etc. The applications know of the table and define what they can do from that entry. Of course, the default is to allow nothing, the scorched earth approach.
Many older applications simply let the user log in with their SQL ID (one level of security), the figured out their level of security from a security table (i.e. module, form, etc.) and performed all functions by proxy as a higher level user, but constrained by the security settings.
-
We use either views or stored procedures to solve this problem. In either case the end user has no direct access to the table. The filtering is done by the view or stored procedure. It is correct that this filtering should be based on security groups and not on individual usernames or logins.
Another option is that the security (in casu the row filtering) resides in the app and the app uses an application role. Again this should be base on groupmembership.
An other option is to build a three-tier application that uses its own account to login to the SQL Server and in this case again the filtering is done by the application.
In all cases the table has a column that accomodates for the filtering.
In all cases the end users have no direct access to the tables.
-
quote:
Is there a better approach I just don't know about.Develop the "business logic" (i.e. these users can only see their data) in a middle-tier (preferably COM component or .net service), and pass userid to the methods in the middle-tier DLL.
As a general rule, treat client application programs (whether windows apps or ASP pages) as presentation layer, and only have the logic and code to present and accept data, have the "guts" of the application in middle-tier components, and only the middle-tier can access the database. This secures the database to only the application with single logon, lightens the client side code to just presentation logic, and controls both application access and database access in the middle tier with its security, pooling, component sharing capabilities. This is scalable, extremely flexible, better performance, able to take advantage of clustering at each level.
Mark -
quote:
quote:
Is there a better approach I just don't know about.Develop the "business logic" (i.e. these users can only see their data) in a middle-tier (preferably COM component or .net service), and pass userid to the methods in the middle-tier DLL. [snip]
Thanks. Hadn't thought of that approach. I do wonder about continuing to develop in a COM/DLL framework for an application we are just starting to deliver, and may investigate .NET equivalents. But the organizational logic of having a separate tier managing security issues makes sense regardless. I appreciate your taking the time to reply.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply