table of Contents
First introduction .................................................................................................
Two demand analysis
(1) The functional requirements of the system ...................................................................... 2
(2) The performance requirements of the system ...................................................................... 2
(3) Operating environment requirements ................................................................. 2
(4) Introduction to Development Tools ................................................................... 2
Triple design
(1) System modular division ................................................................. 2
(2) Design thoughts and structural level ...........................................................
Four database design
(1) Database model diagram ................................................................... 5
(2) Data table design ........................................................................ 5
(3) View ................................................................................. 7
Detailed design of five data access layer ...............................................................................
Six summary ...................................................................................................... 9
Seven thank you .......................................................................................
Main references .....................................................................................................................
Appendix ......................................................................................... 10
Unified identity authentication subsystem database design and data access layer implementation
Shi Chun Li
School of Computer and Information Science, Southwest Normal University, Chongqing 400715, China
Summary: Unified Authentication Subsystem (UIA Subsystem) Unified Management Users and Campus Interior Application System (Member Site). Each registered campus network users have a unified network account (username / password), users can access all school network application systems (member sites) in the campus, providing unified authentication for user applications And single sign-on service. The main content of this paper includes the system requirements analysis, overall design, database design and detailed design and implementation of data access.
Keywords: user, member site, user role
Abstract: Unique Identity Authentication subsystem (UIA) manages users and each branch application system (member site) in campus in unison Each registered campus network user has unified network accounts (user name / the password), users can visit the schools network application system. (member site) in the campus by the same user name / the password, apply to users' application the services that unify identity authentication and some single log-in. This thesis main contents include system demand analyse, overall design, database design and detailed Design And Realization of Data Access Layer.
I. Introduction
For each application system inside the campus, users must register, log in, and troubles in each system, and it is easy to cause confusion, more data resources. Repeated storage. Therefore, there is a need for a dedicated system to manage the user, ie the user only needs to register to log in, you can use different application systems. Of course, for each of the different application systems, it is also logically unified management for the convenience of the user. Therefore, it is necessary to develop a UIA (unified identity authentication) system. When the application system is registered in the UIA, we call it a member site. For the unified management of the user, the one hand is used to register multiple registrations multiple times when accessing each member site. It is also convenient to the user's use, which also saves resources for the member site, and avoids the data redundancy brought by each member site dispersion management unified user. Rest. On the other hand, it also provides convenience to the development of new member sites (new application systems).
Unified management of member sites, on the one hand, the user's operation is convenient, that is, after the user logs in to the UIA subsystem, you can directly access the member sites directly through the link. On the other hand, the member site only needs to give the role type of different users when registering in the system, and various users can perform permission control, and the system is allocated by the system. Therefore, the UIA subsystem (unified identity authentication subsystem) is developed, and the group discussion completed demand analysis and the overall design (module division), which is responsible for the code of the database design and data access layer.
Second, demand analysis
(1) System function requirements
UIA subsystems To implement two functions, one is to authenticate the user and system, and the other is the permission control between the user and the member site. Assign the user's permission to the user through the user's identity and the member site to access the permissions of a member site, through the system identity and the user's permission control, the member site can be viewed in the system. permission.
(2) System performance requirements
The UIA subsystem will manage massive users and member site information. This is high for the performance requirements of the UIA subsystem. At the same time, the performance requirements of the multi-network are also high, so the UIA subsystem must first have a large storage device, At the same time, there is a large bandwidth to ensure that a large number of users will not occur when access is accessed.
(3) operating environment requirements
.NET platform, SQLSERVER database, IIS server
(4) Introduction to Development Tools
Microsoft SQL Sever is a High Performance Customer / Server Relational Database Management System. There are many important new features such as transparent distributed processing, concise management, programming interface based on object embedding and link technology (0le), and integration with Internet, etc., select Microsoft SQL Sever's most important reason is its scalable Sex and high safety. Because Microsoft SQL Sever is a multi-line program-based parallel database core, it can play an advantage of additional processors, in many cases, only using a specific parallel database and operating system to obtain support for symmetric multi-processing technology; of course, it also has Very high security, effectively protecting user data.
Third, the overall design
(1) UIA subsystem module division
The Unified Authentication Subsystem (UIA) mainly has two functional modules: identity authentication module and permission management module.
Identification module manages user identity and member site identity. Provide online registration functions to users, providing information (such as username, password), which is the only evidence of user identity, and the user of the UIA subsystem is the legal user of the UIA subsystem; the identity authentication module also to the member Site provides online registration capabilities, and some basic information about member sites are required to register, including the user-defined role types (such as ordinary users, advanced users, admin users). The authority management module mainly includes: member site for user permission control, user's permission control, member site permission control. When the user applies for allocation permissions to a member site, it is necessary to provide some information to the member site. This information is the permission provided to the member site, and the member site can check the user information after the UIA authentication, and give it User assignments, users who get permissions can access the member site with some identity after UIA authentication. Member Site's permission control of member sites is mainly the call interface that the member site is controlled to other member sites.
The UIA also includes system maintenance modules and use help modules, the main function of the system maintenance module is the management of member sites and maintenance of system data.
The UIA also provides a set of SDKs for use when developing. If the user accesses a member site, the UIA provides a function that returns a user privilege for member site call.
(2) Design ideas and structural level
1. The general design idea of the system is as follows:
The user can log in in single point, that is, the user can access all the members of the UIA subsystem to access all the user who owns the user, and the user does not need to pass the authentication again after accessing a site. You can access the site with permissions, and return to the member site to his corresponding access. Centralized authorization control is the user's application authorization to the member site, and the user provides the information that wants to see the site, ie Member site application authorization; member site is also authorized to apply for the user, the site can be opened for the user, and the user gets the permission at the site; also provides management functions for the member site, including management of fundamentals And its member users, including access to users, disabling users' permissions. 2. Overall system structure hierarchy description:
It mainly illustrates the development model used in development: .NET hierarchical development, is also the development method recommended by Microsoft. The main part mainly has three layers: business logic, business appearance, database access layer. The business appearance layer mainly defines some basic operations, which can call the business logic layer, or call the database access layer and the Model layer, the business logic layer is mainly rules that define some operations, you can call the database access layer and the Model layer, database access layer Mainly the specific operation of the database, you can call the Model layer, through this layer of layer call to implement the main function of the system; the WebService layer can call the business logic layer, the database access layer and the Model layer, and the WebUI is mainly provided to the user. Reflect the actual function of the system, you can access the business appearance layer and the Model layer, the main function of the Model layer is to implement data transfer between the layers, which can be called by other layers. This development method is mainly conducive to the maintenance of the system and future system extensions. If a layer occurs or needs to update the function of a layer, only the corresponding layer is required, and there is no need to modify other layers, so It can be localized and easy to solve the problem.
Fourth, database design
UIA is a dynamic information management system must be based on Internet / intranet technology. Based on web mode, establish a client and server connection, we build this database's purpose, safe and securely save users, and convenient The member site is queried to the user's permissions control and information, so the database system should have high security, and in addition to the addition of new member sites, the system must have a database of external open function. To this end, we consider selecting the Microsoft SQL Sever 2000 as the database management system, creating a database system that is easy to implement data sharing, integration, data analysis, data query, and guarantee data efficient and secure, according to the actual needs, database content It is mainly divided into two major parts, one is the basic information of the idiom site, including information data such as site address, site name, site is open, etc., the second is the user's various information data, so the database should contain four essentials. Form: (1) Database Model:
(2) Design of data sheet
1. Member Site Information Table (TSITES):
This data sheet is used to store basic information of the member site.
Where the USID field is automatically generated by the system, the system unified flag indicates that SiteID is the key field of the data table, which is the only flag member site, which specifies 4-bit shaping, the URL represents the URL of the member site SiteName denotes the screen name of the site, both use the variable length string, but not more than 50 characters; SiteEnable flag, the logo is currently open, if you can access, use a character, The value is "1", indicating that the site is opened, and it has not been opened;
2. User Information Table (TUSERS):
This data table stores the basic information of the user, but also information that needs to be filled in when the user registers.
Where the UUID field is automatically generated by the system, the userID represents the user number. It is a key field, which is different from the different users; NikeName represents the user nickname, represented by the variable length string, but the length cannot exceed 50; email means the user Email, password user password, the two use 50 variable long strings; the useerNable flag, the user is open, that is, whether the registration information takes effect, use a character, "1" is true That is, it is open, and it is falsely unopened; in order to prevent the user from logging in, the password prompt will help the user retrieve the password in the way, Question is the password prompt of the user, Answer is a password prompt answer. Both are represented by variable long strings, and the required length is within 100.
3. The role in a member site (tuserroles):
This table stores different member sites with allocated role types.
Where RoleId represents the role number, it is the key field of the data table. Its unique flag is a role. The specified 4-bit shaping is indicated, and the siteID represents the member site number. RoleName represents the character name, with a variable long string within 50 digits Indicates whether the CANADMITE flag has the management right of the member site of the SiteID field flag. If "1" indicates that the role has this permission, if "0" does not have, general, for a member site, only The field of the role is "1", and the other is "0". 4. The role type of the member site to the user (TROLES):
RoleId represents the role number, and user ID means that the user number is the key field of the data table, and it is represented by 4-bit shaping, and the only role of a certain user is unique. RoleEnable Indicates whether the user role is turned on, indicates that the value is "1", indicating that the user role is open, that is, the user can access a member site, but no open.
(2) Views used in the system:
Vsites: The creation of this view is mainly to easily query the user role in the member site.
Vusers: The creation of this view is mainly for the user's information, including the role in the user's site.
V. Detailed Design of Data Access Layer:
Depending on the system functional requirements and database design can be drawn to the database mainly include four parts: 1, access to member site information sheets; 2. Access to the user information table (TUSERS); 3, to users Access to the Tuserroles in a member site; 4. Access to the user-assigned permission (role) type (Troles) to the member site; therefore, we use the following four classes to be implemented:
(1) Public class sites
This class contains the following functions:
Public int Createsite (SITEDATA SITE) Add a new member site information in the database. Its parameter is a SITEDATA type data. Sitedata is a data type defined by the Model layer. The information obtained from the database is the SQLDataReader type, which needs to be converted into the SITEDATA type in the MODEL layer facilitates the delivery of interlayer data.
Public int updatesite (SITEDATA Site) Modify a member site information in the database
Public int Removesite (System.guid USID) Removes a user in the database, its parameter is the number of the system automatically generated in the data table when registering the member site.
Public SITEDATA GETSITE (System.Guid USID) Automatically assigns all the information automatically assigned by the member site to query all the information of the member site, return to the Sitedata type in the Model layer.
Public IList ListSites () query lists all member site information, returns to the data type of each item in ILIST, using the list ilist
(2) public class users
This class contains the following functions:
Public int CREATEUSER Adds a new user in the database, the parameter is the UserData type, and its meaning is the same as SITEDATA.
Public int updateUser (userData user) Modify a user information in the database.
Public int Removeuser (String Nickname) Removes a user in the database, the parameter is the NicknamePublic UserData GetUser (String Nickname) in the user's information, querying the user's all information according to Nickname, and returns the userData type in the Model layer.
Public ilist listusers () Query all user information Returns the data type Userdata of each item in ILIST, ILIST
(3) Public Class UserRoles
This class contains the following functions:
Private int GtroleID (String Role, int SiteID) Gets the role number, which is the siteID of a member site and a role name.
Public Int AdduserRole (userroledata userrole) Add user role
Public Int RemoveUserRole (userroledata userrole) Delete a user role
Public UserRoledata getUserrole (int userid, int siteid) Gets the role in a member site
Public IList ListallUsersInsite (System.guid USID) Query all member sites
(4) Public Class UserroleRules
This class contains the following functions:
Public Bool Requestrole (String Nickname, String Role, System.guid USID) Request User Role
Public Bool EnableUserrole (String Nickname, System.guid USID) Opening User Roles
Public Bool Disableuserrole (String Nickname, System.guid USID) Disables User Roles
Public Bool RemoveRole (String Nickname, System.guid USID) Delete User Roles
Public Bool Isinrole (String Nickname, String Role, System.guid USID) Judging whether a user has a permission
Public Bool IsRoleEnabled (String Nickname, String Role, System.guid USID) Determines if the user role is opened
(5) DACONFIG
In order to simplify the parameter settings of the data access to database access, the DACONFIG class is upgraded. This class reads the database connection string from the configuration file, which acquires the location of the database server in the UIA subsystem, the name, username, and password of the database, and the content package directly supplied to other classes to access the database (for The function calls preparation parameters in the data access layer), simplifies other classes of data access code.
Six, summary:
Through the efforts of the group members, the system is basically formed, and the expected function is basically achieved. Through this graduation design, I have a certain understanding of .NET technology, I have accumulated valuable experience in the design of the database. As the development process continues to deepen, I have a more profound understanding of software development, and the ability to do it has also been well exercise. While rich knowledge and accumulation experience, my unity and collaboration work ability is also improved in the constant exchange of group members. These will make me benefit from future learning work. However, due to the limited personal capacity, time is rush, the system has no shortcomings, please ask the teachers to make valuable comments.
7, thank you:
This graduation design can not complete the guidance and help of the teacher Zou Xiangchun, and the teacher, Chen Wu, Teacher Chen Wu, Xiao Xiao, Mr. Li also gave great help in this process, to help them Heartfelt thanks. There is also a team of all members to support me, thank you for their heartfelt thanks! main reference:
[1] Van Rurai.NET Data Service C # Advanced Programming. Tsinghua University Press
[2] Wen Qian et al. C # programming technology. People's Posts and Telecommunications
[3] Flash four clear. SQL Sever 2000 Database Management System. Beijing Hope Electronics Press
appendix:
Addition Data Access Layer Source Code
Namespace DataAccess
{
///
/// Daconfig's summary description.
///
Public Class Daconfig
{
Public Static String Connectionstring
{
get
{
Return ConfigurationSettings.AppSettings ["Connectionstring"]. TOSTRING ();
}
}
}
}
Using system;
Using system.collections;
Using system.data;
Using system.data.sqlclient;
Using Microsoft.ApplicationBlocks.data;
Using model;
Namespace DataAccess
{
Public Class Sites
{
Public int Createsite (SITEDATA SITE)
{
// Prepare SQL parameters
Sqlparameter [] ms = new sqlparameter [6];
MS [0] = New SQLParameter ("@ SiteID", SqldbType.InT);
MS [1] = New Sqlparameter ("@ sitename", sqldbtype.nvarchar);
MS [2] = New Sqlparameter ("@ URL", SqldbType.nvarchar);
MS [3] = New SqlParameter ("@ SiteEnabled", SqldbType.bit;
MS [4] = New Sqlparameter ("@ RoleName", SqldbType.nvarchar);
MS [5] = New Sqlparameter ("@ canadminsite", sqldbtype.nvarchar);
/ / Assignment to the parameter
MS [1] .value = site.sitename;
MS [2] .value = site.URL;
MS [3] .value = site.enable;
/ / Insert the parameterized SQL statement of the site
String SQL = "INSERT INTO TSITES (Sitename, URL, SiteEnabled) Values (@ siteename, @ URL, @ SiteENABLED);
// Call the function of the DAB layer
INT i = SQLHELPER.EXECUtenonQuery (Daconfig.Connectionstring, CommandType.Text, SQL, MS);
/ / Query the automatically generated field value
SQL = "SELECT USID, Siteid from Tsites Where SiteName = @ Sitename";
SqlDataReader Reader = SQLHELPER.EXECUTEREADER (Daconfig.Connectionstring, CommandType.Text, SQL, MS); while (Reader.Read ())
{
Site.usid = reader.getGuid (0);
Site.SiteId = Reader.GetInt32 (1);
}
// Insert the role parameter statement of the site
SQL = "INSERT INTO TROLES (SiteID, RoleName, CanadminSite) VALUES (@ siteid, @ rolename";
MS [0] .value = site.siteid;
For (int J = 0; j { / / Assign the SQL parameter MS [4] .Value = ((SITEDATA.ROLE) Site.Roles [J]). ROLENAME; MS [5] .Value = (SITEDATA.ROLE) Site.Roles [J]). SQLHELPER.EXECUTENONQUERY (Daconfig.Connectionstring, CommandType.Text, SQL, MS); } // Query all roles of the site and conversion in data format SQL = "SELECT ROLLEID, ROLENAME, CANADMINSITE from Troles Where SiteId = @ SiteId"; SqlDataReader R2 = SQLHELPER.EXECUTEREADER (Daconfig.Connectionstring, CommandType.Text, SQL, MS); While (R2.Read ()) { Site.Roles.Add (new model.siteData.Role (r2.Getint32 (0), R2.GetString (1), R2.GetBoolean (2)))); } Return I; } / / Change user information Public int updatesite (SITEDATA SITE) { // Prepare SQL parameters Sqlparameter [] ms = new sqlparameter [7]; MS [0] = New SqlParameter ("@ usid", sqldbtype.uniqueidentifier; MS [1] = New SqlParameter ("@ SiteID", SqldbType.InT); MS [2] = New SQLParameter ("@ sitename", sqldbtype.nvarchar); MS [3] = New Sqlparameter ("@ URL", SqldbType.nvarchar); MS [4] = New Sqlparameter ("@ enabled", sqldbtype.bit); MS [5] = New SQLParameter ("@ rolename", sqldbtype.nvarchar); MS [6] = New Sqlparameter ("@ roleid", sqldbtype.int); / / Assignment to the parameter MS [0] .value = site.usid; MS [1] .value = site.siteid; MS [2] .value = site.sitename; MS [3] .value = site.URL; MS [4] .value = site.enable; / / Modify the Site Basic Information Parameterization SQL Statement string sql = "update TSites set SiteName = @ SiteName, Url = @ Url, SiteEnabled = @ SiteEnabled where SiteID = @ SiteID"; int i = SqlHelper.ExecuteNonQuery (DAConfig.ConnectionString, CommandType.Text, sql, ms); SQL = "Update Troles Set RoleName = @ ROLENAME WHERE ROLLEID = @ roleid"; For (int J = 0; j { MS [5] .value = (SITEDATA.ROLE) Site.Roles [j]). ROLENAME; MS [6] .Value = ((SITEDATA.ROLE) site.roles [j]). roleid; SQLHELPER.EXECUTENONQUERY (Daconfig.Connectionstring, CommandType.Text, SQL, MS); } Return I; } / / Delete a site Public int Removesite (System.guid USID) { // Prepare SQL parameters Sqlparameter [] ms = new sqlparameter [1]; MS [0] = New SqlParameter ("@ usid", sqldbtype.uniqueidentifier; MS [0] .value = usid; // Parameterization SQL statement String SQL = "delete from tsites where usid = @ usid"; // Call the DAB layer function to perform the operation Return SQLHELPER.EXECUtenonQuery (Daconfig.Connectionstring, CommandType.Text, SQL, MS); } / / Query site information Public Sitedata getsite (System.guid USID) { // Prepare SQL parameters Sqlparameter [] ms = new sqlparameter [2]; MS [0] = New SqlParameter ("@ usid", sqldbtype.uniqueidentifier; MS [1] = New SqlParameter ("@ SiteID", SqldbType.InT); MS [0] .value = usid; // Parameterization SQL statement String SQL = "SELECT USID, SiteID, SiteName, URL, SiteEnabled from Tsites where usid = @ usid"; // Call the DAB layer function to perform the operation SqlDataReader Reader = SQLHELPER.EXECUTEREADER (Daconfig.Connectionstring, CommandType.Text, SQL, MS); Sitedata Data = NULL; While (Reader.Read ()) { Data = New Sitedata (Reader.getGuid (0), Reader.GetInt32 (1), Reader.getstring (2), Reader.getstring (3), Reader.getBoolean (4)); } // Parameterization SQL statement SQL = "SELECT ROLLEID, ROLENAME, Canadminsite from Troles Where SiteId = @ SiteID"; MS [1] .value = data.siteid; // Call the DAB layer function to perform the operation SqlDataReader R2 = SQLHELPER.EXECUTEREADER (Daconfig.Connectionstring, CommandType.Text, SQL, MS); While (R2.Read ()) { Data.Roles.Add (New Sitedata.Role (r2.get32 (0), R2.GetString (1), R2.GetBoolean (2))); } Return Data; } // Query all users Public IList Listsites () { // Return value is a linked list IList ret = new arraylist (); Sqlparameter [] ms = new sqlparameter [1]; MS [0] = New SQLParameter ("@ SiteID", SqldbType.InT); // Parameterization SQL statement String SQL = "SELECT USID, SiteID, SiteName, URL, SiteEnabled from Tsites"; // Call the DAB layer function to perform the operation SqlDataReader Reader = SQLHELPER.EXECUTEREADER (Daconfig.Connectionstring, CommandType.Text, SQL, NULL); While (Reader.Read ()) { Sitedata Data = NULL; Data = New Sitedata (Reader.getGuid (0), Reader.GetInt32 (1), Reader.getstring (2), Reader.getstring (3), Reader.getBoolean (4)); SQL = "SELECT ROLLEID, ROLENAME, CANADMINSITE from Troles Where SiteId = @ SiteId"; MS [0] .value = data.siteid; // Call the DAB layer function to perform the operation SqlDataReader R2 = SQLHELPER.EXECUTEREADER (Daconfig.Connectionstring, CommandType.Text, SQL, MS); While (R2.Read ()) { / / Format conversion and insert the list Data.Roles.Add (New Sitedata.Role (r2.get32 (0), R2.GetString (1), R2.GetBoolean (2))); } Ret.Add (data); } Return Ret; } } }
