How to create specific user security for schemas?

Question

I am a Junior DBA trying to figure out how to add specific security to specific accounts.

So we have a database that allows analysts to create their own tables/views and mess around with data in with no repercussions to the existing important databases. Each user will make their own views/tables called User1.Address for example. And user2 can make a view/table with the same name but as User2.Address.

How can I make it so that User1 can view a table that User2 makes but cannot alter or delete their created tables/views?

Answer 1

There are two main approaches to achieve the desired security level in your database:

1. Granting SELECT permissions on views:

This approach involves creating views for each user's table and granting SELECT permissions on those views to other users who need to see the data. This ensures data separation while allowing controlled access:

Steps:

Create Views: Each user (User1, User2 etc.) creates their own views with their desired table data. Grant SELECT on Views: Grant SELECT permission on User1's view to User2 and vice versa, using a command like: SQL GRANT SELECT ON User1_Address_View TO User2;

2. Granting SELECT with WITH CHECK OPTION:

This approach involves granting SELECT permission on the original tables with the WITH CHECK OPTION clause. This allows users to see the data but restricts them from modifying the table structure or deleting data that doesn't meet specific criteria:

Steps:

Define a check constraint: Define a check constraint on the table that restricts data based on your needs. For example, a check constraint can ensure specific user ownership of rows. Grant SELECT with WITH CHECK OPTION: Grant SELECT permission on the original table to other users with the WITH CHECK OPTION clause, using a command like: SQL GRANT SELECT WITH CHECK OPTION ON User1_Address TO User2;