大洋收录简易网管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*/