דצמ7

Written by: ronen ariely
07/12/2021 18:07 

Introductions

From time to time someone come to the forum with an interview questions which are not a real scenario on live servers and in other cases the requirement on live servers should be implemented and you better re-design your system so you will not need this recruitment. What ever the reason is, you might want to know how the task can be done and this is what we have in the following request on stackoverflow question.

So.. if someone ask, let's provide the answer...

In this post I will discuss the basic steps in order to give all (or specific) USERs permission to Create table and to provide permission on the table only to the user which created the table.

Discussion time

For the sake of the discussion let's CREATE new LOGIN, new SCHEMA, and new USER.

In theory, you could provide the permission to CREATE TABLE to all users as originally asked for, by simply have a rule which allows CREATE TABLE on specific schema to all users. Something like: GRANT CREATE TABLE ON SCHEMA::SO_Schema TO public;. Remember that the ROLE "public" in SQL Server is used for all users.

In this case we could give everyone the option to CREATE TABLE in the schema and we could simply use DDL trigger on CREATE TABLE in order to add permissions on the new table like SELECT,DELETE,INSERT,UPDATE for the user that created the table.

unfortunately, GRANT CREATE TABLE ON SCHEMA is not supported.

   

---

To CREATE TABLE you Required to have CREATE TABLE permission in the database and ALTER permission on the SCHEMA in which the table is being created.

You can read more in the official documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15#permissions-1

This makes the task more complex and probably not a recommended in most cases, since you will need to provide more permissions than what you really want the USER to have...

   

---

If you still want to get this task (against the recommendation) or you just want to learn about your options, then you will need to GRANT ALTER ON SCHEMA and GRANT CREATE TABLE on database to all - all means "public".

next, you will need to DENY the unwonted permission since the above will give all USERs a lot more power than you want to!

This can be done by CREATE DDL TRIGGER on the DATABASE for any DDL_DATABASE_LEVEL_EVENTS.

https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-event-groups?view=sql-server-ver15

inside the TRIGGER you should check what was the event. If it was something else than CREATE_SCHEMA or the USER that executed the event should not CREATE SCHEMA then you ROLLBACK TRANSACTION;.

Note! Since you do not want to change the trigger each time a new USER need to CREATE TABLE and add the USER name to the hard coded list of users which can CREATE TABLE, it is best to CREATE new ROLE and simply add each USER you need to this ROLE.

In this case that you based on a ROLE like above ModifyTable, you can GRANT ALTER ON SCHEMA and GRANT CREATE TABLE only to the ROLE ModifyTable instead of to public

In addition, in the same TRIGGER if the USER is one of these that should be able to CREATE the table then you should GRAND him permission to INSERT, DELETE, UPDATE, SELECT on the table which he just created.

Remember that if you forget to DENY a permission from this USER or all the rest then you might have a security issue - which is why this is not recommended procedure.Your best option is to re-0design the system so you will not need this exact recruitment. So... you can do it as I explained here, but it is not recommended for most cases.

A much better approach is NOT to permit USERs to CREATE TABLEs except for these you can trust with all tables. You should CREATE THE TABLEs for your users directly or using application which you control, and give them the permission to use the specific table which they need. ALTER SCHEMA is not recommended permission to give to simple users!

Here is a quote from the official documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver15

A user with ALTER permission on a schema can create procedures, synonyms, and views that are owned by the schema's owner. Those objects will have access (via ownership chaining) to information in other schemas owned by the schema's owner. When possible, you should avoid granting ALTER permission on a schema if the schema's owner also owns other schemas.

  

Closure

You can gain the requirement by GRANT ALTER ON SCHEMA and GRANT CREATE TABLE on database, but you will need to CREATE TRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS in order to prevent unwanted actions. In addition you will need the TRIGGER to add permission on the table to the USER which created the table.

This action is not recommend in production if not a must. You should think about re-design the system in order to avoid the need for this action as possible.

You can read more about the same scenario and a sample code in this discussion on sqlservercentral.com site.