| MSDE FunClub |
|
最終更新日 : 2000/08/11 |
|
Microsoft Data Engine FunClub
|
Since 2000.08.11
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第４章２７６ｐ 〜 ２８０ｐ掲載】 |
-- [SQL04_04.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、環境設定を行います
-- 1)SQLServerへの新規ログイン名A,B,Cの３人を登録します
-- 2)新しいデータベース [ストアドテスト用] を新規に作成します
-- 3)[ストアドテスト用]へ データベースユーザA,B,Cを登録します
-- 4)[ストアドテスト用]へ ストアドプロシージャを登録します
-- このユーザには、ストアドプロシージャだけは実行できる
-- 権限を与えます
-- 以上の環境設定を行います
--
-- ************************
-- 作業用一時プロシージャ
-- ************************
CREATE PROCEDURE #Is_LoginExist
@lognm varchar(10)
AS
SET NOCOUNT ON --メッセージの抑止
-- ログイン名の登録検査は、システムビュー表を調査します
IF EXISTS(SELECT * FROM master.dbo.syslogins where loginname = @lognm )
BEGIN
RAISERROR( 'ログイン名 ''%s'' が存在します。削除してください' ,
0 , 127 , @lognm )
RETURN(1)
END
RETURN(0)
GO
-- **********************************************
-- 【STEP-1：スクリプト実行者の権限チェック】
-- 1) ログインユーザを新規登録できる権限は sysadmin
-- および securityadmin だけです
-- sysadmin は securityadminの権限を持っています
-- 2)データベースの新規作成が可能な権限は、dbcreator です
-- **********************************************
IF ( NOT IS_SRVROLEMEMBER( 'securityadmin' ) = 1 )
BEGIN
-- 私は、securityadmin固定サーバーロールに所属しません
PRINT 'システム管理者権限（例えばsa）でログインしてください'
RAISERROR( '私には、ログイン新規登録処理の権限はありません' , 16 , 127 )
RETURN
END
IF ( NOT IS_SRVROLEMEMBER( 'dbcreator' ) = 1 )
BEGIN
-- 私は、dbcreator 固定サーバーロールに所属しません
RAISERROR( '私には、データベースを新規作成することはできません' , 16 , 127 )
RETURN
END
GO
-- **********************************************
-- 【STEP-2：ログインの登録】
-- ログイン'A','B','C'の３人を登録する
-- すでに存在していればエラーとする
-- **********************************************
DECLARE @Ret int
-- ログインA はすでにありますか？
EXECUTE @Ret = #Is_LoginExist 'A'
IF @Ret <> 0 RETURN -- すでに存在します（中止）
-- ログインB はすでにありますか？
EXECUTE @Ret = #Is_LoginExist 'B'
IF @Ret <> 0 RETURN -- すでに存在します（中止）
-- ログインC はすでにありますか？
EXECUTE @Ret = #Is_LoginExist 'C'
IF @Ret <> 0 RETURN -- すでに存在します（中止）
-- ログインの登録（第２引数がパスワードです。この例では省略です）
EXECUTE sp_addlogin 'A' , NULL , 'master' , NULL , NULL , NULL
EXECUTE sp_addlogin 'B' , NULL , 'master' , NULL , NULL , NULL
EXECUTE sp_addlogin 'C' , NULL , 'master' , NULL , NULL , NULL
GO
-- **********************************************
-- 【STEP-3：データベースの作成】
-- データベースを新規に作成します
-- **********************************************
-- すでに同名のデータベースが存在したらエラーとする
IF EXISTS(SELECT * FROM master.dbo.sysdatabases where name = 'ストアドテスト用' )
BEGIN
RAISERROR( 'ストアドテスト用のデータベースが存在しました。削除してください' , 16 , 127 )
RETURN
END
-- データベースを新規に作成する
CREATE DATABASE [ストアドテスト用]
GO
-- **********************************************
-- 【STEP-4：データベースのユーザを登録する】
-- ログインA,B,Cログインの既定データベースを変更する
-- データベースユーザを登録する
-- **********************************************
-- ログイン者の既定データベースをmasterから[ストアドテスト用]に変更する
EXECUTE sp_defaultdb 'A' , 'ストアドテスト用'
EXECUTE sp_defaultdb 'B' , 'ストアドテスト用'
EXECUTE sp_defaultdb 'C' , 'ストアドテスト用'
-- ユーザ名の登録。第１引数がログイン名、第２引数がユーザ名
-- sp_grantdbaccess は、現在のデータベースに対してユーザ登録を行う
-- 現在のデータベースを変更すること
USE [ストアドテスト用]
EXECUTE sp_grantdbaccess 'A', 'USER_A'
EXECUTE sp_grantdbaccess 'B', 'USER_B'
EXECUTE sp_grantdbaccess 'C', 'USER_C'
GO
-- **********************************************
-- 【STEP-5：ストアドプロシージャの登録を行う】
-- このプロシージャを登録するのは、私(dbo)です
-- 所有者はdboです。このことに注意してください
-- **********************************************
-- このプロシージャを実行した人が、テーブルの所有者になる
CREATE PROCEDURE PROC_MakeTable
AS
IF( (object_id('MyTable') IS NOT NULL) AND
OBJECTPROPERTY(object_id('MyTable') ,'IsTable')=1)
BEGIN
DROP TABLE MyTable
END
CREATE TABLE MyTable(
ID int , ID2 char(6)
)
GO
CREATE PROCEDURE PROC_SetData
AS
-- このMyTableの所有者は一体誰でしょうか？？？
DELETE FROM MyTable
INSERT INTO MyTable VALUES( 1 , 'A' )
INSERT INTO MyTable VALUES( 2 , 'B' )
INSERT INTO MyTable VALUES( 3 , 'C' )
SELECT * FROM MyTable
GO
-- ****************************************************
-- 【STEP-6：ストアドプロシージャの実行権限を与える】
-- STEP-4で登録したユーザは、public ロールで何も権限がありません
-- ストアドプロシージャの実行権限を与えます
-- またテーブル作成権限も必要です
-- ****************************************************
-- 実行許可 ストアドの名前 許可するユーザリスト
GRANT EXECUTE ON PROC_MakeTable TO USER_A,USER_B,USER_C
GRANT EXECUTE ON PROC_SetData TO USER_A,USER_B,USER_C
-- CREATE TABLE 作成権限がないとストアドプロシージャ内でエラーが起こる
GRANT CREATE TABLE TO USER_A,USER_B,USER_C
GO
-- 最初に作成した一時ストアドプロシージャを削除する
DROP PROCEDURE #Is_LoginExist
GO
