December 5, 2019 at 6:41 pm
Hello experts,
We have an application that creates new lookup tables on the fly. I want to allow the db user in question to create the necessary tables, but it seems that I have to use either GRANT CONTROL or GRANT ALTER for the dbo schema to allow the user to create tables.
However, I don't see a way to prevent that db user from being able to drop tables - not only the ones it creates but ANY table in the schema.
Thanks for any help. I realize that planning the schemas should have been done before (if that is the solution) but, well, here we are.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 5, 2019 at 6:52 pm
Probably the easiest work-around is likely a DDL trigger that prevents them from issuing any DROP TABLE (and ALTER TABLE on a table they didn't create, if you need that, but then you'd need an accurate way to determine which tables they created).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 5, 2019 at 8:43 pm
Create a stored procedure to create the lookup table. The create procedure would start looking like this:
CREATE PROCEDURE <schemaname>.<procedurename> [parmeter [, parameter]...]
WITH EXECUTE AS OWNER
AS
<procedure body>
You would create the procedure with a user that has the necessary permissions to accomplish the task.
Then, you simply grant the user(s) that don't have permissions to directly create tables to execute this stored procedure.
December 5, 2019 at 8:47 pm
Thanks, Scott, Lynn!
I am going to try Lynn's suggestion first.
Lynn, just to make sure I understand correctly - granting permission to execute the stored procedure means I can just have the proc run CREATE TABLE commands without actually granting the application user any direct CREATE TABLE permissions? (And thereby obviating the need to DENY or LIMIT any associated DROP TABLE permissions)?
Thanks again.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 5, 2019 at 8:59 pm
Correct. I do the same thing for an application where the user app does not have the direct ability to insert or update data in tables. This application uses stored procedures to accomplish the inserts and updates. For this application there are no deletes of data so I don't have any procedures for handling that function (yet).
December 5, 2019 at 9:15 pm
A proc is possible, but most people don't code an actual CREATE TABLE statement, they use the gui. They would have to pass in a block of SQL, which means you have all sorts of issues with possible mal-code, etc.. I tried using temp tables as patterns to create the table from, with them passing in the temp table name, and even that didn't work all that well.
Hopefully you'll fare better with it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 5, 2019 at 9:18 pm
i wrote a decent DDL trigger example for someone that had a similar-ish requirement.
some people needed to be able to create ONLY views.
the solution was arole,a nd a DDL trigger that checked if they were in that role.
take a look at this forum post, and see if it might help:
https://www.sqlservercentral.com/forums/topic/create-user-role-grant-permission
Lowell
December 5, 2019 at 9:19 pm
Thanks, Scott, those are excellent points. I am considering them as we speak. In this case, the tables all seem to be quite similar (i.e., the app uses a common structure that may make it easier to pass in just, say, a name). I also considered what you mentioned - namely, that it seems some kind of dynamic SQL will be needed, which opens up at least the possibility of SQL injection vulnerabilities.
But I will see how it goes. If it proves unwieldy I will go with the DDL trigger instead. I just appreciate that Lynn suggested it because I wasn't even thinking along those lines.
Thanks again.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 5, 2019 at 10:35 pm
Thanks, Scott, those are excellent points. I am considering them as we speak. In this case, the tables all seem to be quite similar (i.e., the app uses a common structure that may make it easier to pass in just, say, a name). I also considered what you mentioned - namely, that it seems some kind of dynamic SQL will be needed, which opens up at least the possibility of SQL injection vulnerabilities.
But I will see how it goes. If it proves unwieldy I will go with the DDL trigger instead. I just appreciate that Lynn suggested it because I wasn't even thinking along those lines.
Thanks again.
-- webrunner
Yes, with dynamic SQL there could be a possibility of SQL injection. The difference here is that you can control and defend against SQL injection attacks. If the lookup tables have identical structures except for the table name it is even easier to defend against.
If you can template the create of the lookup tables then the complexity of using dynamic SQL can be reduced and you don't have to worry about permissions to users regarding creating tables and preventing them from dropping tables that they shouldn't. Users should be given the least privileges needed to accomplish their jobs and nothing more.
December 5, 2019 at 10:58 pm
Expect to have to do lots of tweaking to the code to handle all the things that come up, though.
NULL / NOT NULL for each column (which should always be explicitly specified when creating tables)?
Defaults? Other constraints?
Clustered index? Primary key? Nonclus index(es)?
The difference here is that you can control and defend against SQL injection attacks.
Using the standard methods, right? To me, it's just as hard here as it would be for any dynamic SQL. That's not a trivial process when you can't enforce use of sp_executesql. When they are passing in full SQL, it's not trivial even then.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 6, 2019 at 1:28 am
Expect to have to do lots of tweaking to the code to handle all the things that come up, though.
NULL / NOT NULL for each column (which should always be explicitly specified when creating tables)?
Defaults? Other constraints?
Clustered index? Primary key? Nonclus index(es)?
The difference here is that you can control and defend against SQL injection attacks.
Using the standard methods, right? To me, it's just as hard here as it would be for any dynamic SQL. That's not a trivial process when you can't enforce use of sp_executesql. When they are passing in full SQL, it's not trivial even then.
How do we know that the user is creating full SQL code? Perhaps it is being built based on user input and the parts you are talking about here are predefined in the code written in the stored procedure? I will be honest, I would not allow a user to create any tables using either a GUI interface like SSMS or other such tools or by directly writing the SQL CREATE TABLE statements directly. I have no faith that they would do a good job at it and that is based on the supposed skills of people where I work who think they know how to it. Yea, that's why we get data type mismatches between columns with the same name and data held in various tables, or reserved words used as column names, or worse names with special characters because they used a template and didn't properly replace values.
We don't know what type of environment Webrunner is working in and I provided a possible means of accomplishing what needs to be done. I am not attacking your suggestion as it is a valid suggestion. However it also violates the rule of least privilege that should be strived for when possible.
December 6, 2019 at 3:19 pm
ScottPletcher wrote:Expect to have to do lots of tweaking to the code to handle all the things that come up, though.
NULL / NOT NULL for each column (which should always be explicitly specified when creating tables)?
Defaults? Other constraints?
Clustered index? Primary key? Nonclus index(es)?
The difference here is that you can control and defend against SQL injection attacks.
Using the standard methods, right? To me, it's just as hard here as it would be for any dynamic SQL. That's not a trivial process when you can't enforce use of sp_executesql. When they are passing in full SQL, it's not trivial even then.
How do we know that the user is creating full SQL code? Perhaps it is being built based on user input and the parts you are talking about here are predefined in the code written in the stored procedure? I will be honest, I would not allow a user to create any tables using either a GUI interface like SSMS or other such tools or by directly writing the SQL CREATE TABLE statements directly. I have no faith that they would do a good job at it and that is based on the supposed skills of people where I work who think they know how to it. Yea, that's why we get data type mismatches between columns with the same name and data held in various tables, or reserved words used as column names, or worse names with special characters because they used a template and didn't properly replace values.
We don't know what type of environment Webrunner is working in and I provided a possible means of accomplishing what needs to be done. I am not attacking your suggestion as it is a valid suggestion. However it also violates the rule of least privilege that should be strived for when possible.
In a sense it does give them more permissions, which you then have to block, but that is caused by MS, not because I want to give them additional authority. MS really needs to allow a GRANT CREATE TABLE, with a schema specification, to actually allow that user to create a table under a given schema, instead of also requiring other higher-level permissions.
Until MS fixes that, all I was saying is that we actually tried it the other way and it was a royal pain because niggling things kept coming up. I figured it would be better for the OP to be able to put some though into those things in advance, rather than getting blind-sided by them.
As an aside, I don't really mind users (/developers) creating tables, if it's necessary in the business context. I don't want them creating indexes, since they don't understand the implications and the full options available for each type of index.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 6, 2019 at 4:15 pm
I'm coming in to this debate a bit late, but my preference is to not let users build tables on LIVE systems if I can avoid it.
The developers where I work get full sysadmin on the test systems which likely isn't the best practice, but if they screw up a test system, it is only test that goes down and we can rebuild by restoring from live. We don't store any PII data or anything that would be covered by GDPR or anything, so doing this has little risk.
The developers build up tables, stored procedures, functions, triggers, etc... whatever they need. Then that gets scripted out, reviewed by a DBA, corrected (if necessary) and then pushed out to live. That way the DBA's can make sure the tables are normalized (if they need to be), build appropriate indexes, validate datatypes, etc. One developer had created a table with multiple VARCHAR(8000) columns in it and it raised some red flags for me.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 6, 2019 at 4:18 pm
Lynn Pettis wrote:ScottPletcher wrote:Expect to have to do lots of tweaking to the code to handle all the things that come up, though.
NULL / NOT NULL for each column (which should always be explicitly specified when creating tables)?
Defaults? Other constraints?
Clustered index? Primary key? Nonclus index(es)?
The difference here is that you can control and defend against SQL injection attacks.
Using the standard methods, right? To me, it's just as hard here as it would be for any dynamic SQL. That's not a trivial process when you can't enforce use of sp_executesql. When they are passing in full SQL, it's not trivial even then.
How do we know that the user is creating full SQL code? Perhaps it is being built based on user input and the parts you are talking about here are predefined in the code written in the stored procedure? I will be honest, I would not allow a user to create any tables using either a GUI interface like SSMS or other such tools or by directly writing the SQL CREATE TABLE statements directly. I have no faith that they would do a good job at it and that is based on the supposed skills of people where I work who think they know how to it. Yea, that's why we get data type mismatches between columns with the same name and data held in various tables, or reserved words used as column names, or worse names with special characters because they used a template and didn't properly replace values.
We don't know what type of environment Webrunner is working in and I provided a possible means of accomplishing what needs to be done. I am not attacking your suggestion as it is a valid suggestion. However it also violates the rule of least privilege that should be strived for when possible.
In a sense it does give them more permissions, which you then have to block, but that is caused by MS, not because I want to give them additional authority. MS really needs to allow a GRANT CREATE TABLE, with a schema specification, to actually allow that user to create a table under a given schema, instead of also requiring other higher-level permissions.
Until MS fixes that, all I was saying is that we actually tried it the other way and it was a royal pain because niggling things kept coming up. I figured it would be better for the OP to be able to put some though into those things in advance, rather than getting blind-sided by them.
As an aside, I don't really mind users (/developers) creating tables, if it's necessary in the business context. I don't want them creating indexes, since they don't understand the implications and the full options available for each type of index.
There is a big distinction between users and developers. I would give developers ddl_admin rights in shared development environment, even though I would prefer that developers do their initial development in an individual development environment such that work they may do does not impact other developers. No users or developers should be creating ad hoc tables in a production environment unless done in a strictly controlled manner, which means they aren't writing and executing SQL code directly in the production environment either textually or with a graphical tool such as SSMS. If this is need, build a process that allows them to specify what is needed and build the rest behind the scenes so that appropriate data type, indexes, etc. are properly created.
December 6, 2019 at 5:08 pm
Thanks to everyone for the continued feedback. As I understand it, the need for these tables is that the application creates the lookup tables as needed due to possible new lookup table values on the application side. Having said that, I do see your point, bmg002, that it is best not to allow developers to create tables in live systems, even if that creation is created by proxy using the app.
But this is how the vendor's app apparently works. I believe the developer in question can suspend this feature because I have been creating the tables by hand for them. So I can recommend that we leave that feature off, create tables by hand when needed, and ask the vendor to explain why they can't provide a complete list of all possible lookup tables - thereby avoiding any need for dynamic table creation.
Thanks again.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply