---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第19章 セキュリティ
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 19.1 SQL Server 2005のセキュリティアーキテクチャ
---------------------------------------------------------------------

WITH Perms(class_desc, covering_permission_name, permission_name, level) AS
(
    -- アンカーメンバ（最初の反復）
    SELECT class_desc, covering_permission_name, permission_name, 0 level
    FROM sys.fn_builtin_permissions(default)
    WHERE covering_permission_name = ''
        UNION ALL
    -- 再帰メンバ（以降の反復）
    SELECT o.class_desc, o.covering_permission_name, o.permission_name, p.level + 1 level
    FROM sys.fn_builtin_permissions(default) o INNER JOIN Perms p
        ON o.covering_permission_name = p.permission_name
        AND o.class_desc = p.class_desc
)
SELECT s.class_desc, s.permission_name, s.covering_permission_name, s.level
FROM Perms s INNER JOIN sys.fn_builtin_permissions(default) o
    ON o.permission_name = s.permission_name
    AND o.class_desc = s.class_desc
ORDER BY s.class_desc, s.level;


EXEC sp_srvrolepermission 'processadmin'

---------------------------------------------------------------------
-- 19.2 セキュリティの実装
---------------------------------------------------------------------

CREATE LOGIN [MyDomain\NikolaS] FROM WINDOWS;


CREATE LOGIN [MyDomain\MyGroup] FROM WINDOWS;


CREATE LOGIN nsunderic WITH PASSWORD = 'my,password326'


IF EXISTS(SELECT * FROM sys.server_principals WHERE name = 'nsunderic')
    DROP LOGIN nsunderic


USE Asset5
GO
CREATE USER nsunderic FOR LOGIN nsunderic
GO


IF EXISTS(SELECT * FROM sys.database_principals WHERE name = 'nsunderic')
    DROP USER nsunderic


CREATE ROLE rolBcmManagement


EXEC sp_addrolemember @rolename='rolUser', @membername='nsunderic'


ALTER AUTHORIZATION ON SCHEMA::[util] TO [nsunderic]


USE master
GRANT CREATE DATABASE TO JohnS, [Accounting\TomB]


USE master
GRANT ADMINISTER BULK OPERATIONS,
      ALTER ANY LINKED SERVER,
      CREATE ANY DATABASE,
      VIEW ANY DATABASE
TO JohnS


GRANT EXECUTE, VIEW DEFINITION, ALTER
ON dbo.ap_ChargeLog_Insert
TO rolUser
GO


GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES
ON dbo.Inventory
TO rolUser


USE master
DENY CREATE DATABASE TO [Accounting\TomB]


DENY UPDATE, DELETE
ON dbo.Inventory
TO JohnS


EXEC sp_addrolemember 'AssetOwner', 'JohnS',


DENY UPDATE, DELETE
ON dbo.Inventory
TO JohnS


REVOKE UPDATE, DELETE
ON dbo.Inventory
TO JohnS

---------------------------------------------------------------------
-- 19.3 ログインとデータベースユーザーの同期
---------------------------------------------------------------------

USE master
SET NOCOUNT ON
SELECT 'CREATE LOGIN [' + name + '] '
+ 'with password = ''My1.Password'',
DEFAULT_DATABASE = tempdb,
sid =' , sid
--select *
FROM sys.server_principals
WHERE principal_id > 256
AND type_desc = 'SQL_LOGIN'

SELECT 'CREATE LOGIN [' + name + '] FROM WINDOWS; '
FROM sys.server_principals
WHERE principal_id > 256
AND type_desc = 'WINDOWS_LOGIN'
AND name NOT IN('NT AUTHORITY\SYSTEM', 'BUILTIN\Administrators')

SELECT 'EXEC sp_addsrvrolemember '''+loginname+''', ''sysadmin'''
FROM syslogins
WHERE sysadmin = 1
AND loginname NOT IN('NT AUTHORITY\SYSTEM', 'BUILTIN\Administrators', 'sa')
UNION
SELECT 'EXEC sp_addsrvrolemember '''+loginname+''', ''securityadmin'''
FROM syslogins
WHERE securityadmin = 1
AND loginname NOT IN('NT AUTHORITY\SYSTEM', 'BUILTIN\Administrators', 'sa')
UNION
SELECT 'EXEC sp_addsrvrolemember '''+loginname+''', ''serveradmin'''
FROM syslogins
WHERE serveradmin = 1
AND loginname NOT IN('NT AUTHORITY\SYSTEM', 'BUILTIN\Administrators', 'sa')
UNION
SELECT 'EXEC sp_addsrvrolemember '''+loginname+''', ''setupadmin'''
FROM syslogins
WHERE setupadmin = 1
AND loginname NOT IN('NT AUTHORITY\SYSTEM', 'BUILTIN\Administrators', 'sa')
UNION
SELECT 'EXEC sp_addsrvrolemember '''+loginname+''', ''processadmin'''
FROM syslogins
WHERE processadmin = 1
AND loginname NOT IN('NT AUTHORITY\SYSTEM', 'BUILTIN\Administrators', 'sa')
UNION
SELECT 'EXEC sp_addsrvrolemember '''+loginname+''', ''diskadmin'''
FROM syslogins
WHERE diskadmin = 1
AND loginname NOT IN('NT AUTHORITY\SYSTEM', 'BUILTIN\Administrators', 'sa')
UNION
SELECT 'EXEC sp_addsrvrolemember '''+loginname+''', ''dbcreator'''
FROM syslogins
WHERE dbcreator = 1
AND loginname NOT IN('NT AUTHORITY\SYSTEM', 'BUILTIN\Administrators', 'sa')
UNION
SELECT 'EXEC sp_addsrvrolemember '''+loginname+''', ''bulkadmin'''
FROM syslogins
WHERE bulkadmin = 1
AND loginname NOT IN('NT AUTHORITY\SYSTEM', 'BUILTIN\Administrators', 'sa')

-----------------------------------------
-----------------------------------------

SELECT 'Run these after dbs are created:'

SELECT ' EXEC sp_defaultdb @loginame = ''' + name + ''''
,', @defdb = ''' + COALESCE(default_database_name, 'tempdb') + ''''
FROM sys.server_principals
WHERE principal_id > 256
AND type_desc = 'SQL_LOGIN'

----------------------------------------

SELECT ' EXEC sp_defaultdb @loginame = ''' + name + ''''
,', @defdb = ''' + COALESCE(default_database_name, 'tempdb') + ''''
FROM sys.server_principals
WHERE principal_id > 256
AND type_desc = 'WINDOWS_LOGIN'
AND name NOT IN('NT AUTHORITY\SYSTEM', 'BUILTIN\Administrators')


EXEC sp_change_users_login @Action = 'Report'


EXEC sp_change_users_login @Action = 'Update_one',
                           @UserNamePattern = 'TomB',
                           @LoginName = 'TomB'


EXEC sp_change_users_login @Action = 'Auto_Fix',
                           @UserNamePattern = 'AssetAdmin',
                           @LoginName = NULL,
                           @password = 'my1.password'

---------------------------------------------------------------------
-- 19.4 ストアドプロシージャ／ユーザー定義関数／ビューを使ったセキュリティ
---------------------------------------------------------------------

CREATE DATABASE TestCOC
GO
CREATE LOGIN AnnS WITH PASSWORD = 'My,password', DEFAULT_DATABASE = TestCOC
GO
USE TestCOC
GO
CREATE USER AnnS
GO

CREATE TABLE dbo.aTable(
    Id INT IDENTITY(1, 1),
    Description VARCHAR(20)
)
GO

CREATE PROCEDURE dbo.ap_aTable_List
AS
    SELECT * FROM dbo.aTable

GO

CREATE PROCEDURE dbo.ap_aTable_Insert
    @Desc VARCHAR(20)
AS
    INSERT INTO dbo.aTable(Description)
    VALUES(@Desc)

GO

DENY SELECT, INSERT, UPDATE, DELETE
ON dbo.aTable
TO Public

GRANT EXECUTE
ON dbo.ap_aTable_Insert
TO Public

GRANT EXECUTE
ON dbo.ap_aTable_List
TO Public
GO

---------------------------------------------------------------------
-- 19.6 アプリケーションロールを使ったセキュリティ
---------------------------------------------------------------------

CREATE APPLICATION ROLE Accounting WITH PASSWORD = 'password'


EXEC sp_setapprole @rolename = 'Accounting', @password = 'password'

