Cybercrux

Everything is achievable through technology

Multi Tenant Database


--create a test database
CREATE DATABASE [SecurityTest]
GO
USE SecurityTest
GO
CREATE TABLE [dbo].[table1](
[pkcol] [int] IDENTITY(1,1) NOT NULL,
[col1] [int] NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO
--create test user login
CREATE LOGIN [User1] WITH PASSWORD=N'p@55w0rd'
GO
--create user in test database
CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[Developer_Schema]
GO
--create role
CREATE ROLE [Developer_Role] AUTHORIZATION [dbo]
GO
--create schema
CREATE SCHEMA [Developer_Schema] AUTHORIZATION [User1]
GO
--apply permissions to schemas
GRANT ALTER ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT CONTROL ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT SELECT ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT DELETE ON SCHEMA::[dbo] TO [Developer_Role]
GO
GRANT INSERT ON SCHEMA::[dbo] TO [Developer_Role]
GO
GRANT SELECT ON SCHEMA::[dbo] TO [Developer_Role]
GO
GRANT UPDATE ON SCHEMA::[dbo] TO [Developer_Role]
GO
GRANT REFERENCES ON SCHEMA::[dbo] TO [Developer_Role]
GO
--ensure role membership is correct
EXEC sp_addrolemember N'Developer_Role ', N'User1'
GO
--allow users to create tables in Developer_Schema
GRANT CREATE TABLE TO [Developer_Role]
GO
--Allow user to connect to database
GRANT CONNECT TO [User1]
GRANT EXECUTE ON dbo.procname TO username;

Reference Link

Advertisements

5 thoughts on “Multi Tenant Database

  1. Drop all the tables in a Tenant to drop a schema

    DECLARE @SqlStatement VARCHAR(MAX)
    SELECT @SqlStatement =
    COALESCE(@SqlStatement, ”) + ‘DROP TABLE [nik].’ + QUOTENAME(TABLE_NAME) + ‘;’ + CHAR(13)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = ‘nik’

    PRINT @SqlStatement

  2. Get all Schema from a Database

    SELECT
    SCHEMA_NAME(schema_id) As SchemaName ,
    name As TableName
    from sys.tables
    ORDER BY name

  3. Permission to Execute dbo procedure
    GRANT EXECUTE ON SCHEMA :: dbo TO nikhil WITH GRANT OPTION;

  4. Delete all table from a Schema if no Forign key available

    Exec Sp_msforeachtable @command1=’drop Table ?’,@whereand=’and Schema_Id=Schema_id(”abcdefg”)’

  5. Constrain Escape

    EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
    GO

    EXEC sp_MSForEachTable ‘TRUNCATE TABLE ?’
    GO

    – enable referential integrity again
    EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
    GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s