SQL Server:データベースユーザーのデフォルトスキーマを切り替えてみよう!

◆ Live配信スケジュール ◆
サイオステクノロジーでは、Microsoft MVPの武井による「わかりみの深いシリーズ」など、定期的なLive配信を行っています。
⇒ 詳細スケジュールはこちらから
⇒ 見逃してしまった方はYoutubeチャンネルをご覧ください
VSCode Dev Containersで楽々開発環境構築祭り〜Python/Reactなどなど〜
Visual Studio Codeの拡張機能であるDev Containersを使ってReactとかPythonとかSpring Bootとかの開発環境をラクチンで構築する方法を紹介するイベントです。
https://tech-lab.connpass.com/event/311864/

はじめに

こんにちは!中途入社のなーがです。前回はIP ネットワーク制限をかけたストレージアカウントにアクセスする方法でしたが、今回はデータベースユーザーのデフォルトスキーマを切り替えることについて書こうと思います。設定方法だけを知りたい方はこちらから確認できます。

皆さんは開発・テストのDBをどのように用意されていますか。config等の設定ファイルで接続するDBを切り替えるという方法もあると思いますが、データベースによってはスキーマを切り替えることで1つのDBで同名のテーブルを管理することが出来ます。

最近業務でデータベースユーザーのスキーマを切り替えたいことがありました。必要とされている要件は以下の通りです。

  • アプリケーション用ユーザー「app_user」では「dbo」スキーマのみを参照する
  • テスト用ユーザー「test_user」ではテスト用スキーマ「test」のみを参照する

簡略化していますが、クエリを確認すると以下のように記載されていました。

UPDATE [dbo].[TestMaster]
SET
  ID = @id,
  NAME = @name;

そこで、下記のように変数schemaでスキーマを切り替えられるように修正しようとしたのですが、このように設定してしまうと全ての呼び出し元でスキーマを指定する必要があることに気がつきました。これではあまり良い修正方法とは言えません。

UPDATE [{schema}].[TestMaster]
SET
  ID = @id,
  NAME = @name;

普段実装する上でクエリのスキーマについてあまり意識していなかったので、この際に調べてみようと思いました。

スキーマとは

まず、DBにおけるスキーマとはどのようなものをいうのでしょうか。wikiによると、以下のように記載があります。

データベースの構造であり、データベース管理システム (DBMS) でサポートされている形式言語で記述される。

つまり、DBの設計書のようなものと考えることが出来ます。

検証用DBの作成

今回はローカルで検証用のDBを作成してテスト用ユーザーとスキーマの作成を試そうと思います。SQLServerのインストーラーでローカルにインストールしてしまうと検証後の削除が大変なので、Dockerで環境を作りたいと思います。作成方法はMSの公式サイトを参考にしました。

DockerイメージのPullとコンテナの実行

公式のSQL Server Linux コンテナイメージをpullします。

sudo docker pull mcr.microsoft.com/mssql/server:2022-latest

イメージからコンテナを実行します。XXXXXにはsaユーザーのパスワードを設定します。

sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=XXXXX" \
-p 1433:1433 --name sql1 --hostname sql1 \
-d \
mcr.microsoft.com/mssql/server:2022-latest

コンテナが実行できているか確認します。

docker ps

サーバーにアクセス

実行できているようなので、SQL Server Management Studio (SSMS)からサーバーに接続したいと思います。localhostでは接続できないようなので、WSL2で立ち上げているLinuxのIPに直接接続する必要があるそうです。WSL2のeth0のIPアドレスは以下のコマンドで確認できるそうです。こちらを参考にさせて頂きました。

ip a | grep eth0 | grep inet

サーバー名「上記で調べたIPアドレス,(カンマ)ポート番号」、ログイン「sa」、パスワード「上記で設定したパスワード」を入力して接続をクリックします。

データベースの作成

テストデータベースを作成します。

CREATE DATABASE TestDB;

「接続先データベース」で「TestDB」を選択する。

新しいクエリウィンドウを開いて使用する場合は、クエリウィンドウを開く度にDBを指定するのが面倒大変なので、saユーザーのデフォルトDBを「master」から「TestDB」に変更しておくと楽です。

検証用のローカル環境であるためこの手順を行っていますが、本番環境等で実行してしまうとDBを削除した場合や名前を変更した場合にログインが出来なくなってしまうため注意が必要

ALTER LOGIN sa WITH DEFAULT_DATABASE=TestDB;

テスト用ユーザーの作成

テスト用ユーザーを作成します。

