はじめに
管理権限を持っていると毎回alter userの依頼が飛んできて面倒ですよね？
例えばよくあるのは以下のような依頼です。
- サポート起票したいのでfirst_name, last_nameを埋めて欲しい
- キーペア認証をしたいので公開鍵を登録して欲しい
procedureのcallerオプションをうまく使えば自分自身のパラメータを変更できるようになります。
procedure
以下のようにprocedureを作成します。 EXECUTE AS OWNERで定義するのがポイントです。
input validationの部分は変更したいパラメータ種別を適宜追加してください。
CREATE OR REPLACE PROCEDURE alter_me(param STRING, value STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
// get current user
var sqlCommand = `SELECT CURRENT_USER()`;
var result = snowflake.execute({ sqlText: sqlCommand });
result.next();
var userName = result.getColumnValue(1);
// input validation
var allowedParams = ['FIRST_NAME'];
if (allowedParams.indexOf(PARAM.toUpperCase()) === -1) {
throw new Error('Invalid parameter name');
}
// alter current user
try {
var alterUserQuery = `ALTER USER "${userName}" SET ${PARAM} = '${VALUE}'`;
snowflake.execute({ sqlText: alterUserQuery });
return 'Succeeded.';
} catch (err) {
return `Failed query: ${err.message}`;
}
$$;
ownershipの移行
基本的にuserはuseradminで作成している方がほとんどだと思うのでuseradminへprocedureのownershipを移管します。
grant ownership on procedure alter_me(string, string) to role useradmin;
usage
変更したいパラメータと値の組み合わせを引数にしてcallすれば自分のユーザーパラメータが更新されます。
call alter_me('FIRST_NAME', 'TARO');
注意
正直なところあらかじめ定義された権限を超えた操作をすることになるので、使わないで済むならそれが一番望ましいと思います。ドキュメントにもある通り、動的なSQLの作成は常にインジェクションの危険があるため、引数はバリデーション、もしくはエスケープを常に行うようにしましょう。
また、ACCOUNTADMIN/SECURITYADMINをownerとすることはCIS Benchmarkから外れるので避けましょう（Trust Centerの検査にも引っ掛かります）。
Discussion