July 31, 2002 at 2:32 pm
Hi. We have VB 6.0 developers writing applications and in these applications they want to check the users security to see what type of security they have to the table they are working on. I have 2 questions concerning this: 1)Can someone tell me what sql tables the program would check and how this is done. Keep in mind we use NT authentication with users being grouped together in NT groups. So I don't have individual logins defined in sql security. I will only have an NT group defined and 2) what if a user in 1 database has allforms of security on a table and that SAME user in ANOTHER database needs different security on the SAME table . How is this accomplished?
And I guess I'll throw this in since we are new to sql server and VB.. If a vb application can access the correct sql security tables to verify a users security, is there a need to have ANOTHER security routine written incorporated into the vb application to go on top of the already existing sql security?
Thank you in advance for ANYTHING in this area.
July 31, 2002 at 6:39 pm
Generally...very generally...you grant access to all the tables needed by the application, then the application controls who can change what and how. This way you can enforce additional business logic and gate access at the same time. Sometimes it will be as simple as looking at the NT login of the user running the app, determine if they are a "manager" or "admin" or whatever, and then perhaps show an extra tab, allow access to additional menu options, etc.
In some cases you may need to build your own security scheme on top of SQL if you need to do things like control access to individual rows.
Sorry for the slow reply, has been a busy time for us! Please post more questions if you have them and if you don't get a reply within a couple days, just post a reminder note asking if anyone has ideas.
Andy
August 1, 2002 at 11:45 am
I agree with Andy but on several recent projects, I have allowed access to the database only via stored procedures and then set access security on these. If you do want to use application security, I have a neat dll which is can be hosted in MTS ( or COM+) on the server and will verify/authenticate the passed username/password and also return what groups the user belongs to.
August 1, 2002 at 1:05 pm
HI. I would very much be interested in the dll. i have a feeling the developers are going to want to put application security.
And one of my questions was how to take a userid of the person logged on and finding out what nt group he/she belongs to..
August 1, 2002 at 5:35 pm
I'd like to see the dll as well.
Andy
August 1, 2002 at 7:12 pm
Not a problem. I will send the source and you can change as you like. This code came from an article by L.J. Johnson in his "Ask the Windows NT Pro' column and you should be able to find it on DevX. Search for "Confirming Users". His example included a public enumeration which mapped to user groups but I modified the code to just return a variant array of group membership. Right now, this is setup to run on a standalone NT box, therefore, I am not passing a domain name but have the server name hardcoded. There is a private function in the source which can be used to query the PDC name if you like. Also I have not implemented any form of encryption for the username/password data and for the dll to run properly, it should run under its own user account which has "Act as part of the Operating System" and "Log on as Batch Job" rights. This last bit may be a problem with network administrators but for my purposes was not a problem. Juanita, if you can give me some way to contact you, I will send it your way as well. The dll is very simple and only includes two public functions, one for authenticating and one to return user groups, but these could be combined into one function easily. Enjoy...
August 1, 2002 at 7:19 pm
Juanita... I sent the source via the email address in your user profile. If this is OK, you should get it soon, otherwise give me an alternative address.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply