Management database title visibility in Amazon RDS for SQL Server cases

Multi-tenant SQL Server environments face an architectural problem the place uncovered database names can reveal delicate tenant data. On-premises or self-managed SQL Server operating on Amazon Elastic Compute Cloud (Amazon EC2) can tackle this problem by manually denying server-level permissions to particular logins. In Amazon Relational Database Service (Amazon RDS) for SQL Server, database visibility is configured utilizing a devoted saved process.

By default, SQL Server’s PUBLIC function permits authenticated customers to view all database names, a characteristic meant for transparency that may develop into a big concern in multi-tenant structure. For unbiased software program distributors (ISVs) and software program as a service (SaaS) suppliers internet hosting a number of buyer databases on the identical occasion, this default conduct requires cautious consideration and mitigation methods to guard tenant identities.

On this submit, we reveal tenant isolation on the visibility degree, stopping tenants from seeing database names belonging to different prospects whereas sustaining their entry to their very own assets.

Resolution overview

This answer addresses an necessary architectural consideration in multi-tenant SQL Server environments the place database names would possibly reveal tenant data. By utilizing the Amazon RDS for SQL Server customized saved process msdb.dbo.rds_manage_view_db_permission, customers can successfully management database visibility on a per-login foundation whereas sustaining full utility performance.

Importantly, this answer solely manages database visibility; logins with acceptable database permissions can nonetheless totally entry and use the databases in line with their granted permissions, even once they can’t see the database names in SQL Server Administration Studio (SSMS) or different SQL Server shoppers. That is significantly worthwhile for SaaS suppliers and ISVs internet hosting multi-tenant databases on shared cases.

The implementation follows these high-level steps:

1. Put together the RDS for SQL Server occasion with multi-tenant databases and logins.
2. Apply the customized saved process to disclaim database visibility for particular logins.
3. Validate the configuration by confirming restricted visibility.
4. Preserve the power to revert modifications when wanted.

This answer enhances safety posture by lowering the danger of knowledge disclosure and gives a clear tenant expertise with out exposing the database names to tenants. The next diagrams illustrate the safety posture earlier than and after implementation.

Stipulations

You should have the next stipulations:

• Entry to an AWS account
• Primary understanding of SQL Server and safety ideas
• An RDS for SQL Server DB occasion and login data to connect with it

Deploying Amazon RDS for SQL Server will incur costs. Overview AWS Pricing earlier than continuing.

Put together RDS for SQL Server occasion

Create two new databases and two logins and grant acceptable permissions:

1. Create an RDS for SQL Server occasion utilizing the AWS CLI or AWS Administration Console.
2. Connect with the RDS for SQL Server occasion utilizing the Major login.
3. Create two databases, Tenant1DB and Tenant2DB:

   CREATE DATABASE Tenant1DB
   GO
   CREATE DATABASE Tenant2DB
   GO

4. Create two logins, Tenant1 and Tenant2:

   USE [master]
   GO
   CREATE LOGIN [Tenant1] WITH PASSWORD=N'xxxxxx' 
   GO
   USE [master]
   GO
   CREATE LOGIN [Tenant2] WITH PASSWORD=N'xxxxxx' 
   GO

5. Grant Tenant1 permissions to Tenant1DB:

   USE [Tenant1DB]
   GO
   CREATE USER [Tenant1] FOR LOGIN [Tenant1]
   GO
   USE [Tenant1DB]
   GO
   ALTER ROLE [db_owner] ADD MEMBER [Tenant1]
   GO

6. Grant Tenant2 permissions to the Tenant2DB:

   USE [Tenant2DB]
   GO
   CREATE USER [Tenant2] FOR LOGIN [Tenant2]
   GO
   USE [Tenant2DB]
   GO
   ALTER ROLE [db_owner] ADD MEMBER [Tenant2]
   GO

Overview database permissions

Log in utilizing every tenant’s login and validate the default conduct that each one database names are seen:

1. Open SSMS.
2. Log in utilizing Tenant1.
3. Develop the Databases folder.
4. Verify that Tenant1 can see all of the database names.
   
   
5. Equally, log in to the occasion utilizing Tenant2.
6. Verify Tenant2 can see all database names.
   
   

Modify view database title permissions

On this part, you apply the Amazon RDS for SQL Server customized saved process to alter database visibility for logins:

1. Connect with the occasion utilizing the Major login.
2. Execute the next script:

   EXEC msdb.dbo.rds_manage_view_db_permission @permission='DENY', @server_principal="Tenant1"

As acknowledged earlier, the saved process doesn’t management database permissions; as a substitute, it solely hides database names.

Validate view database title permissions

Now you can validate that Tenant1 can not see database names:

1. Open SSMS.
2. Log in utilizing Tenant1.
3. Develop the Databases folder.
4. Verify that Tenant1 can’t see any of the database names.
   
   

Database names are hidden. Nevertheless, when you hook up with the occasion with the Tenant2 login, it is best to see the database names. The reason being that the customized saved process was not utilized to the Tenant2 login.

Database names are seen.

Revert modifications

To revert the modifications, full the next steps:

1. Revert the modifications by operating the next command:

   EXEC msdb.dbo.rds_manage_view_db_permission @permission='GRANT', @server_principal= ‘Tenant1’

2. Sign off and open a brand new question session to the RDS occasion utilizing the Tenant1 login.
3. Verify you’ll be able to see the database names once more.
   
   
   
   Database names are seen.

Limitations

The answer has the next key limitations and operational issues:

• When a login is dropped and recreated, the saved process have to be re-executed to reapply the permission.
• This saved process doesn’t handle database entry permission; database entry permissions should nonetheless be managed individually via acceptable safety measures.
• Database names would possibly stay seen in SQL Server traces, error logs, and particular dynamic administration views (DMVs), even when this permission is utilized.
• When the permission is revoked, the database names will likely be seen to that login.
• Permissions are set on the server degree. When a database is restored to a brand new occasion utilizing the restore methodology, the permissions have to be reapplied.
• The database title visibility management can’t be utilized to the Major login; all database names all the time will likely be seen for the Major login.

Clear up

When you created a check database to comply with together with this demonstration, be certain that to clear up assets to keep away from pointless costs. Take away the check customers, logins, and databases out of your RDS for SQL Server occasion. When you particularly created an RDS occasion or EC2 host for this demo, delete these assets via the Amazon RDS and Amazon EC2 consoles, respectively, in the event that they’re not getting used. This can assist keep away from incurring undesirable costs.

Conclusion

On this submit, we demonstrated tips on how to handle database title visibility in a multi-tenant atmosphere utilizing Amazon RDS for SQL Server. We walked via the method of hiding database names utilizing the Amazon RDS for SQL Server customized saved process, which helps stop different tenants on the identical host from seeing doubtlessly delicate buyer data that could be revealed via database names. You’ll be able to apply this answer to manage database title visibility in your Amazon RDS for SQL Server environments whereas sustaining the required entry permissions to your purposes.

To be taught extra about Amazon RDS particular frequent DBA duties, consult with Frequent DBA duties for Amazon RDS for Microsoft SQL Server.

In regards to the Authors