March 15, 2013 at 3:09 pm
Hello All,
Couldn't find anything that addresses this specifically, so here goes.
I have a group that I want to allow to view process on their dev server, but I don't want to grant view server state permissions and all that it exposes, so I'm trying to write a proc that will execute sp_who as me (the DBA) for users, but it still only returns one row instaed of all of them; what am I missing? MS's docs say this should work.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_who] Script Date: 03/14/2013 14:57:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================
-- Author:
-- Created: 03/14/13
-- Desc:sp_who for users
-- =============================
ALTER PROCEDURE [usp_who]
with execute as self
AS
BEGIN
SET NOCOUNT ON;
exec sp_who;
END
March 18, 2013 at 12:38 am
You can try creating temporary table in your procedure
where all the information for sp_who will be stored so that we can
get the information from the table as you like
statement are
insert #temptable
exec sp_who
March 18, 2013 at 2:28 am
If you only want the results for the user have you tried something along the lines of this:-
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_who] Script Date: 03/14/2013 14:57:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================
-- Author:
-- Created: 03/14/13
-- Desc: sp_who for users
-- =============================
CREATE PROCEDURE [usp_who] @who INT = @@spid
with execute as self
AS
BEGIN
SET NOCOUNT ON;
exec sp_who @who;
END
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
March 18, 2013 at 3:13 am
... but I don't want to grant view server state permissions and all that it exposes...
What exactly are you afraid of? What dangerous things does it expose other than ability to use DMV's and DMF's to monitor server health/problems and tune performance?
March 18, 2013 at 10:11 am
With "with execute as ..." clause on a procedure/function/trigger you define a database user to execute under, not a server login.
Database user cannot have a server-level privilege such as viewing server processes. Only login can, with granted server-level permissions.
That means you cannot use "with execute as" to view all the sessions on the server or any other server-scope info.
Solution is this:
You will build a plain procedure, with no "execute as" clause added to the procedure, and make it run under a highly priviledged LOGIN by signing the procedure with the certificate. Then grant execute on that procedure to a low-priviledged user (or better, not directly to a user but grant it to a database role and assign the role to the user).
It's not that hard as it sounds. That is the only secure way you can grant specific sysadmin-only activities to a low priviledged login.
Here is one example: http://colleenmorrow.com/2011/12/19/tossing-pebbles-using-certificates-to-grant-privileges/[/url]
March 18, 2013 at 10:25 am
Alternative would be to grant "VIEW DATABASE STATE" to the user (or better, db role) in each database you want that login to view the sessions.
After that, you do not need a special procedure, they can call sp_who2 directly and see only sessions on databases you have granted to.
That is less secure than signing method because VIEW DATABASE STATE permission also grants access to dmv's (limited to that databases), which might be not what you want.
March 19, 2013 at 2:39 pm
Thank you everyone!
SQLX - both great ideas, I'm going to try the first one... um... first.
DWG
March 19, 2013 at 2:42 pm
What exactly are you afraid of?
It's a former DBA that hates me because unlike him, I take my job seriously and won't give him sa permissions. He thinks because he "used to be a DBA at this company" that he's still entitled to the luxuries of sa rights without the responsibility of keeping the servers up and operational. Clear as mud?
March 19, 2013 at 6:30 pm
Here is a complete and tested example with signed procedure:
-- Script by Vedran Kesegic, 20.3.2013.
-- We will create a database and a procedure which calls sp_who2.
-- We will also create a low-privileged login and give him exec permission on that procedure.
create database TestCert
create login LowPriv with password='Str0ngPWD!'
GO
use TestCert
create user LowPriv for login LowPriv
GO
create procedure dbo.show_processes
--with execute as 'dbo' -- even this wont help, because dbo is db user and can receive only privileges at db level, not server level
as
begin
select * from sys.login_token -- who am i
select * from sys.fn_my_permissions(null,'server') -- what server rights do i have
exec sys.sp_who2
end
GO
grant exec on dbo.show_processes to LowPriv
GO
execute as login='LowPriv'
exec sys.sp_who2 -- shows only my session, not good
exec dbo.show_processes -- shows only my session, not good
revert
GO
-- Create login to which we will grant view server state. Login will be mapped to certificate.
-- Sign procedure with that certificate - meaning procedure will execute under that high-priv login.
-- certificate must be in master database in order to be mapped to login
use master -- create certificate in master database
create certificate HighPrivCert
ENCRYPTION BY PASSWORD = 'Str0ngPWD!'
WITH SUBJECT = 'Certificate for signing stored procedures'
select * from sys.certificates
GO
create login HighPrivCertLogin from certificate HighPrivCert -- create high priv login
grant view server state to HighPrivCertLogin
GO
-- in order to grant db user rights, we must transfer to that db the same certificate
backup certificate HighPrivCert to file='C:\temp\HighPrivCert.CER'
WITH PRIVATE KEY
(FILE = 'C:\temp\HighPrivCert.PVK',
DECRYPTION BY PASSWORD = 'Str0ngPWD!', -- pwd to open the key
ENCRYPTION BY PASSWORD = 'FilePWD!1' -- must protect the file
)
GO
use TestCert
create certificate HighPrivCert from file='C:\temp\HighPrivCert.CER'
WITH PRIVATE KEY
(FILE = 'C:\temp\HighPrivCert.PVK',
DECRYPTION BY PASSWORD = 'FilePWD!1', -- to read the file
ENCRYPTION BY PASSWORD = 'Str0ngPWD!' -- pwd to protect the key
)
select * from sys.certificates
-- Manually delete cert files NOW!
GO
-- by signing procedure, it will run under login associated with that certificate
ADD SIGNATURE TO OBJECT::dbo.show_processes
BY CERTIFICATE HighPrivCert
WITH PASSWORD='Str0ngPWD!'
GO
execute as login='LowPriv'
exec sys.sp_who2 -- shows only my session
exec dbo.show_processes -- shows all sessions! We are running under HighPrivCertLogin which have VIEW SERVER STATE permission.
revert
GO
-- CLEANUP
/*
use master
drop database TestCert
drop login HighPrivCertLogin
drop certificate HighPrivCert
drop login LowPriv
*/
Enjoy!
March 20, 2013 at 2:21 am
datwungai (3/19/2013)
What exactly are you afraid of?
It's a former DBA that hates me because unlike him, I take my job seriously and won't give him sa permissions. He thinks because he "used to be a DBA at this company" that he's still entitled to the luxuries of sa rights without the responsibility of keeping the servers up and operational. Clear as mud?
Then you are taking the right stance if they are a former DBA - they now have no right to access (except though any application)- stick to your guns.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
March 20, 2013 at 3:35 am
datwungai (3/19/2013)
What exactly are you afraid of?
It's a former DBA that hates me because unlike him, I take my job seriously and won't give him sa permissions. He thinks because he "used to be a DBA at this company" that he's still entitled to the luxuries of sa rights without the responsibility of keeping the servers up and operational. Clear as mud?
I do like your work ethic! Just as mater of interest, why just simply not kill him? There are different options available: poison, knife, gun, box of dynamite at the end. Exterminate! :w00t:
On a serious note, if your users just an ordinary database/application users, your are absolutely right. Granting this permission is too generous for them. Saying that, they would hardly ever ask for this one anyway. However if your user is a developer and that is development or test environment , then this permission is very important as it allows to monitor server activity and performance. I can hardly see how access to server stats
effects "keeping the servers up and operational".
But again, if he rally hates you, just offer him cup of coffee or tea, then just add a bit of polonium 210 and your enemy will be destroyed! :hehe:
March 20, 2013 at 4:34 am
Eugene, very well put.
I personally would arrange a meeting with you, the developer involved, his boss and your boss, and ask him to state why he needs these rights.
Then put a case where you can offer him something like the View Database state on Dev boxes only and maybe UAT, but production is off limits.
If he keeps pushing the case for SA rights simply state that he would also then need to take part responsibility for those boxes where hes grated those rights such as working weekends as part of being on call, doing the mundane daily work.
I would also give him a specific login for this rather than grant his standard network account with SA rights.
You might then see him back down quite quickly.
I'd also set up server level auditing so that if/when he screws up you have him by the soft and dangly things, and you have the perfect ammunition to get his rights revoked or have him removed, and you get the got the added bonus of the fact he did it all by himself. 😉
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 22, 2013 at 10:54 am
Thanks Vedran!
The certificate works like a charm!
dwg
March 22, 2013 at 10:56 am
Thanks Stuart, my sentiments exactly. It's not the presidency, if you forfiet your title here, it's gone for good!
March 22, 2013 at 11:08 am
Well said Jason.
I've had that meeting with our respective boss' - the underpinning is a political game that I can't win necessarily. However, I've played this game before and religiously document and audit where ever I can. With that, I've caught him off-guard and had him stammering in a room full of people who were ready to believe he needed the access, and he ultimately didn't get it. Small victory for me.
My whole MO is to keep it professional and act in good faith with regard to my duties as a DBA and it serves me well. I would have much rather partnered up with this guy because he's a very smart person and would've been a great asset, but his attitude killed it all when he felt he didn't need to treat me with respect because I didn't give him what he wants. And since he was a former DBA at this company, you'd think he'd be more understanding when it comes to the constraints IS has to work under (CM policies, etc.), but he chose the "Uncle Tom" route and burned just about every bridge he had in IS - now nobody wants to help this guy with anything.
What can you do when grown men behave like children? My philosiphy: "It's not my kid."
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply