はーい、よーいスタート(小声)
ご猥拶
めんどくさいことしたくないからRPA始めたのにパスワード管理でめんどくさいことし始めているRTA、はーじまーるよー。
今回走っていくのはRSAで暗号化したパスワードをRPAで利用するためのシステム作りです。
みんな大好きWinActorは国産RPAツールとして導入率ナンバーワンとして人気を博しています。
しかし、WinActorはRPA(Robotic Process Automation)というよりはRDA(Robotic Desktop Automation)としての趣が強く、各個人のタスクを自動化するには十分ですが、導入から時間が立ち、全社展開など大規模化してきたときに管理能力の無さが目立ちます。
そこで今回はWinActorの控えめに言ってｳﾝﾁｰｺﾝｸﾞちょっと足りない管理能力を補うため、パスワードの登録・更新・取得を行うためのシステムを自作したいと思います。
もっとも、WinActor以外の主要5大RPAソフトウェアはパスワードの保管方法を標準で持ってるんですけどね、初見さん。
参考：【RPA】ログイン情報の保管方法/ツール別比較
前置き
WinActorには変数にマスクをかけて外部から見えないようにする機能があり、これで見えてほしくない値を管理してくださいと言ってます。
これ幸いと、ログイン処理を必要とする業務を自動化するように依頼されたときに
- パスワードを意味する変数を作成、初期値を設定してマスクをかける
- 外部から見えないし、変数一覧を出力しても内容は出てこない
- パスワード更新時は初期値を再度設定する
- WinActor変数の初期値に直接入力するため、WinActorの編集権限を持つ人にパスワードを教える
- ログイン失敗時は例外処理でユーザーあてにパスワードを更新するようメール送信する
これでパスワード管理、ヨシ！ とされた方、1年後に「どうしてあの時ヨシ！って言ったんですか？」となるかもしれません。
各社の管理形態にも依りますが、弊社ではこのようなことが起こりました。
- パスワードが期限切れになり、ログイン処理が失敗する
- ユーザーが更新期限間近になったパスワードを更新するが、RPAで利用しているパスワードが更新されないため失敗する
- ログイン失敗したら通知する仕組みにしていたが、そもそもユーザー側がパスワード管理に気をつけないと必ず失敗する仕組みにダメ出しされる
- 複数のRPAシナリオでそれぞれパスワード変数を持っているので、同一ユーザーのパスワードを複数のシナリオで使っていたときに修正漏れが発生する
- パスワードを変更するときに現場から口頭またはメールで教えてもらうため、管理者が不当にパスワードを知ってしまうことが問題視された
だから以下の要件をすべて満たすパスワード管理ソフトを用意する必要があったんですね。
- パスワードをユーザーが管理者の手を借りず外部から変えられるようにする
- 平文で保存せず、暗号化した状態で保存する
- ユーザーに見えない場所からRPAツールが復号したパスワードを取得できる
- 更新時期が近づいたらパスワードを更新するようユーザーに促す
開発環境
OpenSSL
RSA暗号鍵を作成するアプリ？です。
コマンドラインでopensslと入力し、以下のメッセージが表示される場合はWebからインストーラをダウンロードしましょう。
Win32/Win64 OpenSSL
openssl
'openssl' は、内部コマンドまたは外部コマンド、
操作可能なプログラムまたはバッチ ファイルとして認識されていません。
Visual Studio
RSA暗号鍵を利用してパスワードの暗号化・復号を行うアプリケーションを作成します。
インストール方法や使い方は腐るほどページがあります。ググりましょう。
DB(Microsoft SQL Server)
管理できるならAccessでもExcelでも、最悪csvでも良いです。
ストアドプロシージャや制約を使える利便性を考えればRDBを使うのが一番だと思います。
レギュレーション
古の作法に則り、暗号化・復号を行うアプリ君と、更新時期警告アプリ君に名前をつけた時点から始めます。
入力速度を考慮してhomo.exe,les.exe,yajuu.exeでもいいですが、そんなことしたら保守する人に怒られちゃうだろ！となるのでわかりやすい名前にしましょう。
- RPA_Password_Uploader.exe
- ユーザーが自身の社員番号、システム名、平文のパスワードを入力する
- アプリケーションがパスワードを暗号化し、DBにアップロードする
- ユーザーが利用するため、公開領域にフォームアプリケーションで作りましょう。
- RPA_Password_Getter.exe
- 社員番号、システム名を引数にDBから暗号文を取得し、復号したパスワードを取得する
- フォームである必要はありません。コンソールアプリケーションで作りましょう。
- ユーザーから見えず、RPAツールのみアクセスできる領域に置きましょう。
- RPA_Password_Alerter.exe
- DBから更新時期が近づいたパスワードを取得し、ユーザーにメールで警告する
- タスクスケジューラで毎日実行させるのでコンソールアプリケーション１択です。
タイマーストップはアプリケーションのコーディングが終了し、以下の要件をすべて満たすシステムが完成したところです。
- パスワードをユーザーが管理者の手を借りず外部から変えられるようにする
- 平文で保存せず、暗号化した状態で保存する
- ユーザーに見えない場所からRPAツールが復号したパスワードを取得できる
- 更新時期が近づいたらパスワードを更新するようユーザーに促す
計測開始
それでは計測開始です。
秘密鍵、公開鍵の作成
先駆者様が詳しく説明して頂いてます。是非LGTMしましょう。
C#でRSA暗号を使って署名や暗号化する
コマンドラインに以下を打ち込み、2048bitの秘密鍵を作り、それを参照して公開鍵を作ります。
$ openssl genrsa -out private-key 2048
$ openssl rsa -in private-key -pubout -out public-key
これによりprivate-key.pemとpublic-key.pemができました。
.pemは見慣れない拡張子だと思いますが、これをメモ帳で開くと以下の形のテキストファイルになっています。
-----BEGIN RSA PRIVATE(PUBLIC) KEY-----
(Base64エンコード)
-----END RSA PRIVATE KEY-----
このBase64エンコードされた文字列が暗号鍵になっており、これをDER(バイナリー形式)に変換して利用します。
先駆者様はC#でPEMファイルをBase64デコードして、DER(バイナリー形式)にするチャートを採用していますが、私は初めからPEMをDERに変換した状態でコーディングするチャートにしました。
試走ではPEMファイルを使うチャートでしたが、PEMをそのままの内容で読み込むとエラーとなります(環境によるかもしれませんが)。
詳しくはこちらをご覧ください。私はこれで4時間溶かすガバをしました。
OpenSSLで作成したRSA暗号鍵をC#で読み込む方法 4. 実行結果
上記のようにPEMファイルのまま利用するにはbyte変換コードを加える必要があるのですが、今回のレギュレーションはPEM縛りではないのでDERに遠慮なく変換します。
$ openssl rsa -in private-key.pem -out private-key.der -outform der
$ openssl rsa -pubin -in public-key.pem -out public-key.der -outform der
Uploader, Getterの暗号化・復号部分の作成
テスト用プロジェクトを作成し、秘密鍵private-key.derと公開鍵public-key.derをリソースファイルにぶち込んで、暗号化と復号が問題なく行えることを確認しましょう。
リソースファイルは名前をそれぞれprivate_key,public_keyに設定し、FileTypeにBinaryを指定します。
以下のコードを打ち込みます。
using System;
using System.IO;
using System.Text;
using System.Security.Cryptography;
namespace Crypto_Test
{
class Program
{
static void Main(string[] args)
{
Console.Write("契約書だよ。ここにパスワードを書きな。 > ");
string PlaneText = Console.ReadLine();
Console.WriteLine($"PlaneText: {PlaneText}");
byte[] PlaneByte = Encoding.UTF8.GetBytes(PlaneText);
byte[] CipherByte = Encrypt(PlaneByte);
string CipherText = Convert.ToBase64String(CipherByte);
Console.WriteLine($"CipherText: {CipherText}");
byte[] EncodingByte = Convert.FromBase64String(CipherText);
byte[] DecryptByte = Decrypt(EncodingByte);
string DecryptText = Encoding.UTF8.GetString(DecryptByte);
Console.WriteLine($"DecryptText:{DecryptText}");
Console.ReadLine();
}
/// <summary>復号</summary>
/// <param name="encrypt">暗号化されたデータ</param>
public static byte[] Decrypt(byte[] encrypt)
{
var provider = new RSACryptoServiceProvider();
provider.ImportParameters(CreateParameter(Properties.Resources.private_key));
return provider.Decrypt(encrypt, false);
}
/// <summary>暗号化</summary>
/// <param name="data">暗号元データ</param>
public static byte[] Encrypt(byte[] data)
{
var provider = new RSACryptoServiceProvider();
provider.ImportParameters(CreatePublicParameter(Properties.Resources.public_key));
return provider.Encrypt(data, false);
}
private static RSAParameters CreateParameter(byte[] der)
{
byte[] sequence = null;
using (var reader = new BinaryReader(new MemoryStream(der)))
{
sequence = Read(reader);
}
var parameters = new RSAParameters();
using (var reader = new BinaryReader(new MemoryStream(sequence)))
{
Read(reader); // version
parameters.Modulus = Read(reader);
parameters.Exponent = Read(reader);
parameters.D = Read(reader);
parameters.P = Read(reader);
parameters.Q = Read(reader);
parameters.DP = Read(reader);
parameters.DQ = Read(reader);
parameters.InverseQ = Read(reader);
}
return parameters;
}
private static RSAParameters CreatePublicParameter(byte[] der)
{
byte[] sequence1 = null;
using (var reader = new BinaryReader(new MemoryStream(der)))
{
sequence1 = Read(reader);
}
byte[] sequence2 = null;
using (var reader = new BinaryReader(new MemoryStream(sequence1)))
{
Read(reader); // sequence
sequence2 = Read(reader); // bit string
}
byte[] sequence3 = null;
using (var reader = new BinaryReader(new MemoryStream(sequence2)))
{
sequence3 = Read(reader); // sequence
}
var parameters = new RSAParameters();
using (var reader = new BinaryReader(new MemoryStream(sequence3)))
{
parameters.Modulus = Read(reader); // モジュラス
parameters.Exponent = Read(reader); // 公開指数
}
return parameters;
}
private static byte[] Read(BinaryReader reader)
{
// tag
reader.ReadByte();
// length
int length = 0;
byte b = reader.ReadByte();
if ((b & 0x80) == 0x80) // length が128 octet以上
{
int n = b & 0x7F;
byte[] buf = new byte[] { 0x00, 0x00, 0x00, 0x00 };
for (var i = n - 1; i >= 0; --i)
buf[i] = reader.ReadByte();
length = BitConverter.ToInt32(buf, 0);
}
else // length が 127 octet以下
{
length = b;
}
// value
if (length == 0)
return new byte[0];
byte first = reader.ReadByte();
if (first == 0x00) length -= 1; // 最上位byteが0x00の場合は、除いておく
else reader.BaseStream.Seek(-1, SeekOrigin.Current); // 1byte 読んじゃったので、streamの位置を戻しておく
return reader.ReadBytes(length);
}
}
}
契約書だよ。ここにパスワードを書きな。 > ii4koi4
PlaneText: ii4koi4
CipherText: dgYcFYeyKRz......
DecryptText:ii4koi4
これでPlaneTextがCipherTextを経由してDecryptTextになっていることを確認できました。
あとは暗号化部分のコードとpublic-keyをRPA_Password_Uploaderに、復号コードとprivate-keyをRPA_Password_Decrypterに移植すれば工事完了です...
安定を取ってテスト用プロジェクトを挟みましたが、再送するならここがタイム短縮ポイントになると思います。
SQL ServerのDB構築
SQL Serverの構築方法も公式ドキュメントが充実しています。読みましょう。
データベースを作る
RPA管理者と現場の担当者の両方がアクセスできるサーバーにデータベース[rpa_db]を作ります。
ローカルで作ってテストしてから移すのが安パイです。
テーブルを作る
以下のマスタを作ります。
- m_system(RPAで利用するシステム一覧)
- id
- name
- interval_month(パスワード更新間隔)
- m_person(人員一覧)
- id
- name
- smtp_address
- t_password(登録されたパスワード一覧)
- id
- person_id
- system_id
- cipher_text
- updated_at(更新日時)
今回のパスワード管理で最低限必要なテーブルです。
これらをJOINしたビュー(v_password)を作っておくと後々の開発に役立ちます。
あとは任意で以下の設定をするとより強固になります。
- created_at(作成日時), updated_at(更新日時)の追加
- いつから使われているか
- PK(Primary Key), UK(Unique Key)の設定
- idにPKを設定する
- m_systemに同名のシステムが登録されないようにする
- t_passwordでperson_idとsystem_idの組み合わせが被らないようにする
- FK(Foreign Key)の設定
- 人員一覧にないperson_idやシステム一覧にないsystem_idが登録されないようにする
新しいデータベースダイアグラムから作ると関係がわかりやすいです。
ストアドプロシージャを作る
@person_id, @system_id, @cipher_textを引数に、t_passwordにパスワードを新規登録、または更新するストアドプロシージャを作ります。
このストアドプロシージャの振る舞いは、
- t_passwordに@person_idと@system_idの組み合わせが
- 存在しない場合 -> INSERT
- 存在する場合 -> UPDATE
であることが求められるため、MERGE構文を使いましょう。
CREATE PROCEDURE [dbo].[s_update_password]
@person_id INT,
@system_id INT,
@password VARCHAR(MAX)
AS
BEGIN
MERGE
INTO dbo.t_password DA
USING (
SELECT
@person_id person_id
, @system_id system_id
, @password cipher_text
) w_param
ON (
DA.person_id = w_param.person_id
AND DA.system_id = w_param.system_id
) WHEN MATCHED THEN UPDATE
SET
cipher_text = w_param.cipher_text
, updated_at = GETDATE() WHEN NOT MATCHED THEN
INSERT (
person_id
, system_id
, cipher_text
, created_at
, updated_at
)
VALUES (
w_param.person_id
, w_param.system_id
, w_param.cipher_text
, GETDATE()
, GETDATE()
)
;
END
Uploader, GetterのSQL Server対応
ここまでくれば後は消化試合です。
Uploaderは社員番号、システム名、パスワードを入力してもらい、パスワードを暗号化してs_update_passwordを起動するフォームアプリに、
Getterは社員番号、システム名を引数にパスワードを復号するコンソールアプリにしましょう。
実際のコードは自分で打って確かめてくれよな！！
自分で作ったはいいものの、主要な部分だけを抜き出すのが難しいコードになってしまい、コードを載せることができませんでした。ﾕﾙｼﾃ･･･
Alerter.exeの作成
c#でメールを送信する場合、.NETのSmtpClientを利用する記事は多いですが、SmtpClientは廃棄予定らしいです。
NuGetでMailKitをインストールし、MailKitからメール配信を行います。
今回は本文の装飾にもこだわり、HTMLで文字の強調やリンクを付けるようにしました。
using MailKit.Net.Smtp;
using MailKit.Security;
using MimeKit;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.IO;
namespace RPA_Password_Alerter
{
class MailManager
{
private string From { get; set; } = "RPA担当者@hoge.com";
public string[] To { get; set; } = new string[] { "RPA担当者@hoge.com" }; // Main内で指定するが、nullにならないよう初期化しておく
public string[] Cc { get; set; }
public string Subject { get; set; } = "自動送信";
public HtmlBuilder HtmlBody { get; set; } = new HtmlBuilder();
public string[] Attatchments { get; set; } = new string[] { };
private string Host { get; set; } = "hogehoge.net";
private int Port { get; set; } = 25;
static MailManager()
{
ServicePointManager.ServerCertificateValidationCallback += (sender, cert, chain, sslPolicyErrors) => true;
}
public async Task SendEmailAsync()
{
MimeMessage mime = new MimeMessage();
mime.From.Add(new MailboxAddress("", From));
this.To.ToList().ForEach(v => mime.To.Add(new MailboxAddress("", v)));
this.Cc?.ToList().ForEach(v => mime.Cc.Add(new MailboxAddress("", v)));
mime.Subject = this.Subject;
Multipart multipart = new Multipart("mixed")
{
new TextPart(MimeKit.Text.TextFormat.Html) { Text = this.HtmlBody.HtmlString }
};
foreach (var item in Attatchments)
{
multipart.Add(new MimePart()
{
Content = new MimeContent(File.OpenRead(item)),
ContentDisposition = new ContentDisposition(),
ContentTransferEncoding = ContentEncoding.Base64,
FileName = Path.GetFileName(item)
});
}
mime.Body = multipart;
using (var client = new SmtpClient())
{
await client.ConnectAsync(this.Host, this.Port, SecureSocketOptions.StartTls);
await client.SendAsync(mime);
await client.DisconnectAsync(true);
}
}
public class HtmlBuilder
{
StringBuilder builder = new StringBuilder();
public void AddStyle(string v) => builder.AppendLine("<style>" + v + "</style>");
public void AddParagraph(string v) => builder.AppendLine("<p>" + v + "</p>");
public void AddLink(string displayname, string path) => AddParagraph($"<a href=\"{path}\">{displayname}</a>");
public string HtmlString => builder.ToString();
}
}
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
namespace RPA_Password_Alerter
{
class Program
{
static void Main(string[] args)
{
MainAsync().Wait();
}
private static async Task MainAsync()
{
foreach (var item in SqlData.data.Where(d => d.ExpireDate.AddDays(-14) < DateTime.Now))
{
MailManager mail = new MailManager()
{
To = new string[] { item.PrimarySmtpAddress },
Cc = new string[] { "RPA担当者@hoge.com" },
Subject = $"[{item.SystemName}]のパスワードを更新してください"
};
mail.HtmlBody.AddParagraph($"{item.PersonName} さん");
mail.HtmlBody.AddParagraph($"RPAで利用している[{item.SystemName}]のパスワードの有効期限が残り<strong>{(item.ExpireDate - DateTime.Now).Days}日</strong>です。");
mail.HtmlBody.AddParagraph($"<strong>{item.ExpireDate.ToShortDateString()}までに</strong>下記リンクからパスワードの更新を実施してください。");
mail.HtmlBody.AddLink("パスワード更新フォーム", Properties.Resources.Password_Updater); // UploaderのPath
mail.HtmlBody.AddParagraph($"※ 本メールは前回のパスワード更新({item.UpdateDate})から{item.IntervalMonth}ヵ月後に訪れるパスワード有効期限の2週間前から発信されます。");
mail.HtmlBody.AddParagraph($"※ パスワード更新後、メールは配信されなくなります。");
await mail.SendEmailAsync();
}
}
}
static class SqlData
{
public static List<Schema> data = new List<Schema>();
private static DataTable dataTable { get; set; } = new DataTable();
private static SqlConnectionStringBuilder stringBuilder => new SqlConnectionStringBuilder() { DataSource = "localhost", InitialCatalog = "RPA_DB", UserID = "sa", Password = "hogefuga" };
static SqlData()
{
using (SqlConnection connection = new SqlConnection(stringBuilder.ConnectionString))
{
connection.Open();
using(SqlCommand command = new SqlCommand("SELECT [system_name], [person_name], [updated_at], [interval_month], [alert_date], [smtp_address] FROM [v_password]", connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(dataTable);
}
}
}
dataTable.AsEnumerable().ToList().ForEach(d => data.Add(new Schema(d)));
}
public class Schema
{
public string SystemName { get; set; }
public string PersonName { get; set; }
public string PrimarySmtpAddress { get; set; }
public readonly DateTime UpdateDate;
public readonly int IntervalMonth;
public readonly DateTime ExpireDate;
public Schema(DataRow dataRow)
{
try
{
SystemName = dataRow["system_name"].ToString();
PersonName = dataRow["person_name"].ToString();
DateTime.TryParse(dataRow["updated_at"].ToString(), out UpdateDate);
int.TryParse(dataRow["interval_month"].ToString(), out IntervalMonth);
DateTime.TryParse(dataRow["alert_date"].ToString(), out ExpireDate);
PrimarySmtpAddress = dataRow["smtp_address"].ToString();
}
catch { }
}
}
}
}
メールサーバーを持たない会社の場合、Outlookを起動して送信するプログラム等にしましょう。
もしTeamsやSlackでBotを作れるなら、Botに発言させるのもいいと思います。
終わり
ここでタイマーストップ！
記録は・・・30時間弱ですかね（体感）
さて、完走した感想ですが（激うまギャグ）、
自分で0から仕組みを考え、環境を作り、コーディングする作業はやりがいがあるものの、RPAでは味わえないコーディングの楽しみを覚えました。
しかし、C#の経験が薄いせいか、もう少し詰めたコーディングができたかと思ってます。
データベースが用意できるならDataSetを使えば簡潔にコードを書けたし、UploaderやGetterのコードも載せることができたかとおもいます。
あとは、仮にもRTA実況風解説を冠するなら、動画とは言わずとも写真とかうまく使えれば面白く作れたかと思います。技術記事は難しいですね。
今回の記事はここまでとします。ご視聴、ありがとうございました。
皆さんのRPA (Robotic Process Automation) Advent Calendar 2020記事、楽しみにしております。