Correct least privileges - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

Correct least privileges

Author

Topic

Maleks
Starting Member

10 Posts

Posted - 2008-11-27 : 12:38:26

Hey guys,

I've just spent some time writing a script which will create a role and give it access to stored procedures only and then assign an existing sql server login to that role.

Could you guys give me some feedback on if I have implemented the 'least privileges' approach correctly?

Some points. Our stored procedures are named spStoredProcedure which is why you see the if statement around the start of the procudure name.

Here's the code:

/* ======================================================
VARIABLES
====================================================== */
DECLARE @Site AS VARCHAR(255)
DECLARE @DatabaseName VARCHAR(255)
DECLARE @UserName CHAR(50)
DECLARE @Password CHAR(50)
DECLARE @RoleName CHAR(50)

/* ======================================================
SET UP
====================================================== */
SET @Site = N'ShopDev'
SET @DatabaseName = N'db' + @Site
SET @UserName = N'su_' + LOWER(@Site) + N'user'
SET @Password = N'password'
SET @RoleName = N'db_spaccess'

/* ======================================================
CREATE THE ROLE
====================================================== */
PRINT('======== Setting up roles')
PRINT('- Adding role ' + @RoleName)
EXEC dbo.sp_addrole @rolename = @RoleName

/* ======================================================
SET PERMISSIONS FOR STORED PROCEDURES
====================================================== */
PRINT('======== Setting up stored procedure permissions')
DECLARE @ProcedureName VARCHAR(255)
DECLARE _Procedures CURSOR FORWARD_ONLY FOR
SELECT
Name
FROM
sys.procedures

-- Iterate through the cursor
OPEN _Procedures
FETCH NEXT FROM _Procedures INTO @ProcedureName
WHILE @@FETCH_STATUS = 0
BEGIN
IF LEFT(@ProcedureName, 3) = 'sp_'
BEGIN
EXEC('DENY EXECUTE ON ' + @ProcedureName + ' TO ' + @RoleName)
EXEC('REVOKE EXECUTE ON ' + @ProcedureName + ' TO ' + @RoleName)
PRINT('- Denying all permissions for ' + @RoleName + ' on ' + @ProcedureName)
END
ELSE IF LEFT(@ProcedureName, 2) = 'sp'
BEGIN
EXEC('GRANT EXECUTE ON ' + @ProcedureName + ' TO ' + @RoleName)
PRINT('- Granting permissions for ' + @RoleName + ' on ' + @ProcedureName)
END
ELSE
BEGIN
EXEC('DENY EXECUTE ON ' + @ProcedureName + ' TO ' + @RoleName)
EXEC('REVOKE EXECUTE ON ' + @ProcedureName + ' TO ' + @RoleName)
PRINT('- Denying all permissions for ' + @RoleName + ' on ' + @ProcedureName)
END

FETCH NEXT FROM _Procedures INTO @ProcedureName
END
CLOSE _Procedures
DEALLOCATE _Procedures

/* ======================================================
ADD USER TO ROLE
====================================================== */
PRINT('======== Setting up users')
PRINT('- Adding user ' + @UserName + ' to role ' + @RoleName)
EXEC sp_adduser @UserName, @UserName, @RoleName

Some questions I have:
- do I need to explicity deny/revoke access to the built in stored procedures like sp_adduser or its a privilege only so they only have access to what I grant them access to?

Thanks for wading through this. It was a big one.

Maleks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 12:51:09

What is your requirement? Can you explain exactly so we can further assist?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-11-27 : 14:33:44

quote:
EXEC('DENY EXECUTE ON ' + @ProcedureName + ' TO ' + @RoleName)
EXEC('REVOKE EXECUTE ON ' + @ProcedureName + ' TO ' + @RoleName)

You do realise that revoke removes a previous grant or deny permission? Hence you're denying permission and then immediately removing that deny.

You don't need to explicit deny permission to the system procs. Unless all your users are db_owner or sysadmin they won't be able to run them anyway. For example, sp_adduser can only be run by a database owner. sp_addrolemember requires with db_owner, db_securityadmin or ALTER permission on the role been affected, etc

--
Gail Shaw
SQL Server MVP

Maleks
Starting Member

10 Posts

Posted - 2008-11-28 : 05:52:45

Thanks Gail.

Maybe posting the script was not the best idea. I should be talking methodolgies.

OK so what I want to achieve is setting up a user which has least privileges for a database . In our case it means they should only have access to stored procedures.

I understand to do this I need to:
- create a role
- grant that role access to the stored procedures
- I do NOT need to deny or revoke access to any system stored procedures such as sp_addrolemember
- I do NOT need to deny or revoke access to any table, function or view
- add the user to the role

Is this correct?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-11-28 : 06:09:24

Correct. If you don't grant permissions on the tables, then the users won't have any, so there's no need to revoke.
Make sure the users are not members of any of the fixed database or server roles.

--
Gail Shaw
SQL Server MVP

Maleks
Starting Member

10 Posts

Posted - 2008-11-28 : 06:19:21

Thanks Gail. Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources