Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.
PERFORM APPLICATION’S USER PRIVILEGES USING WINDOWS AUTHENTICATION FEATURE IN MICROSOFT SQL SERVER 2000

The information in this article can only be applied in a system that uses Windows 2000 platform as operating system (Windows 2000/2003 Server, Windows 2000 Prof, and Windows XP Prof) and Microsoft SQL Server as backend database server.

ABSTRACT
Security is a major issue in designing an application and some people have their own user privileges database and apply an encryption method. But here, I will explain how to retrieve user information from Active Directory Users Database or Computer Management on Win2000 platform operating systems instead establishing your own user database. I choose the MSSQL as backend database server here because it has windows user authentication feature that use Microsoft Windows 2000 (W2K) OS user information, and MSDE is bundled with VFP. The windows authentication feature enabling your front end application fetch the W2K user/group information easily, no API programming would be needed for that.


WHY SHOULD WE USE WINDOWS USER PRIVILEGES INFORMATION
- To minimize user login name and password entry. Some people would like to have their own user database to store the information about user privileges and password. Actually, that is fine. You can still store the encrypted user privileges data in your database. However, user will have to reenter his login name and password each time your application is started. By retrieving W2K user privileges, you don’t have to prompt a login name and password each time your application is started.
- User doesn’t have to memorize different passwords for their W2K password and your application password. If they choose to use the same password, that’s mean we help them by directly use their information in W2K user authentication.
- It is secure enough and you don’t have to be bothered by developing encryption storage system for user information.
- It has capability to categorize your user into user-defined group. This feature will be needed to implement a good user privileges restriction system (discussed in the next section).

USER VS GROUP
You can restricted a user based on two information, first is from user login name, the second is from which group is the user. For you that still using your own user privileges database, you still have to consider about using group instead of login name.
It would be a little hard and crowded to be managed if you are restricting a user based from the login name. It’s ok if you have about 5-10 users. But if it turns into 20 or more users, it will be hard to documenting and managing the privileges data. Besides, you will have to restrict the application menu based on login name, and when the user changes his login name, you will also have to change the data and module restriction configuration.
Group is very good to be used in user privileges restriction. You can even define the user privileges in your application statically. For examples, I define in my application that General Ledger module can be opened only by users which is a member of ‘Accounting’ group. When the job position of a user (user A) replaced by another person (user B), we don’t need to edit our code and recompile the application. All we have to do is to open the W2K User Management console (W2KUM) which is Active Directory Users and Computers (for network that use active directory) or Computer Management (for network that do not use active directory), both can be accessed from Start Menu – Program - Administrative Tools. Then delete the user A, add user B and define B as a member of ‘Accounting’. And remember, a user can have more than one group. So some people can have more than one privilege type if needed. This will surely make the privileges management easier. You can make a lot of groups with unique privileges, and then manage the user by assigning them into the proper group. A member of accounting department that is allowed to open the ledger and in the same time allowed to entry purchasing should be a member of both ‘Accounting’ and ‘Purchasing’.

INSERTING W2K USERS/GROUPS INTO MSSQL DATABASES
MSSQL has a built in user table in each database you created and it is related to the user database of your W2K operating system. This section will guide you inserting user or group from W2K into ‘Northwind’ database which included in your standard MSSQL installation.
Before you proceed, make sure you have created desired group/user for your application in W2KUM. Remember that for network without domain, your dummy ‘domain’ name that will be used is the computer/server name. Follow these steps:

1: Open your Enterprise Manager, expand your server, expand ‘Security’ folder, choose ‘Logins’, right click the right section white area, choose ‘New Login’ menu.



2: New window will be showed up, enter your group with your domain (or computer) name or you can browse it using browse command button. Do not bother to modify the other info, let them be default.




3: Choose ‘Database Access’ page frame, check your database (in this case is Northwind).



4: Back to ‘General’ page frame, click ‘Ok’ button. And now expand your database, click ‘User’ section, check whether your group has been put inside your database.



FETCH USER NAME/GROUPS FROM MSSQL
After all of your user names/groups have been inserted into your MSSQL Database, you can now fetch the information using SQL Pass Through command. The user database is stored in a table called ‘sysusers’. You can issue

SELECT name FROM sysusers WHERE isntname=1

