locked
Mass security implementation... RRS feed

  • Question

  • We have a new QA person coming on board. They want him to have

    SQL Tabled :

    can read, write, and update data

    No ability to create or modify the table structure, or delete tables.

    Stored procs and views:

    Ability to create them but not delete them

    What the most straightforward way to implement this security?  The DB has  300+ tables, with schemas, so executing individual GRANT\REVOKE  ("manually typed in") statements seems like a long path to follow.

    TIA,

    edm2


    • Edited by edm2 Thursday, July 17, 2014 10:42 PM
    Thursday, July 17, 2014 10:42 PM

Answers

  • SQL Tabled :

    can read, write, and update data

    No ability to create or modify the table structure, or delete tables.

    I assume with "delete tables" mean "drop tables". That is, the person should be permitted to delete rows?

    In such case, add the person to the built-in database roles db_datareader and db_datawriter.

    Stored procs and views:

    Ability to create them but not delete them

    This is not achievable, because to create procedure in a schema, you need ALTER permission on that schema and that permission is sufficient to drop the procedure. Then again, since you may create an object in error (wrong name, for instance), it's probably a good thing to be able to drop the procedure you have created.

    What you don't want is the user to be able drop existing procedures in the database. I would suggest that you create a schema for this user and grant this user ALTER and EXEC permission on this schema. You also need to run these GRANT commands:

    GRANT CREATE PROCEDURE TO xxx
    GRANT CREATE VIEW TO xxx

    And definitely not need for any cursor.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 18, 2014 9:09 AM
  • As Erland points out, there is no reason to use this approach and many reasons not to use it.  Consider what happens when a new table is added.  Will anyone remember to grant the appropriate permissions to this table to all appropriate users?  No - at least not until someone complains.  I'll also point out that "deny" is not the same as "revoke" and it is "revoke" that is the more appropriate choice for removing previously granted permissions.  The select statement used for the CurDeny cursor is poorly written since it selects everything - not just tables as the script implies.  But since no error checking is performed, who really cares?  The code also assumes that everything is in the dbo schema and that all identifiers (i.e., table names) adhere to the requirements for regular identifiers.
    • Proposed as answer by Sofiya Li Monday, July 21, 2014 7:49 AM
    • Marked as answer by Sofiya Li Friday, July 25, 2014 9:31 AM
    Friday, July 18, 2014 1:45 PM

