RBAC permission framework_MVC permission framework

Posted by nilesh on Tue, 13 Aug 2019 13:48:42 +0200

RBAC Role-Based Access Control (RBAC) is a role-based access control framework. It is very convenient to manage privileges through user-role-privilege association. What is it? RBAC Please do it yourself. Baidu.

Thank you for your support. If there are any mistakes in the article, please contact me or email me linjie.rd@gmail.com. A small gift will be sent after the correction. Thank you.

thinking

Xiao Zhang, Xiao Wang and Xiao Li are going to the zoo to see animals. Xiao Zhang and Xiao Wang bought tickets online in advance. Xiao Li didn't get tickets.

Three people went to the zoo public exhibition area. There was no need to check tickets. Three people could see the animals in this area (anonymous access is allowed).

Continue to move forward, to the special exhibition area, need to verify tickets, Xiao Zhang and Xiao Wang have tickets smoothly into the special exhibition area, Xiao Li was blocked (identity authentication).

Xiao Li was unwilling. He found a corner and stole over the wall. As a result, he was caught by the scenic area personnel and taken to the police station for education (illegal visits).

Xiao Wang was so slow in the special exhibition area that he was caught sneaking into a place without authority.

 

Relative relationship

 

Permission: Permission refers to whether it is allowed to be accessed (viewed) and used as a means, usually designed with a relationship between superiors and subordinates, in this paper the use of tree structure.

User: User of the operating system, through which to complete a part of the work and life.

Roles: A kind of classification of users, sorting out one part of authority, such as: ordinary employees have the authority of ordinary employees, managers and managers.

In real life, a person has many identities, such as the children of parents, parents of children, workers at work and so on. And there are many ordinary employees in a company. So, users and roles are many-to-many relationships.

A role may also have multiple permissions. For example, a doctor can also provide medical care in addition to curing illness and saving people. While fighting crime, a policeman can also help others according to laws and regulations.

A single authority may be owned by multiple roles. A policeman is fighting crime, and ordinary citizens can participate. Often a single event (analogous to a permission) involves multiple roles.

So roles and powers are also many-to-many relationships.

 

Database Design

Because it is RBAC, there must be users, roles, permissions. Users and roles are many-to-many relationships, and roles and permissions are many-to-many relationships. Therefore, we need user table, user role Association table, role table, role permission association table and permission table.

User table: User code, username, password.

Role table: role code, role name.

User role Association table: user code, role code.

Privilege table: privilege code, privilege name, link address, parent privilege code.

Role permission Association table: role code, permission code.

These are the basic tables, often life and work will be much more complex than this. In some cases, special user rights associated management tables are also required.

Programming

According to the requirements, administrators can manage permissions and maintain user information, so users can realize their own task requirements.

Then, the most basic is to obtain permissions and determine whether there are corresponding permissions, to display the corresponding functions on the page, and to determine the operation of the wood has the right to operate.

For example, administrators can login, add role user rights, and so on. User login, can manage goods, order information and so on.

Environmental Science

Using vs2015, SQL Server 2017 programming, Win10 Home x64 system software, compatible with IE8, IE9, IE10, Google browser, using PowerDesigner modeling, and so on.

Framework Technology

The overall framework uses MVC + multi-tier, and the page uses MiniUI framework. Considering the small number of users, Code First is used.

Code

Because of the confidentiality regulations, the business part is not displayed and only the authority is given.

* Access permissions, users login, according to the user's role information to obtain the corresponding permissions.

* Judge whether there is permission or not. Judge whether there is permission before the user operates.

Realization

* Login: When a user opens a website, he first decides whether the current page is not allowed to visit anonymously, whether the Session has user Session information or not, and then loads user information if it exists. Cookie does not read Cookie. Cookie has login information and does not expire. This loads user information or jumps to the login page.

After login is successful or user information is loaded, access list is obtained and corresponding modules are displayed.

* Operations: When the user clicks on the function or enters the address access in the address bar, first of all, through the thought of face-to-face programming, use action filter or permission filter to determine whether there is corresponding permission, permission to operate, no permission is considered invalid operation or illegal operation.

Invalid operation: Return to the previous page or step

Illegal operation: empty login information, empty Session, release Cookie, jump to illegal access warning page, jump to login page after 3 seconds

