SQL Server user administration

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server user administration

Author

Topic

groston
Yak Posting Veteran

61 Posts

Posted - 2004-08-23 : 17:59:45

I am in the process of rolling out a new version of an ASP.NET application. I am planning to make this version more secure than its predecessor. To this end, I plan to used a Trusted Connection, as opposed to specifying a SQL login and password in the connection string.

Here is that which is troubling me:
In Enterprise Manager, I have added my local ASPNET account as a SQL login. I have then assigned this login as a user on a database. By default, this user is assigned to role 'public'. With this assignment, querying the database fails with the message : EXECUTE permission denied on object. Not terribly surprising.

If I now assign ASPNET user to the role db_datareader, I get the same behavior. Specifically, querying the database fails with the message : EXECUTE permission denied on object.

If I now assign ASPNET user to the role db_owner, the query is executed. Of course, I have now given this user more privilege than is necessary.

If I now unassign db_owner, assign db_datareader, click the permissions button and specifically allow execute access to the stored procedures, the query works properly.

So, my question is this: For "maximum" security, is it really necessary to specifically allow access to the specific objects within the database? This seems like a lot of extra work - is there a shortcut?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-23 : 18:21:07

Yes it's necessary to grant permissions to the objects, but there is a shortcut. Here's a stored procedure that I wrote that grants EXEC to all stored procedures to a role:



CREATE   PROC isp_Grant_Permissions
AS

SET NOCOUNT ON

DECLARE @objName varchar(80)
DECLARE @objType char(2)
DECLARE grant_perms_on_sps CURSOR FOR	
SELECT name, type
FROM SYSOBJECTS 
WHERE type IN ('P', 'FN', 'TF', 'U', 'V')
	AND uid = 1	AND	status > -1

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps 
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @objType NOT IN ('TF', 'T', 'V')
		EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO SomeRole')
	ELSE
		EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO SomeRole')	

	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

GO

My code is actually a bit longer as I make sure the objects are named according to our standards. But this is a generic version of it.

Tara

Kristen
Test

22859 Posts

Posted - 2004-08-24 : 01:27:43

Just dotting i's and crossing t's here:

Also grants SELECT on all tables and views - which groston may not need

And might be worth changing
DECLARE @objName varchar(256)
in case groston has some long table/SProc etc. names

groston, how do you create your SProcs? I make each one in a separate file (rather than using, say, Entperise Manager right-click-properties), and in there I have the Grant Permissions stuff - here's a bit of a template as an example:


--
PRINT 'Create procedure MySProc'
GO
IF EXISTS (SELECT * FROM sysobjects 
		WHERE	id = object_id(N'[dbo].[MySProc]') 
			AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MySProc
END
GO

CREATE PROCEDURE dbo.MySProc
	@foo	int,
	@bar	int
AS
... stuff here ...
GO

IF exists (SELECT * FROM sysobjects 
		WHERE	id = object_id(N'[dbo].[MySProc]') 
			AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	GRANT EXECUTE ON dbo.MySProc TO MyRole
END
GO
PRINT 'Create procedure MySProc DONE'
GO

Kristen

groston
Yak Posting Veteran

61 Posts

Posted - 2004-08-24 : 07:43:50

Thank you both for your help!

My SProc names tend to be rather short - this is not a problem.
As for creating them, you will probably consider this to be barbaric, but I create them from within Visual Studio.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources