locked
deny all to certain tables RRS feed

  • Question

  • I have a group of 5 tables Foot_table1, Foot_table2.....Foot_table5

    Now these are in the dbo schema like hundreds of other tables, but these have sensitive data and I want to deny everybody rights to these tables and leave permissions to only one "Secure_P" group.

    What should be the best way to achieve this? I do not want to go through the lenghty procedure of denying each and every type of right to one user at a time, there are 50 users on this one, want to be able to do this easily...

    All help appreciated...

    Thanks,


    Dhananjay Rele

    Friday, March 20, 2015 7:05 PM

Answers

  • I was under the impression that since Prod_team is the owner of this schema only members of Prod_team would be able to run a select query on this table and not anyone outside of this group could do so unless they were either added to this group or given permission exclusively to access that table or schema...

    That depends what permissions they have been granted on the database. They may have been granted SELECT permission on database level or membership in db_datareader. Here is an example that illustrates. user1 is not able to access the secret table, but user2 is.

    CREATE USER user1 WITHOUT LOGIN
    CREATE USER user2 WITHOUT LOGIN
    CREATE USER prod_team WITHOUT LOGIN
    go
    CREATE SCHEMA prod_team AUTHORIZATION prod_team
    go
    GRANT SELECT ON SCHEMA::dbo TO user1
    EXEC sp_addrolemember 'db_datareader', 'user2'
    go
    CREATE TABLE secrettable (a int NOT NULL)
    INSERT secrettable(a) VALUES (89)
    go
    ALTER SCHEMA prod_team TRANSFER dbo.secrettable
    go
    EXECUTE AS USER = 'prod_team'
    go
    SELECT USER, a FROM prod_team.secrettable
    go
    REVERT
    go
    EXECUTE AS USER = 'user2'
    go
    SELECT USER, a FROM prod_team.secrettable
    go
    REVERT
    go
    EXECUTE AS USER = 'user1'
    go
    SELECT USER, a FROM prod_team.secrettable
    go
    REVERT
    go
    DROP TABLE prod_team.secrettable
    DROP SCHEMA prod_team
    DROP USER user1
    DROP USER user2
    DROP USER prod_team


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Michelle Li Thursday, April 2, 2015 12:30 PM
    Tuesday, March 24, 2015 10:47 PM

All replies

  •  How are the user permissions assigned? through roles or just individually,

    if it is through roles, you can deny permission for them by denying permission to the role.

    if it is just individual users, then script them out and you can execute in one go.

    or maybe you can create a role that has deny permissions on the tables and users to the role.

    but this can get complicated as more things come into mix.


    Hope it Helps!!


    • Edited by Stan210 Friday, March 20, 2015 8:25 PM
    Friday, March 20, 2015 7:53 PM
  • From the perspective of managing permissions, the best would be to move these tables to a separate schema. Then you could make sure that only Secure_P is granted permissions on this schema. No one else are granted or denied anything.

    If these tables are in dbo, and people have SELECT permission on the dbo schema, you would need to deny them permission on these tables. But if you lump them all in a group, you must make sure that the Secure_P people are not in this group, because DENY takes precedence over GRANT. On the other hand, if you enter people manually in the group that is denied permission on these tables, new users may get access to them, if you fail to add those users to that group.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, March 20, 2015 10:57 PM
  • Hello ,

    You need to do following for securing your tables:

    you can let a user log in to SQL Server without necessarily granting database rights for individual databases. Second, you can create database user accounts that correspond to login accounts, and you can grant permissions to the user accounts. These user accounts and permissions are specific to individual databases. From Access projects, you can implement SQL Server security for tables in a SQL Server database. You create user accounts and assign permissions to those accounts by selectively designating membership in fixed database roles.

    You can't drop the dbo user, which has all permissions for all objects within the database, including permission to create new user accounts.

    SQL Server has nine fixed database roles, which define standard kinds of database access. When you assign a user to a fixed database role, you give that user a predefined cluster of permissions. From their memberships in fixed database roles, users inherit permissions.

    User accounts that belong to the db_ddladmin role can create and drop tables in a database. However, membership in this role doesn't include the permission to execute SELECT, INSERT, UPDATE, or DELETE statements. A user can belong to any combination of fixed database roles. Therefore, by assigning a user account to the db_datareader and db_datawriter roles but not the db_ddladmin role, you can enable only SELECT, INSERT, UPDATE, and DELETE permissions without conveying table-creation capabilities.

    The db_owner role has all the permissions in a database, including the ability to assign role status to other users. Membership in this role establishes a user as a dbo user. If a login is a dbo user or if a user has membership in the db_owner role, the corresponding user account has unlimited permissions within the database.


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Tuesday, March 24, 2015 10:36 AM
  • Hi Erland,

    Firstly thank you for your quick response.

    I have a follow-up question.

    There is a user-group called prod_team and I am a part of that user-group.

    I created a new schema called "secu" and assigned prod_team to be the owner of that schema. Next I transferred dbo.Foot1 table to secu schema.

    Next I asked someone from outside Prod_team to run a select query on secu.Foot1 and surprisingly they were able to run this query successfully.

    I was under the impression that since Prod_team is the owner of this schema only members of Prod_team would be able to run a select query on this table and not anyone outside of this group could do so unless they were either added to this group or given permission exclusively to access that table or schema...

    All help appreciated


    Dhananjay Rele

    Tuesday, March 24, 2015 5:22 PM
  • IMHO, the answer to that depends on what permissions do the user who ran that command have?  does he have db_owner  or db_datareader role?  or select access on the schema. but since you said  you just created the schema, i wonder if he is a part of the database fixed  roles,

    you can also use this function, to get the permissions of a user 

    https://msdn.microsoft.com/en-us/library/ms176097.aspx


    Hope it Helps!!

    Tuesday, March 24, 2015 5:47 PM
  • I was under the impression that since Prod_team is the owner of this schema only members of Prod_team would be able to run a select query on this table and not anyone outside of this group could do so unless they were either added to this group or given permission exclusively to access that table or schema...

    That depends what permissions they have been granted on the database. They may have been granted SELECT permission on database level or membership in db_datareader. Here is an example that illustrates. user1 is not able to access the secret table, but user2 is.

    CREATE USER user1 WITHOUT LOGIN
    CREATE USER user2 WITHOUT LOGIN
    CREATE USER prod_team WITHOUT LOGIN
    go
    CREATE SCHEMA prod_team AUTHORIZATION prod_team
    go
    GRANT SELECT ON SCHEMA::dbo TO user1
    EXEC sp_addrolemember 'db_datareader', 'user2'
    go
    CREATE TABLE secrettable (a int NOT NULL)
    INSERT secrettable(a) VALUES (89)
    go
    ALTER SCHEMA prod_team TRANSFER dbo.secrettable
    go
    EXECUTE AS USER = 'prod_team'
    go
    SELECT USER, a FROM prod_team.secrettable
    go
    REVERT
    go
    EXECUTE AS USER = 'user2'
    go
    SELECT USER, a FROM prod_team.secrettable
    go
    REVERT
    go
    EXECUTE AS USER = 'user1'
    go
    SELECT USER, a FROM prod_team.secrettable
    go
    REVERT
    go
    DROP TABLE prod_team.secrettable
    DROP SCHEMA prod_team
    DROP USER user1
    DROP USER user2
    DROP USER prod_team


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Michelle Li Thursday, April 2, 2015 12:30 PM
    Tuesday, March 24, 2015 10:47 PM