How to enable common users to execute the xp_mongoshell stored procedure without improving User Permissions

Environment requirements:

SQL Server 2005 and later versions

 

Background

Xp_mongoshell is a very dangerous stored procedure. It can access the resources of the operating system, but sometimes we also need to use it for some special processing.

From the security perspective, disabling xp_eclipsehll is the most secure. Even if you want to use xp_eclipsehll for special purposes, it is best to write user stored procedures that implement this special purpose, only xp_mongoshell is used in this user stored procedure, while common users can only use these user stored procedures.



Correct Solution

The following example shows how to enable a common user to call a stored procedure that contains xp_mongoshell code without the permission to execute the Stored Procedure xp_mongoshell.

-- 1. logon with xp_cmdshell permission

USE master;

GO

-- 1. a. Create a logon

Create login Cmd_Login

With password = n'pwd. 123 ',

CHECK_POLICY = OFF;

GO

-- 1. B. This logon is built-in and cannot be logged on. This can reduce security hiding.

DENY CONNECT SQL

TO Cmd_Login;

GO



-- 1. c. Because you want to call xp_mongoshell, you must have a user and permissions in the master.

Create user login _login

For login pai_login

WITH DEFAULT_SCHEMA = dbo;



Grant execute on sys. xp_cmdshell

TO Cmd_Login;

GO





-- 2. User Database

USE tempdb;

GO



-- 2.a creates a user for the logon who executes the xp_cmdshell permission

Create user login _login

For login pai_login

WITH DEFAULT_SCHEMA = dbo;

GO



-- 2. B test the Stored Procedure

Create proc dbo. p

With execute as n' Cmd _ Login -- specifies the context when the stored procedure is executed

AS

EXEC master. sys. xp_mongoshell 'dir c :\'

GO





-- 3. Call the Common Logon of the stored procedure

USE master;

GO

-- 3.a Logon

Create login test

With password = n'abc. 123 ',

CHECK_POLICY = OFF;

GO



-- 3. B Database User

USE tempdb;

GO

Create user test

For login test;

GO



-- 3.c permission for executing stored procedures

Grant execute on dbo. p

TO test;

GO



-- 3.d run the test

Execute as login = n' test ';

GO

EXEC dbo. p;

GO

REVERT;

GO



-- 4. delete test

Drop proc dbo. p;

Drop user test;

Drop user Login _login;



USE master;

Drop login test;

Drop user Login _login;

Drop login logs _login;