Restricting access to Stored Procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Restricting access to Stored Procedures

Author

Topic

someguy51
Starting Member

11 Posts

Posted - 2005-10-05 : 17:29:33

I'm going through the SQLSecurity Checklist I found at sqlsecurity.com. One of the points it says to "Restrict to sysadmins-only access to stored procedures and extended stored procedures that you believe could pose a threat." It also lists a bunch of stored procs and extended stored procs that you should consider restricting to sysadmins only. I was wondering if someone could give me some pointers on how to do this? I would like to write a script that I could run on every sql server 2000 install that would do this. How could I ensure that every user does not have access except the sysadmins?

Thanks,
Chris

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-05 : 21:44:56

you can issue an explicit deny or revoke on those objects

revoke [permission] on [object] from [user]
deny [permission] on [object] to [user]

--------------------
keeping it simple...

someguy51
Starting Member

11 Posts

Posted - 2005-10-06 : 13:14:33

Thanks for the reply jen.

I was actually trying the "deny" command yesterday but I had it wrong. I was trying to deny access to a server role, which according to the documentation you can't do.

So if I didn't want anyone to be able to run the "sp_sdidebug" stored proc the following script would work?

USE Master
GO
DENY execute on sp_didebug TO public
DENY execute on sp_didebug TO db_accessadmin
DENY execute on sp_didebug TO db_backupoperator
DENY execute on sp_didebug TO db_datareader
DENY execute on sp_didebug TO db_datawriter
DENY execute on sp_didebug TO db_ddladmin
DENY execute on sp_didebug TO db_denydatareader
DENY execute on sp_didebug TO db_denydatawriter
DENY execute on sp_didebug TO db_owner
DENY execute on sp_didebug TO db_securityadmin
GO

Do I have to do this for every database? I don't think so, but I just wanted to double check. So if I had a database called "Main" would I have to do the following?

USE Main
deny execute...etc

Thanks for all your help,
Chris

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-06 : 13:21:09

Why do you want to restrict sp_didebug? It allows developers to debug stored procedures. It is very useful for them in a development environment.

Tara

someguy51
Starting Member

11 Posts

Posted - 2005-10-06 : 13:40:51

Hi,

The security checklist I'm following says that it could be a security risk. I could always grant access later if we needed it.

Chris

bakerjon
Posting Yak Master

145 Posts

Posted - 2005-10-06 : 17:27:58

Here's a script that I recommend running. I have used it a few times in the past. You can pick and choose what makes sense for your environment. It's surprising that all of these procs are granted to public out of the box!

USE master
go
REVOKE EXECUTE ON dbo.xp_execresultset FROM public
go
USE master
go
REVOKE EXECUTE ON dbo.xp_printstatements FROM public
go
USE master
go
REVOKE EXECUTE ON dbo.xp_displayparamstmt FROM public
go
USE master
go
REVOKE EXECUTE ON xp_regaddmultistring FROM public
go
USE master
go
REVOKE EXECUTE ON xp_regdeletekey FROM public
go
USE master
go
REVOKE EXECUTE ON xp_regdeletevalue FROM public
go
USE master
go
REVOKE EXECUTE ON xp_regenumvalues FROM public
go
USE master
go
REVOKE EXECUTE ON xp_regenumkeys FROM public
go
USE master
go
REVOKE EXECUTE ON xp_regread FROM public
go
USE master
go
REVOKE EXECUTE ON xp_regremovemultistring FROM public
go
USE master
go
REVOKE EXECUTE ON xp_regwrite FROM public
go
USE master
go
REVOKE EXECUTE ON xp_instance_regaddmultistring FROM public
go
USE master
go
REVOKE EXECUTE ON xp_instance_regdeletekey FROM public
go
USE master
go
REVOKE EXECUTE ON xp_instance_regdeletevalue FROM public
go
USE master
go
REVOKE EXECUTE ON xp_instance_regenumkeys FROM public
go
USE master
go
REVOKE EXECUTE ON xp_instance_regenumvalues FROM public
go
USE master
go
REVOKE EXECUTE ON xp_instance_regread FROM public
go
USE master
go
REVOKE EXECUTE ON xp_instance_regremovemultistring FROM public
go
USE master
go
REVOKE EXECUTE ON xp_instance_regwrite FROM public
go
use master
go
REVOKE EXECUTE ON master.dbo.sp_runwebtask FROM public
GO
USE msdb
go
REVOKE EXECUTE ON dbo.sp_add_job FROM public
go
USE msdb
go
REVOKE EXECUTE ON dbo.sp_add_jobstep FROM public
go
USE msdb
go
REVOKE EXECUTE ON dbo.sp_add_jobserver FROM public
go
USE msdb
go
REVOKE EXECUTE ON msdb.dbo.sp_enum_dtspackages FROM public
go
USE msdb
go
REVOKE EXECUTE ON msdb.dbo.sp_get_dtspackage FROM public
go
USE msdb
go
REVOKE ALL ON msdb.dbo.mswebtasks FROM public
go
USE master
go
REVOKE EXECUTE ON master.dbo.sp_readwebtask FROM public
go

Jon
-Like a kidney stone, this too shall pass.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-06 : 17:56:17

Why do the users have access to the master database in the first place?

Tara

someguy51
Starting Member

11 Posts

Posted - 2005-10-06 : 18:54:11

Thanks for the script bakerjon. I added them to the script I had already started.

Tara, I never gave users access to the master database. But it appears people can access parts of it anyways through the public role. When we first started working with SQL Server a few months ago we didn't do any security procautions at all and our development sql server got hacked. I want to make sure I make this new installation I'm working on as secure as possible. I may be going overboard now but I think its better to be paranoid and take procautions you don't need to take then not to take enough procautions.

Chris

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-07 : 01:50:36

yes, i agree, better to be a little bit paranoid than sorry...

but be careful on those explicit denies, they override whatever permissions you have, even with sysadmin

so if you're a member of one those roles, they overrride your permission set

--------------------
keeping it simple...

someguy51
Starting Member

11 Posts

Posted - 2005-10-07 : 10:08:57

Thanks for the tip jen. I wound up just denying to public. You actually get an error message when you try to deny stuff to the other built-in database roles. Although I guess technically everyone is in public. I could also grant back execute rights to a stored procedure if we needed one.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-10-19 : 07:44:32

quote:
Originally posted by jen

you can issue an explicit deny or revoke on those objects

revoke [permission] on [object] from [user]
deny [permission] on [object] to [user]

--------------------
keeping it simple...

what is the diffrence between the revoke and deny?
beacuse obvious its not the sanme so where is the actuall difrrence

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-19 : 07:51:09

a deny supercedes any permission, you can clearly see the difference with a user who is a member of a role
user has select rights to tableA
role has select rights to tableA

a revoke on user, will still allow him to access tableA because he is a member of the role, but a deny on the user, will prevent him from doing so

from bol

quote:

DENY
Creates an entry in the security system that denies a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships.

quote:

REVOKE
Removes a previously granted or denied permission from a user in the current database.

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources