I was playing with a few SQL server idiosyncrasies more than a year ago before becoming so completely distracted with the whole SAP protocol-decoding business. Having some time on my hands for once, I thought I would blog it.
Early last year, I found it possible to create jobs owned by other users on MS SQL Server (2000, 2005 and 2008) by an unprivileged user – providing the user had the capability of creating or altering stored procedures in the [master].[dbo] schema. The reason for this, comes as a result of cross-database permissions being chained, by default, across the system databases [master], [msdb] and [tempdb]. According to Microsoft, this is by design.
Where the issue comes in is that should a lower-privileged user have the capability of creating or altering stored procedures within [master].[dbo], it now becomes possible to insert records into the [msdb].[dbo].[sysjobs*] by executing the stored procedure – even without having any direct access to insert records into these tables. This is not particularly different from other system stored procedures (such as sp_addjob, for example) which allow users to create jobs, but the difference comes in in terms of the data we’re allowed to populate.
SQL Server allows jobs and job steps to be executed under the context of specific user accounts. Whilst the majority of users (by default) are able to schedule jobs on the SQL Server, they can only schedule jobs which execute under their own account context and only members of the sysadmin server roles can add jobs which execute under the context of other user accounts. The underlying system stored procedures provided by Microsoft (sp_addjob and similar) prevent this functionality from being abused by lower privileged users. However, should it be possible to create/alter a stored procedure within [master].[dbo], we can insert records into the various [msdb] job tables with data of our choosing.
Hard-coding the [owner_sid] field in [msdb].[dbo].[sysjobs] to 0x01 (the default sid for the ‘sa’ user account) and the [database_user_name] field in [msdb].[dbo].[sysjobsteps] to ‘sa’ will allow us to create a job and associated job-step owned by the ‘sa’ user even though we are using an unprivileged account and do not have any permissions on the underlying tables.
In the following image, user eoppoc has no direct access to [msdb].[dbo].[sysjobs].
Executing a stored procedure, however, allows this access.
The following two images show the job created us user ‘sa’, with a single job step configured to execute as user ‘sa’.
Honestly, I don’t really see this as any form of issue. In order for it to be exploitable, there are far too many prerequisites and requirements and these prerequisites open other cans of worms. Furthermore, whilst one has the capability to schedule jobs to run as other users, one does not have the privilege level required to update the job cache. This means the newly created and scheduled job will only run after the SQL Server Agent has been restarted. It is nevertheless interesting and blog-worthy. And who knows, maybe this will be interesting / useful to somebody.
A zip file containing a procedure for SQL2000, and a procedure for SQL2005/2008 can be downloaded from here.
Oh – as a final note, Microsoft mentions that: “These databases are strictly system databases and is recommended not to create any user objects in these databases”.
/ian