æ°æ®åºæ¶æschemaå®ç°äºï¼æ°æ®åºå¯¹è±¡ä¸æ°æ®åºç¨æ·ç´æ¥æ å°çå离ï¼ç¨æ·ä¸æ°æ®åºå¯¹è±¡å¯ä»¥éè¿schemaæ¥æ å°æ§å¶ã
æ°æ®åºå¯¹è±¡ï¼è§å¾ï¼è¡¨ï¼å½æ°çï¼å¯ä»¥å±äºæ个schema.æ°æ®åºç¨æ·å¯ä»¥å±äºæ个schema,表示æ¥ææ个schemaä¸å¯¹è±¡çæéãå¦ææªæå®ï¼æ°æ®åºå¯¹è±¡å±äºæ¶æï¼schema)dbo,saç¨æ·é»è®¤å±äºdboæ¶æï¼dbo:database ownerï¼ãå½ç¨æ·è®¿é®æ°æ®åºå¯¹è±¡æ¶ï¼å¦éç¨server..objectsæ¶é»è®¤å¯»æ¾å¯¹åºschemaä¸çæ°æ®åºå¯¹è±¡ï¼æ¯å¦ç¨æ·tmp_userå±äºschema:tmpï¼åä¸è¿°è®¿é®é»è®¤è½¬åæï¼server.tmp.objects.
å建ç¨æ·ï¼å¹¶æå®ç¨æ·é»è®¤çschema:
PgSQL
CREATE LOGIN [User_tmp] WITH PASSWORD = N'123456'
CREATE USER [User_tmp] FOR LOGIN [User_tmp] WITH DEFAULT_SCHEMA = [tenant]
1
2
CREATE LOGIN [User_tmp] WITH PASSWORD = N'123456'
CREATE USER [User_tmp] FOR LOGIN [User_tmp] WITH DEFAULT_SCHEMA = [tenant]
å建scheme:
PgSQL
CREATE SCHEMA [tenant] AUTHORIZATION [dbo]
1
CREATE SCHEMA [tenant] AUTHORIZATION [dbo]
ç»schema设置æéï¼
PgSQL
GRANT INSERT, SELECT, UPDATE, DELETE, EXECUTE, REFERENCES ON SCHEMA:: [tenant] TO User_tmp
1
GRANT INSERT, SELECT, UPDATE, DELETE, EXECUTE, REFERENCES ON SCHEMA:: [tenant] TO User_tmp
å建带schemaçæ°æ®åºå¯¹è±¡,æ¯å¦å建ä¸ä¸ªè§å¾ï¼
PgSQL
CREATE VIEW tenant.PrinterCookies
WITH SCHEMABINDING
AS
SELECT ticket_no, user_agent, create_date, create_by
FROM dbo.PrinterCookies
GO
1
2
3
4
5
6
CREATE VIEW tenant.PrinterCookies
WITH SCHEMABINDING
AS
SELECT ticket_no, user_agent, create_date, create_by
FROM dbo.PrinterCookies
GO
注æï¼å¸¦WITH SCHEMABINDINGæ è®°æ¶ï¼å¦æ对åºç表åäºä¿®æ¹ï¼å¢å å段é¤å¤ï¼ï¼éè¦å é¤åè§å¾ï¼åå»ä¿®æ¹å¯¹åºçç©ç表ï¼å建带schemaçè§å¾ã
ç¨æ·ç»å½æ¶ï¼åªæ¾ç¤ºä»æå¨schemaä¸çæææ°æ®åºå¯¹è±¡ï¼å¦ä¸è¿°ç¨æ·åªæ¾ç¤ºå¸¦tenantç对象ï¼æ æ³æ¥çå
¶å®dboçæ°æ®åºå¯¹è±¡ãä½tenantçææè
æ¯dboï¼æ以dboä¸å¯ä»¥çå°æætenantç对象ã
åæ ·çï¼å 为ä¹åç»tenantåé
äºæ°æ®åºçå¢ï¼å ï¼æ¹æ¥æéï¼å¯ä»¥å¯¹tenantä¸çæ°æ®å¯¹è±¡ä½ç¸åºçæä½æéã
å½ä½¿ç¨ä¸é¢çSQLè¯å¥æ¶ï¼SELECT * FROM SqlTest..PrinterCookiesï¼å½ç¨ç¨æ·[User_tmp]ç»å½æ¥æ¾æ¶ï¼ä¼å¹é
å°SqlTest.tenant.PrinterCookiesè§å¾å¯¹è±¡ï¼èç¨dboç»å½æ¶ï¼ä¼å¹é
å°SqlTest.dbo.PrinterCookiesç©ç表对象ãå½ç¶ï¼åèªæ¥è¯¢åç°çæ°æ®æ¯ä¸ä¸æ ·çã
综ä¸æè¿°ï¼åºäºschemaçä¸åï¼æ们å¯ä»¥è§çº¦åºå¾å¤ä¸åçæéãåæ¶ï¼æ ¹æ®schemaçé»è®¤å¹é
ï¼å¯å°æ°æ®åºçç©ç表é»è¾å¾ä¸å±éè¿schemaæ¥ç®¡çæ§å¶ãä»è让ä¸åçç»å½ç¨æ·ï¼æå·®å«ç访é®åä¸ä¸ªæ°æ®åºèµæºï¼è¾¾å°
æ°æ®ç®¡ççç®çã