Creating a limited access MS-SQL user, especially in scenarios involving a DMZ server and corporate SQL server, requires careful consideration of security and access control. Here are some best practices and alternative approaches:
Schema-Based Access:
Instead of granting access at the database level, consider granting access at the schema level. Create a specific schema within DB_X and grant permissions only on that schema to WEBAPI_USER. This way, the user can see and interact with objects within that schema but won't have visibility into other schemas or databases.
Stored Procedures:
Limit interactions with the database through stored procedures. Grant execute permissions on specific stored procedures that the WEBAPI_USER needs to access, and ensure that these procedures encapsulate the necessary logic. This way, direct access to tables or other objects is not required.
Row-Level Security (RLS):
Depending on your SQL Server version, consider utilizing Row-Level Security (RLS) if applicable. RLS allows you to control access to rows in a table based on a user's identity or role. This can provide fine-grained control over data access.
Database Ownership Chaining:
While it's true that making WEBAPI_USER the owner of the database (DB_X) would grant visibility to all objects, it's generally not recommended for security reasons. Instead, consider enabling database ownership chaining selectively for specific scenarios where it makes sense.
Custom Views:
Create custom views that encapsulate the necessary logic and limit the columns and rows that WEBAPI_USER can access. Grant permissions on these views rather than directly on tables.
Use Roles:
Leverage database roles to group permissions logically. Grant the roles the necessary permissions and then add WEBAPI_USER to these roles. This helps in managing permissions in a more organized manner.
