<jupyter_start><jupyter_text>User Privileges本篇介紹基本的使用者權限，如果你是 DBA 的話，這是必然會面對的議題。本手冊操作一些常用的指令，並且帶入基本的設計概念。- **這份筆記的執行次序需要自行調整**但請特別注意，實際上權限設計，最好能夠**和組織文化相容，才能降低管理的複雜度**。資料庫的使用者權限和其他系統很類似，作為最基礎性的權限規畫方式，良好的使用者權限規畫，可以減少其他細部權限設定的困擾。1. 權限由小到大2. 群組相容人員組織  版權本筆記由 [PostgreSQL 台灣使用者社群](https://postgresql.tw)提供，採 [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/deed.zh_TW) 授權。  開始囉<jupyter_code># 載入 SQL 延伸套件，每次重開筆記都必須再次執行！
%load_ext sql
# 連接你的資料庫系統吧！(最好是測試系統)
# 使用 Binder 的話，請保持下面設定直接連線；否則請依你的資料庫連線資訊調整。
# postgresql://username:password@host/database
%sql postgresql://localhost/postgres
# 看看你的資料庫裡有哪些「role」？
# role 是 PostgreSQL 中的「使用者」或「群組」
%sql SELECT * FROM pg_roles;<jupyter_output><empty_output><jupyter_text>- 有關於 pg_roles 的欄位，請參閱[手冊說明](https://docs.postgresql.tw/internals/system-catalogs/pg_roles)。- role 的介紹在手冊的[第 21 章](https://docs.postgresql.tw/server-administration/user-manag)。<jupyter_code># 如果有很多，我想要只看我自己呢？
# current_user 指的是目前使用者名稱
%sql SELECT * FROM pg_roles WHERE rolname=current_user<jupyter_output><empty_output><jupyter_text>欄位很多，目前只看 rolsuper，瞭解自己是否為「超級使用者」（super user）。和其他系統意義相同，就是可以做到任何事的使用者。代表的危機意義也相同，除了小心使用之外，通常我們也會需要其他權限有所限制的使用者，以免發生人為的疏失。如果你使用 psql，會看到類似這樣的提示字串：```postgres=```和一般 Linux 系統相同，「」代表是系統管理者。  使用者<jupyter_code># 建立使用者
%sql CREATE ROLE alice
# 你可以先回到前面步驟查詢 pg_roles，是否有 alice？<jupyter_output><empty_output><jupyter_text>- [CREATE ROLE](https://docs.postgresql.tw/reference/sql-commands/create-role) 你應該會找到 alice，但無法進行下一步，因為無法登入（rolcanlogin=false），也沒有密碼。通常這樣建立的帳號是用於群組使用。<jupyter_code># 刪除使用者
%sql DROP ROLE alice<jupyter_output><empty_output><jupyter_text>- [DROP ROLE](https://docs.postgresql.tw/reference/sql-commands/drop-role)<jupyter_code># 建立有密碼，可登入的使用者
%sql CREATE ROLE alice WITH LOGIN PASSWORD '<PASSWORD>'
# 現在 alice 有密碼，也可正常登入了，但我想加上使用期限，以免到期時忘記刪除。
%sql ALTER ROLE alice VALID UNTIL '2018-12-31'
# 執行完後你可以再查詢 pg_roles 的 rolvaliduntil 欄位資訊<jupyter_output><empty_output><jupyter_text>- [ALTER ROLE](https://docs.postgresql.tw/reference/sql-commands/alter-role)<jupyter_code># 你也可以調整 alice 所能使用的資源
%sql ALTER ROLE alice SET maintenance_work_mem = 100000;<jupyter_output><empty_output><jupyter_text>權限<jupyter_code># 建立一個測試權限用資料表
%sql CREATE TABLE IF NOT EXISTS test (key text, value text)
# 檢查一下你現在登入的使用者是誰?
%sql SELECT current_user
# 試著把 alice 在資料表 test 的 SELECT 權限取消
# 預設是 public，也就是其他人，可使用該資料表，所以也要取消 (時常會疏忽的部份)
# 重跑此行時要注意是否用管理者帳號登入
%sql REVOKE SELECT ON TABLE test FROME alice, public
# has_table_privilege 可以檢查存取權限
%sql SELECT has_table_privilege('alice', 'test', 'SELECT')
# 再次授予權限
%sql GRANT SELECT ON TABLE test TO alice, public
# 也可以真的使用 alice 登入來試試看下面兩個步驟
%sql postgresql://alice:alicepassword@localhost/postgres
%sql SELECT * FROM test
# alice 只有使用權，而沒有管理權
%sql DROP TABLE test<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12106975063245393, 'fraction_numerical': 0.00795084929526563, 'mean_word_length': 8.885714285714286, 'pattern_counts': {'":': 0, '<': 30, '<?xml version=': 0, '>': 30, 'https://': 7, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '428846', 'license': 'MIT', 'n_tokens_mistral': 1697, 'n_tokens_neox': 1794, 'n_words': 181, 'path': '01_User Privileges.ipynb', 'repo_name': 'pgsql-tw/notebook', 'size': '2770'}