Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Server Security: The db_executor Role Expand / Collapse
Author
Message
Posted Tuesday, May 20, 2003 6:45 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 4, 2007 4:36 AM
Points: 27, Visits: 1
Agreed all round. We've been using this model for some years years now and it has made a world of difference.

It's a moot point - but how many DBA's out there are in a position where they have a say in what authentication models get used?

I am surprised by the number of of client's DBA's who are held responsible for the integrity of the databases under their jurisdiction - whilst at the same time have absolutely no say in the security/access models used by developers.

Have other folks out there found this to be a problem?




Post #61946
Posted Tuesday, May 20, 2003 6:57 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Yup, sure have. And I've been in this position as a systems administrator with responsibility over the enterprise no authority. That's just as bad.


K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #61947
Posted Friday, December 12, 2003 2:08 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 6, 2013 2:16 AM
Points: 693, Visits: 124
We should also grant to db_executor the EXECUTE permission for scalar functions.

It is interesting to note that:
- Inline Table-valued Functions have SELECT, INSERT, UPDATE and DELETE permissions
- Multi-statement Table-valued Functions have only the SELECT permission
- Scalar Functions have only the EXECUTE permission

Here is how I would write the SELECT statement for the cursor:

SELECT USER_NAME(uid) as Owner, [name] as StoredProcedure
FROM sysobjects so WHERE (xtype='P' or xtype='FN')
AND OBJECTPROPERTY([id], N'IsMSShipped')=0
AND [id] NOT IN (SELECT [id] FROM sysprotects sp
WHERE UID=USER_ID('db_executor') AND [action]=224)

Razvan




Post #61948
Posted Tuesday, May 18, 2004 2:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 4, 2006 3:35 AM
Points: 31, Visits: 1

I'd also add to the comments about production and use of change management procedures. I'd never allow a procedure that automatically granted changed permissions to users to run in production (or for that matter test). Permissions to production data should be controlled, and in my view test should replicate the live environment - with the exception of the specific change that you are testing.

 





Niall Litchfield
Senior DBA
http://www.niall.litchfield.dial.pipex.com
Post #116356
Posted Thursday, March 9, 2006 2:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 10, 2011 2:18 PM
Points: 156, Visits: 106

Dear Brian:

I really am a fan of yours after going thru this..

In my case, I took it a step further by adding the Table functions and the regular functions... I thank you very much for your contribution...I am a happy DBA!

Enclosed is the code for a stored proc which incorporates the granting of permissions to stored procs, table functions and regular functions. By creating this procedure in master database, I can call it from any user database to maintain permissions for the db_executor role.

MELVYN

Create proc sp_grantpermsprocsfunctions
as
/**  Procedure :   sp_grantpermsprocsfunctions
**                    
**  File      :    
**    
**  Author: MELVYN PATRICK LOPEZ     
**  Date:  03/08/2006
**
**  Description : This Procedure is to be used to maintain db_executor role

**                    Inspired by Brian Kelly at SQL SERVER CENTRAL
**                   
**                      
**               
**  Parameters : NONE
**
**  Return Values :      
**   
**  Data Retrieval Values: 
** 
**  Errors: 
**
**  Tables
**     Select : cursor
**                    
**       
**     Insert :
**       
**     Update :
**                     
**     Delete : 
**        
**  Procedures :
**     Calls :   sp_executesql 
**       
**     Called by : 
**     
**  Modified:
**
**
*/ 


BEGIN

DECLARE @SQL nvarchar(4000),
  @Owner sysname,
  @StoredProcedure sysname,
  @Return int

-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
FROM sysobjects
WHERE xtype = 'P'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @StoredProcedure

-- Set the return code to 0
SET @Return = 0

-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
  BEGIN
    -- Create the SQL Statement. Since we're giving
    -- access to all stored procedures, we have to
    -- use a two-part naming convention to get the owner.
    SET @SQL = 'GRANT EXECUTE ON [' + @Owner
          + '].[' + @StoredProcedure
          + '] TO db_executor'

    -- Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    -- Get the next row
    FETCH NEXT FROM cursStoredProcedures
    INTO @Owner, @StoredProcedure
  END

-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
  BEGIN
    -- Exited fine, commit the permissions
    COMMIT TRAN
  END
ELSE
  BEGIN
    -- Exited with an error, rollback any changes
    ROLLBACK TRAN
   
    -- Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @StoredProcedure + ']'
    RAISERROR(@SQL, 16, 1)
  END
----------------------------------------------------------------------------
DECLARE cursTableFunctions CURSOR FAST_FORWARD
FOR
  SELECT USER_NAME(uid) Owner, [name] TableFunction
   FROM sysobjects
   WHERE xtype = 'TF'


OPEN cursTableFunctions

-- "Prime the pump" and get the first row
FETCH NEXT FROM cursTableFunctions
INTO @Owner, @StoredProcedure

-- Set the return code to 0
SET @Return = 0

-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
  BEGIN
    -- Create the SQL Statement. Since we're giving
    -- access to all stored procedures, we have to
    -- use a two-part naming convention to get the owner.
    SET @SQL = 'GRANT SELECT ON [' + @Owner
          + '].[' + @StoredProcedure
          + '] TO db_executor'

    -- Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    -- Get the next row
    FETCH NEXT FROM cursTableFunctions
    INTO @Owner, @StoredProcedure
  END

-- Clean-up after the cursor
CLOSE cursTableFunctions
DEALLOCATE cursTableFunctions

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
  BEGIN
    -- Exited fine, commit the permissions
    COMMIT TRAN
  END
ELSE
  BEGIN
    -- Exited with an error, rollback any changes
    ROLLBACK TRAN
   
    -- Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @StoredProcedure + ']'
    RAISERROR(@SQL, 16, 1)
  END

-------------------------------------

DECLARE cursFunctions CURSOR FAST_FORWARD
FOR
  SELECT USER_NAME(uid) Owner, [name] fFunction
   FROM sysobjects
   WHERE xtype = 'FN'


OPEN cursFunctions

-- "Prime the pump" and get the first row
FETCH NEXT FROM cursFunctions
INTO @Owner, @StoredProcedure

-- Set the return code to 0
SET @Return = 0

-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
  BEGIN
    -- Create the SQL Statement. Since we're giving
    -- access to all stored procedures, we have to
    -- use a two-part naming convention to get the owner.
    SET @SQL = 'GRANT EXECUTE ON [' + @Owner
          + '].[' + @StoredProcedure
          + '] TO db_executor'

    -- Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    -- Get the next row
    FETCH NEXT FROM cursFunctions
    INTO @Owner, @StoredProcedure
  END

-- Clean-up after the cursor
CLOSE cursFunctions
DEALLOCATE cursFunctions

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
  BEGIN
    -- Exited fine, commit the permissions
    COMMIT TRAN
  END
ELSE
  BEGIN
    -- Exited with an error, rollback any changes
    ROLLBACK TRAN
   
    -- Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @StoredProcedure + ']'
    RAISERROR(@SQL, 16, 1)
  END

END
GO

 

 

Post #264653
Posted Tuesday, July 25, 2006 4:22 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 4:26 PM
Points: 582, Visits: 451
Melvin, that's an excellent idea that will be very helpful to me. One question, though. Why not assign all permissions through one cursor, like so?

DECLARE cursAllExecutables CURSOR FAST_FORWARD
FOR
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
FROM sysobjects
where xtype in ('P', 'TF', 'FN')

Seemed to work here.
Post #297268
Posted Sunday, January 21, 2007 7:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 5:55 PM
Points: 255, Visits: 297

Excellent article and discussion, what would I do without sqlservercentral?

It occurred to me that with SQL 2005 you could combine it with a ddl trigger and eliminate the need for scheduling and the sql agent altogether:

create trigger tdGrantExecute
on database for
create_procedure, create_function
as
begin
 
 declare @Exe varchar(128)
 declare @sql varchar(1000)

 declare cExe cursor forward_only for
  select objects.name
  from sys.objects
  inner join sys.schemas on objects.schema_id = schemas.schema_id
  where schemas.name = 'dbo'
   and type in ('P', 'FN', 'FS','AF','PC')

 open cExe
 fetch next from cExe into @Exe

 while @@fetch_status = 0
 begin
  set @sql = 'GRANT EXECUTE ON dbo.[' + @Exe + '] TO db_executor'
  exec (@sql)

  fetch next from cExe into @Exe
 end

 close cExe
 deallocate cExe

/*
** SQL 2005 Object Types **
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored Procedure
PC = Assembly (CLR) stored Procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-Procedure
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML trigger
IF = SQL inlined table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored Procedure
IT = Internal table
*/
end

Note that in my environment I've restricted access to only the dbo schema.

Thanks again!

Post #338538
Posted Monday, July 13, 2009 11:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 11:56 AM
Points: 70, Visits: 144
hi, this may be a stupid question, so apologies in advance (i'm not a dba!)... does this solution work if the users are not members of the db_datawriter role?

for example:
if I have a stored procedure that does an INSERT and user executing the stored procedure is a member of the user created "db_executer" role but not of the "db_datawriter" role would the user still be able run the stored procedure so that it inserts data into a table?

cheers
Post #752239
Posted Monday, July 13, 2009 11:24 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Yes, if you have taken advantage of ownership chaining.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #752251
Posted Monday, July 13, 2009 11:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 11:56 AM
Points: 70, Visits: 144
wow - quick response

thanks!

think i'll have to look into "ownership chaining" but if you have any good links to this feel free to pass them on.

cheers brian!
Post #752253
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse