http://www.microsoft.com/TechNet/analpln/cs/sam_sql.htm (PC Press Internet CD, 03/1996)
| Updated: March 14,1996 |  Go To TechNet Home Page |
The Challenge: Effective Security Management
3M Austin Center's Application Development Group developed the Security Access Manager (SAM) to address some of these issues. SAM is designed to handle all security functions for Austin Center's SQL Server Applications. SAM provides 3M with application administration control from any network desktop. There is no need to visit the end user workstations to establish database connection information or install executables.
SAM limits the drain on IT resources by sharing administration between the IT department and end user administrators. IT maintains information common to all applications (that is, employees, servers) while an end user administrator maintains information about individual applications (that is, user access, version number, and availability status).
SAM supports Visual Basic and PowerBuilder applications. It will work with Microsoft SQL Server, OS/2, and Sybase databases. 3M Austin's current installation uses Microsoft NT SQL server and Sybase engines. Clients connect to SAM via Sockets* on a Microsoft Lan Manager 2.2 network. Over 150 users access SAM in Austin's phase one rollout. This number is expected to reach 600 or more as seats are added to the site IT network and additional client-server applications are developed.
Problems with Prior Administration Efforts
Austin Center is the home of 3M's Electro and Communications Systems Group. The recent centralization of application development and database administration efforts there triggered the development of SAM.
Prior to SAM's implementation, 3M faced the traditional problems of client-server security administration. Application administration and security access were manually intensive processes. Application developers were responsible for their own applications and databases, which led to the following problems:
A team of four developers set out to eliminate security administration efforts within the IT application development group. SAM functionality was mapped out in about two hours one Saturday afternoon. A single developer was able to implement the prototype within a week. It took less than three months for that same developer to bring SAM into production.
The heart of SAM is its security database that maintains information about applications, servers, and users. The SAM database contains the following six tables:
Administrator
Identifies all application administrators.
Application
Tracks all applications controlled by SAM including Application-specific information such as version number, DBMS, executable path, and availability status.
Application Access
Contains applications users may access as well as their access privileges.
Server Connection
Contains all network SQL servers along with their TCP/IP addresses and query ports.
User
Contains all potential users available in the corporate human resource files.
User Log
Contains user access information by application including number of connections and the date and time of the last user access.
The Austin IT SAM database currently resides on a Sybase SQL Server engine.
SAM's Application Launcher and Application Administrator
The Application Launcher and Application Administrator are both MS Windows 3.1 applications. They are written in PowerBuilder, but also call Visual Basic programs for file management tasks.
The Application Launcher and the Application Administrator provide the following services.
Application Launcher
As its name implies, the Application Launcher is a program that allows end users to initiate other programs. The Application Launcher:
When users run the Application Launcher they are presented with a list of all applications they are authorized to access. This list is pulled directly from the SAM security database. Users can then start an application from within the SAM Application Launcher by selecting it from this list. The Application Launcher will start the selected program and log the user into the appropriate SQL Server database. If the main security server is not found during login, SAM accesses a backup security server and continues running. From the main window users have three options:
1. Run an application.
2. Update the SAM password.
3. Copy network files.
Application Launcher Main Window
Running Applications
A number of services are performed when a user runs an application from within the SAM Application Launcher.
1. SAM verifies that the application is not already running on the client. This prevents more than one version of an application from starting.
2. SAM verifies that the application is online. This prevents a user from accessing an application or database that is undergoing maintenance (see SAM Application administrator for information on taking an application off-line).
3. SAM scans all local and mapped network drives until the application is found. This means a user is not required to know application drives or path names. The user need only be connected to a drive containing the application.
4. The client's working directory is updated to the application working directory.
5. The executable is launched with the appropriate parameters for the user, stored in the SAM database.
6. The user is logged into the database for the application they have selected.
7. The connection is recorded in the user log table.
8. If any of the above tasks fails, SAM provides appropriate messaging to the end user.
Once an application is launched, SAM remains minimized on the desktop. This allows the user to launch additional applications without restarting SAM or logging into the SAM database a second time.
Applications launched from within SAM are automatically passed a number of parameters from the Security Database, including user id, user access level, application specific parameters, and directory path information.
Updating The Password
The password option opens a dialog box allowing the user to perform password updates. This changes the password used to login to SAM itself.
Copying Network Files
The file copy buttons hide the PowerBuilder application and launch a Visual Basic program with which users can copy the application executable, DLLs, or the application icon to their local drives. This is especially useful in improving performance for remote users.
An additional feature of the Network Copy window is the Help option. As shown below, Help tells users where the files they are currently using reside. Help also gives recommendations for making a local copy.
Application Administrator
The Application Administrator is the program used by IT personnel as well as end user administrators. The IT department will use the Application Administrator to update information relevant to all applications such as the list of Austin servers and end users.
End user administrators are really "power users" who are responsible for the day-to-day administration of an application. They will use the Application Administrator to assign users to their applications as well as track the current version of their applications. This information is stored in the SAM database.
The Application Administrator provides a number of services, including:
Managing Application Information
An end user administrator maintains critical application information from the application management screen. Changes to this data update the application table on the SAM database.
Key application information includes:
Assigning Users To Applications
An application administrator assigns access privileges to end users from the User Access Control screen. The administrator grants and revokes access; dynamically updating the end users' SAM Application Launcher screen and the application access table in the SAM database.
Pressing the Update Access button launches a dialog box where the administrator assigns database privileges (view, update, admin, and so forth).
Maintaining Server Information
The IT system administrator maintains connectivity information to SQL Servers via the Server Information screen. Changes made to the server connection tables update the user WIN.INI file when the application launcher is initiated. For example the TCP/IP address of the servers displays as 999.99.999.99.
Identifying Application Administrators
The IT system administrator authorizes application administrators within SAM. Changes on this screen update the administrator table.
Application administrators are assigned to groups based on the business unit they are in.
Likewise, applications are also assigned to a group. This means application administrators have rights to all applications within their group.
Generating Application Access Reports
The following is a sample user access report from the SAM Application Administrator. It tracks the number of times a user has launched an application as well as the date and time of last access.
This information is useful for setting priorities because it allows IT to focus on enhancement requests made by application users. It also helps measure the impact of development efforts within IT by identifying which applications are actually being used.
SAM Benefits
By managing all servers and login accounts and providing a single point of access for SQL applications, releasing a new client-server executable is literally a five minute task.
SAM also makes it easy for the end user by providing a single login to all applications and allowing file downloads to a local drive, improving performance.
Specific improvements provided by SAM include:
The sharing of security administration between end user application administrators and the IT department has all but eliminated security concerns among developers. This allows the IT applications group to spend more time addressing client needs.
Industry
Communications
Business Solution
SQL Server Applications Security System
Architecture
An applications security database that contains six tables and resides on a Sybase SQL Server engine. Two applications use the SAM database: the Application Launcher and Application Administrator.
Products Used
Microsoft Visual Basic 3.0
Visual Basic provided a quick means for implementing functionality
that is not standard within PowerBuilder. Disk and file I/O functions
as well as timestamp support was provided by Visual Basic.
SQL Server For NT 4.2/ Sybase 4.9.2
Enterprise level DBMSs used throughout 3M. Preferred back-end
for corporate client-server application development.
PowerBuilder 3.0a
PowerBuilder was selected as the primary development tool because
of its powerful datawindow objects which ease database access
and manipulation. PowerBuilder is also the 3M standard for client-server
development.
Development Resources
One developer
Development Time and Cost
SAM functionality was mapped out in about two hours. A single developer implemented the initial prototype in about one week. It took less than three months for that same developer to bring SAM into production. The total development costs for SAM ran about $10,000.00.
Benefits
n Administration efforts have decreased 80%.
n Can now install and assign 50 users to a new application from one PC in about 15 minutes; this savings applies to application updates as well.
3M Company - Austin, TX
Robert Merx is a Programmer/Analyst at 3M Company with over 5 years experience in applications development. Bob has worked on a variety of hardware and software platforms. He is proficient in Powerbuilder, Visual Basic, and C/C++ and is 3M Austin's resident Sybase/SQL Server expert.
Scott Campbell is an Advanced Developer at 3M Company and has been working on client-server applications for 3 years. Scott has an Accounting degree and an MBA from the University of Texas at Austin. The user friendliness of today's development tools such as PowerBuilder, SQL Server for NT, Visual Basic, and the Microsoft Office Suite made his migration from Finance to 3M's IT department an easy one.
* A bi-directional pipe for incoming and outgoing data between networked computers.
 |
Click Here to Search TechNet Web Contents | TechNet CD Overview | |
Microsoft TechNet Credit Card Order Form At this time we can only support electronic orders in the US and Canada. International ordering information. |
| Go To TechNet Home Page |
©1996 Microsoft Corporation |  Go To Microsoft Home Page |