May 4, 2018 at 5:15 am
Is it possible to grant permissions to a role to allow certain users the ability to create and delete schemas and use them for what they like but make it so they cannot not to alter any objects in or change a specified schema?
The reason is there are some data scientist who want to create their own schemas and use them then drop them. But I don't want to give them any permissions (other than read and write) to one specific schema.
May 4, 2018 at 7:07 am
Jonathan AC Roberts - Friday, May 4, 2018 5:15 AMIs it possible to grant permissions to a role to allow certain users the ability to create and delete schemas and use them for what they like but make it so they cannot not to alter any objects in or change a specified schema?
The reason is there are some data scientist who want to create their own schemas and use them then drop them. But I don't want to give them any permissions (other than read and write) to one specific schema.
I do believe GRANT CREATE SCHEMA to User will imply they can ALTER ANY SCHEMA on the service. This is not the same as granting them permission to SELECT, INSERT, EXECUTE, and so forth. You can certainly specify a user can or cannot do any of these permissions once a schema is created. In meaning, you can allow a user to SELECT from one schema, but not SELECT from another schema. The problem for you is that in your case, the SCHEMA is not created yet. Therefore, how can you not GRANT them the option to restrict themselves without having first given them the ability to assign permissions to ANY SCHEMA first if I'm not mistaken.
However, you could try to DENY the ALTER permission on a specific schema to a specific user that also has the CREATE SCHEMA permission and see if that permission circumvents the ability for said user to ALTER that schema specifically, but can alter all other schemas. I can see this working being the global permission is factored first, then the local permission goes into effect to deny them second. Test it on a test user and see what happens, can't hurt.
How I handle this with the data science team I am assigned to is just create a playground schema for them to play in. Then when we need a specific schema, I have the DBA do this for the team, not the team itself. Mostly, the data scientist cares less what schema and permissions actually mean to the service. They just want to be able to dump or access data in the database. Thus, it's better to just give them that playground schema so they can do this and when things get serious, take the time to create the proper schema and permissions later.
Remember, the database is just a service and like any service, it has to be maintained. You're not trying to restrict the use of this service. You're just trying to ensure it's maintained correctly so it doesn't impload on itself where no one can use the service.
May 4, 2018 at 8:18 am
xsevensinzx - Friday, May 4, 2018 7:07 AMI do believe GRANT CREATE SCHEMA to User will imply they can ALTER ANY SCHEMA on the service. This is not the same as granting them permission to SELECT, INSERT, EXECUTE, and so forth. You can certainly specify a user can or cannot do any of these permissions once a schema is created. In meaning, you can allow a user to SELECT from one schema, but not SELECT from another schema. The problem for you is that in your case, the SCHEMA is not created yet. Therefore, how can you not GRANT them the option to restrict themselves without having first given them the ability to assign permissions to ANY SCHEMA first if I'm not mistaken.However, you could try to DENY the ALTER permission on a specific schema to a specific user that also has the CREATE SCHEMA permission and see if that permission circumvents the ability for said user to ALTER that schema specifically, but can alter all other schemas. I can see this working being the global permission is factored first, then the local permission goes into effect to deny them second. Test it on a test user and see what happens, can't hurt.
How I handle this with the data science team I am assigned to is just create a playground schema for them to play in. Then when we need a specific schema, I have the DBA do this for the team, not the team itself. Mostly, the data scientist cares less what schema and permissions actually mean to the service. They just want to be able to dump or access data in the database. Thus, it's better to just give them that playground schema so they can do this and when things get serious, take the time to create the proper schema and permissions later.
Remember, the database is just a service and like any service, it has to be maintained. You're not trying to restrict the use of this service. You're just trying to ensure it's maintained correctly so it doesn't impload on itself where no one can use the service.
Thanks, Yes, I think the DENY ALTER ON SCHEMA::dbo should do the trick.GRANT CREATE SCHEMA TO myRole;
GRANT CREATE TABLE TO myRole;
DENY ALTER ON SCHEMA::dbo TO myRole;
I think this allows users to create schemas, create and drop tables on their schema and drop their schema. But it doesn't allow them to change anything on the dbo schema.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply