10. 資料庫安全性

1. 建立示範資料庫結構

1.1 資料庫架構

SQL Server 資料庫架構

1.2 使用者操作資料庫架構

使用者操作資料庫架構

1.3 建立示範資料庫

步驟 1. 新增資料庫

步驟 2. 輸入資料庫名稱(erpdb)及路徑(d:\db)並新增

步驟 3. 新增資料庫

步驟 4. 輸入資料庫名稱(crmdb)及路徑(d:\db)並新增

1.4 建立示範資料表及資料

示範資料表語法

DROP TABLE IF EXISTS [dbo].[Users]
GO
 
DROP TABLE IF EXISTS [dbo].[Orders]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderNo] [nvarchar](50) NULL,
	[OrderDate] [date] NULL,
	[CustomerName] [nvarchar](50) NULL,
	[Amounts] [int] NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserNo] [nvarchar](50) NULL,
	[UserNane] [nvarchar](50) NULL,
	[UserId] [nvarchar](50) NULL,
	[GenderNo] [nvarchar](50) NULL,
	[ContactTel] [nvarchar](50) NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
INSERT [dbo].[Orders] ( [OrderNo], [OrderDate], [CustomerName], [Amounts], [Remark]) VALUES (N'O20250101001', CAST(N'2025-01-01' AS Date), N'明新科技', 500000, N'')
INSERT [dbo].[Orders] ( [OrderNo], [OrderDate], [CustomerName], [Amounts], [Remark]) VALUES (N'O20250101002', CAST(N'2025-01-02' AS Date), N'尚華科技', 800000, N'')
GO
 
INSERT [dbo].[Users] ([UserNo], [UserNane], [UserId], [GenderNo], [ContactTel], [Remark]) VALUES (N'U001', N'王大明', N'H128004226', N'M', N'02-31283848', N'')
INSERT [dbo].[Users] ([UserNo], [UserNane], [UserId], [GenderNo], [ContactTel], [Remark]) VALUES (N'U002', N'李小華', N'H238016129', N'M', N'02-91878374', N'')
INSERT [dbo].[Users] ([UserNo], [UserNane], [UserId], [GenderNo], [ContactTel], [Remark]) VALUES (N'U003', N'陳小國', N'H122834662', N'M', N'02-33747756', N'')
GO
 
USE [crmdb]
GO
 
DROP TABLE IF EXISTS [dbo].[Users]
GO
 
DROP TABLE IF EXISTS [dbo].[Orders]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderNo] [nvarchar](50) NULL,
	[OrderDate] [date] NULL,
	[CustomerName] [nvarchar](50) NULL,
	[Amounts] [int] NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserNo] [nvarchar](50) NULL,
	[UserNane] [nvarchar](50) NULL,
	[UserId] [nvarchar](50) NULL,
	[GenderNo] [nvarchar](50) NULL,
	[ContactTel] [nvarchar](50) NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
INSERT [dbo].[Orders] ( [OrderNo], [OrderDate], [CustomerName], [Amounts], [Remark]) VALUES (N'O20250101001', CAST(N'2025-01-01' AS Date), N'明新科技', 500000, N'')
INSERT [dbo].[Orders] ( [OrderNo], [OrderDate], [CustomerName], [Amounts], [Remark]) VALUES (N'O20250101002', CAST(N'2025-01-02' AS Date), N'尚華科技', 800000, N'')
GO
 
INSERT [dbo].[Users] ([UserNo], [UserNane], [UserId], [GenderNo], [ContactTel], [Remark]) VALUES (N'U001', N'王大明', N'H128004226', N'M', N'02-31283848', N'')
INSERT [dbo].[Users] ([UserNo], [UserNane], [UserId], [GenderNo], [ContactTel], [Remark]) VALUES (N'U002', N'李小華', N'H238016129', N'M', N'02-91878374', N'')
INSERT [dbo].[Users] ([UserNo], [UserNane], [UserId], [GenderNo], [ContactTel], [Remark]) VALUES (N'U003', N'陳小國', N'H122834662', N'M', N'02-33747756', N'')
GO

步驟 1. 新增查詢

步驟 2. 執行示範資料表語法

2. 安全性需求說明

2.1 客戶端需求

今有一 客戶X 有兩的資料庫 erpdb 及 crmdb 如下

資料庫列表

資料表結構

範例資料

資訊部門人員結構

姓名	職稱	資料庫帳號	負責資料庫
楊小月	資訊部門經理	sa	全有資料庫
李小同	資訊部門MIS	zoe	erpdb
陳小方	資訊部門MIS	tom	crmdb

2.2 外包端需求

外包端需求說明

• 客戶X 將 ERP 系統的 部份功能 外包給 廠商Y，
• 允許 廠商Y 對 Orders 表格有所有權限，
• 僅允許讀取 Users 表格中的 UserNo 及 UserName 兩個欄位

---

廠商Y人員結構

姓名	職稱	資料庫帳號	資料庫權限
李小天	程式設計師	v001	ERP 資料庫的 Orders 表格有所有權限 及 Users 表格中的 UserNo 及 UserName 兩個欄位

2.3 帳號權限示意圖

帳號權限示意圖

2.4 資料庫帳號權限列表

帳號	使用人員	角色	erpdb權限	crmdb權限	說明
sa	楊小月	sysadmin	dbo (完全控制)	dbo (完全控制)	可管理整台伺服器及所 有資料庫。
zoe	李小娟	public	db_owner (管理者)	無法存取	僅能管理 ERP 資料庫。
tom	方小同	public	無法存取	db_owner (管理者)	僅能管理 CRM 資料庫。
v001	外包廠商 (共用)	public	特定 Views 的 SELECT 權限	無法存取	權限被嚴格限制在 ERP 資料庫的兩個檢視表上。

3. 實作客戶端帳號權限管理

3.1 zoe 帳號

實作 zoe 帳號權限管理

3.2 tom 帳號

實作 tom 帳號權限管理

4. 實作外包端帳號權限管理

4.1 新增 v001 帳號

實作 v001 帳號權限管理

4.2 建立 erpdb 資料庫 vi_users 檢視

實作建立 vi_users 檢視

4.3 設定 vi_users 權限

實作設定 vi_users 權限