【Metabase】限制/區隔一般 user 的查詢權限

有時，基於工作/職務劃分，公司的資料會進行可視資料權限的控管；例如：行銷部的可以看到有關客戶名單，但不能看業務部有關銷售的業績、人事資料...等，反之亦然。

Metabase 社群版沒有限制使用者可視資料的功能(付費版才有)，也就是說，當系統管理員建立了一個資料庫連線，則所有 Metabase 的一般使用者都能看到該連線的所有 table。

不過我們有變通做法 -- 從資料庫分別設定幾個限制權限的帳號，本筆記以 MySQL8 做範例，例如建立帳號 user1(代表業務部)，只授予 user1 能查詢檢視 table1，建立帳號 user2(代表行銷部)，只能查詢檢視 table2。

以 root 帳號登入 phpMyAdmin 或 登入 Linux 開一個命令列視窗然後建立一個 MySQL 連線，本例示範在 phpMyAdmin 操作

 
-- 建立使用者
CREATE  USER  'user1'@'localhost' IDENTIFIED BY '密碼';

-- 授予 USER1 查詢資料庫 db1 的 table1 的權限
GRANT  SELECT  ON  db1.table1  TO  'user1'@'localhost';

-- 刷新權限(選擇性命令, 通常會自動生效)
FLUSH  PRIVILEGES;
 

驗證：登出 phpMyAdmin，以 user1 登入，就能看到 user1 只能看到 db1 的 table1 的資料。

以管理員帳號登入 Metabase → 設定 → 管理員設定

資料庫 → 新增資料庫(以 MySQL8 為例)

輸入在前面為行銷建立的帳號 user1，並在【顯示名稱】輸入 sales(Linuxmint22.2 的 MySQL8 設定安裝可參考相關筆記)，重複步驟建立 user2，【顯示名稱】輸入 marketing。

到【權限管理】→【所有使用者群組】，這個群組預設是可查詢所有資料庫的，而且會高過其他群組的設定，所以要把 marketing、sales 的【建立提問】取消

設定業務部只能查詢 sales 資料庫

而行銷部只能查詢 marketing 資料庫

---

相關筆記 ----

LinuxMint22.2，設定為開機即啟動 Metabase 服務

【MySQL8】匯入大容量 .sql 檔

phpMyAdmin 只能匯入容量 2M 以內的 .sql 檔，若要匯入大容量 .sql，則需要以命令列操作

 
# 切換到放置 .sql 檔的目錄下,
# 本例中, aa.sql 置於桌面
user$  cd  桌面

# 進入 su 模式
user$  sudo  su

# 匯入檔案
root#  mysql  -u  root  -p  資料庫名 < aa.sql
 

【Database】LinuxMint 22.2 安裝設定 MySQL 8.0 + phpMyadmin

參考資料 ----

ALTER USER Statement

* 這篇筆記是從 Mint Linux 20.3 安裝設定 MySQL 8.0 + phpMyadmin 那篇更新過來的，所以只要能理解、看得懂，部份截圖就沿用 LinuxMint20 的了。😇

VMWare Workstation 開放個人免費使用了！😄

新建一台 VMWare Workstation 虛擬機

偷懶，這次仍是安裝 MySQL，作業系統為 LinuxMint 22.2 MATE, MySQL 8.0。

