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

 

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

DisableXp_eclipsehllIt is the most safe. Even if you want to use it for a special purpose, it is best to write some user stored procedures to achieve this special purpose.Xp_mongoshellBut ordinary users can only use these user stored procedures.

The following example shows how to enable a common user to execute the Stored Procedure xp_mongoshell without the permission.CodeUser stored procedures.

-- 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 'COMMAND _ 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;
Additional instructions In most cases, the database owner isSAClass 1SysAdminFixed server role members. In this case, you can also directly specify the database owner as the security context for the stored procedure execution.

-- 2. User Database

Use tempdb;

Go

-- 2. B test the Stored Procedure

Create proc DBO. p

With execute as N 'dbo' -- specifies the context when the stored procedure is executed

Asexec 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;

 

Use master;

Drop login test;

When using the preceding method, the instance must have an xp_cmdshell Proxy account (which is not available by default). Otherwise, the following error message is returned.

message 15153 , level 16 , status 1 , process xp_mongoshell , 1 rows

Xp_mongoshellThe proxy account information cannot be retrieved or is invalid. Verify### Xp_mongoshell_proxy_account ##'The Credential exists and contains valid information.

 

You can use the following code to create an xp_mongoshell Proxy account.

Use master;

Go

 

Declare

@ User sysname,

@ Password sysname,

@ SQL varchar (1000 );

 

-- Create a Windows user for the xp_cmdshell Proxy account in the operating system

Select

@ User = n' xpcmdaccount ',

@ Password =N'p @ ssw0rd .',

@ SQL = 'net user "'+ @ user +'" "'+ @ password +'"/add ';

Exec SYS. xp_cmdshell @ SQL;

 

-- Create an xp_cmdshell Proxy account

Select

@ User = convert (sysname, serverproperty (n'machinename '))

+ N' \ '+ @ user;

Exec sp_xp_mongoshell_proxy_account @ user, @ password;

 

To use xp_mdshell, you must open the "xp_mongoshell" option of the server. refer to the following code.

Exec sp_configure 'show advanced options', 1;

Reconfigure;

 

Exec sp_configure 'xp _ Your shell', 1;

Reconfigure;

This article is from the csdn blog. For more information, see the source:Http://blog.csdn.net/puddingpudding/archive/2008/12/04/3445833.aspx