Best practice for creating a limited access ms-sql user ?
-
Given: 1) Developing a WebService on a DMZ server which will connect back to corporate SQL2019 server. 2) There are lots of other databases on this server, however there will be only one database (DB_X) which these WebServices need to interact. 3) Assume user, WEBAPI_USER, is created. Also executed: DENY VIEW ANY DATABASE TO WEBAPI_USER; 4) Now, that user cannot see any databases via MS SQL Server Studio, not even the DB_X database which has been granted access Note: Kind of crazy because if you run the query: SELECT * FROM SYSOBJECTS WHERE XTYPE = 'U', you will get a full list of all the tables. If done some research on this and it appears that WEBAPI_USER would need to become the owner of the databaase to see it in MSSS. Question: What is everyone else doing to create a limited access MS-SQL user ? Thank you in advance. David
-
Given: 1) Developing a WebService on a DMZ server which will connect back to corporate SQL2019 server. 2) There are lots of other databases on this server, however there will be only one database (DB_X) which these WebServices need to interact. 3) Assume user, WEBAPI_USER, is created. Also executed: DENY VIEW ANY DATABASE TO WEBAPI_USER; 4) Now, that user cannot see any databases via MS SQL Server Studio, not even the DB_X database which has been granted access Note: Kind of crazy because if you run the query: SELECT * FROM SYSOBJECTS WHERE XTYPE = 'U', you will get a full list of all the tables. If done some research on this and it appears that WEBAPI_USER would need to become the owner of the databaase to see it in MSSS. Question: What is everyone else doing to create a limited access MS-SQL user ? Thank you in advance. David
Your description seems to be going backwards. What you should be doing. First create the user. Second give it access to the specific database. Until you give it access it should not be able to do anything. Which is why your description seems off. Seems to suggest that you created it with access to everything and now you want to restrict that access. So whatever you did to create it in the first place is wrong.
-
Given: 1) Developing a WebService on a DMZ server which will connect back to corporate SQL2019 server. 2) There are lots of other databases on this server, however there will be only one database (DB_X) which these WebServices need to interact. 3) Assume user, WEBAPI_USER, is created. Also executed: DENY VIEW ANY DATABASE TO WEBAPI_USER; 4) Now, that user cannot see any databases via MS SQL Server Studio, not even the DB_X database which has been granted access Note: Kind of crazy because if you run the query: SELECT * FROM SYSOBJECTS WHERE XTYPE = 'U', you will get a full list of all the tables. If done some research on this and it appears that WEBAPI_USER would need to become the owner of the databaase to see it in MSSS. Question: What is everyone else doing to create a limited access MS-SQL user ? Thank you in advance. David
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.