安裝完成後，設定 "更新管理員" 的 "軟體來源" -- 鏡像站改為國內 國家高速運算中心(https://www.nchc.org.tw/)。

安裝 vim(Mint 預設已裝 vi，這個適合 80 鍵鍵盤，老人家用不慣 😓)

 
~$  sudo  apt  install  vim
 

暫時關閉防火牆

 
~$  sudo  ufw  disable

# 確認防火牆沒有啟動
~$  sudo  ufw  status
 

安裝 MySQL，Mint 會連帶安裝其他相關的套件

 
~$  sudo  apt  install  mysql-server
 

安裝完成後，MySQL server 應該會自行啟動執行

 
# 下述 3 個指令只是順手筆記, 不執行

# 查詢 MySQL 狀態
~$  sudo  systemctl  status  mysql

# 啟動 mysql
~$  sudo  systemctl  start  mysql

# 停止 mysql
~$  sudo  systemctl  stop  mysql
 

安裝後，進行安全性設定

 
~$  sudo  mysql_secure_installation
 

問答式設定，依指示回答，以下是老人家的設定，請依自己需求設定

是否要設定密碼強度規則?

密碼強度選擇 -- 1：中等 -- 8 個字碼以上，其中必須 至少 1 個大寫英文 + 至少 1 個小寫英文 + 至少 1 個數字 + 至少 1 個特殊字元

補充說明：密碼強度２(最強) -- 8 個字碼以上，除了必須滿足中等強度外 + 不能有容易被聯想的字(ex: P@ssw0rd → 可聯想到 password)，本來我想設最強，但嚐試了好幾次，都被判定不符合強度2 的規定，只好退而求其次，改選強度 1。

是否移除匿名使用者：我選 y

是否 "不允許" MySQL 的 root 從遠端登入 MySQL：我選 y

是否移除 test 資料庫(因為稍後設定 MySQL 時要指定 phpMyAdmin 啟動時預設連線的資料庫，所以先留著，不刪)

重新載入權限資料表：我選 y

設定完成

如果重新執行 mysql_secure_installation，上述的步驟會略過一開始的【設定密碼強度】，若想完整重新執行 mysql_secure_installation，則需要先移除密碼強度套件 component_validate_password

 
# 剛安裝好 MySQL 時，預設的管理者帳號是 root，所以, 第 1 次以命令形式登入 MySQL，也不需要密碼
~$ sudo  mysql

# 進到 MySQL, 提示字元變成 >
-- SQL 指令沒有大/小寫分別，不過慣例上都會以大寫表示
-- (記得要加 分號 做為命令的結束)

-- 查詢已安裝的元件
> SELECT * FROM mysql.component;
+--------------+--------------------+------------------------------------+
| component_id | component_group_id | component_urn                      |
+--------------+--------------------+------------------------------------+
|            1 |                  1 | file://component_validate_password |
+--------------+--------------------+------------------------------------+
1 row in set (0.00 sec)

-- 刪除密碼強度套件
> UNINSTALL COMPONENT 'file://component_validate_password';

-- 退出 mysql
> EXIT
 

設定 MySQL 時區為台北(Taipei)

 
~$  sudo  vim  /etc/mysql/mysql.conf.d/mysqld.cnf

[mydqld]
...
...
default-time-zone = '+8:00'

:x 存檔離開
 

 
# 再次登入 MySQL
~$  sudo  mysql


-- 建立資料庫(順手筆記, 不執行)
>  CREATE  資料庫名稱;


-- 列出目前 MySQL 已建立的使用者帳號(順手筆記, 不執行)
>  SELECT user FROM mysql.user;


-- 新建一個使用者 admin, 給後續安裝的 phpMyAdmin
>  CREATE USER 'admin'@'localhost' IDENTIFIED  WITH  caching_sha2_password  BY '密碼';
-- 預設的密碼格式為 caching_sha2_password
-- 其他格式有：mysql_native_password, sha256_password, ...
-- 不過官方還是推薦 caching_sha2_password


-- 賦予 admin 最高權限
>  GRANT  ALL  PRIVILEGES  ON  *.*  TO  'admin'@'localhost' WITH GRANT OPTION;

>  exit
 

注意：

MySQL 自 8.0 起預設不再支援 MyISAM 了

安裝 phpmyadmin

 
~$  sudo  apt  install  phpmyadmin

# 若安裝過程有失誤 或 不滿意, 想重新跑設定步驟, 可執行
~$  sudo dpkg-reconfigure phpmyadmin
 

因為這版的設定步驟跟之前不同，所以先選 <否>

再執行 dpkg-reconfigure phpmyadmin 重新設定

phpmyadmin 的帳戶改為我們在前面建立的 admin@localhost

打開本機的瀏覽器(預設是火狐Firefox)，在網址列輸 入 http://localhost/phpmyadmin ， 注意：不是 https

目前，我們還沒有啟用(加密)安全連線，也無法從別的電腦訪問這個內網

為 MySQL 的 root 帳戶加上密碼

 
~$  sudo  mysql

-- SQL 指令沒有大/小寫分別，不過慣例上都會以大寫表示
-- (記得要加 分號 做為命令的結束)
>  ALTER  USER  'root'@'localhost'  IDENTIFIED  WITH  CACHING_SHA2_PASSWORD  BY  '新密碼';

-- 退出，密碼才能生效
> EXIT


-- 從此之後登入 MySQL 就會要求您輸入密碼
~$  sudo  mysql  -u  root  -p
# 參數 -u 是要登入的使用者帳號
# -p 是稍後要輸入密碼
# 上面的指令按下 ENTER 後, 系統就會提示您要輸入密碼


# 另一種登入指令, 但不建議使用, 因為是將密碼以明碼方式顯示
#　注意: -p 跟密碼間沒有空格
# 但這麼一來, 您的密碼就顯示在命令列中, 旁人一覽無遺了
~$  sudo  mysql  -u  root  -p密碼


# 登入 MySQL 後, 提示字元變成 >
-- 檢視 root 帳戶
> SELECT  user,  host,  plugin,  authentication_string  FROM  mysql.user  WHERE  user='root';
+------+-----------+-----------------------+------------------------------------------------------------------------+
| user | host      | plugin                | authentication_string                                                  |
+------+-----------+-----------------------+------------------------------------------------------------------------+
| root | localhost | caching_sha2_password | $A$005$__HLm.}1*#ZzXhae2kc4K7Cvfg7TR0UxCxBKY2y2Ji9qieVGaXzMWL4Fn9 |
+------+-----------+-----------------------+------------------------------------------------------------------------+
1 row in set (0.00 sec)
 

