How can I restrict a database user from accessing The tables indirectly through view or procedures

Question

I have create login called limited_a and then I created user for limited_a called limited_u for the databse test_db. I have table t1, t2, t3, t4 and user limited_u has select permission over only t1 and t2 and their is a view called get_t3 which retrieve the data from the table t3 and a procedure called update_t4 that update the data in the table t4 .As we know user limited_u don't have access to the table t3 and table t4 but still it can manipulate the data indirectly using the view of procedure or any other methods like functions. I need to restrict the user limted_u. How can a achieve that restriction over the user. I cant restrict the user from accessing the procedures or view but I need to restrict the user from accessing the tables through view and procedures.

I already try using alter authorization with this I can achieve that but then it revoke the permission from other user. I tried deny , revoke permission over tables but it still user can access the tables through view or procedures

Answer 1

Here's how you can restrict a database user from accessing tables indirectly through views or procedures in SQL Server:

Grant Permissions on Views and Procedures:

• By default, users inherit permissions on views and procedures based on their underlying table permissions. However, you can explicitly grant permissions on views and procedures independently of the tables they access.
• Grant only the necessary permissions (SELECT, INSERT, UPDATE, DELETE) on the view or procedure to the user. This restricts the user from performing unintended actions on the underlying tables.

Example:

1. Create a table named Customers with customer information.
2. Create a view named CustomerList that exposes only specific columns from the Customers table.

T-SQL:

CREATE TABLE Customers (
  CustomerID int PRIMARY KEY,
  CustomerName nvarchar(50) NOT NULL,
  Email nvarchar(100),
  Phone varchar(20)
);

CREATE VIEW CustomerList AS
SELECT CustomerID, CustomerName
FROM Customers;

3. Create a user named SalesUser and grant them only SELECT permission on the CustomerList view.

T-SQL:

CREATE LOGIN SalesUser WITH PASSWORD = 'StrongPassword!';
CREATE USER SalesUser FOR LOGIN SalesUser;

GRANT SELECT ON CustomerList TO SalesUser;

In this scenario, the SalesUser can access customer names and IDs through the CustomerList view, but they cannot directly access the Customers table and potentially sensitive information like email addresses and phone numbers.

Additional Techniques:

• Schema Binding: Bind views and procedures to a specific schema that the user doesn't have access to. This prevents them from executing the view or procedure even if they have permissions on the underlying tables.
• Row Level Security: Implement row-level security on the tables to restrict data access based on user attributes. This allows controlled access even when the user has permissions on the view or procedure.

Remember:

• Restricting access at the most granular level (views/procedures) offers the best security.

Answer 2

The ability to access tables t3 and t4 through views and stored procedures while the user does not have direct access, is due to what's called "Ownership Chaining" in SQL Server.

SQL Server uses an approach where, by default, if the object (e.g., table) and the procedure/view are owned by the same user, it does not check permissions on the object. This is the "ownership chain".

Approach 1: Break the Ownership Chain You could break the ownership chain by changing the owner of the table to a different user than the owner of the procedure/view. Then SQL Server will check the user's permissions on the underlying tables when the procedure/view is executed.

ALTER AUTHORIZATION ON OBJECT::t3 TO AnotherUser;
ALTER AUTHORIZATION ON OBJECT::t4 TO AnotherUser;

This could have implications on other users or roles, so it needs careful analysis.

Approach 2: Use View with SCHEMABINDING Another method would be to create the views with the WITH SCHEMABINDING option. When SCHEMABINDING is in use, SQL server will enforce permission check on the underlying table even if it's part of an ownership chain. But in this way, the view will be bind to the schema of the base table, so this might have implications in altering the base tables t3 and t4.

Approach 3: Use EXECUTE AS in Stored Procedures You can define a user to run the stored procedure. This will limit the stored procedure to the access of the specified user.

CREATE PROCEDURE dbo.update_t4 WITH EXECUTE AS 'limited_u'

This will limit the operations within the stored procedure to the access rights of limited_u which should restrict access to t3 and t4. However, this can also have some security implications so be sure to understand how EXECUTE AS works before applying it.

Lastly, remember each solution comes with its own set of caveats and potential variation in the behavior of your SQL Server database, depending on its current setup, so careful assessment and adequate testing in a non-production environment are necessary before making such changes.