----------------  Common  ----------------

 1  public class UserPermissionFilter:System.Web.Mvc.ActionFilterAttribute
 2     {
 3         public override void OnActionExecuting(System.Web.Mvc.ActionExecutingContext filterContext)
 4         {
 5             #region Judge if this is the case controller and action Authority
 6             LoginUserViewModel user = AdminUserBll.GetLoginUser();
 7             var permissionListAll = ModuleBll.Instance.GetLevelModuleListIsArrayAllInCache();
 8             if (null != user && !string.IsNullOrEmpty(user.user_name))
 9             {
10                 bool ret = true;
11                 if (user.PermissionList != null && user.PermissionList.Count > 0)
12                 {
13                     string action = filterContext.ActionDescriptor.ActionName;
14                     string controller = filterContext.ActionDescriptor.ControllerDescriptor.ControllerName;
15                     string url = (controller + "/" + action).ToUpper();
16                     var module = permissionListAll.FirstOrDefault(c => c.action_url.ToUpper() == url);
17                     if ((controller.ToUpper() != "HOME" && action.ToUpper() != "LOGIN") &&
18                         controller.ToUpper() != "MENU" && module != null) /*No permissions under this Action*/
19                                                          //&& module != null
20                     {
21                         ret = AdminUserBll.LoginUserIsPermission(controller, action, user);
22                     }
23                     if (!ret)
24                     {
25                         filterContext.HttpContext.Response.Redirect("/Home/NoRight", true);
26                         //throw new System.Web.HttpException(403, "No access");
27                     }
28                 }
29             }
30             else
31             {
32                 filterContext.HttpContext.Response.Redirect("/login", true);
33 
34             }
35 
36 
37             #endregion
38 
39             base.OnActionExecuting(filterContext);
40         }
41     }
Interpretation of Operational Permission 
1         /// <summary>
2         /// Get all one-dimensional modules View list,In the cache
3         /// </summary>
4         /// <returns></returns>
5         public List<LevelModuleViewModel> GetLevelModuleListIsArrayAllInCache()
6         {
7             return SystemCacheManager.GetCache("ModuleBll_GetLevelModuleListIsArrayAllInCache", 60, GetLevelMudleListIsArrayAll);
8         }
Getting a list of modules in the cache 
 1 public static class SystemCacheManager
 2     {
 3         /// <summary>
 4         /// 
 5         /// </summary>
 6         /// <param name="key"></param>
 7         /// <param name="timeOut"></param>
 8         /// <param name="function"></param>
 9         /// <returns></returns>
10         public static T GetCache<T>(string key, int timeOut, Func<T> function)
11         {
12             if (string.IsNullOrEmpty(key)) return default(T);
13             var configs = HttpContext.Current.Cache[key];
14             if (configs == null)
15             {
16                 configs = function();
17                 if (timeOut > -1)
18                 {
19                     HttpContext.Current.Cache.Insert(key, configs,
20                                                 null, DateTime.Now.AddSeconds(timeOut),
21                                                 System.Web.Caching.Cache.NoSlidingExpiration);
22                 }
23                 else
24                 {
25                     HttpContext.Current.Cache.Insert(key, configs);
26                 }
27                 return (T)configs;
28             }
29             return (T)configs;
30         }
31 
32 
33         /// <summary>
34         /// 
35         /// </summary>
36         /// <param name="key"></param>
37         /// <param name="timeOut"></param>
38         /// <param name="function"></param>
39         /// <returns></returns>
40         public static T GetCache<T,Z>(string key, int timeOut, Func<Z,T> function,Z u1)
41         {
42             if (string.IsNullOrEmpty(key)) return default(T);
43             var configs = HttpContext.Current.Cache[key];
44             if (configs == null)
45             {
46                 configs = function(u1);
47                 if (timeOut > -1)
48                 {
49                     HttpContext.Current.Cache.Insert(key, configs,
50                                                 null, DateTime.Now.AddSeconds(timeOut),
51                                                 System.Web.Caching.Cache.NoSlidingExpiration);
52                 }
53                 else
54                 {
55                     HttpContext.Current.Cache.Insert(key, configs);
56                 }
57                 return (T)configs;
58             }
59             return (T)configs;
60         }
61     }
Getting cached data 
 /// <summary>
        /// Determine whether the logged-in user has permission to request
        /// </summary>
        /// <param name="controller"></param>
        /// <param name="action"></param>
        /// <param name="login"></param>
        /// <returns></returns>
        public static bool LoginUserIsPermission(string controller, string action, LoginUserViewModel login)
        {
            if (login.PermissionList != null && login.PermissionList.Count > 0)
            {
                string strUrl = controller + "/" + action;
                if (login.PermissionList.FirstOrDefault(c => c.action_url.ToUpper() == strUrl.ToUpper()) != null)
                {
                    return true;
                }
            }
            return false;
        }
Determine whether the user has operational privileges on the current request

 

 1 /// <summary>
 2         /// Getting logged-in users
 3         /// </summary>
 4         /// <returns></returns>
 5         public static LoginUserViewModel GetLoginUser()
 6         {
 7             if (System.Web.HttpContext.Current.Session["LoginUser"] != null)
 8             {
 9                 return (LoginUserViewModel)System.Web.HttpContext.Current.Session["LoginUser"];
10             }
11             else
12             {
13                 string userName = Library.Web.Cookie.CookieManager.GetCookie(userCookieKey);
14                 if (!string.IsNullOrEmpty(userName))
15                 {
16                     userName = StringDes.DesDecrypt(userName);
17                     AdminUserDb user = Instance.GetModel(userName);
18                     if (user != null)
19                     {
20                         LoginUserViewModel view = Instance.IniLogin(user);
21                         return view;
22                     }
23                 }
24             }
25             return new LoginUserViewModel();
26         }
Getting logged-in users in Session and Cookie 
public ActionResult Login(string loginName, string passWord)
        {

            ViewBag.LoginName = loginName;
            ViewBag.PassWord = passWord;
            ViewBag.ErrorMessage = "";
            string md5PassWord = Library.Tools.Text.StringMd5.Md5Hash32Salt(passWord);
            AdminUserDb user = AdminUserBll.Instance.GetModel(loginName);
            if (user != null)
            {
                if (user.pass_word.ToUpper() == md5PassWord.ToUpper()&&user.user_status==1)
                {
                    user.last_lgoin_date = DateTime.Now;
                    user.last_login_ip = Request.UserHostAddress;
                    AdminUserBll.Instance.IniLogin(user);
                    return RedirectToAction("Index", "Home");
                }
                else
                {
                    ViewBag.errorMessage = "User password error";
                }
            }
            else
            {
                ViewBag.errorMessage = "User password error";
            }
            return View();
        }
User login 
 1         /// <summary>
 2         /// Gets the user with the specified username
 3         /// </summary>
 4         /// <param name="userName"></param>
 5         /// <returns></returns>
 6         public AdminUserDb GetModel(string userName)
 7         {
 8 
 9             return PermissionDal.GetModel(c => c.user_name == userName);
10 
11         }
Getting User Information 
 1         /// <summary>
 2         /// Initialize logged-in users
 3         /// </summary>
 4         /// <param name="user">log on user</param>
 5         /// <returns></returns>
 6         public LoginUserViewModel IniLogin(AdminUserDb user)
 7         {
 8             if (user != null)
 9             {
10                 LoginUserViewModel loginUserViewModel = new LoginUserViewModel();
11                 loginUserViewModel.user_full_name = user.user_full_name;
12                 loginUserViewModel.user_name = user.user_name;
13                 List<ModuleDb> moduleDbList = ModuleBll.Instance.GetModuleList(user.user_name).ToList();
14                 if (moduleDbList != null && moduleDbList.Count > 0)
15                 {
16                     loginUserViewModel.PermissionList = moduleDbList;
17                     loginUserViewModel.PermissionListLevel = ModuleBll.Instance.IniLevelModuleList(moduleDbList);
18                 }
19                 UpdateUserLogin(user);
20                 System.Web.HttpContext.Current.Session["LoginUser"] = loginUserViewModel;
21                 Library.Web.Cookie.CookieManager.SetCookie(userCookieKey, StringDes.DesEncrypt(loginUserViewModel.user_name));
22                 return loginUserViewModel;
23             }
24             return new LoginUserViewModel();
25         }
Initialize user information

 

  1 --CREATE DATABASE center 
  2 --USE center
  3 --GO 
  4 CREATE TABLE [dbo].[admin_user](
  5     [user_name] [nvarchar](50) PRIMARY KEY NOT NULL,
  6     [pass_word] [nvarchar](100) NOT NULL,
  7     [user_full_name] [nvarchar](100) NULL,
  8     [user_status] [int] NOT NULL,
  9     [last_lgoin_date] [datetime] NULL,
 10     [last_login_ip] [nvarchar](255) NULL,
 11     [creator_name] [nvarchar](50) NOT NULL,
 12     [creator_date] [datetime] NOT NULL,
 13     [modifi_name] [nvarchar](50) NULL,
 14     [modifi_date] [datetime] NULL,
 15     [user_img] [varchar](max) NULL
 16 )
 17 GO
 18 
 19 CREATE TABLE [dbo].[admin_user_role_relation](
 20     [id] [INT] PRIMARY KEY IDENTITY(1,1) NOT NULL,
 21     [user_name] [nvarchar](50) NOT NULL,
 22     [role_id] [int] NOT NULL,
 23     [creator_name] [nvarchar](50) NOT NULL,
 24     [creator_date] [datetime] NOT NULL
 25 )
 26 GO
 27 
 28 CREATE TABLE [dbo].[dictionary_table](
 29     [dt_key] [nvarchar](100) PRIMARY KEY NOT NULL,
 30     [dt_type_key] [nvarchar](100) NOT NULL,
 31     [dt_name] [nvarchar](100) NOT NULL,
 32     [dt_status] [int] NOT NULL,
 33     [dt_orderby] [decimal](18, 2) NOT NULL,
 34     [creator_name] [nvarchar](50) NOT NULL,
 35     [creator_date] [datetime] NOT NULL,
 36     [modifi_name] [nvarchar](50) NULL,
 37     [modifi_date] [datetime] NULL
 38 )
 39 GO
 40 
 41 CREATE TABLE [dbo].[dictionary_type_table](
 42     [dt_type_key] [nvarchar](100) PRIMARY KEY NOT NULL,
 43     [dt_type_name] [nvarchar](100) NOT NULL,
 44     [dt_type_remark] [nvarchar](200) NULL,
 45     [dt_type_orderby] [decimal](18, 0) NOT NULL,
 46     [creator_name] [nvarchar](50) NOT NULL,
 47     [creator_date] [datetime] NOT NULL,
 48     [modifi_name] [nvarchar](50) NULL,
 49     [modifi_date] [datetime] NULL
 50 )
 51 GO
 52 
 53 CREATE TABLE [dbo].[module_db](
 54     [module_code] [nvarchar](50) PRIMARY KEY NOT NULL,
 55     [module_name] [nvarchar](100) NOT NULL,
 56     [parent_code] [nvarchar](50) NOT NULL,
 57     [module_level] [int] NOT NULL,
 58     [is_menu] [int] NOT NULL,
 59     [is_action] [int] NOT NULL,
 60     [action_url] [nvarchar](500) NULL,
 61     [order_by] [decimal](10, 0) NOT NULL,
 62     [module_status] [int] NOT NULL,
 63     [creator_name] [nvarchar](50) NOT NULL,
 64     [creator_date] [datetime] NOT NULL,
 65     [modifi_name] [nvarchar](50) NULL,
 66     [modifi_date] [datetime] NULL
 67 )
 68 GO
 69 
 70 CREATE TABLE [dbo].[role_db](
 71     [role_id] [INT] PRIMARY KEY IDENTITY(1,1) NOT NULL,
 72     [role_name] [nvarchar](50) NOT NULL,
 73     [role_status] [int] NOT NULL,
 74     [creator_name] [nvarchar](50) NOT NULL,
 75     [creator_date] [datetime] NOT NULL,
 76     [modifi_name] [nvarchar](50) NULL,
 77     [modifi_date] [datetime] NULL
 78 )
 79 GO
 80 
 81 CREATE TABLE [dbo].[role_module_relation](
 82     [id] [INT] PRIMARY KEY IDENTITY(1,1) NOT NULL,
 83     [role_id] [int] NOT NULL,
 84     [module_code] [nvarchar](255) NOT NULL,
 85     [creator_name] [nvarchar](50) NOT NULL,
 86     [creator_date] [datetime] NOT NULL
 87 )
 88 GO
 89 INSERT [dbo].[admin_user] ([user_name], [pass_word], [user_full_name], [user_status], [last_lgoin_date], [last_login_ip], [creator_name], [creator_date], [modifi_name], [modifi_date], [user_img]) VALUES (N'adm', N'dc2e86a6ae8315a3da26cae880baae8e', N'adm', 1, NULL, NULL, N'admin', CAST(N'2019-05-28T12:44:12.380' AS DateTime), NULL, NULL, NULL)
 90 INSERT [dbo].[admin_user] ([user_name], [pass_word], [user_full_name], [user_status], [last_lgoin_date], [last_login_ip], [creator_name], [creator_date], [modifi_name], [modifi_date], [user_img]) VALUES (N'admin', N'dc2e86a6ae8315a3da26cae880baae8e', N'Administrators', 1, CAST(N'2019-05-28T14:48:15.503' AS DateTime), N'::1', N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime), NULL, NULL, NULL)
 91 INSERT [dbo].[admin_user] ([user_name], [pass_word], [user_full_name], [user_status], [last_lgoin_date], [last_login_ip], [creator_name], [creator_date], [modifi_name], [modifi_date], [user_img]) VALUES (N'test', N'dc2e86a6ae8315a3da26cae880baae8e', N'Test Account', 1, CAST(N'2019-05-21T11:29:30.137' AS DateTime), N'::1', N'admin', CAST(N'2019-05-05T14:02:23.687' AS DateTime), NULL, NULL, NULL)
 92 SET IDENTITY_INSERT [dbo].[admin_user_role_relation] ON 
 93 
 94 INSERT [dbo].[admin_user_role_relation] ([id], [user_name], [role_id], [creator_name], [creator_date]) VALUES (40, N'admin', 2, N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
 95 INSERT [dbo].[admin_user_role_relation] ([id], [user_name], [role_id], [creator_name], [creator_date]) VALUES (120, N'admin', 1, N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
 96 INSERT [dbo].[admin_user_role_relation] ([id], [user_name], [role_id], [creator_name], [creator_date]) VALUES (123, N'test', 2, N'admin', CAST(N'2019-05-05T14:02:23.687' AS DateTime))
 97 INSERT [dbo].[admin_user_role_relation] ([id], [user_name], [role_id], [creator_name], [creator_date]) VALUES (124, N'adm', 1, N'admin', CAST(N'2019-05-28T12:44:12.380' AS DateTime))
 98 INSERT [dbo].[admin_user_role_relation] ([id], [user_name], [role_id], [creator_name], [creator_date]) VALUES (125, N'adm', 2, N'admin', CAST(N'2019-05-28T12:44:12.380' AS DateTime))
 99 SET IDENTITY_INSERT [dbo].[admin_user_role_relation] OFF
100 INSERT [dbo].[dictionary_table] ([dt_key], [dt_type_key], [dt_name], [dt_status], [dt_orderby], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (N'fa-sex-false', N'fa-sex', N'female', 1, CAST(1.00 AS Decimal(18, 2)), N'admin', CAST(N'2019-05-07T13:51:17.207' AS DateTime), NULL, NULL)
101 INSERT [dbo].[dictionary_table] ([dt_key], [dt_type_key], [dt_name], [dt_status], [dt_orderby], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (N'fa-sex-true', N'fa-sex', N'male', 1, CAST(0.00 AS Decimal(18, 2)), N'admin', CAST(N'2019-05-07T13:50:48.100' AS DateTime), NULL, NULL)
102 INSERT [dbo].[dictionary_type_table] ([dt_type_key], [dt_type_name], [dt_type_remark], [dt_type_orderby], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (N'fa-sex', N'Gender', N'Gender', CAST(0 AS Decimal(18, 0)), N'admin', CAST(N'2019-05-07T13:49:48.733' AS DateTime), NULL, NULL)
103 INSERT [dbo].[module_db] ([module_code], [module_name], [parent_code], [module_level], [is_menu], [is_action], [action_url], [order_by], [module_status], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (N'fa-book', N'Dictionary Management', N'fa-puzzle-piece', 3, 1, 1, N'DictionaryManager/List', CAST(0 AS Decimal(10, 0)), 1, N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime), NULL, NULL)
104 INSERT [dbo].[module_db] ([module_code], [module_name], [parent_code], [module_level], [is_menu], [is_action], [action_url], [order_by], [module_status], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (N'fa-desktop', N'system management', N'yixin_public', 2, 1, 2, N'', CAST(0 AS Decimal(10, 0)), 1, N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime), N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
105 INSERT [dbo].[module_db] ([module_code], [module_name], [parent_code], [module_level], [is_menu], [is_action], [action_url], [order_by], [module_status], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (N'fa-graduation-cap', N'Module menu management', N'fa-desktop', 3, 1, 1, N'ModuleManager/list', CAST(3 AS Decimal(10, 0)), 1, N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime), N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
106 INSERT [dbo].[module_db] ([module_code], [module_name], [parent_code], [module_level], [is_menu], [is_action], [action_url], [order_by], [module_status], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (N'fa-paw', N'Role management', N'fa-desktop', 3, 1, 1, N'rolemanager/list', CAST(1 AS Decimal(10, 0)), 1, N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime), NULL, NULL)
107 INSERT [dbo].[module_db] ([module_code], [module_name], [parent_code], [module_level], [is_menu], [is_action], [action_url], [order_by], [module_status], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (N'fa-puzzle-piece', N'configuration management', N'yixin_public', 2, 1, 2, N'', CAST(2 AS Decimal(10, 0)), 1, N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime), NULL, NULL)
108 INSERT [dbo].[module_db] ([module_code], [module_name], [parent_code], [module_level], [is_menu], [is_action], [action_url], [order_by], [module_status], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (N'fa-th-list', N'Dictionary Category Management', N'fa-puzzle-piece', 3, 1, 1, N'DictionaryTypeManager/List', CAST(1 AS Decimal(10, 0)), 1, N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime), N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
109 INSERT [dbo].[module_db] ([module_code], [module_name], [parent_code], [module_level], [is_menu], [is_action], [action_url], [order_by], [module_status], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (N'fa-user', N'user management', N'fa-desktop', 3, 1, 1, N'AdminUserManager/list', CAST(3 AS Decimal(10, 0)), 1, N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime), NULL, NULL)
110 INSERT [dbo].[module_db] ([module_code], [module_name], [parent_code], [module_level], [is_menu], [is_action], [action_url], [order_by], [module_status], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (N'yixin_public', N'XX Public Platform', N'', 1, 1, 2, N'', CAST(0 AS Decimal(10, 0)), 1, N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime), N'admin', NULL)
111 SET IDENTITY_INSERT [dbo].[role_db] ON 
112 
113 INSERT [dbo].[role_db] ([role_id], [role_name], [role_status], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (1, N'Administrators', 1, N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime), N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
114 INSERT [dbo].[role_db] ([role_id], [role_name], [role_status], [creator_name], [creator_date], [modifi_name], [modifi_date]) VALUES (2, N'user', 1, N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime), N'admin', CAST(N'2019-05-21T11:29:05.307' AS DateTime))
115 SET IDENTITY_INSERT [dbo].[role_db] OFF
116 SET IDENTITY_INSERT [dbo].[role_module_relation] ON 
117 
118 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (127, 1, N'yixin_public', N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
119 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (128, 1, N'fa-desktop', N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
120 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (131, 1, N'fa-puzzle-piece', N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
121 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (132, 1, N'fa-paw', N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
122 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (133, 1, N'fa-graduation-cap', N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
123 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (134, 1, N'fa-user', N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
124 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (135, 1, N'fa-th-list', N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
125 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (136, 1, N'fa-book', N'admin', CAST(N'2019-05-01T00:00:00.000' AS DateTime))
126 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (1226, 2, N'yixin_public', N'admin', CAST(N'2019-05-21T11:29:05.307' AS DateTime))
127 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (1227, 2, N'afghfeat', N'admin', CAST(N'2019-05-21T11:29:05.307' AS DateTime))
128 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (1228, 2, N'fa-puzzle-piece', N'admin', CAST(N'2019-05-21T11:29:05.307' AS DateTime))
129 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (1229, 2, N'fa-book', N'admin', CAST(N'2019-05-21T11:29:05.307' AS DateTime))
130 INSERT [dbo].[role_module_relation] ([id], [role_id], [module_code], [creator_name], [creator_date]) VALUES (1230, 2, N'fa-th-list', N'admin', CAST(N'2019-05-21T11:29:05.307' AS DateTime))
131 SET IDENTITY_INSERT [dbo].[role_module_relation] OFF
132 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'admin_user', @level2type=N'COLUMN',@level2name=N'user_name'
133 GO
134 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User password ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'admin_user', @level2type=N'COLUMN',@level2name=N'pass_word'
135 GO
136 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Full Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'admin_user', @level2type=N'COLUMN',@level2name=N'user_full_name'
137 GO
138 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User State 1:Effective 2:invalid' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'admin_user', @level2type=N'COLUMN',@level2name=N'user_status'
139 GO
140 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last logon date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'admin_user', @level2type=N'COLUMN',@level2name=N'last_lgoin_date'
141 GO
142 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last logon ip' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'admin_user', @level2type=N'COLUMN',@level2name=N'last_login_ip'
143 GO
144 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creator login name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'admin_user', @level2type=N'COLUMN',@level2name=N'creator_name'
145 GO
146 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'admin_user', @level2type=N'COLUMN',@level2name=N'creator_date'
147 GO
148 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Modifier login name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'admin_user', @level2type=N'COLUMN',@level2name=N'modifi_name'
149 GO
150 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'modification date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'admin_user', @level2type=N'COLUMN',@level2name=N'modifi_date'
151 GO
152 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creator login name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'admin_user_role_relation', @level2type=N'COLUMN',@level2name=N'creator_name'
153 GO
154 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation time' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'admin_user_role_relation', @level2type=N'COLUMN',@level2name=N'creator_date'
155 GO
156 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Dictionaries key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_table', @level2type=N'COLUMN',@level2name=N'dt_key'
157 GO
158 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Dictionary Categories key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_table', @level2type=N'COLUMN',@level2name=N'dt_type_key'
159 GO
160 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Dictionary Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_table', @level2type=N'COLUMN',@level2name=N'dt_name'
161 GO
162 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Dictionary State 1:Effective 2:invalid' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_table', @level2type=N'COLUMN',@level2name=N'dt_status'
163 GO
164 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'sort field' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_table', @level2type=N'COLUMN',@level2name=N'dt_orderby'
165 GO
166 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creator login name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_table', @level2type=N'COLUMN',@level2name=N'creator_name'
167 GO
168 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_table', @level2type=N'COLUMN',@level2name=N'creator_date'
169 GO
170 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Modifier login name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_table', @level2type=N'COLUMN',@level2name=N'modifi_name'
171 GO
172 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'modification date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_table', @level2type=N'COLUMN',@level2name=N'modifi_date'
173 GO
174 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Dictionary Categories' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_type_table', @level2type=N'COLUMN',@level2name=N'dt_type_key'
175 GO
176 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Dictionary Category Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_type_table', @level2type=N'COLUMN',@level2name=N'dt_type_name'
177 GO
178 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Dictionary Category Remarks' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_type_table', @level2type=N'COLUMN',@level2name=N'dt_type_remark'
179 GO
180 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Founder' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_type_table', @level2type=N'COLUMN',@level2name=N'creator_name'
181 GO
182 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation time' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_type_table', @level2type=N'COLUMN',@level2name=N'creator_date'
183 GO
184 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Modifier' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_type_table', @level2type=N'COLUMN',@level2name=N'modifi_name'
185 GO
186 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Modification time' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dictionary_type_table', @level2type=N'COLUMN',@level2name=N'modifi_date'
187 GO
188 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Modular code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'module_code'
189 GO
190 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Module name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'module_name'
191 GO
192 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Parent module code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'parent_code'
193 GO
194 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Module level' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'module_level'
195 GO
196 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Is it menu 1?:Menu 2:Not menus' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'is_menu'
197 GO
198 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Is it menu 1?:Request 2:Not menus' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'is_action'
199 GO
200 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'request url' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'action_url'
201 GO
202 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'sort field' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'order_by'
203 GO
204 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Module State 1:Effective 2:invalid' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'module_status'
205 GO
206 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creator username' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'creator_name'
207 GO
208 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'creator_date'
209 GO
210 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Modify User Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'modifi_name'
211 GO
212 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'modification date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'module_db', @level2type=N'COLUMN',@level2name=N'modifi_date'
213 GO
214 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'role id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'role_module_relation', @level2type=N'COLUMN',@level2name=N'role_id'
215 GO
216 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Modular code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'role_module_relation', @level2type=N'COLUMN',@level2name=N'module_code'
217 GO
218 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Create User Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'role_module_relation', @level2type=N'COLUMN',@level2name=N'creator_name'
219 GO
220 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation time' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'role_module_relation', @level2type=N'COLUMN',@level2name=N'creator_date'
221 GO
Database Code

Source code: https://github.com/linjierd/Jurisdiction

[Download only, not for commercial use]

Topics: ASP.NET Session Database Programming SQL

Hot Topics

©2024 Programmer Think