All replies

  • Hi,

    Modify below script with your object names and use it. Test it is UAT and then use in Production.

    --Creating a login

    USE [master]

    GO

    CREATE LOGIN [domain\loginID] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

    GO

    --Creating a User

    USE [DBName]

    GO

    CREATE USER [domain\loginID] FOR LOGIN [domain\loginID] WITH DEFAULT_SCHEMA=[dbo]

    GO

    --Removing access to all DB Objects

    USE  [DBName]

    GO 

    DECLARE @TableName  AS varchar(max)

    DECLARE @Script     AS varchar(max) 

    DECLARE CurGrant CURSOR FOR SELECT   

    name FROM sys.objects

    OPEN CurDeny FETCH NEXT FROM CurGrant INTO @TableName

    WHILE @@FETCH_STATUS = 0 

    BEGIN  

    SET @Script = 'DENY SELECT ON dbo.' + @TableName + ' TO [domain\loginID]'

    EXEC (@Script)   

    SET @Script = 'DENY INSERT ON dbo.' + @TableName + ' TO [domain\loginID]'

    EXEC (@Script)

    SET @Script = 'DENY DELETE ON dbo.' + @TableName + ' TO [domain\loginID]'

    EXEC (@Script)

    SET @Script = 'DENY UPDATE ON dbo.' + @TableName + ' TO [domain\loginID]'

    EXEC (@Script)

    FETCH NEXT FROM CurGrant 

    INTO @TableName   

    END

    CLOSE CurGrant DEALLOCATE CurGrant

    --Granting access to specific objects

    USE  [DBName]

    GO 

    DECLARE @TableName  AS varchar(max)

    DECLARE @Script     AS varchar(max) 

    DECLARE CurGrant CURSOR FOR SELECT   

    name FROM sys.tables WHERE   

    name ='Table_Name' OR   

    name ='Table_Name' OR   

    name ='Table_Name' OR   

    name = 'Table_Name'

    OPEN CurGrant FETCH NEXT FROM CurGrant INTO @TableName

    WHILE @@FETCH_STATUS = 0 

    BEGIN  

    SET @Script = 'GRANT SELECT ON dbo.' + @TableName + ' TO [domain\loginID]'

    EXEC (@Script)   

    FETCH NEXT FROM CurGrant 

    INTO @TableName   

    END

    CLOSE CurGrant DEALLOCATE CurGrant


    Swapna

    Thursday, July 17, 2014 10:55 PM
  • Hope you are using sql 2008 ? then below will work for you..

    USE [mydatabase];
    go
    EXEC sp_addrolemember db_datareader, [myuser];
    go
    EXEC sp_addrolemember db_datawriter, [myuser];
    go

    Only specific tables :

    GRANT SELECT ON OBJECT::dbo.Table1 TO User2;
    GRANT INSERT ON OBJECT::dbo.Table1 TO User2;
    GRANT UPDATE ON OBJECT::dbo.Table1 TO User2;
    GRANT DELETE ON OBJECT::dbo.Table1 TO User2;

    http://technet.microsoft.com/en-us/library/ff878066.aspx


    Raju Rasagounder Sr MSSQL DBA


    Friday, July 18, 2014 12:04 AM
  • Swapna,

    Interesting idea. I'm just surprised that something as simple as assigning permissions (a common task)  involves a CURSOR and looping through sys.obejcts. I can use this ideas but need to be careful because I want the new user to be able to I,U,D data into tables but not be able to create, modify or delete the tables themselves. It's just messier than I had hoped.

    edm2

    Friday, July 18, 2014 12:52 AM
  • I myself used this script to assign permissions to specific tables and didn't receive any issue to execute it. You can try it. Actually I got this from Google. Thanks to the one who provided it. If it is helpful then propose my reply as answered.

    Thanks


    Swapna

    Friday, July 18, 2014 1:16 AM
  • SQL Tabled :

    can read, write, and update data

    No ability to create or modify the table structure, or delete tables.

    I assume with "delete tables" mean "drop tables". That is, the person should be permitted to delete rows?

    In such case, add the person to the built-in database roles db_datareader and db_datawriter.

    Stored procs and views:

    Ability to create them but not delete them

    This is not achievable, because to create procedure in a schema, you need ALTER permission on that schema and that permission is sufficient to drop the procedure. Then again, since you may create an object in error (wrong name, for instance), it's probably a good thing to be able to drop the procedure you have created.

    What you don't want is the user to be able drop existing procedures in the database. I would suggest that you create a schema for this user and grant this user ALTER and EXEC permission on this schema. You also need to run these GRANT commands:

    GRANT CREATE PROCEDURE TO xxx
    GRANT CREATE VIEW TO xxx

    And definitely not need for any cursor.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 18, 2014 9:09 AM
  • As Erland points out, there is no reason to use this approach and many reasons not to use it.  Consider what happens when a new table is added.  Will anyone remember to grant the appropriate permissions to this table to all appropriate users?  No - at least not until someone complains.  I'll also point out that "deny" is not the same as "revoke" and it is "revoke" that is the more appropriate choice for removing previously granted permissions.  The select statement used for the CurDeny cursor is poorly written since it selects everything - not just tables as the script implies.  But since no error checking is performed, who really cares?  The code also assumes that everything is in the dbo schema and that all identifiers (i.e., table names) adhere to the requirements for regular identifiers.
    • Proposed as answer by Sofiya Li Monday, July 21, 2014 7:49 AM
    • Marked as answer by Sofiya Li Friday, July 25, 2014 9:31 AM
    Friday, July 18, 2014 1:45 PM