大洋收录简易网管sql脚本

slshoulu_users.sql

------------------------------------------------
---------------------2013.05.23 V3.0.0----------
-----1、主要包括：建库脚本和创建超级用户--------
------------------------------------------------


CREATE TABLE [dbo].[shiro_users](
  [user_id] 		varchar(36) NOT NULL,
	[name] 			varchar(250) NULL,
	[dyid] 			varchar(250) NULL,
	[dyname] 		varchar(250) NULL,
	[dyemail] 		varchar(250) NULL,
	[workcode] 		varchar(250) NULL,
	[used] 			int NULL,
	[dy20verifyid] 	varchar(250) NULL,
	[dyversion] 	int NULL,
	[password] 		varchar(128) NULL,
	[date_created] 	datetime NULL,
	[date_updated] 	datetime NULL,
	CONSTRAINT PK_shiro_users PRIMARY KEY ([user_id])
)ON [PRIMARY]
GO

CREATE TABLE [dbo].[permissions](
	[perm_id] 		varchar(36) NOT NULL,
	[value] 		varchar(250) NULL,
	[description] 	varchar(500) NULL,
	CONSTRAINT PK_permissions PRIMARY KEY ([perm_id])
	)ON [PRIMARY]
GO

CREATE TABLE [dbo].[notice](
	[id] 			varchar(36) NOT NULL,
	[title] 		varchar(250) NULL,
	[contents] 		varchar(2000) NULL,
	[senderGuid] 	varchar(36) NULL,
	[senderName] 	varchar(128) NULL,
	[dateCreated] 	datetime NULL,
	[lastUpdated] 	datetime NULL,
	CONSTRAINT PK_notice PRIMARY KEY CLUSTERED([id]) 
	)ON [PRIMARY]
GO

CREATE TABLE [dbo].[network_paths](
	[path_id] 		varchar(36) NOT NULL,
	[protocol] 		varchar(32) NULL,
	[host] 			varchar(32) NULL,
	[port] 			varchar(10) NULL,
	[relativePath] 	varchar(255) NULL,
	[username] 		varchar(128) NULL,
	[password] 		varchar(128) NULL,
	[description] 	varchar(500) NULL,
    CONSTRAINT PK_network_paths PRIMARY KEY CLUSTERED([path_id]) 
	)ON [PRIMARY]
GO

CREATE TABLE [dbo].[columns](
	[column_id] 	varchar(36) NOT NULL,
	[name] 			varchar(250) NULL,
	[parent_id] 	varchar(250) NULL,
	[description] 	varchar(500) NULL,
	[type] 			varchar(500) NULL,
	[ftp_id] 		varchar(36) NULL,
	CONSTRAINT PK_columns PRIMARY KEY CLUSTERED([column_id])
	)ON [PRIMARY]
GO

CREATE TABLE [dbo].[roles_permissions](
	[role_id] varchar(36) NOT NULL,
	[perm_id] varchar(36) NOT NULL,
	CONSTRAINT PK_roles_permissions PRIMARY KEY CLUSTERED([perm_id],[role_id])
	)ON [PRIMARY]
GO

CREATE TABLE [dbo].[workflowbeans](
	[workflowbean_id] 	varchar(36) NOT NULL,
	[status] 			varchar(250) NULL,
	[preaction] 		varchar(250) NULL,
	[oldStatus] 		varchar(500) NULL,
	[lastUpdated] 		datetime NULL,
	[startDate] 		datetime NULL,
	[finishDate] 		datetime NULL,
	[dyuserid] 			varchar(250) NULL,
	[flowName] 			varchar(250) NULL,
	[execuser_id] 		varchar(36) NULL,
	CONSTRAINT PK_workflowbean PRIMARY KEY CLUSTERED([workflowbean_id])
	)ON [PRIMARY]
GO	

CREATE TABLE [dbo].[users_roles](
	[user_id] 			varchar(36) NOT NULL,
	[role_id] 			varchar(36) NOT NULL,
	CONSTRAINT PK_users_roles PRIMARY KEY CLUSTERED([role_id],[user_id])
	)ON [PRIMARY]
GO	


CREATE TABLE [dbo].[workflowhistory](
	[workflowhistory_id] 	varchar(36) NOT NULL,
	[action] 				varchar(250) NULL,
	[oldStatus] 			varchar(250) NULL,
	[info] 					varchar(250) NULL,
	[createDate] 			datetime NULL,
	[workflowbean_id] 		varchar(36) NULL,
	[user_id] 				varchar(36) NULL,
	[id] 					varchar(36) NULL,
	[status] 				varchar(250) NULL,
	CONSTRAINT PK_workflowhistory PRIMARY KEY CLUSTERED([workflowhistory_id])
	)ON [PRIMARY]
GO	

CREATE TABLE [dbo].[users_columns](
	[user_id] 			varchar(36) NOT NULL,
	[column_id] 		varchar(36) NOT NULL,
	CONSTRAINT PK_users_columns PRIMARY KEY CLUSTERED([user_id],[column_id])
	)ON [PRIMARY]
GO

CREATE TABLE [dbo].[roles](
	[role_id] 			varchar(36) NOT NULL,
	[name] 				varchar(36) NOT NULL,
	[description]     	varchar(500) NULL,
	CONSTRAINT PK_roles PRIMARY KEY CLUSTERED([role_id])
	)ON [PRIMARY]
GO

--------------创建超级用户------------------------------------- 
INSERT into shiro_users(user_id, name, dyid, dyname, dyemail, workcode, used, dy20verifyid, dyversion, password, date_created, date_updated) VALUES ('83cc1c38-c9d5-4109-93a4-3af621dbb9a4', 'admin', '02D464CB-D510-4F9C-BACE-B3A449895936', 'admin', '', '88888888', 0, NULL, 3, 'admin', NULL, NULL);
--------------创建用户角色-------------------------------------
INSERT into roles (role_id, name, description) VALUES ('06860f95-5299-4299-a252-0e86e4bb9255', 'ptAdmin', '编单超级用户');
INSERT into roles (role_id, name, description) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', 'admin', '后台管理帐户');
INSERT into roles (role_id, name, description) VALUES ('35cc508e-e05d-48e0-8f62-f84cf956f6b3', 'iptAdmin', '具有下载/迁移任务的管理权限');
--------------创建用户权限-------------------------------------
INSERT into permissions (perm_id, value, description) VALUES ('0600411b-7ecd-4161-baf6-84c7edf94314', 'otice:*', '公告管理权限');
INSERT into permissions (perm_id, value, description) VALUES ('0f16e2ff-9892-495b-b693-10aad6524817', 'pretask:audit,rollback', '审核权限');
INSERT into permissions (perm_id, value, description) VALUES ('2c71434a-045e-45c2-8173-b80c255094aa', 'maage:*', '后台管理的所有权限');
INSERT into permissions (perm_id, value, description) VALUES ('407e45a8-2fdc-4bdc-98d2-8fd14a05b72d', 'iptask:audit2', 'IPDowloadTask二级审核');
INSERT into permissions (perm_id, value, description) VALUES ('6e63bb56-1e9d-41ce-8573-1c7a4c44167f', 'pretask:geerate', '生成权限');
INSERT into permissions (perm_id, value, description) VALUES ('74d80916-2ea5-41cf-857d-4f1e676fa50f', 'pretask:fidBy:sposer', '是否允许按提交者查询');
INSERT into permissions (perm_id, value, description) VALUES ('7a732448-8a5d-4255-bde4-3108930dcc4a', 'pretask:remove', '预约单删除权限');
INSERT into permissions (perm_id, value, description) VALUES ('7b8ee00a-a1ee-4d65-aad9-799cc1f9f1cf', 'iptask:rollback', '下载/迁移退回权限');
INSERT into permissions (perm_id, value, description) VALUES ('8cb8367d-4a23-4d69-8933-491d32626608', 'moitor:view', '具有访问监控项目的权限');
INSERT into permissions (perm_id, value, description) VALUES ('ab1a05e4-c6f5-4fad-b8df-710b70da9eb1', 'iptask:*', '下载/迁移任务所有权限');
INSERT into permissions (perm_id, value, description) VALUES ('b78d2ce9-84fe-4508-b76e-27d35a89652b', 'iptask:audit1', 'IPDowloadTask一级审核');
INSERT into permissions (perm_id, value, description) VALUES ('b93421f0-4b3e-4526-9f2f-8b77bcee4372', 'pretask:state', '使用预约编单提交审核流程');
INSERT into permissions (perm_id, value, description) VALUES ('cef63852-6952-4d12-90e3-364ab0e258b5', 'iptask:audit3', 'IPDowloadTask三级审核(终审)');
INSERT into permissions (perm_id, value, description) VALUES ('d7eaf59c-8299-4b4f-9c63-cef76d8aff58', 'pretask:*', '预约单全部权限');
------------------建立用户与角色的关联关系---------------------------------
INSERT into	users_roles (user_id, role_id) VALUES ('83cc1c38-c9d5-4109-93a4-3af621dbb9a4', '28aa1810-b058-42a2-b511-806ad315308e');
------------------建立权限与角色的关联关系---------------------------------
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', '0600411b-7ecd-4161-baf6-84c7edf94314');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', '0f16e2ff-9892-495b-b693-10aad6524817');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', '2c71434a-045e-45c2-8173-b80c255094aa');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', '407e45a8-2fdc-4bdc-98d2-8fd14a05b72d');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', '6e63bb56-1e9d-41ce-8573-1c7a4c44167f');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', '74d80916-2ea5-41cf-857d-4f1e676fa50f');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', '7a732448-8a5d-4255-bde4-3108930dcc4a');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', '7b8ee00a-a1ee-4d65-aad9-799cc1f9f1cf');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', '8cb8367d-4a23-4d69-8933-491d32626608');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', 'ab1a05e4-c6f5-4fad-b8df-710b70da9eb1');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', 'b78d2ce9-84fe-4508-b76e-27d35a89652b');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', 'b93421f0-4b3e-4526-9f2f-8b77bcee4372');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', 'cef63852-6952-4d12-90e3-364ab0e258b5');
INSERT into roles_permissions (role_id, perm_id) VALUES ('28aa1810-b058-42a2-b511-806ad315308e', 'd7eaf59c-8299-4b4f-9c63-cef76d8aff58');

GO

/*12332123123*/