August 17, 2010 at 6:27 am
UserA has the right to create and alter any stored procedures in his own database, no special Server permissions have been granted to this user. Now he adds following code to one of his procedures.
BEGIN TRY
ALTER LOGIN [UserA] WITH PASSWORD=N'xxxxxxxx'
EXEC master..sp_addsrvrolemember @loginame = N'UserA', @rolename = N'sysadmin'
END TRY
BEGIN CATCH
END CATCH
The stored Procedure is scheduled over an SQL Agent Job on regular basis. As the SQL Agent User is sysadmin the code will be executed successfull and the user will be Member of sysadmin role.
What will be "best practice" to deny backdoor sysadmins or other extended permissions from userA while executed under a priviliged User context like SQL Agent?
Thanks,
August 17, 2010 at 7:00 am
I believe the procedure will fail, because only an existing sysadmin can create another sysadmin.
so even though your UserA is db_owner in his database,
as long as his personal logon is not a sysadmin, he can script anything he wants, but it will fail without the underlying permissions.
for the second part, your end user has the ability to create jobs, or a job was already created and he can add records to be processed?
Lowell
August 17, 2010 at 7:43 am
Unfortunately the stored procedure does not fail (if executed under SQL Agent Job). If tested the scenario with following simplified stored procedure
-- Create "bad" procedure
create procedure [dbo].[spadmintest] as
begin
CREATE LOGIN [admintest] WITH PASSWORD=N'xxx', DEFAULT_DATABASE=[master];
EXEC master..sp_addsrvrolemember @loginame = N'admintest', @rolename = N'sysadmin';
end
If now UserA has the right to execute/alter the stored procedure. The exucute fails, as expected
Msg 15247, Level 16, State 1, Procedure spadmintest, Line 3
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_addsrvrolemember, Line 29
User does not have permission to perform this action.
But if the stored procedure is called within the SQL Agent Job (Job owner is the disabled sa-account). The caller is no more the user UserA and the CREATE LOGIN will be executed successfully.
-- T-SQL Agent Job
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'admintest',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'test',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec [dbo].[spadmintest]',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
I understand that this is "by designed" and that the procedure should be defined as 'EXECUTED as owner', but the point I'm interested in is: How to prevent bad guys from getting system privileges by manipulating Stored Procedures/T-SQL Code which are executed under another security context, on a save and easy to admin way.
August 17, 2010 at 9:37 am
If a developer is trying to sneak that by me, first thing as DBA I will revoke all his access and escalate it with manager. So kudos to you for catching it :). If this is a theoretical practice, interesting case; luckily I haven't had any developer trying to sneek code like that in on me yet.
I did have a developer once try to use binary string to get higher level access (he didn't get far :P).
Back to how to prevent it, one way any new SQL Jobs creation should go through you. If there is ability given to application/developers to create it. They must be reviewed by you.
Another option is DDL trigger for ALTER LOGIN (http://msdn.microsoft.com/en-us/library/ms189799.aspx & http://msdn.microsoft.com/en-us/library/bb522542.aspx).
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 18, 2010 at 1:52 am
Thanks for the replies. I will check the DDL Triggers for auditing this and simular grants.
Unfortunately we serve a lot of third party applications on our sql server farm and many installations are executed with temporary higher privileges to allow creation like SQL Agent Jobs. I review these jobs and everything looks OK at the moment, but the developer will have the opportunity to change this stored procedure in his own database afterwards, so reviewing is no 100% guarantee.
August 18, 2010 at 9:01 am
Then DDL trigger is your only choice mate; in production environments I support. No one does change on production servers, they all go through the dba team.
At the end it is DBA who is expected to keep the lights on and is expected to answer if something blows up, or stolen (information). So it is our responsibility to do one of two things either cover ourselves by not letting people muck around or having audit trail so we are safe.
I would recommend saving the default trace files so you know what is happening on that instance in case you have to do digging through the dirt for some non-authorized changes.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply