| Author |
 Topic  |
|
CanadaDBA
Aged Yak Warrior
Canada
583 Posts |
 Posted - 02/11/2005 : 07:44:06
|
I have users which are not members of the sysadmin fixed server role. How can I grant them execute right to be able to execute xp_cmdshell?
Canada DBA |
|
|
Andraax
Aged Yak Warrior
Sweden
790 Posts |
Posted - 02/11/2005 : 08:39:57
|
You can give permissions as you would on a normal SP, IE use GRANT EXECUTE (look up in BOL for syntax).
However, I have to advice you that it's very dangerous to permit users to use xp_cmdshell. Remember that the user will be executing stuff in the context of the NT user which is used by the MSSQLServer service. If that user has local or domain admin rights, the user could gain access potentially to everything on your domain. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 02/11/2005 : 23:26:19
|
This is not completely correct. The account xp_cmdshell runs under depends on if they are an ordinary user or a sysadmin.
From SQL 2000 BOL:
"When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running.
When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail.
This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server."
quote:
Originally posted by Andraax
... Remember that the user will be executing stuff in the context of the NT user which is used by the MSSQLServer service...
Codo Ergo Sum |
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 02/17/2005 : 16:13:04
|
People!
Forget about xp_cmdshell (and SQL Server Agent proxy account)
for ordinary users for good! Much simpler and safer it will be:
use pubs
GO
EXEC sp_addlogin 'asd', 'pwd', 'pubs'
GO
EXEC sp_grantdbaccess 'asd', 'asd_pubs'
GO
CREATE procedure asdShell
as
declare @obj int
exec sp_oacreate 'WScript.Shell', @obj out
exec sp_oamethod @obj,
'Run("c:\mssql7\binn\bcp pubs..authors out d:\au.txt -c -S(local) -Usa -Ppwd", 0)'
exec sp_oadestroy @obj
return
GO
GRANT EXECUTE ON asdShell TO asd_pubs
GO
The only OS/shell damage user asd_pubs can cause is that inside of the sproc.
|
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 02/18/2005 : 05:03:46
|
actually I don't understand why all this fuss about granting xp_cmdshell.
We always can wrap it in a "good" sproc and then grant execution of the sproc instead of granting execution of xp_cmdshell itself.
What I missed here? |
|
|
|
DBADave
Constraint Violating Yak Guru
USA
366 Posts |
Posted - 02/18/2005 : 13:28:39
|
xp_cmdshell is a touchy subject. You do need to be very careful when granting permission to this procedure. I've used it in the past to access a third-party's server proving a point their server was not secure. I could easily have copied a backup of Master, restored it to my server and cracked their SQL passwords. I also could have accessed and cracked the passwords in their SAM.
Providing non-admins the ability to execute xp_cmdshell is possible, but requires the following steps.
(1) Turn off the SQL Agent option restricting CmdExec/ActiveX scripting job to members of sysadmin
(2) Define a Proxy account
(3) Grant access to master for those users you wish to provide the ability to execute xp_cmdshell
(4) Grant EXECUTE permission on xp_cmdshell to those logins.
One item to keep in mind is the Agent setting will allow CmdExec Job steps to be created by non-sysadmins and executed using the Proxy account. Since by default this allows any account to create a Job, you may wish to Deny various permissions in msdb.
The use of sp_OACreate is an option, however only members of sysadmin are permitted to execute this command. See BOL for details.
The stored procedure approach is an option, but it's not as simple as granting Execute on xp_cmdshell. You need to follow the steps I listed above.
Hope this helps,
Dave |
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 02/18/2005 : 15:38:11
|
Thanks, Dave, but what namely do you mean by this:
> The use of sp_OACreate is an option, however only members of
> sysadmin are permitted to execute this command. See BOL for details.
My above (non sysadmin) user asd_pubs can execute it (indirectly).
But that's quite enough for him to be happy. |
|
|
|
DBADave
Constraint Violating Yak Guru
USA
366 Posts |
Posted - 02/18/2005 : 15:57:59
|
Stoad,
That's one I confess I've never tested for myself from a stored procedure. BOL states "Only members of the sysadmin fixed server role can execute sp_OACreate". I assumed it was locked down like xp_cmdshell. If it works for you then I stand corrected.
Thanks, Dave |
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 02/19/2005 : 06:56:15
|
To All;
What is the best (fastest, simplest, secure enough) approach for next
kind-of-forum problem: after inserting new row into table forums I want
trigger forums_mailer starts sending emails to those who subscribed to
get post texts from the forum with just inserted forum_id?
create table forums (forum_id int, post_text varchar(8000))
GO
create table subscribers (email varchar(80), forum_id int)
GO
create trigger forums_mailer on forums for insert
as
set nocount on
commit tran
< ... ??? ... >
GO
GRANT INSERT ON forums TO forum_members
GO
|
Edited by - Stoad on 02/20/2005 00:35:23 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 02/19/2005 : 19:50:01
|
That only works with database objects in the same database, so you proc would have to be in the master databse. Since xp_cmdshell is in the master database, the user running the proc in any other database would still have execute permission on master.dbo.xp_cmdshell.
quote:
Originally posted by Stoad
actually I don't understand why all this fuss about granting xp_cmdshell.
We always can wrap it in a "good" sproc and then grant execution of the sproc instead of granting execution of xp_cmdshell itself.
What I missed here?
Codo Ergo Sum |
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 02/19/2005 : 23:34:15
|
use pubs
GO
EXEC sp_addlogin 'asd', 'pwd', 'pubs'
GO
EXEC sp_grantdbaccess 'asd', 'asd_pubs'
GO
create procedure Ego
as
exec master..xp_cmdshell 'dir d:\'
GO
grant execute on Ego to asd_pubs
GO
Then why user asd_pubs can successfully execute sproc Ego?
Of course, this user cannot execute xp_cmdshell directly:
Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo'.
|
Edited by - Stoad on 02/19/2005 23:36:49 |
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 02/19/2005 : 23:40:26
|
It's NOT my case:
Important If you choose to use a Windows NT account that is NOT a member
of the local administrator's group for the MSSQLServer service, users who are
not members of the sysadmin fixed server role cannot execute xp_cmdshell.
|
Edited by - Stoad on 02/19/2005 23:43:37 |
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 02/20/2005 : 10:23:55
|
quote:
Originally posted by DBADave
Providing non-admins the ability to execute xp_cmdshell is possible, but requires the following steps.
(1) Turn off the SQL Agent option restricting CmdExec/ActiveX scripting job to members of sysadmin
(2) Define a Proxy account
(3) Grant access to master for those users you wish to provide the ability to execute xp_cmdshell
(4) Grant EXECUTE permission on xp_cmdshell to those logins.
I don't like steps (3) and (4). Are you sure they are necessary
for executing by a non-admin pubs_user this stored procedure:
use pubs
GO
create procedure Test
as
exec master..xp_cmdshell 'dir d:\'
GO
grant execute on Test to pubs_non_admin_user
GO
???
And, separating bees from honey, imo there are two different things:
Permission to perform shell actions for local account SQLAgentCmdExec (in ver.7)
and
Permission to execute master..xp_cmdshell per se.
|
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 02/21/2005 : 04:58:14
|
HA! Dave, in some sense we are both right!
Run this BUT UNDER SA LOGIN and you'll see the difference!
When I run it as <domain>\Administrator (in a DB created by this sysadmin) then
indeed user asd_pubs got EXECUTE permission denied on object 'xp_cmdshell'.
use pubs
GO
EXEC sp_addlogin 'asd', 'pwd', 'pubs'
GO
EXEC sp_grantdbaccess 'asd', 'asd_pubs'
GO
CREATE procedure dir_c
as
exec master..xp_cmdshell 'dir c:\'
GO
GRANT EXECUTE ON dir_c TO asd_pubs
GO
Account SQLAgentCmdExec is a member of groups Users & Domain Users on the machine.
Any comments?
|
Edited by - Stoad on 02/21/2005 05:55:32 |
|
|
|
DBADave
Constraint Violating Yak Guru
USA
366 Posts |
Posted - 02/21/2005 : 10:58:07
|
I'm not sure I understand your last post. Are you saying you experience no problems when running the stored proc under SA? If so that's as expected.
In our environment running your code to create the stored procedure and grant the appropriate permission to asd yields the following error when connecting as asd.
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1314 from CreateProcessAsUser on line 636
This is due to the permissions we assign to the SQL Server service account. Our account is setup to not provide users with the ability to run xp_cmdshell so I cannot test your scenarios.
The permissions that need to be set for the service account in order for users to execute xp_cmdshell are:
Act as part of operating system
Replace process level token
After setting these permissions I will still get Excecute Permission Denied until the 4 steps I identified previously are performed.
Hope this helps,
Dave
|
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 02/21/2005 : 12:58:22
|
1.
My SS is started and running under <domain>\Administrator account,
which (the account) is both local and domain GOD.
2.
From QA (and thru NT authentication) I (<domain>\Administrator) run this script:
CREATE DATABASE db1 ...
GO
use db1
GO
EXEC sp_addlogin 'asd1', 'pwd1', 'db1'
GO
EXEC sp_grantdbaccess 'asd1', 'asd1_db1'
GO
CREATE procedure dir_c1
as
exec master..xp_cmdshell 'dir c:\'
GO
GRANT EXECUTE ON dir_c1 TO asd1_db1
GO
3.
Then I run absolutely the same script (with all "1"s replaced by "2"s; e.g., db2
instead of db1 and so on) BUT THIS TIME I RUN IT (THE ABOVE SCRIPT) UNDER
LOGIN SA, i.e., thru SQL Server authentication.
4.
Then I test these two new, a minute ago created, logins: asd1 and asd2.
5.
From QA I connect under login asd1 "to" asd1's defaultdb db1 and run exec dir_c1
and I get EXECUTE permission denied on object 'xp_cmdshell'.
6.
Then I connect under login asd2 "to" asd2's defaultdb db2 and run exec dir_c2
and THIS WORKS FINE FOR ME (ASD2)!.
Dave, don't you find it interesting? Or is it all obvious for you?
|
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 02/21/2005 : 14:06:35
|
| Btw, if domain\NT\PDC Admin has to keep his eye on SQL Server sysadmins then, of course, it's a totally different story and, in principle, there is nothing for us to discuss. But pretty often all these admins are the same person. |
|
|
|
DBADave
Constraint Violating Yak Guru
USA
366 Posts |
Posted - 02/21/2005 : 16:31:58
|
Interesting. A few questions.
(1) Are you saying you are using the domain administrator id or are you using a domain account that is in the domain administrator's group.
(2) Have you granted SQL Server access directly to your domain account or are your relying on Builtin\Administrators?
(3) If using Builtin\Administrators, what permissions are assigned
to this account?
(4) If you query syslogins for SA and the domain id you are using, are the values in the following columns identical for both ids.
dbname,
denylogin,
hasaccess,
sysadmin,
securityadmin,
serveradmin,
setupadmin,
processadmin,
diskadmin,
dbcreator,
bulkadmin,
loginname
Dave |
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 02/22/2005 : 03:15:42
|
(1)
Are you saying you are using the domain administrator id or are you using
a domain account that is in the domain administrator's group.
the domain administrator id
(4)
dbname denylogin hasaccess sysadmin securityadmin serveradmin
-------- ----------- ----------- ----------- ------------- -----------
master 0 1 1 0 0
master 0 1 1 0 0
master 0 1 1 0 0
setupadmin processadmin diskadmin dbcreator loginname
----------- ------------ ----------- ----------- ----------------------
0 0 0 0 MYDOM\Administrator
0 0 0 0 BUILTIN\Administrators
0 0 0 0 sa |
Edited by - Stoad on 02/22/2005 03:51:55 |
|
|
|
DBADave
Constraint Violating Yak Guru
USA
366 Posts |
Posted - 02/22/2005 : 12:02:09
|
I'll take a stab at an explanation.
I believe the issue is with the use of the EXEC statement. The user who owns the stored procedures in db1 and db2 is dbo, however dbo in db1 corresponds to a different SID then dbo in db2.
BOL states
When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure. However, if a user owns two stored procedures in which the first procedure calls the second, then EXECUTE permission checking is not performed for the second stored procedure.
In your example the user name is the same for both objects, but the actual login id corresponding to the user is different. The same holds true for the owner of the database. For this reason I believe permissions are checked for the EXEC statement to see if the user (not the owner) has authority to execute xp_cmdshell.
This is not just an xp_cmdshell issue. For example, the same Execute Permission Denied error will occur if you execute xp_sendmail instead of xp_cmdshell. However if you create your stored procedure in master and not asd1, the user will be able to successfully execute the procedure and the underlying xp_cmdshell.
Dave
|
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 02/22/2005 : 13:09:29
|
> I believe the issue is ...
lol, Dave! What issue? I did not put forward any issue! My initial background
"thought" in this thread was:quote:
actually I don't understand why all this fuss about granting xp_cmdshell.
We always can wrap it in a "good" sproc and then grant execution of the
sproc instead of granting execution of xp_cmdshell itself.
But I was told:quote:
Providing non-admins the ability to execute xp_cmdshell is possible, but requires the following steps.
(3) Grant access to master for those users you wish to provide the ability to execute xp_cmdshell
(4) Grant EXECUTE permission on xp_cmdshell to those logins.
... ... ...
... ... ...
That only works with database objects in the same database, so you proc would have to be in
the master databse. Since xp_cmdshell is in the master database, the user running the proc in
any other database would still have execute permission on master.dbo.xp_cmdshell.
Btw, your explanation seems to be right. When I (NT Admin) passed the ownership of my db1
to SA (exec sp_changedbowner 'sa') Execute Permission Denied error vanished for login asd1.
> This is not just an xp_cmdshell issue. For example, the same Execute Permission Denied
> error will occur if you execute xp_sendmail instead of xp_cmdshell.
Yes! And absolutely the same thing with those nasty but imo very useful sp_OA... sprocs. |
|
|
|
Topic |
|
xp_cmdshell execute right