【MySQL】國定假日應用

參考資料 ----

中華民國政府行政機關辦公日曆表

自 政府資料開放平台 下載下來的檔案格式為 csv，內容依序為【西元日期】(格式為 yyyymmdd)、【星期】(國字)、【是否放假】(0=上班, 2=放假)、【備註】 4 個欄位，以逗點【,】分隔。自 01/01 ~ 12/31 一天一列。

比照這個 csv，在資料庫(我採用 MySQL) 中建一個 table，不過我的 table 有多一個【年度】欄位，並將【是否放假】欄位改名為【休假】。

我需要的只有放假日，也就是【休假】欄位值 = 2，因此先透過 phpMyAdmin 存入資料庫，再將【平日】刪除。

 
-- csv 最前面補加 西元年度欄位

-- 將 csv 上傳存入 holiday_tmp
-- holiday 和 holiday_tmp 差別只在 holiday 有 autoincrement 的 primary key

-- 刪除平日, 只留假日
DELETE FROM holiday_tmp
    WHERE 休假=0

-- 存入 holiday
INSERT INTO holiday
(年度,日期,星期,休假,備註)
SELECT * FROM holiday_tmp

-- 清空 holiday_tmp
DELETE FROM holiday_tmp
 

【SQLite】以預設值新增一筆記錄

當 CREATE TABLE 的欄位定義有預設值，或無預設值(NULL) 且未限制 NOT NULL 時，可使用下面指令。

 
INSERT  INTO  資料表名  DEFAULT  VALUES;
 

【MSSQLserver】日期的運算(適 MSSQL2016(含)後版本)

參考資料 ----

DATEPART (Transact-SQL)

EOMONTH (Transact-SQL)

DATEFROMPARTS (Transact-SQL)

DATEADD (Transact-SQL)

 
-- 假設給予日期：2024-2-2(五)


-- 求得 2024-2-2 是當年的第幾週
SELECT DATEPART(week, '2024-2-2')    -- 第 5 週


-- 求得 2024-2-2 是星期幾
SELECT DATEPART(weekday, '2024-2-2')    -- 星期五


-- 當月首日：2024-2-1
-- 方法一
-- EOMONTH 求得上個月的最末日
-- DATEADD 再運算加 1 日
SELECT DATEADD(day, 1, EOMONTH('2024-2-2',-1))

-- 方法二
SELECT DATEFROMPARTS(YEAR('2024-2-2'),MONTH('2024-2-2'),1)


-- 當月最末日：2024-2-29
SELECT EOMONTH('2024-2-2')


-- 當年首日：2024-1-1
SELECT DATEFROMPARTS(YEAR('2024-2-2'),1,1)


-- 當年最末日：2024-12-31
SELECT DATEFROMPARTS(YEAR('2024-2-2'), 12, 31)
 

【Metabase】LinuxMint20 上，社群版(Community) 將系統資料移至大型資料庫 MySQL8

參考資料 ----

Migrating to a production application database

Configuring the Metabase application database

注意：這篇筆記的環境為 LinuxMint20 + MySQL8，LinuxMint22 環境請參考
LinuxMint22.2上，社群版(Community) 將系統資料移至大型資料庫 MySQL8

Metabase 自帶的系統資料庫為 H2，僅用於測試試用期間，若要轉為正式應用(production)，官方強烈建議將系統資料移至大型資料庫。

建議版本為 MySQL 5.7.7(含) 或 MariaDB 10.2.2(含) 或 PostgreSQL 9.4(含) 以上；本筆記為 MySQL 8.0。

注意：避免同時做 Metabase 更新升級 及 資料庫移植

關閉 Metabase，目前我的練功主機是開終端機視窗，在家目錄以

 
[user]~$ java  -jar  metabase.jar
 

的指令啟動 Metabase

所以只要在瀏覽器的 Metabase 頁面登出，然後在終端機視窗 Ctrl+C 中斷 Metabase 即可。

備份 H2︰複製 metabase.db.mv.db 到另一個安全的目錄存放。

操作 phpMyAdmin

在 MySQL 新增 metabase 帳戶

注意：建議密碼自已設定，而不要由 phpMyAdmin 幫您產生，以免密碼中不知道有哪個字元踩到了雷!!目前已知的有 【@】、【:】、【/】、【?】、【#】，會造成 java 錯誤解析 URI 字串，若是一定要用時，則在組連線字串時，須將上述字元改成 URI 編碼，後面再進一步說明。

建立 metabase 空白資料庫，要求字集選擇 utf8mb4_unicode_ci(下圖是舊圖, 請忽略)。

授予 metabase 帳戶對 metabase 資料庫具有全部的權限

執行移植指令

 
# 切換到 metabase.jar 所在目錄, 在本例為 [user]~/metabase/
[user]~/metabase$  export MB_DB_TYPE=mysql
[user]~/metabase$  export MB_DB_CONNECTION_URI="jdbc:mysql://主機IP:3306/metabase?user=metabase帳戶&password=密碼"
[user]~/metabase$  java  -DMB_DB_TYPE=mysql  -DMB_DB_CONNECTION_URI="jdbc:mysql://主機IP:3306/metabase?user=帳號&password=密碼;"  -jar  metabase.jar  load-from-h2  metabase.db    # 注意檔名
 

前面提到，如果密碼非得要用那幾個特殊字元，則在組 java 的 JDBC 連線字串時，要改成 URI 編碼，如下：
@ → %40
: → %3A
/ → %2F
? → %3F
# → %23

因為有備份 H2，所以可以放心刪除 metabase 所在目錄下的 H2 檔案，以確認 metabase 存取的是 MySQL。

重新啟動 metabase

 
[user]~/metabase$  export MB_DB_TYPE=mysql
[user]~/metabase$  export MB_DB_CONNECTION_URI="jdbc:mysql://主機IP:3306/metabase?user=metabase帳戶&password=密碼"
[user]~/metabase$  java  -DMB_DB_TYPE=mysql  -DMB_DB_CONNECTION_URI="jdbc:mysql://主機IP:3306/metabase?user=metabase帳號&password=密碼"  -jar  metabase.jar
 

重登入 metabase，可以看到之前製作的儀表板都在。

---

相關筆記 ----

設定 Metabase 為開機即啟動服務

Mint Linux 20.3 安裝設定 MySQL 8.0 + phpMyadmin

【Oracle】查詢指定月份的天數

重點是這個語法不論平年、閏年都可查詢得知(而閏年的 2 月有 29 天)。

 

 
SELECT EXTRACT(day FROM LAST_DAY(date'2016-02-01')) AS day_count FROM dual;
 

【Metabase】LinuxMint20.3 安裝設定 免費、開源的 BI -- Metabase

參考資料 ----

Working with MySQL in Metabase

Metabase 是一套 BI(Business Intelligence)，標榜/訴求 主要使用者為 行銷人員，操作直覺簡易，不需有 資料庫 或 數據分析 技能。

可選擇付費的雲端服務，跟 Tablau 等知名、大牌的 BI 相比，價格親民的多；或是在自己公司內部架設伺服主機，免費版本為社群版，Metabase 有提供論壇，讓使用者在論壇尋求支援或解答。

之前接觸 Tablau，覺得 Tablau 雖然功能強大，但也需要有資料庫(尤其是 SQL 語法) 基礎以上的觀念才較能駕馭；而除非公司本身的性質就是與數據分析有關，員工才可能都有概念，不然操作 BI 大多是一般行銷人員，不見得能完全發揮 Tablau 的功能。

系統需求：Java8，目前版本為：0.44.6(不過下面的截圖是 0.41 的)

在 /家目錄/ 建立一個目錄給 Metabase

 
~$  cd ~
~$  mkdir  metabase
 

直接下載 jar

執行 Metabase 最簡單的方式：

開一個終端機視窗, 切換到 metabase 目錄，執行 Metabase

 
~$ cd  ~
~$ java  -jar  metabase.jar    #(注意要含副檔名)
 

然後就會看到一長串 Metabase 執行的訊息。

打開瀏覽器, 輸入本機網址, 預設的 port 為 3000

ex：http://localhost:3000(不是 https)

第一次執行，會出現設定畫面

按一下 "Let's get started" 鈕

選擇語言

設定 Metabase 系統管理員

設定連線資料庫，目前 Metabase 幾乎支援主流資料庫，不過社群版不支援連線 Oracle

輸入資料庫的連線設定，這裡以 MSSQL 為例

設定完成

此時回到 Metabase 目錄看到有新產生了幾個檔案，其中有副檔名為 .db 的 metabase.db.mv.db ，這是 Metabase 系統自己的 H2 資料庫，存放 Metabase 的系統資訊，例如您建立了哪些儀表板、圖表...等；之後若想讓 Metabase 在正式主機上運行，Metabase 建議自 H2 移植到 MySQL、MariaDB 或 Postgres 這幾個資料庫上。

也因如此，若您把 Metabase 玩壞了 或 玩一段時日後想重來，只要把這個檔案刪除，一切就重頭開始了。

稍微瞄一下，Metabase 內建的圖表有下圖幾種

Metabase 目前不支援多資料庫同時呈現在一個儀表板上

v0.33(含) 之後的版本有支援 多資料表 join 查詢，也支援 SQL 語法

雖然 MySQL/MariaDB 系出同源，不過 Metabase 預設支援 MariaDB 的連線方式，若要連接 MySQL，需做微調設定，登入的加密方式改為 mysql_native_password。

 
# 登入 MySQL
~$  sudo  mysql  -u  root  -p

# 變更 Metabase 連線 MySQL 的密碼加密方式
>  ALTER  USER 'admin'@'%'  IDENTIFIED  WITH  mysql_native_password  BY  '密碼';
 

即使您目前沒有可連線的資料庫也不打緊，Metabase 自帶了一個 Sample 資料庫可讓您玩，官網上的說明文件也是以 Sample 資料庫做為範例。

---

相關筆記 ----

【Metabase】社群版(Community) 將系統資料移至大型資料庫

設定 Metabase 為開機即啟動服務

Mint Linux 20.3 安裝設定 MySQL 8.0 + phpMyadmin

【Database】Mint Linux 20.3 安裝設定 MySQL 8.0 + phpMyadmin

參考資料 ----

ALTER USER Statement

看似 Ubuntu/Mint 家族並未棄用 MySQL，採 Mariadb/MySQL 並存的方式；偷懶，這次仍是安裝 MySQL，作業系統為 LinuxMint 20.3 MATE， 預設的版本為 MySQL 8.0。

新建一台 VirtualBox 虛擬機

安裝 Guest Additions 讓 虛擬機的螢幕 自動調整客體顯示大小

