GRANT
å¨å®å
¨ç³»ç»ä¸å建项ç®ï¼ä½¿å½åæ°æ®åºä¸çç¨æ·å¾ä»¥å¤çå½åæ°æ®åºä¸çæ°æ®ææ§è¡ç¹å®ç Transact-SQL è¯å¥ã
è¯æ³
è¯å¥æéï¼
GRANT { ALL | statement [ ,...n ] }
TO security_account [ ,...n ]
对象æéï¼
GRANT
{ ALL [ PRIVILEGES ] | permission [ ,...n ] }
{
[ ( column [ ,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [ ,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
TO security_account [ ,...n ]
[ WITH GRANT OPTION ]
[ AS { group | role } ]
åæ°
ALL
表示æäºææå¯ç¨çæéã对äºè¯å¥æéï¼åªæ sysadmin è§è²æåå¯ä»¥ä½¿ç¨ ALLã对äºå¯¹è±¡æéï¼sysadmin å db_owner è§è²æååæ°æ®åºå¯¹è±¡ææè
é½å¯ä»¥ä½¿ç¨ ALLã
statement
æ¯è¢«æäºæéçè¯å¥ãè¯å¥å表å¯ä»¥å
æ¬ï¼
CREATE DATABASE
CREATE DEFAULT
CREATE FUNCTION
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
BACKUP DATABASE
BACKUP LOG
n
ä¸ä¸ªå ä½ç¬¦ï¼è¡¨ç¤ºæ¤é¡¹å¯å¨éå·åéçå表ä¸éå¤ã
TO
æå®å®å
¨å¸æ·å表ã
security_account
æ¯æéå°åºç¨çå®å
¨å¸æ·ãå®å
¨å¸æ·å¯ä»¥æ¯ï¼
Microsoft® SQL Server™ ç¨æ·ã
SQL Server è§è²ã
Microsoft Windows NT® ç¨æ·ã
Windows NT ç»ã
å½æé被æäºä¸ä¸ª SQL Server ç¨æ·æ Windows NT ç¨æ·å¸æ·ï¼æå®ç security_account æ¯æéè½å½±åå°çå¯ä¸å¸æ·ãè¥æé被æäº SQL Server è§è²æ Windows NT ç»ï¼æéå¯å½±åå°å½åæ°æ®åºä¸è¯¥ç»æ该è§è²æåçææç¨æ·ãè¥ç»æè§è²åå®ä»¬çæåä¹é´åå¨æéå²çªï¼æä¸¥æ ¼çæé (DENY) ä¼å
èµ·ä½ç¨ãsecurity_account å¿
é¡»å¨å½åæ°æ®åºä¸åå¨ï¼ä¸å¯å°æéæäºå
¶å®æ°æ®åºä¸çç¨æ·ãè§è²æç»ï¼é¤é已为该ç¨æ·å¨å½åæ°æ®åºä¸å建æç»äºäºè®¿é®æéã
两个ç¹æ®çå®å
¨å¸æ·å¯ç¨äº GRANT è¯å¥ãæäº public è§è²çæéå¯åºç¨äºæ°æ®åºä¸çææç¨æ·ãæäº guest ç¨æ·çæéå¯ä¸ºææå¨æ°æ®åºä¸æ²¡æç¨æ·å¸æ·çç¨æ·ä½¿ç¨ã
å½æäºæ个 Windows NT æ¬å°ç»æå
¨å±ç»æéæ¶ï¼è¯·æå®å¨å
¶ä¸å®ä¹è¯¥ç»çååæ计ç®æºåï¼ç¶åä¾æ¬¡è¾å
¥åæ线åç»åãä½æ¯ï¼è¥è¦æäºè®¿é® Windows NT å
ç½®æ¬å°ç»çæéï¼è¯·æå® BUILTIN èä¸æ¯ååæ计ç®æºåã
PRIVILEGES
æ¯å¯ä»¥å
å«å¨ç¬¦å SQL-92 æ åçè¯å¥ä¸çå¯éå
³é®åã
permission
æ¯å½åæäºç对象æéãå½å¨è¡¨ã表å¼å½æ°æè§å¾ä¸æäºå¯¹è±¡æéæ¶ï¼æéå表å¯ä»¥å
æ¬è¿äºæéä¸çä¸ä¸ªæå¤ä¸ªï¼SELECTãINSERTãDELETEãREFENENCES æ UPDATEãåå表å¯ä»¥ä¸ SELECT å UPDATE æéä¸èµ·æä¾ãå¦æåå表æªä¸ SELECT å UPDATE æéä¸èµ·æä¾ï¼é£ä¹è¯¥æéåºç¨äºè¡¨ãè§å¾æ表å¼å½æ°ä¸çææåã
å¨åå¨è¿ç¨ä¸æäºç对象æéåªå¯ä»¥å
æ¬ EXECUTEãå¨æ éå¼å½æ°ä¸æäºç对象æéå¯ä»¥å
æ¬ EXECUTE å REFERENCESã
ä¸ºå¨ SELECT è¯å¥ä¸è®¿é®æ个åï¼è¯¥åä¸éè¦æ SELECT æéãä¸ºä½¿ç¨ UPDATE è¯å¥æ´æ°æ个åï¼è¯¥åä¸éè¦æ UPDATE æéã
为å建å¼ç¨æ个表ç FOREIGN KEY 约æï¼è¯¥è¡¨ä¸éè¦æ REFERENCES æéã
为使ç¨å¼ç¨æ个对象ç WITH SCHEMABINDING åå¥å建 FUNCTION æ VIEWï¼è¯¥å¯¹è±¡ä¸éè¦æ REFERENCES æéã
column
æ¯å½åæ°æ®åºä¸æäºæéçååã
table
æ¯å½åæ°æ®åºä¸æäºæéç表åã
view
æ¯å½åæ°æ®åºä¸è¢«æäºæéçè§å¾åã
stored_procedure
æ¯å½åæ°æ®åºä¸æäºæéçåå¨è¿ç¨åã
extended_procedure
æ¯å½åæ°æ®åºä¸æäºæéçæ©å±åå¨è¿ç¨åã
user_defined_function
æ¯å½åæ°æ®åºä¸æäºæéçç¨æ·å®ä¹å½æ°åã
WITH GRANT OPTION
表示ç»äºäº security_account å°æå®ç对象æéæäºå
¶å®å®å
¨å¸æ·çè½åãWITH GRANT OPTION åå¥ä»
对对象æéææã
AS {group | role}
æå½åæ°æ®åºä¸ææ§è¡ GRANT è¯å¥æåçå®å
¨å¸æ·çå¯éåãå½å¯¹è±¡ä¸çæé被æäºä¸ä¸ªç»æè§è²æ¶ä½¿ç¨ ASï¼å¯¹è±¡æééè¦è¿ä¸æ¥æäºä¸æ¯ç»æè§è²çæåçç¨æ·ãå 为åªæç¨æ·ï¼èä¸æ¯ç»æè§è²ï¼å¯æ§è¡ GRANT è¯å¥ï¼ç»æè§è²çç¹å®æåæäºç»æè§è²æåä¹ä¸ç对象çæéã
注é
ä¸å
许æè·¨æ°æ®åºæéï¼åªè½å°å½åæ°æ®åºä¸ç对象åè¯å¥çæéæäºå½åæ°æ®åºä¸çç¨æ·ãå¦æç¨æ·éè¦å¦ä¸ä¸ªæ°æ®åºä¸ç对象çæéï¼è¯·å¨è¯¥æ°æ®åºä¸å建ç¨æ·å¸æ·ï¼æè
ææç¨æ·å¸æ·è®¿é®è¯¥æ°æ®åºä»¥åå½åæ°æ®åºã
说æ ç³»ç»åå¨è¿ç¨æ¯ä¾å¤ï¼å 为 EXECUTE æéå·²ç»æäº public è§è²ï¼å
许任ä½äººå»æ§è¡ãä½æ¯å¨æ§è¡ç³»ç»åå¨è¿ç¨åï¼å°æ£æ¥ç¨æ·çè§è²æåèµæ ¼ãå¦ææ¤ç¨æ·ä¸æ¯è¿è¡æ¤åå¨è¿ç¨æéè¦çéå½çåºå®æå¡å¨ææ°æ®åºè§è²çæåï¼åæ¤åå¨è¿ç¨ä¸ä¼ç»§ç»æ§è¡ã
REVOKE è¯å¥å¯ç¨äºå é¤å·²æäºçæéï¼DENY è¯å¥å¯ç¨äºé²æ¢ç¨æ·éè¿ GRANT è¯å¥è·å¾æéç»ä»ä»¬çç¨æ·å¸æ·ã
æäºæéå é¤ææäºçº§å«ï¼ç¨æ·ãç»æè§è²ï¼ä¸çå·²æç»æéæå·²åºé¤æéãå¨å¦ä¸çº§å«ï¼è¯¸å¦å
å«æ¤ç¨æ·çç»æè§è²ï¼ä¸è¢«æç»çåä¸æéä¼å
èµ·ä½ç¨ãä½æ¯ï¼è½ç¶å¨å¦ä¸çº§å«ä¸æåºé¤çåä¸æéä»ç¶éç¨ï¼ä½å®å¹¶ä¸é»æ¢ç¨æ·è®¿é®è¯¥å¯¹è±¡ã
å¦æç¨æ·æ¿æ´»åºç¨ç¨åºè§è²ï¼å¯¹æ¤ç¨æ·éè¿è¯¥åºç¨ç¨åºè§è²è®¿é®çä»»ä½å¯¹è±¡ï¼GRANT çä½ç¨ä¸ºç©ºãå æ¤ï¼å°½ç®¡ä¸ä¸ªç¨æ·å¯è½è¢«æäºäºå¯¹å½åæ°æ®åºä¸çæå®å¯¹è±¡ç访é®æéï¼ä½æ¯å¦ææ¤ç¨æ·ä½¿ç¨å¯¹æ¤å¯¹è±¡æ 访é®æéçåºç¨ç¨åºè§è²ï¼åå¨åºç¨ç¨åºè§è²æ¿æ´»æé´ï¼æ¤ç¨æ·ä¹æ²¡ææ¤å¯¹è±¡ç访é®æéã
sp_helprotect ç³»ç»åå¨è¿ç¨æ¥åå¨æ°æ®åºå¯¹è±¡æç¨æ·ä¸çæéã
æé
GRANT æéä¾èµäºææäºçè¯å¥æéåæéä¸æ¶åç对象ãsysadmin è§è²ä¸çæåå¯å¨ä»»ä½æ°æ®åºä¸æäºä»»ä½æéã对象ææè
å¯ä¸ºä»ä»¬ææ¥æç对象æäºæéãdb_owner æ db_securityadmin è§è²çæåå¯æäºå
¶æ°æ®åºä¸ä»»ä½è¯å¥æ对象ä¸çä»»ä½æéã
éè¦æéçè¯å¥æ¯é£äºå¨æ°æ®åºä¸å¢å 对象ï¼æ对æ°æ®åºæ§è¡ç®¡çæ´»å¨çè¯å¥ãæ¯æ¡éè¦æéçè¯å¥é½æä¸ä¸ªç¹å®çè§è²éï¼èªå¨ææéæ§è¡æ¤è¯å¥ãä¾å¦ï¼sysadminãdb_owner å db_ddladmin è§è²çæåé»è®¤æCREATE TABLE æéãsysadmin å db_owner è§è²ä»¥å表çææè
é»è®¤æ对表æ§è¡ SELECT è¯å¥çæéã
æä¸äº Transact-SQL è¯å¥ä¸è½è¢«æäºæéï¼æ§è¡è¿äºè¯å¥è¦æ±æåºå®è§è²ä¸çæåèµæ ¼ï¼æ¤è§è²æé»ç¤ºæ§è¡ç¹æ®è¯å¥çæéãä¾å¦ï¼è¥è¦æ§è¡ SHUTDOWN è¯å¥ï¼ç¨æ·å¿
须添å 为 serveradmin è§è²ä¸çæåã
dbcreatorãprocessadminãsecurityadmin å serveradmin åºå®æå¡å¨è§è²çæåä»
æææ§è¡ä»¥ä¸ Transact-SQL è¯å¥ã
è¯å¥ dbcreator processadmin securityadmin serveradmin bulkadmin
ALTER DATABASE X
CREATE DATABASE X
BULK INSERT X
DBCC X (1)
DENY X (2)
GRANT X (2)
KILL X
RECONFIGURE X
RESTORE X
REVOKE X (2)
SHUTDOWN X
(1) æå
³æ´å¤ä¿¡æ¯ï¼è¯·åè§ DBCC è¯å¥ã
(2) ä»
éç¨äº CREATE DATABASE è¯å¥ã
说æ diskadmin å setupadmin åºå®æå¡å¨è§è²çæå没ææéæ§è¡ä»»ä½ Transact-SQL è¯å¥ï¼ä»ä»¬åªè½æ§è¡ç¹å®çç³»ç»åå¨è¿ç¨ãä½æ¯ï¼sysadmin åºå®æå¡å¨è§è²çæåææéæ§è¡ææç Transact-SQL è¯å¥ã
ä¸é¢çåºå®æ°æ®åºè§è²çæåææéæ§è¡æå®ç Transact-SQL è¯å¥ã
è¯å¥
db_owner
db_datareader
db_datawriter
db_ddladmin db_backupoperator db_securityadmin
ALTER DATABASE X X
ALTER FUNCTION X X
ALTER PROCEDURE X X
ALTER TABLE X (1) X
ALTER TRIGGER X X
ALTER VIEW X (1) X
BACKUP X X
CHECKPOINT X X
CREATE DEFAULT X X
CREATE FUNCTION X X
CREATE INDEX X (1) X
CREATE PROCEDURE X X
CREATE RULE X X
CREATE TABLE X X
CREATE TRIGGER X (1) X
CREATE VIEW X X
DBCC X X (2)
DELETE X (1) X
DENY X X
DENY on object X
DROP X (1) X
EXECUTE X (1)
GRANT X X
GRANT on object X (1)
INSERT X (1) X
READTEXT X (1) X
REFERENCES X (1) X
RESTORE X
REVOKE X X
REVOKE on object X (1)
SELECT X (1) X
SETUSER X
TRUNCATE TABLE X (1) X
UPDATE X (1) X
UPDATE STATISTICS X (1)
UPDATETEXT X (1) X
WRITETEXT X (1) X
(1) æéä¹éç¨äºå¯¹è±¡ææè
ã
(2) æå
³æ´å¤ä¿¡æ¯ï¼è¯·åè§ DBCC è¯å¥ã
说æ db_accessadmin åºå®æ°æ®åºè§è²çæå没ææ§è¡ä»»ä½ Transact-SQL è¯å¥çæéï¼åªå¯æ§è¡ç¹å®çç³»ç»åå¨è¿ç¨ã
ä¸éè¦æéå³å¯æ§è¡ç Transact-SQL è¯å¥æï¼å·²èªå¨æäº publicï¼:
BEGIN TRANSACTION COMMIT TRANSACTION
PRINT RAISERROR
ROLLBACK TRANSACTION SAVE TRANSACTION
SET
æå
³æ§è¡ç³»ç»åå¨è¿ç¨æéæéçæ´å¤ä¿¡æ¯ï¼è¯·åè§ç¸åºçç³»ç»åå¨è¿ç¨ã
示ä¾
A. æäºè¯å¥æé
ä¸é¢ç示ä¾ç»ç¨æ· Mary å John 以å Windows NT ç» Corporate\BobJ æäºå¤ä¸ªè¯å¥æéã
GRANT CREATE DATABASE, CREATE TABLE
TO Mary, John, [Corporate\BobJ]
B. å¨æéå±æ¬¡ä¸æäºå¯¹è±¡æé
ä¸ä¾æ¾ç¤ºæéçä¼å
顺åºãé¦å
ï¼ç» public è§è²æäº SELECT æéãç¶åï¼å°ç¹å®çæéæäºç¨æ· MaryãJohn å Tomãäºæ¯è¿äºç¨æ·å°±æäºå¯¹ authors 表çæææéã
USE pubs
GO
GRANT SELECT
ON authors
TO public
GO
GRANT INSERT, UPDATE, DELETE
ON authors
TO Mary, John, Tom
GO
C. ç» SQL Server è§è²æäºæé
ä¸é¢ç示ä¾å° CREATE TABLE æéæäº Accounting è§è²çæææåã
GRANT CREATE TABLE TO Accounting
D. ç¨ AS é项æäºæé
ç¨æ· Jean æ¥æ表 Plan_DataãJean å°è¡¨ Plan_Data ç SELECT æéæäº Accounting è§è²ï¼æå® WITH GRANT OPTION åå¥ï¼ãç¨æ· Jill æ¯ Accounting çæåï¼ä»è¦å°è¡¨ Plan_Data ä¸ç SELECT æéæäºç¨æ· Jackï¼Jack ä¸æ¯ Accounting çæåã
å 为对表 Plan_Data ç¨ GRANT è¯å¥æäºå
¶ä»ç¨æ· SELECT æéçæéæ¯æäº Accounting è§è²èä¸æ¯æ¾å¼å°æäº Jillï¼ä¸è½å 为已æäº Accounting è§è²ä¸æå该æéï¼è使 Jill è½å¤æäºè¡¨çæéãJill å¿
é¡»ç¨ AS åå¥æ¥è·å¾ Accounting è§è²çæäºæéã
/* User Jean */
GRANT SELECT ON Plan_Data TO Accounting WITH GRANT OPTION
/* User Jill */
GRANT SELECT ON Plan_Data TO Jack AS Accounting
温馨提示:答案为网友推荐,仅供参考