-- SQL Serverログイン作成
CREATE LOGIN test_user WITH PASSWORD = 'test_U$er1234', DEFAULT_DATABASE = TestDB;
-- データベースユーザー作成
CREATE USER test_user FOR LOGIN test_user;

スキーマの作成

切り替えたいスキーマを作成します。

CREATE SCHEMA test;

テーブルの作成

dboスキーマとtestスキーマのそれぞれにUsersテーブルを作成します。

CREATE TABLE dbo.Users(
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(50)
);
CREATE TABLE test.Users(
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(50)
);

「TestDB」のテーブルを開くと、「dbo」スキーマと「test」スキーマのUsersテーブルが表示されています。

Usersテーブルにデータを追加します。下記のクエリはBing Chatで作成しました、便利ですね!(saでログインしているので、dboスキーマのUsersテーブルにのみデータが作成されます)

INSERT INTO Users(name) VALUES ('Alice');
INSERT INTO Users(name) VALUES ('Bob');
INSERT INTO Users(name) VALUES ('Charlie');
INSERT INTO Users(name) VALUES ('David');
INSERT INTO Users(name) VALUES ('Eve');
INSERT INTO Users(name) VALUES ('Frank');
INSERT INTO Users(name) VALUES ('Grace');
INSERT INTO Users(name) VALUES ('Helen');
INSERT INTO Users(name) VALUES ('Ivan');
INSERT INTO Users(name) VALUES ('Jack');

追加したデータを確認します。

SELECT * FROM dbo.Users;

SELECT * FROM test.Users;

dboスキーマのUsersテーブルにはデータが作成されていますが、testスキーマのUsersテーブルは空のままです。テスト用のデータベースとテーブルが作成できたので、スキーマを変更したいと思います。

設定方法

デフォルトスキーマの切り替え

上記で作成したテスト用ユーザーのデフォルトスキーマにテスト用スキーマを設定します。

SQL Server 2005 以降では、ALTER USER文を使用することでデフォルトのスキーマを指定することが出来ます。指定しない場合は、デフォルトのスキーマとして「dbo」が設定されます。

ALTER USER test_user WITH DEFAULT_SCHEMA = test;

また、既定のスキーマでデータベース ユーザーを作成する場合は、以下のクエリで実行できます。

CREATE LOGIN test2 WITH PASSWORD = 'test_U$er1234'; 
USE TestDB;
CREATE USER test2 FOR LOGIN test2 WITH DEFAULT_SCHEMA = test;

テスト用ユーザー「test_user」ではテスト用スキーマ「test」のみを参照するようにするため「dbo」スキーマの権限を削除し、「test」スキーマの権限を付与します。

DENY CONTROL ON SCHEMA :: dbo TO test_user;
GRANT CONTROL ON SCHEMA :: test TO test_user;

テスト用ユーザーでサーバーにアクセス

テスト用ユーザーの作成で作成したユーザーでログインします。

「TestDB」のテーブルを開くと、「test」スキーマのUsersテーブルのみが表示されています。

それでは、それぞれのスキーマでデータにアクセスしてみます。

SELECT * FROM dbo.Users;

SELECT * FROM test.Users;

「dbo」スキーマを指定した場合は「UsersテーブルのSELECT句を実行する権限が無い」というエラーになっていますが、「test」スキーマではクエリが正常に実行されています。これで要件を満たすテスト用ユーザーとスキーマを作成できたと思います。

さいごに

今回はデータベースユーザーのデフォルトスキーマを切り替えることについて書きました。デフォルトスキーマは普段実装する上であまり意識していなかったので、少しDBの理解が深まったと思います。これからも業務で学んだことをブログにしていきたいと思います。

アバター画像
About なーが 13 Articles
前職は.NETやPythonを用いた業務アプリケーション、プロトタイプAPI開発に従事。2023年中途入社。とりあえずやってみて、苦しんで覚える系エンジニア。AWS認定3冠(SAA、MLS、DAS)

ご覧いただきありがとうございます! この投稿はお役に立ちましたか?

役に立った 役に立たなかった

0人がこの投稿は役に立ったと言っています。


ご覧いただきありがとうございます。
ブログの最新情報はSNSでも発信しております。
ぜひTwitterのフォロー&Facebookページにいいねをお願い致します!



>> 雑誌等の執筆依頼を受付しております。
   ご希望の方はお気軽にお問い合わせください!

Be the first to comment

Leave a Reply

Your email address will not be published.


*


質問はこちら 閉じる