March 9, 2020 at 5:28 pm
I am willing to give them db_owner on their own database, if the code they provide kills that products database I know it isn't my problem, and I have backups!
Just make sure that the database is owned by a low-privileged login, and not sa.
I have had one application that was written so badly that it had to run with 'sa' and wouldn't work otherwise, I told the department to buy their own SQL server as it wasn't running on ours.
To be fair, sometimes instance-level permissions are needed, depending on the task / operation. HOWEVER, it is never required to add a login to the sysadmin fixed server role, or to grant CONTROL SERVER. All that is needed is for the vendor to show you the code that requires the elevated permissions (so that you can verify that it isn't doing more than they claim it is doing), and then you would do the following:
[master]These permissions cannot be extended without your knowledge and involvement due to:
EXECUTE AS )
For more info, please see:
Take care, Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 9, 2020 at 5:39 pm
wait until you get an application that wants to create sql agent jobs and then delete them after. not a hope in hell stopping that process running as SA
Mike, you spoke too soon. There absolutely is not only a hope, but a very real method of granting limited access without making the vendor's login a member of the sysadmin fixed server role. You, or the vendor, can create a stored procedure that creates that specific job, and another stored procedure that drops that specific job. Then you simply use Module Signing to grant those two stored procedures the necessary permissions. Even if the permissions needed are sysadmin, the vendor's login will never have that access. And, this is even better than adding their login to [msdb] as a user just so that you can add them to one of the built-in SQL Server Agent roles that allows for creating and/or managing jobs, because those are open-ended permissions whereas this approach only allows for creating and dropping this one specific job, without allowing for the vendor to create any other job (I am not including managing / dropping here as that is already kinda restricted if you make their login the owner of the job, as they can modify their own jobs but not those owned by anyone else).
Take care, Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 9, 2020 at 5:48 pm
My favorite project of all time used all stored procedures. It was my favorite because the only privs the app needed was CONNECT and EXECUTE on certain stored procedures.
Exactly. And this is easy to accomplish, even if elevated database-level or even instance-level permissions are needed, and without resorting to using any combination of EXECUTE AS (i.e. impersonation), Cross-DB Ownership Chaining, or TRUSTWORTHY ON, by using Module Signing. If more people knew about this option, there would be fewer requests for sa (without constraining any functionality).
Take care, Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 9, 2020 at 7:08 pm
MVDBA (Mike Vessey) wrote:wait until you get an application that wants to create sql agent jobs and then delete them after. not a hope in hell stopping that process running as SA
we ended up putting database triggers on to make sure that account was not being missued
Heh... yeah... that's just not going to happen on my watch. At the very worst, there will be a properly written stored procedure that very carefully creates such a job (carefully means highly constrained to not allow any "creativity" by the app) that I'll give the app EXECUTE privs on, but that's about it. You also don't need to every grant such processes SA privs. MSDB has it's own special privs but I'm just as adamant about minimal privs there, as well.
My favorite project of all time used all stored procedures. It was my favorite because the only privs the app needed was CONNECT and EXECUTE on certain stored procedures.
+1000 to that Jeff.
March 9, 2020 at 7:42 pm
Definitely there isn't even a need for db_owner for developers.
Based mainly on the work from Solomon I have implemented a solution in one of our servers (to be expanded to other teams) that is shared by the actuaries on my company (multiple Business Units).
Only permissions they have on each DB (in dev) are db_reader, db_writer and ddl_admin (so they can create code/objects)
in Prod only db_reader and execute/select permissions on functions/procs
whenever they create any function/proc it gets automatically signed by a cert and execute/select permissions are granted to that object to a list of AD groups that are associated with the current database (all from config tables)
this signing is done through a DDL trigger at database level that does the following
Its a bit of work to set it up but it works well
Now if I could only sort the issues with SQL Jobs with proxies and linked servers and impersonation it would be great!!!
March 10, 2020 at 10:10 am
MVDBA (Mike Vessey) wrote:wait until you get an application that wants to create sql agent jobs and then delete them after. not a hope in hell stopping that process running as SA
Mike, you spoke too soon. There absolutely is not only a hope, but a very real method of granting limited access without making the vendor's login a member of the
sysadminfixed server role. You, or the vendor, can create a stored procedure that creates that specific job, and another stored procedure that drops that specific job. Then you simply use Module Signing to grant those two stored procedures the necessary permissions. Even if the permissions needed aresysadmin, the vendor's login will never have that access. And, this is even better than adding their login to[msdb]as a user just so that you can add them to one of the built-in SQL Server Agent roles that allows for creating and/or managing jobs, because those are open-ended permissions whereas this approach only allows for creating and dropping this one specific job, without allowing for the vendor to create any other job (I am not including managing / dropping here as that is already kinda restricted if you make their login the owner of the job, as they can modify their own jobs but not those owned by anyone else).Take care, Solomon...
I use "execute as" quite a lot, but sadly this is a 3rd party app and it was made very clear to us that we could not touch anything in the database. if we do then we lose our support agreement..
its ok - I just shifted it to a new virtual server that had no other systems on
MVDBA
March 10, 2020 at 8:24 pm
Maybe the answer is much simpler...
Will it be the DBA or the Developer that gets woken up at 3:00am because the application is slow/not working? That person can decide proc or no proc. 😉
Of course I'm being a little fecetious but the number of times I've been tasked with "fixing" something that was written using LINQ...
March 10, 2020 at 9:17 pm
Kevin Steffer [MCP] wrote:When working with SP inside a .net project feels like Blackbox development. The Code you write looks like it assumes too much and needs a magic trust to what data an SP will return.
With Linq2sql you get compiler warnings when you cast types wrong or misspell fields etc. That is much faster to develop with Linq2Sql and less error prone.
I'd argue this is something you should be able to deal with. There aren't that many types and you can scaffold this out easily. I would also argue that your speed of development is overwhelmed by the limitations of tight coupling. We use methods to abstract and enable refactoring. Views and procedures are part of this in RDBMS development.
Most modern programming environments are capable of controlling the degree of coupling between modules, its just not an SQL Server thing.
March 11, 2020 at 2:42 am
Maybe the answer is much simpler...
Will it be the DBA or the Developer that gets woken up at 3:00am because the application is slow/not working? That person can decide proc or no proc. 😉
Of course I'm being a little fecetious but the number of times I've been tasked with "fixing" something that was written using LINQ...
I don't believe I ever remember having an application developer called in to help in the middle of the night. It was always us DBA's. Whatever the cause, we were the ones who had to get things going.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
March 11, 2020 at 8:41 am
Y.B. wrote:Maybe the answer is much simpler...
Will it be the DBA or the Developer that gets woken up at 3:00am because the application is slow/not working? That person can decide proc or no proc. 😉
Of course I'm being a little fecetious but the number of times I've been tasked with "fixing" something that was written using LINQ...
I don't believe I ever remember having an application developer called in to help in the middle of the night. It was always us DBA's. Whatever the cause, we were the ones who had to get things going.
+1
its always me , and the dev team say "it's not my job"... my non argumentative reply is "perhaps if you stopped breaking code then I could sleep"
MVDBA
March 13, 2020 at 1:32 pm
What i like about stored procedures is that the code is closest to the database. This is especially important if you have more than one way of accessing the database. i.e. SSRS, SSIS, c#, Ef etc.
The last thing you really want to do is duplicate code.
More of our code is in a c# project with tens of thousands (or more) lines of code. Sometimes I need to duplicate some of the logic for SSIS. Not easy to do and there is always the issue of someone changing one version of the code and forgetting or not knowing that there is other code that needs to match it.
Also anything outside of the database can end up having a lot more round trips between the SQL server and other machines.
March 14, 2020 at 3:02 am
skeleton567 wrote:Y.B. wrote:Maybe the answer is much simpler...
Will it be the DBA or the Developer that gets woken up at 3:00am because the application is slow/not working? That person can decide proc or no proc. 😉
Of course I'm being a little fecetious but the number of times I've been tasked with "fixing" something that was written using LINQ...
I don't believe I ever remember having an application developer called in to help in the middle of the night. It was always us DBA's. Whatever the cause, we were the ones who had to get things going.
+1
its always me , and the dev team say "it's not my job"... my non argumentative reply is "perhaps if you stopped breaking code then I could sleep"
Heh... it's IT... anyone that says "it's not my job" needs to be careful that they don't get that wish. It's ok to say "I don't know how but <insert name of SME here> does" but "it's not my job" seems to grate people. I can't say that I blame them.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 61 through 71 (of 71 total)
You must be logged in to reply to this topic. Login to reply