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

はじめに

こんにちは!中途入社のなーがです。前回は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の理解が深まったと思います。これからも業務で学んだことをブログにしていきたいと思います。

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

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

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

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です