July 21, 2017 at 6:37 am
Can privileges be granted to create tables in several schemas while limiting the creation of stored procedures to only one schema. I don't *think* this is possible but thought I'd check there wasn't some clever workaround.
As far as I can see if I grant ALTER to multiple schemas as well as CREATE TABLE and PROCEDURE, then tables and procedures can be created in all of the schemas with ALTER rights.
Thanks.
July 21, 2017 at 6:53 am
On the assumption that you are correct about what you see, I don't see why you couldn't create a role that has the necessary ALTER rights for specific schemas, and then create another role that does have the ALTER rights to the schema for stored procedures.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 21, 2017 at 6:54 am
And if that doesn't do it, you may just need to add some DENY permissions to the role or roles in question.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 21, 2017 at 7:46 am
Unfortunately I don't have flexibility on the roles - they're pre-defined within the application. Probably should have mentioned that.
July 21, 2017 at 10:34 am
Pete Bishop - Friday, July 21, 2017 7:46 AMUnfortunately I don't have flexibility on the roles - they're pre-defined within the application. Probably should have mentioned that.
I was referring to roles within the database.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 21, 2017 at 10:50 am
So was I 🙂
There is a database role called "admin", for example, which has:
ALTER on schemas a, b and c
CREATE TABLE
CREATE PROCEDURE
What I'd like to do is restrict the "CREATE PROCEDURE" to schema "b" while allowing tables to be created in all three schemes.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply