SQL SERVER系統或使用者資料庫物件

Posted on 2016-05-02 Edited on 2023-11-24 In SQL SERVER Views: Disqus:

SQL SERVER系統或使用者資料庫物件都是由物件組成

一、使用者資料庫物件

物件	說明
資料庫圖表	使用圖形方式來顯示關聯表綱要
資料表	即關聯表(Relations)
檢視	即視界(Views)的虛擬關聯表
同義字	替本機或遠端伺服器的資料庫物件建立別名
可程式性	一些可程式化的相關物件
儲存體	全文檢索目錄、資料分割配置和函數等相關物件
安全性	安全性管理的相關物件

1、同義字

請先往下參考「連結的伺服器」的範例再來學習如何使用「同義字」功能會比較有感覺

由於每次查詢都要輸入一大串名稱，所以想使用同義字功能來幫一個名稱取一個別名，

承「連結的伺服器」的範例，我們在ORDER_DB資料庫的Synonyms物件新增一個別名，

如下圖第一部份為指定別名，第二部份為填入原本想被替換的資料表名稱與相關資訊

現在「[27.105.53.188].vip_db.dbo.VIP」名稱同義於「VIP」名稱了。

2、可程式性物件下面又可分

物件	說明
預存程序	將例行、常用和複雜的資料庫操作預先建立Transact-SQL指令敘述集合，這是在資料庫管理系統執行的指令敘述集合，可以簡化相關或重複的資料庫操作
函數	將一或多個Transact-SQL指令敘述建立成函數，以便能夠重複使用這些函數
資料庫觸發程序	一種特殊用途的預存程序，屬於主動執行的程序，不像預存程序是使用者執行，它是當資料表操作符合特定條件時，就自動執行觸發程序
規則	設定與CHECK條件相同功能的檢查條件，同一個規則物件可以套用在多個資料表的不同欄位
預設值	定義欄位的預設值，同一個預設物件可以套用在多個資料表的不同欄位
順序	SQL Server資料庫物件，可以根據建立順序時指定的開始值、增量和結束值來產生數值序列，即流水號

「規則」與「預設值」物件在SQL Server 2008就已經不建議使用，

SQL Server2014仍然看的到，也只是為了與舊版SQL Server相容。

3、安全性

物件	說明
使用者	資料庫的使用者，即允許存取資料庫的使用者清單
角色	角色是將使用資料庫的權限以扮演的角色來進行分類，我們可以直接使用角色快速指定使用者的權限
結構描述	結構描述(Schema)可以替資料庫物件新增分類名稱，換句話說，SQL Server資料庫物件名稱的全名是「結構描述.物件名稱」
非對稱金鑰	資料加密建立的非對稱金鑰
憑證	資料加密建立的憑證
對稱金鑰	資料加密建立的對稱金鑰

二、系統資料庫物件

1、連結的伺服器

此功能主要可以讓你存取遠端伺服器的資料，接下來就來看如何操作

(1)先準備測試環境

被連結的伺服器IP：27.105.53.188

被連結的資料表

CREATE TABLE VIP 
  ( 
     name NVARCHAR(10) NOT NULL,
     level CHAR(8), 
     PRIMARY KEY (name) 
  ) 

go

INSERT [VIP] VALUES ('Bill','金級會員');
INSERT [VIP] VALUES ('Mary','商業會員');

本機裡的資料表

CREATE TABLE [ORDER] 
  ( 
     no      INT NOT NULL, 
     name    [NVARCHAR](10) NOT NULL, 
     year    SMALLINT NOT NULL, 
     quarter TINYINT NOT NULL, 
     item    [NVARCHAR](10) NOT NULL, 
     price   [INT] NOT NULL, 
     amount  [INT] NOT NULL, 
     PRIMARY KEY (no) 
  ) 

go 

INSERT [ORDER] VALUES (1,'Bill',2005,1,'CPU',3000,1);
INSERT [ORDER] VALUES (2,'Bill',2005,2,'RAM',2000,4);
INSERT [ORDER] VALUES (3,'Bill',2006,4,'MB',2000,1);
INSERT [ORDER] VALUES (4,'Mary',2010,1,'SSD',2500,5);
INSERT [ORDER] VALUES (5,'Mary',2010,1,'LCD',6700,1);
INSERT [ORDER] VALUES (6,'Bryan',2015,1,'KB',500,2);
INSERT [ORDER] VALUES (7,'Bryan',2015,3,'ROUTER',1500,2);
INSERT [ORDER] VALUES (8,'Tim',2016,1,'CPU',12000,1);
INSERT [ORDER] VALUES (9,'Tim',2016,4,'MB',5000,1);

(1)、於「Linked Server」按右鍵新增一個連結伺服器

(2)、當然也可以連結其他形態的伺服器，但現在我們以連結遠端伺服器為例子

(3)、設定登入遠端資料庫伺服器的安全性

SQL SERVER對遠端資料庫伺服器的安全性處理，可分為以下兩大類

a、本機伺服器登入與遠端伺服器登入對應

在SQL SERVER中的帳號稱為「登入」(login)，

此處可以加入本機「登入」對應到遠端伺服器另一個「登入」的對應關係。

例如上圖中上半部說明

(a)、「本機登入」為dale對應到「遠端使用者」andy及密碼為****：

也就是當本機使用dale帳號登入時，即可同時利用遠端的andy帳號及密碼登入至遠端伺服器。

(b)、若是勾選其中的「模擬」選項，則會用本機的「登入」和「密碼」模擬成遠端的使用者和密碼來進行登入；

所以右側的「遠端使用者」如「遠端密碼」就不用且不能輸入。

因此要使用「模擬」的方式，必須本機和遠端必須都有相同的「登入」以及相同的「密碼」才行。例如圖中的andy。

b、如果不是上述清單所定義的登入，可將連結設定為：

(a)、「不建立」，表示未列於上方對應清單中的「登入」皆不得存取遠端伺服器。

(b)、「不使用安全性內容建立」，表示未列於上方對應清單中的「登入」，

皆不使用安全性內容存取遠端伺服器，因此僅可以存取未設安全性控制的資料庫，例如：Access。

(c)、「使用登入的目前安全性內容建立」，表示未列於上方對應清單中的「登入」，

皆會嘗試以本機的登入和密碼來登入遠端伺服器。

倘若遠端伺服器內沒有相同的登入與密碼，則無法進行存取。

(d)、「使用此安全性內容建立」，直接透過所輸入的「遠端登入」和「指定密碼」

來存取遠端伺服器；也就是說，直接使用遠端的帳號與密碼來登入。

此例我們直接使用遠端的帳號與密碼來登入。

下面為已設定好的應用結果。

SELECT no,name,year,quarter,item,price,amount 
FROM   [ORDER] 

SELECT name,level 
FROM   [27.105.53.188].vip_db.dbo.VIP 

SELECT * 
FROM   [ORDER] 
WHERE  NAME IN(SELECT NAME 
               FROM   [27.105.53.188].vip_db.dbo.VIP)