to fetch all user names/groups that has been inserted into the Northwind database. Use ‘WHERE isntgroup=1’ to filter the data into user groups only, or use ‘WHERE isntuser=1’ to filter the data into user names only. For the next experiment, add your user you are currently logon, into some groups using W2KUM and try to run the code below from your VFP.

LOCAL cDomain
cDomain= ALLTRIM(GETENV("UserDomain"))
SQLEXEC(SqlHandle,[SELECT name FROM sysusers WHERE is_member(?cDomain+"\"+name)=1])

BINGO, you will now get the user name/groups of user you are currently logon into, in a cursor. Try to change the current user groups from W2kUM, logoff and re-logon, run your program again and see the result. Now you can always refer to this cursor to check the privileges for current user. It’s easy, without encryption, and is secure enough.

Data Restrictions in MSSQL
Module restrictions should be implemented in your application program code. However, the data restrictions should be defined in your MSSQL Database. In that way, unwanted user can not access the data using another application either. Each object (tables, user-defined functions, stored procedures, etc) in MSSQL can be restricted based on your W2K users/groups. And you can define it uniquely for each SQL command; SELECT, INSERT, UPDATE, DELETE and EXEC. Check the table permissions properties for these purposes.

ABOUT THE AUTHOR: FOXHOUND

Foxhound Puji Hasana Wijaya (AKA Foxhound) wrote his first database application at the age of 11. When he was 14 years old, he wrote his first virus using assembly language. He graduated in 1998. Nowadays, Puji owns an IT Consultant Company which established with his partner in Singapore, but they also operate in Indonesia. They build most of their applications (clients are government, hospitals, manufacturers, trading & services companies) in VFP and MSSQL Server. If he's not writing software, he's playing games or making Jazz music.

FEEDBACK

puwantoro @ 8/28/2008 7:43:06 AM
how to running application using window authentification ??
i mean...if user not login to windows password ......user cannot running that application with or without username and password application

thank's before

Foxhound @ 8/28/2008 5:27:31 PM
@Purwantoro

If u r using SQL Server, the connection between ur front end application and the SQL Server back end would never be established with Trusted_Connection as long as your user groups/privileges were not enlisted in the SQL Server security

So:
1. Make sure u r using SQL Server trusted connection
2. Enlist Windows Active Directory groups u want to be trusted in SQL Server security
3. Assign your application user in one of more groups that u had enlisted in SQL Server security

purwantoro @ 8/29/2008 3:15:21 PM
how can i do that .......pls give step by step configuration Or send me email to wong_cari@yahoo.com

thank's before

Foxhound @ 8/29/2008 6:01:55 PM
...by exactly following the instruction in this article?

Foxhound @ 8/29/2008 6:03:48 PM
@Purwantoro
post question at the forum, email me the link of your thread to my email, I will reply asap

WWW.RE:DOMAIn.pedro-Gonzalez.net @ 2/10/2014 7:22:56 PM
opp app org-biz net more mail out to me seriver making money make up much more than a dollar thank you thank pedro jgonzalez

PEDRO J GONZALEZ @ 11/28/2014 3:29:41 AM
foxtice via PRO tech itserivers runing opp app id s top line call about it

PEDRO J GONZAlEZ LTD the BOSS @ 2/23/2015 9:39:53 PM
USA LTD POB #380160 bklyn NY 11238 MRG loans 450000.000 running bussiness pob mailout to me MR G LTD FOX HOund

MR ltd as GONZALEZ DAILY NEW FAMILY PETE LATION BLACK USA @ 12/29/2015 10:36:48 PM
OPPERATION SOFWARE FOXTICE TPRO

PEDRO j GONZALEZ @ 8/4/2016 11:29:37 PM
TECH commcation as boss for bussiness app t moble .com

MR GONZALEZ @ 8/22/2016 5:10:05 AM
MAIL OUT TO ME POB 380160 BKLYN NY 11238 IT MANAGERs serivers oit seriver for bussiness.
9292042380

HI MR GONZALEZ @ 5/24/2017 1:08:55 AM
LOKING TO MAKING SOME MONEy PROGRAM hackers operations CEIl phone systeam MAIL OUT US POB 380160 BKLYN NY 11238 MR G



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: