Non-Admins Creating Stored Procedures
-
I have a situation in my office where some people that are not admins on our SQL Servers (and should not be) want the ability to migrate stored procedures into our production environment.
I have argued against this but I am overruled.
The one concession I have been given is if I can develop an application that lets them choose the source (server, database, and procedure) and the target (server and database) for the migration, we can let them use that and not give them the full rights.One thing that has to be done is to script the target procedure (if it exists) into a text file (.sql) in case it needs to be recovered.
I can figure out how to do most things (find procedures, script procedures, run the script files). The problem comes when I need to do those things that these users don't have authority to do.
I have looked at using application roles, external programs, kicking off SQL Server Agent jobs, and anything else I can find but I also run into a brick wall.
Has anyone out there setup anything like this or do you have any insight on how I might do this?
If I can't come up with a solution, I am going to have to give way too many people DDL access to our production databases.
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
-
Might not be what you want to hear but :
A quote from rudy komacsar
time for:
exec sp_update_resume
and
exec sp_distribute_resume
If they don't understand the risk for that then you can't really do your job and I just wouldn't wanna work like that (cause you'll be responsible if something screws up).
-
Type up a letter to your boss/management:
Dear ,
I have been asked to allow users to create their own stored procedures in the database. This would require giving them admin access. The proper procedure would be for the user to provide me the script and as the database administrator, I will create the stored procedure for the user.
Giving users admin access is a very bad idea. If I am required to do provide users admin access, then I can no longer be held responsible for what happens on the database as I can not control what the user does with this access.
Thank you,
-SQLBill
-
Ya better plan >>
exec sp_update_resume
exec sp_sendBossLetter
if @@Error > 0
exec sp_distribute_resume
-
Here is quote from a major bank's new security access policy:
"No member(s) of any staff or consulting group involved in the development of information systems can request access to a system in a production environment."
A word to your auditors may result in their request being denied.
That said, you could limit their rights by using the deny privilege. Here is a SQL example that would allow them to create, alter or drop procedures owned by any user but not have any other privileges.
exec sp_addrolemember N'db_ddladmin', N'Migrators_of_SPs'
GO
deny CREATE DEFAULT to Migrators_of_SPs
deny CREATE FUNCTION to Migrators_of_SPs
deny CREATE RULE to Migrators_of_SPs
deny CREATE TABLE to Migrators_of_SPs
deny CREATE VIEW to Migrators_of_SPs
deny BACKUP DATABASE to Migrators_of_SPs
deny BACKUP LOG to Migrators_of_SPs
go
Hope this helps.
SQL = Scarcely Qualifies as a Language
-
From a practical point of view, everyone who's posted so far is correct. Write and submit a CYA letter, let people know you are not happy with the situation... and if or when things go screwy, you've got tough decisions to make.
From a theoretical point (meaning it's complex and I don't know if it'd work):
- Shmoes create carefully formatted scripts containing script to implement changes. One file per object, the name of the file is the name of the object generated by the script
- These files are copied to a "loading" directory
- There's a SQL Agent job (runs with SysAdmin rights) that runs every, oh, 5 - 15 minutes
- The job loads all *.SQL files in the Loading directory to a temp table
- For each file name loaded, read/archive the original code for that procedure
- xp_cmdshell out, run carefully formatted OSQL with -E (NT authentication) parameter, using the file as the input for the OSQL
- Log what got done and delete the file
But this is messy, prone to unanticipated errors, and assumes the shmoes are able to consistely produce infallible scripts (which even top SQL Server Central DBAs can't do without effort and debugging).
Good luck, however it turns out!
Philip
-
True story alert....
Back up a few years....I'm a pretty new DBA. My Project Manager (PM) requests that I give the Technical Manager (TM) access to remotely run profiling/baselining scripts. I can't figure out how to do this without giving the person admin access. Letter (email) to PM:
,
I understand you want me to give (TM name omitted) access to run scripts/profiler against the database. From my knowledge and research, the only way to give (TM) this ability is to give him admin privileges. I would prefer that all users send their scripts and/or requests to me. Then I will run them and provide the responses.
-
Response was, do it anyways.
Next email from me:
(PM),
I will do as you request and provide (TM) admin access. However, I need to make clear that I do not agree with this. Admin access allows a person to do whatever they desire, they can even hide what they have done. (TM) in the past, has show that he mis-uses admin access rights. Then not only does he not inform us as to what he will be doing, he denies that he has done anything even when we have the proof.
While it against my better judgement, I will do as you require.
-(my name)
Then I 'caught', by chance, that the TM had run some scripts and moved some files. When I confronted him, he denied it until I showed him the proof. Then he said I should be more concerned that he could do what he did and I needed to secure my databases better. I reminded him that he had admin privileges so it didn't matter how secure the database was. He tried to deny he had admin privileges. I found my last email to my PM in my 'sent' box and forwarded it the PM with the email chain with the TM included. I also told the PM just how bad this could have been if files had been deleted.
On top of it all, the TM was doing his GIAC (SANS Institute) paper on SQL Server security and vulnerablities. I wonder if he added to his paper that every system is vulnerable if the attacker has admin rights - but somehow I doubt he did.
The PM quickly told me to remove the TM's admin access.
Moral of the tale: Cover your a$$, but do it in the most polite way possible. In my case, this wasn't the first problem with the TM that we had reported to the PM, so I included that in my letter. Make sure that you clearly state your doubts with the requirement and provide the solution you feel should be used. Then if the boss sticks to their requirement, you agree to it and keep the email/letter chain. Make sure you state that "I will do as you request."
-SQLBill
-
External auditors love this kind of stuff. You need emails to CYA if management insist.
Several years ago, I actually started BCC'ing emails to my web based private account to CYA myself.
That's how bad it was.
oh, and update your CV...
-
Thanks everyone. This is what I thought but I had to due my "due diligence".
Pardon my ignorance, I've seen it a lot on the boards but I don't know what CV stands for.
Thanks again,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
-
UK for "resume"
Latin: "Curriculum Vitae"
-
By the way, I just realized my method is utter crap. Anyone could put anything they wanted into the file called by OSQL (DROP TABLE CLIENT, anyone?) and you'd have no practical way to verify/validate it.
Philip
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply