June 29, 2010 at 1:25 am
I am making an app in VB 2008 with its db in SQL Server 2005 Express.
In each table in the db, I have a column called user_id.
I normally update this column from vb using the logged in user's id.
I would like to know if it is possible for SQL Server to do the same by way of an update trigger WITHOUT using a parameter for the user id, i.e. without sending the user id from VB, but rather getting the user id value from within SQL Server?
What internal variable of SQL Server can be used to identify the logged in user of each session?
June 29, 2010 at 1:31 am
ORIGINAL_LOGIN() should be what you're after, but be careful: it could be different from what you expect, depending on how you authenticate the users in your application.
-- Gianluca Sartori
June 29, 2010 at 1:37 am
June 29, 2010 at 1:41 am
It does, if each user authenticates with his own credentials on the database.
You could also have different scenarios, with an application that requires windows authentication fo the users but doesn't propagate these credentials on the database connection (for instance uses a fixed SQL Server login). If this is your case, ORIGINAL_LOGIN() will always return that fixed SQL login.
-- Gianluca Sartori
June 29, 2010 at 1:49 am
Gianluca Sartori (6/29/2010)
It does, if each user authenticates with his own credentials on the database.You could also have different scenarios, with an application that requires windows authentication fo the users but doesn't propagate these credentials on the database connection (for instance uses a fixed SQL Server login). If this is your case, ORIGINAL_LOGIN() will always return that fixed SQL login.
Yes... or an application where users log in with different "application users" but the connection uses the same SQL user for all the different application users.
"Keep Trying"
June 29, 2010 at 1:53 am
ChiragNS (6/29/2010)
Yes... or an application where users log in with different "application users" but the connection uses the same SQL user for all the different application users.
That's what I was trying to say, but now that I read my post I see my wording was really poor...
-- Gianluca Sartori
June 29, 2010 at 2:17 am
Thanks to all those who replied.
The app is still under development, and the proposed authentication model is as follows:
1. Roles will be created in SQL Server, each with a set of rights/privileges.
2. Logins will be created in SQL Server for each user to access the db via the app.
3. SQL Logins will be associated with SQL roles, and to respective users' Windows logins.
4. Users will be created in the app, each with a set of access rights to the app.
5. I do not know of any mechanism to restrict the SQL logins to their respective user IDs; if there is I would love to know how.
6. The user_id to be saved to updated records is that of #4, and NOT the associated SQL login.
7. This can be done via VB code directly in CRUD statements or indirectly by passing a value to a parameterised SQL trigger.
Q1. Is it possible to save the associated SQL login in the Users table?
Q2. If it can be achieved by avoiding the parameter and the VB code, I will be grateful.
Regards,
June 29, 2010 at 12:44 pm
June 29, 2010 at 1:06 pm
If I understand you correctly you're wanting to have your app pass through credentials of accounts from users to the SQL Server.
I'll be honest ... I really don't like the setup. I think you're way way way better off having the VB app (this a Windows app or a web app?) access the SQL Server through a separate account that none of your users know anything about.
This is the problem: if your users have the actual SQL Server credentials, that means they can directly access whatever data you've given them permission to access without going through your application. Now, if it's read only access and you don't care that is one thing, but you're describing a process for them to update data which means they can log in to the server directly and delete everything. Not good.
SQL Server isn't really designed to be an end-user server. It's designed to serve data to a middle tier, which in turn (sometimes through multiple layers) serves data to end-users. The access control, business logic, etc. reside in that middle tier. So the users would have access to that, but not to the data itself.
The other thing I don't understand is why you're worried about not passing the user as a parameter. It's extremely trivial to do that from VB, so I'm wondering if I'm missing something in what you're trying to do.
June 29, 2010 at 2:24 pm
Thank you very much, bt. Yes, you are right and right!
I am very green at SQL Server Security best practice, although I understand logins, roles and authentication and so I am fishing for the best technique to implement.
Of course, I have no intention of giving users details of their underlying SQL Server logins, just wanted to handle it transparently.
So I agree with your suggestion, but then I have always wondered, isn't having a single separate account for all users counterproductive? Wouldn't it be better to have one login for each set of privileges?
As for updating the userid from VB, it's not a problem at all. It is just that, since I discovered this site 3 years ago, I have learnt many time-saving techniques to get the same thing done much faster and easier from SQL Server rather than VB. I just wanted to know if this could also be done.
So, I guess, that answers both my questions, that it is not adviseable and I better stick to the VB coding option.
Thank you.
June 29, 2010 at 3:17 pm
goodguy (6/29/2010)
Of course, I have no intention of giving users details of their underlying SQL Server logins, just wanted to handle it transparently.So I agree with your suggestion, but then I have always wondered, isn't having a single separate account for all users counterproductive? Wouldn't it be better to have one login for each set of privileges?
I believe you said earlier that you're in an environment that you can use Windows authentication, so there is a fairly straight-forward method available.
First off, keep in mind that the actual account used to access the data is simply to give the application the ability to do what it needs to do and that's all. The authentication of users against the application is a separate process - that's where your control is.
What I have done in the past is to store a table in my database (that my application has access to) that has the permission settings relevant to my application with a primary key of the network user ID. So, when a user opens your app, your app checks their network ID against that table (via a proc) to determine what they can and cannot do. So within that, your security structure can be as complex (with groups and rules and whatnot) or as simple as you need it.
With this system in place you can add processes to track whatever changes you need to track because whenever any data is being changed by the application, the application is aware of who that user is. Generally, I really like to be able to know who did what so I tend to add an auditing structure and process to anything that is user edit-able.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply