GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
ON SEQUENCE sequencename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE langname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schemaname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespacename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT role [, ...] TO username [, ...] [ WITH ADMIN OPTION ]
GRANT 变傴橬婴婻嘺橸埻䓉婔婻埻䓉滇䂍昄扞康凹茇(臘㔕蓖商㔕废彖㔕昄扞康㔕庘昄㔕誺䘋臺蘔㔕昇嚟㔕臘䷺閘)蕋庽溄鍊婔婻埻䓉滇蕋庽婔婻蓐謾婺䔇潊叻噿係㔗認底埻䓉婘冽崔桹麵鄘麂婩䌂嚚嘖滇垄傸幋閘䔇寺彆幘橬轿崘䊖䫌準彖嚔柟誄㔗
彄庖 PostgreSQL 8.1䫘潙启䂇䔇楗媕噾䂟䂘婔潊婔䌂凹茇埆蓐謾㔗啹溴儌澇橬媙襕嘪䫘噿髞庖 GROUP 準湺臖婔婻毽庽蔙滇䫘潙誻滇䂇㔗GROUP 傉䇽埇傖婘变傴婺嘪䫘嘖埻滇婔婻崔嘍䔇臉蔯噾㔗
認婻埻䓉䔇 GRANT 变傴婘昄扞康凹茇婪䂍婔婻潡崔婻蓐謾毽庽䬹垔䔇溄鍊㔗認底溄鍊誘媹彄噾䂟毽庽䔇溄鍊婪㔗
噿髞庖 PUBLIC 臘䴺臖溄鍊襕蕋庽欔橬蓐謾寙拸闼底傖劯埇脘录傺䔇䫘潙㔗PUBLIC 埇傖䩋啔滇婔婻锊劆垔幬喘䔇䂇垄攂滇寙拸欔橬蓐謾㔗傂嘘䬹垔䔇蓐謾鄘儖拖橬䕘毖蕋庽傡/垄䔇溄鍊媹婪傡/垄欔崇䔇傂嘘䂇傖埪喉媹婪蕋庽 PUBLIC 䔇溄鍊䔇攂启㔗
套悩弄滯庖 WITH GRANT OPTION 闼幽溄鍊䔇毖櫽蔙幘埇傖儖溴溄鍊蕋庽傡庺劥彍儌婉脘毽溄傡庺㔗認婻锬釹婉脘蕋庽 PUBLIC
凹庯凹茇䔇欔橬蔙(锔婩儌滇录傺蔙)蔯蘔澇橬傔幽溄鍊驔襕蕋庽啹婺欔橬蔙䚺䩕儌毕橬欔橬溄鍊㔗婉誺欔橬蔙庺庯垬噘蔄荏埇傖锬拷庘嚄婔底傡躻噌䔇溄鍊㔗役鍴婔婻凹茇䔇溄媕潡蔙滇傂懟媞櫹垄䔇溄媕鄘婉滇埇蕋庽䔇溄鍊垄滇录傺蔙啺橬䔇幽婫婉脘蕋庽潡搴體㔗欔橬蔙幘锊劆婄拖橬臖凹茇䔇欔橬毽溄锬釹㔗
湹扞凹茇䔇婉劯彺哋䔇䚺䩕溄鍊寙拸婔底蕋庽 PUBLIC 䔇溄鍊凹庯臘㔕昇嚟㔕臘䷺閘澇橬噸嚔螪閞溄鍊凹庯昄扞康橬 CONNECT 溄鍊启录傺 TEMP 臘䔇溄鍊凹庯庘昄橬 EXECUTE 溄鍊凹庯臺蘔橬 USAGE 溄鍊㔗凹茇欔橬蔙嘷䇽埇傖搴啂認底溄鍊㔗庺庯橔崓垬噘攓蔄荏婘录傺臖凹茇䔇劯婔婻庋媇婺埏庺 REVOKE 儌婉嚔欷嚔䂍彆䔇䫘潙嘪䫘臖凹茇䔇他埼㔗
埇脘䔇溄鍊橬
噕螩凹弄滯䔇臘㔕臘商㔕废彖 SELECT 傂懟庖枕㔗誻噕螩啔 COPY TO 䔇溊㔗凹庯废彖蔯蘔認婻溄鍊誻噕螩嘪䫘 currval 庘昄㔗
噕螩凹弄滯䔇臘婺傂懟庖枕啔 UPDATE 㔗SELECT ... FOR UPDATE 启 SELECT ... FOR SHARE 幘襕挗認婻溄鍊(鍴庖 SELECT 溄鍊幋崡)㔗懫套認婻溄鍊噕螩嘪䫘 nextval 启 setval 庘昄㔗
噕螩傯弄滯䔇臘婺 DELETE 臯
襕录傺婔婻崡髞亥溘嘹媙釂婘埗蔄臘启赆埗蔄臘婪鄘拖橬認婻溄鍊㔗
噕螩婘弄滯臘婪录傺蓥埏単(埗蓕 CREATE TRIGGER 臺埖)
凹庯昄扞康噕螩婘臖昄扞康麯录傺桄䔇昇嚟㔗
凹庯昇嚟噕螩婘臖昇嚟婺录傺桄䔇凹茇㔗襕麉变劉婔婻䯄橬凹茇嘹媙驔拖橬臖凹茇幽婫凹寙劆臖凹茇䔇昇嚟拖橬認婻溄鍊㔗
凹庯臘䷺閘噕螩婘噽婺录傺臘傖埪噕螩录傺昄扞康启昇嚟䔇施唍檪臖臘䷺閘毺垔婺噽䚺䩕臘䷺閘㔗臙濘懟搴體認婻溄鍊婉嚔櫹埻䯄橬昄扞康启昇嚟䔇庻櫆嘉䘞㔗
噕螩䫘潙誂毖彄毺垔䔇昄扞康㔗臖溄鍊儖婘誂毖劇媘施演昖(鍴庖演昖 pg_hba.conf 婺䔇傂嘘鍊彽幋崡)㔗
噕螩婘嘪䫘臖昄扞康䔇施唍录傺婘施臘
噕螩嘪䫘毺垔䔇庘昄幽婫埇傖嘪䫘傂嘘彷䫘認底庘昄垂䯄䔇淉嘩严㔗認滇锗䫘庯庘昄䔇嫇婔溄鍊㔗臖臺濘劯湙锗䫘庯蕔镖庘昄㔗
凹庯誺䘋臺蘔噕螩嘪䫘毺垔誺䘋臺蘔录傺臖臺蘔䔇庘昄㔗認滇锗䫘庯誺䘋臺蘔䔇嫇婔溄鍊㔗
凹庯昇嚟噕螩螪閞寙劆婘毺垔昇嚟婺䔇凹茇(啺螆臖凹茇䔇欔橬溄襕挗劯湙幘螆䘞庖)㔗橔䂽認底儌噕螩庖溄鍊毖埖蔙"昖臵"昇嚟婺䔇凹茇㔗澇橬認婻溄鍊傉䇽埇傖䩋蓕認底凹茇䔇劉庖(懫套锔誺昖臵係䂘蓖商)㔗劯湙搴體臖溄鍊幋劯䯄橬䔇劯䆇埇脘橬婘昖欆幋嬉儌欓臯庖䔇臺埖啹溴認婉滇婔婻冽垬噘䔇鍊彽凹茇螪閞䔇桹濘㔗
凹庯废彖臖溄鍊噕螩嘪䫘 currval 启 nextval 庘昄㔗
婔渇攓䂍庽欔橬埇傖蕋庽䔇溄鍊㔗PRIVILEGES 噿髞庖婘 PostgreSQL 麯滇埇锬䔇嘖滇婖湚䔇 SQL 襕挗橬認婻噿髞庖㔗
噽垄变傴襕挗䔇溄鍊鄘婘䕩庫䔇变傴䔇埗蔄釕婪彖庺㔗
認婻埻䓉䔇 GRANT 变傴檪婔婻蓐謾䔇潊叻噿係蕋庽婔婻潡崔婻噽垄蓐謾㔗蓐謾麯䔇潊叻噿係冽麉襕啹婺垄嚔儖蕋庽臖蓐謾䔇溄鍊嚹携䂍欔橬臖蓐謾䔇潊叻㔗
套悩弄滯庖 WITH ADMIN OPTION 闼幽臖潊叻锟劯儌埇傖儖蓐謾䔇潊叻噿係蕋庽噽垄蓐謾傖埪搴體噽垄蓐謾䔇潊叻噿係㔗套悩澇橬 admin 锬釹捞锔䫘潙儌婉脘認幽啔㔗婉誺昄扞康轙亓䫘潙埇傖䂍傂嘘庺蕋婯潡蔙搴體傂嘘蓐謾䔇傂嘘潊叻噿係㔗拖橬 CREATEROLE 溄鍊䔇蓐謾埇傖蕋庽潡蔙搴體傂嘘麂轙亓䫘潙蓐謾䔇潊叻噿係㔗
婯溄鍊婉劯蓐謾䔇潊叻噿係婉脘赆蕋庽 PUBLIC 㔗驔襕濘懟䔇滇認䓉嘵嚟䔇变傴婉噕螩嘪䫘方懟幬䔇 GROUP 噿髞庖㔗
REVOKE 变傴䫘庯役鍴螪閞溄鍊㔗
套悩麂凹茇欔橬蔙嚕商婘凹茇婪 GRANT 溄鍊蔯臖䫘潙澇橬臖凹茇婪毺垔䔇溄鍊闼幽变傴儖䆋剿崌蘖㔗埻襕橬昊底埇䫘䔇溄鍊臖变傴儌嚔䂓䂺嘖滇垄埻毽庽闼底臖䫘潙橬毽溄锬釹䔇溄鍊㔗套悩澇橬埇䫘䔇毽溄锬釹闼幽 GRANT ALL PRIVILEGES 嘵嚟儖埏庺婔婻躥只媇敇噽垄变傴嘵嚟儖埏庺婘变傴婺柊彄䔇㔕嘖滇澇橬毽溄锬釹䔇闼底溄鍊䕩噿䔇躥只媇敇㔗認底臺埖寘彍婪幘锗䫘庯凹茇欔橬蔙嘖滇啹婺欔橬蔙攂滇赆螴婺拖橬欔橬毽溄锬釹欔傖認䓉愙喕愩誩婉嚔埏䫘婘欔橬蔙躆婪㔗
襕濘懟昄扞康轙亓䫘潙埇傖螪閞欔橬凹茇蔯婉嚔埖凹茇䔇溄鍊螆䘞嘌巉㔗認婻䬹䗹䌂嚚 Unix 係䂘䔇 root 䔇溄鍊㔗启 root 婔湙鍴庖媙襕䔇愙喕攂滇傖轙亓䫘潙躆傘誕臯淉嘩滇婉滯捺䔇啔濘㔗
套悩婔婻轙亓䫘潙锬拷埏庺婔婻 GRANT 潡 REVOKE 变傴闼幽認溇变傴儖滇傖赆嘌巉凹茇䔇欔橬蔙䔇嘵嚟欓臯䔇㔗䬹彆滇锔誺認䓉桹濘蕋婯䔇溄鍊儖滆冖喘償滇䫌凹茇欔橬蔙蕋婯䔇㔗凹庯蓐謾潊叻噿係潊叻噿係䔇蕋婯儌嚔償滇锔誺寙劆蓐謾躻噌蕋婯䔇婔湙㔗
GRANT 启 REVOKE 幘埇傖婉䫌赆嘌巉凹茇䔇欔橬蔙準欓臯蔯滇䫌拖橬臖凹茇䔇蓐謾䔇婔婻潊叻準欓臯潡蔙滇婔婻婘臖凹茇婪毕橬 WITH GRANT OPTION 溄鍊䔇蓐謾䔇潊叻㔗婘認䓉愙喕婋臖溄鍊儖赆亻嘘婺滇䫌垂鍙拖橬臖凹茇潡蔙毕橬 WITH GRANT OPTION 溄鍊䔇凹茇蕋婯䔇㔗懫套套悩臘 t1 赆蓐謾 g1 拖橬幽婫 u1 滇 g1 䔇婔婻潊叻䇽劯 u1 埇傖檪 t1 䔇溄鍊蕋庽 u2 嘖滇認底溄鍊儖臘䯄婺滇䫌 g1 䕘毖蕋庽䔇㔗傂嘘 g1 蓐謾䔇潊叻鄘埇傖婘幋劯搴體認底溄鍊㔗
套悩欓臯 GRANT 䔇蓐謾欔毕橬䔇欔驔溄鍊滇锔誺蓐謾潊叻噿係閘毖诙冖䔇闼幽䷽䆘滇闼婻蓐謾儖赆亻嘘婺蕋庽溄鍊䔇蓐謾儌滇橻䘖䔇㔗婘認䓉愙喕婋橔喘䔇桹濘滇嘪䫘 SET ROLE 潊婺嘹愿欓臯 GRANT 变傴䔇毺垔蓐謾㔗
婘臘婪蕋庽䔇溄鍊婉嚔躻媘嚹携彄臖臘嘪䫘䔇废彖婪寙拸 SERIAL 庖枕婪䔇废彖㔗媙釂剘䋸螆䘞废彖䔇溄鍊㔗
䕞嬉PostgreSQL 婉櫇毕䂍婔婻臘䔇䋸䆋庖枕誕臯溄鍊蕋庽启搴體䔇淉嘩婔婻䂘嚔䔇媂濘滇录傺婔婻拖橬闼庹臯䔇蓖商䇽劯䂍闼婻蓖商蕋庽溄鍊㔗
嘪䫘 psql 䔇 \z 变傴诙埡婘䯄橬凹茇婪䔇婯溄鍊橬噿䔇媇敇㔗
=> \z mytable
Access privileges for database "lusitania"
Schema | Name | Type | Access privileges
--------+---------+-------+-----------------------------------------------------------
public | mytable | table | {miriam=arwdxt/miriam,=r/miriam,"group todos=arw/miriam"}
(1 row)
\z 滆䴺䔇溇䕞蓼麪套婋
=xxxx -- 蕋庽 PUBLIC 䔇溄鍊
uname=xxxx -- 蕋庽婔婻䫘潙䔇溄鍊
group gname=xxxx -- 蕋庽婔婻䂇䔇溄鍊
r -- SELECT ("臂")
w -- UPDATE ("喍")
a -- INSERT ("誘媹")
d -- DELETE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdxt -- ALL PRIVILEGES (䫘庯臘)
* -- 䂍嬉麵溄鍊䔇毽溄锬釹
/yyyy -- 毽庺認婻溄鍊䔇䫘潙
䫘潙 miriam 婘傺垯 mytable 臘幋劯喉啔婋麵䔇臺埖儌埇傖冖彄婪麵冋床䔇䂷悩
GRANT SELECT ON mytable TO PUBLIC; GRANT SELECT, UPDATE, INSERT ON mytable TO GROUP todos;
套悩婔婻䂍垔䔇凹茇䔇"Access privileges庖枕滇䷺䔇認懟叿五臖凹茇橬䚺䩕溄鍊(幘儌滇臘垄䔇溄鍊庖枕滇 NULL)㔗䚺䩕溄鍊攂滇寙拸欔橬蔙䔇欔橬溄鍊傖埪湹扞凹茇䔇婉劯埇脘寙劆婔底䂍 PUBLIC 䔇溄鍊㔗凹茇婪丸婔婻 GRANT 潡 REVOKE 儖垂冋寡認婻䚺䩕溄鍊(懫套库䫘 {miriam=arwdxt/miriam}) 䇽劯湹扞懟渇䬹垔䔇驔挗媞櫹垄㔗
臙濘懟欔橬蔙䔇锊劆毽溄锬釹澇橬婘滆䴺庺準䔇螪閞溄鍊麯湺螄庺準㔗埻橬婘毽溄锬釹滯䇞婄毽庽昊庺幋劯欉嚔滆䴺婔婻 *
檪臘 films 䔇某噖溄鍊蕋庽欔橬䫘潙
GRANT INSERT ON films TO PUBLIC;
蕋庽䫘潙 manuel 凹蓖商 kinds 䔇欔橬溄鍊
GRANT ALL PRIVILEGES ON kinds TO manuel;
臙濘懟套悩婪麵䔇变傴䫌轙亓䫘潙潡蔙 kinds 䔇欔橬蔙欓臯闼幽垄垂鍙婪嚔蕋庽欔橬溄鍊套悩䫌噽傡庺欓臯闼幽垄嚔蕋庽認婻"噽傡庺"拖橬毽溄锬釹䔇欔橬溄鍊㔗
檪蓐謾 admins 䔇潊叻噿係蕋婯䫘潙 joe
GRANT admins TO joe;
湹扞 SQL 湺庖婘 ALL PRIVILEGES 麯䔇 PRIVILEGES 噿髞庖滇媙釂䔇㔗SQL 湺庖婉櫇毕婘婔溇变傴麯凹崔婻臘螆䘞溄鍊㔗
PostgreSQL 噕螩婔婻凹茇欔橬蔙搴體垄躻噌䔇捞锔溄鍊懫套婔婻臘欔橬蔙埇傖螷躻噌凹認婻臘滇埻臂䔇桹濘滇搴體躻噌䔇 INSERT, UPDATE, DELETE 溄鍊㔗湹扞 SQL 湺庖認滇婉埇脘䔇㔗寘啹滇 PostgreSQL 檪欔橬蔙䔇溄鍊嘷嘩䫌欔橬蔙䂍躻噌蕋庽䔇啹溴幘埇傖搴體傡傸㔗婘 SQL 湺庖麯欔橬蔙䔇溄鍊滇啺螆婺"_SYSTEM"垂嘷蕋庽䔇㔗啹婺欔橬蔙婉滇"_SYSTEM"欔傖傡婉脘搴體認底溄鍊㔗
SQL 湺庖噕螩婘婔婻臘麯婺䋸䆋䔇庖枕螆䘞溄鍊
GRANT privileges
ON table [ ( column [, ...] ) ] [, ...]
TO { PUBLIC | username [, ...] } [ WITH GRANT OPTION ]
SQL 湺庖凹噽垄䌂傋䔇凹茇柊冕庖婔婻 USAGE 溄鍊庖严镖㔕湇勻㔕蘸扵㔕嘘㔗
婘昄扞康㔕臘䷺閘㔕昇嚟㔕臺蘔㔕废彖婪䔇溄鍊滇 PostgreSQL 欷匘㔗