安裝完成後，設定 "更新管理員" 的 "軟體來源" -- 鏡像站改為國內 國家高速運算中心(https://www.nchc.org.tw/)。

安裝 vim(Mint 預設已裝 vi，這適合 80 鍵鍵盤，老人家用不慣 😓)

 
~$  sudo  apt  install  vim
 

暫時關閉防火牆

 
~$  sudo  ufw  disable

# 確認防火牆沒有啟動
~$  sudo  ufw  status
 

安裝 MySQL，Mint 會連帶安裝其他相關的套件

 
~$  sudo  apt  install  mysql-server
 

安裝完成後，MySQL server 應該會自行啟動執行

 
# 查詢 MySQL 狀態
~$  sudo  systemctl  status  mysql

# 啟動 mysql
~$  sudo  systemctl  start  mysql

# 停止 mysql
~$  sudo  systemctl  stop  mysql
 

安裝後，進行安全性設定

 
~$  sudo  mysql_secure_installation
 

問答式設定，依指示回答，以下是老人家的設定，請依自己需求設定

是否要設定密碼強度規則?

密碼強度選擇 -- ２：最強 -- 8 個字碼以上，其中必須 至少 1 個大寫英文 + 至少 1 個小寫英文 + 至少 1 個數字 + 至少 1 個特殊字元

輸入 MySQL 的 root 密碼(MySQL 也是以 "root" 這個名稱做管理者，不過這裡設定的不是 Linux 的 root 密碼)

是否要剛才設的密碼即刻生效?

是否移除匿名使用者

是否 "不允許" MySQL 的 root 從遠端登入 MySQL

是否移除 test 資料庫(因為稍後設定 MySQL 時要指定 phpMyAdmin 啟動時預設連線的資料庫，所以先留著，不刪)

重新載入權限資料表

設定完成

設定 MySQL 時區

 
~$  sudo  vim  /etc/mysql/mysql.conf.d/mysqld.cnf

[mydqld]
default-time-zone = '+8:00'

:x 存檔離開
 

剛安裝好 MySQL 時，預設的管理者帳號是 root，所以, 第 1 次以命令形式登入 MySQL，也不需要密碼

 
~$  sudo  mysql

-- SQL 指令沒有大/小寫分別，不過慣例上都會以大寫表示
-- (記得要加 分號 做為命令的結束)
>  ALTER  USER  'root'@'localhost'  IDENTIFIED  WITH  CACHING_SHA2_PASSWORD  BY  '新密碼';

-- 退出，密碼才能生效
> EXIT
 

因為先前的步驟我們指定了密碼強度為 2，所以密碼若不符合這個規則就會出錯

再重新登入就會要求輸入密碼了

 
~$  sudo  mysql  -u  root  -p
# 隨後會要求您輸入密碼
# 參數 -u 是要登入的使用者帳號
# -p 是稍後要輸入密碼


# 另一種登入指令, 但不建議使用, 因為是將密碼以明碼方式顯示
#　注意: -p 跟密碼間沒有空格, 但這麼一來, 您的密碼就顯示在命令列中, 旁人一覽無遺了
~$  sudo  mysql  -u  root  -p密碼


# 登入後, 命令提示字元會變成 >

-- 建立資料庫 
>  CREATE  資料庫名稱;


-- 列出目前 MySQL 已建立的使用者帳號
>  SELECT user FROM mysql.user;


-- 新建一個使用者 admin
>  CREATE USER 'admin'@'localhost' IDENTIFIED  WITH  caching_sha2_password  BY '密碼';
-- 預設的密碼格式為 caching_sha2_password
-- 其他格式有：mysql_native_password, sha256_password


-- 賦予 admin 最高權限
>  GRANT  ALL  PRIVILEGES  ON  * . *  TO  'admin'@'localhost';

>  exit
 

注意：

MySQL 自 8.0 起預設不再支援 MyISAM 了

MySQL 預設的資料庫管理工具是 MySQL Workbench，不過在 Mint 20.3 執行時，會出現不支援 Mint 的訊息，而且我才玩了一下下就掛了!!

安裝 phpmyadmin

 
~$  sudo  apt  install  phpmyadmin

# Mint 會自動幫您安裝 apache2
 

打開本機的瀏覽器(預設是火狐Firefox)，在網址列輸入 http://localhost，注意：不是 https

目前，我們還沒有啟用(加密)安全連線，也無法從別的電腦訪問這個內網

而 phpMyadmin 的網址為

http://localhost/phpmyadmin