SQL Server でデータベースに対してSQLコマンドを発行できるようになるには特別なサーバロールが付与されている場合を除き、SQL サーバへのログインユーザと各データベースのデータベースユーザを作成する必要があります。本サンプルでは、SQLサーバへのログインユーザとデータベースユーザを作成、操作する方法を記載します。
動作確認は SQL Server 2005 Developer Edition を使用しています。
1. SQLサーバのログインにWindowsユーザを追加、変更、削除
SQL Server 2000 では sp_grantlogin ストアドプロシージャを使用しますが、次のようにCREATE LOGIN SQLコマンドを使用します。
USE [master] GO -- SQLサーバのログインにWINDOWSユーザを追加 CREATE LOGIN [VS2K301\TestUser] FROM WINDOWS -- デフォルトデータベースを指定する場合 -- 既定の言語などを指定できる CREATE LOGIN [VS2K301\TestUser] FROM WINDOWS WITH DEFAULT_DATABASE = [Component]
WINDOWSログインのユーザを変更する場合はALTER LOGIN SQLコマンドを使用します。
-- WINDOWSログインの変更 -- ユーザの有効、無効、 -- 既定のデータベース、言語を変更可能 ALTER LOGIN [VS2K301\TestUser] DISABLE
SQL Server にログインできるWINDOWSユーザはsys.server_principalsカタログビューから確認できます。
SELECT *
FROM sys.server_principals
WHERE type_desc in ('WINDOWS_LOGIN','WINDOWS_GROUP')
WINDOWSユーザをログインユーザから削除する場合は DROP LOGIN コマンドを使用します。
DROP LOGIN [VS2K301\TestUser]
特定のWINDOWSユーザ、グループからアクセスを許可、拒否する場合はDENY CONNECT SQL, GRANT CONNECT SQL を使用します。
-- 特定のWindowsのユーザ、グループからのアクセスを -- 拒否する場合 DENY CONNECT SQL TO [VS2K301\TestUser] -- 許可する場合 GRANT CONNECT SQL TO [VS2K301\TestUser]
2. SQLサーバのユーザを作成、変更、削除する
SQLサーバレベルで管理するSQLユーザもWindowsユーザをログインユーザとする場合とほぼ同様に追加、変更、削除できます。
-- SQLサーバが管理するログインユーザを作成
CREATE LOGIN [TestSqlUser]
WITH PASSWORD = 'Password'
-- 既定のデータベースを指定する場合
CREATE LOGIN [TestSqlUser]
WITH PASSWORD = 'Password'
,DEFAULT_DATABASE = [Component]
-- 削除はWindowsログインの場合と同様
DROP LOGIN [TestSqlUser]
SQL Serverユーザのログイン情報もsys.server_principals セキュリティーカタログビューから確認できます。
Books Online を確認すると、SQL サーバユーザの詳細な作成オプション(複雑性、有効期限など)を確認できます。
3. サーバロールへの追加、削除
SQLログインに追加したWindiws,SQL Server ユーザにサーバロールを付与ふるには sp_addsvrrolemember , sp_dropsrvrolemember を使用します。サンプルではSQLログインユーザのTestSqlUserにsysadminロールを付与しています。
-- SQLユーザTestSqlUserにsysadminサーバロールを追加 EXEC master.sys.sp_addsrvrole 'TestSqlUser', 'sysadmin' -- SQLユーザTestSqlUserにsysadminサーバロールを削除 EXEC master.sys.sp_dropsrvrolemember 'TestSqlUser', 'sysadmin'
サーバロール情報を表示する場合はsys.server_principalsセキュリティカタログビューやsp_helpsrvroleストアドプロシージャを使用します。
-- Serverロールを取得する SELECT * FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE' -- サーバロール一覧を列挙する,publicは一覧に表示されない EXEC master.sys.sp_helpsrvrole
特定のサーバロールを付与されているメンバを取得する場合は sp_helpsrvrolemember ストアドプロシージャを使用します。
-- サーバロールに含まれるメンバーを取得する EXEC master.sys.sp_helpsrvrolemember -- 特定のサーバロールのメンバーを取得する EXEC master.sys.sp_helpsrvrolemember @srvrolename = 'sysadmin'
4. データベースユーザを作成する
データベースユーザは作成したSQLサーバのログインユーザをデータベースユーザにマッピングを行うことで作成します。サンプルではWindowsユーザのログインを作成し、そのユーザTestUserに対してAdventureWorksDWデータベースにCREATE USER コマンドを使用してデータベースユーザを指定します。Books Online を参照すると、デフォルトスキーマの指定や証明書の設定を行えます。
-- 1.データベースユーザを作成する。 USE [master] GO CREATE LOGIN [VS2K301\TestUser] FROM WINDOWS WITH DEFAULT_DATABASE = [AdventureWorksDW] DROP LOGIN [VS2K301\TestUser] -- 2.データベースAdventureWorksDW -- データベースユーザを作成 USE [AdventureWorksDW] GO CREATE USER [VS2K301\TestUser]
FOR LOGINを指定することで、SQLのログインユーザとは別名のデータベースユーザ名でデータベースユーザを作成できます。下のサンプルではログインユーザVS2K301\TestUserに対してデータベースユーザをOtherNameという名前で作成しています。
CREATE USER [OtherName] FOR LOGIN [VS2K301\TestUser]
データベースユーザを削除するにはDROP文を使用します。例では先ほど作成したデータベースユーザOtherNameを削除しています。
-- データベースユーザを削除する DROP USER [OtherName]
ユーザ情報を変更する場合はALTER USERコマンドを使用します。
-- データベースユーザ名を変更 ALTER USER [OtherName] WITH NAME = [OtherName2]
データベースユーザ情報(ログイン名やグループ名)を表示する場合は sp_helpuser システムストアドプロシージャを使用します。
-- データベースユーザ情報を表示 EXEC sp_helpuser @name_in_db = 'OtherName2'
パラメタを指定せずに呼び出すとデータベースのすべてのユーザ情報が表示されます。
ログインユーザとDBユーザのマッピングを表示するクエリは次のようになります。
select s.name LoginName, d.name DBName, d.sid
from sys.database_principals d
inner join sys.server_principals s
on d.sid = s.sid
- データベースユーザとSQL Serverログインユーザのマッピングが失われた場合
バックアップしたデータベースを別のデータベースやデータベースサーバのインスタンスにリストアするデータベースユーザとSQLログインユーザのマッピングが失われます。データベースユーザとSQLサーバのログインユーザのマッピングを修正する場合は sp_change_users_login ストアドプロシージャを使用します。パラメタの@Actionの値にReportやauto_fix,update_oneを指定することでマッピングの失われたユーザのレポートや修正を行えます。
固定データベースロール情報の表示する場合はsp_helpdbfixedroleストアドプロシージャを使用します。
EXEC sp_helpdbfixedrole
sp_helprolememberストアドを使用すると固定データベースロールに割り当てられたユーザ一覧を取得できます。
5. データベースロールメンバシップの付与と削除
データベースロールにデータベースユーザやロールを付与するにはsp_addrolemember,sp_droprolememberを使用します。例ではデータベースユーザTestSqlUserにdb_ownerデータベースロールを付与、削除しています。
sp_addrolemember 'db_owner', 'TestSqlUser' sp_droprolemember 'db_owner', 'TestSqlUser'
ユーザ定義のデータベースロールを作成、修正、削除するにはCRATE ROLE, ALTER ROLE, DROP ROLE コマンドを使用します。データベースロールへのユーザの追加削除は、sp_addrolemember,sp_droprolememberを使用できます。追加されたロール情報は sp_helprole ストアドプロシージャで確認できます。
EXEC sp_helprole
作成したロールにGRANT,DENYコマンドを使用して必要な権限を持つロールを作成して管理しやすいロールを作成します。
データベースロールの作成や修正派CREATE ROLE [role_name],ALTER ROLEを使用します。ロールに対してGRANTをすることで、権限の設定を行えるようになります。
6.アプリケーションロールの管理
アプリケーションロールを使用すると、sp_setapproleストアドプロシージャを使用してアプリケーションロールを活性化した場合、そのセッション中にsp_setapproleで指定したアプリケーションロールの権限でクエリを実行するようになります。そのため、アプリケーションロールに権限を付与して始めて効果があります。
アプリケーションロールの作成、修正、削除はCREATE APPLICATION ROLE, ALTER APPLICATION ROLE, DROP APPLICATION ROLE を使用します。アプリケーションロールを作成した後、GTANTコマンドで権限をアプリケーションロールに設定します。
sp_setapproleにアプリケーションロール名とCREATE APPLICATION ROLE で指定したパスワードを使用してアプリケーションロールを活性化することで、同じセッション中にアプリケーションロールの権限でクエリを使用するようにできます。
7. おわり
説明は以上です。間違い、誤り等がありましたらご連絡ください。
さんのコメント: さんのコメント: