Read-only schema allows write

  • Once again, I thought I understood permissions, and once again, turns out I don't know jack.

    I have a view on a table. The table has no special permissions. The view is in a schema, named RO, which I thought allowed only read access to anything in it. It has Grant Execute and Select permissions for a Database Role, PaleoDataHost, which is the lowest in a set of cascaded permissions roles. Anybody in a higher role can do specific things granted to that role, as well as anything allowed to a lower role.

    Messing with a new stored procedure, I accidentally issued an Update command to this view, and it executed. I don't understand why my read-only permissions on the view were ignored, and am a bit disturbed by it. I use these permissions rather heavily, and have relied on them to keep unauthorized users out of places they don't belong. I also have stuff in the application to check access rights, as well as users that are not given to obnoxious behavior, so it hasn't really been a security issue, but it bothers me. This should work, and I should be able to trust it.

    Is there some specific format for posting permissions, so that people can look at it and tell me what I screwed up? I tried letting SSMS script the schema and role, but all it comes up with is this:

    CREATE SCHEMA [RO] AUTHORIZATION [dbo]
    CREATE ROLE [PaleoDataHost] AUTHORIZATION [dbo]

    which says nothing about permissions. When I make a change, only the change is scripted, not the entire set of permissions.

    What might I be doing wrong with my permissions, and how do I post the necessary information here for people to analyze my mistakes?

  • Were you logged into the system with a login/user that only has the role 'PaleoDataHost'?  If not - then whatever permissions the login/user was granted is what will be utilized.  I am assuming that you were logged in as yourself - and that you have sysadmin - which means you have full access.

    If you were logged into the system with an account that only has that role - then the update would not have been allowed.

    I am not sure how you set 'read-only' permissions to a view...again, permissions are set on users and/or roles.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I do not routinely use sysadmin, due to security concerns. I do have an account that has dbo access, which is what I connected with. Sysadmin I use only when absolutely necessary, and it a has a hellishly complex password.

    I thought that the permissions on a schema applied to everything in that schema. Specifically, if the schema has Select permission, and not Insert or Update, I thought everything in that schema (specifically, this view) would have only that level of access to the underlying table. That seems to not be the case, and it's far from the first thing that I have completely misunderstood about security and permissions.

    Since the schema does not seem to have the effect I thought it had, what is the point of putting a view into a schema? Does it actually do anything? Does putting a stored procedure into a schema do anything? It seems to me that I have previously fixed permission problems by putting views or procedures into the correct schema, although I do not recall exact details at the moment. But I routinely overlook permissions, test something, then discover that users do not have the necessary permission to execute an action that worked for me as dbo. I always fix it by fiddling things into the proper schema, one that has necessary permissions for a role, such as Update or Insert. I thought I knew what I was doing, because I've always managed to get things working that way, but now I seem to be up against a wall again.

  • pdanes - Monday, May 28, 2018 1:07 PM

    I do not routinely use sysadmin, due to security concerns. I do have an account that has dbo access, which is what I connected with. Sysadmin I use only when absolutely necessary, and it a has a hellishly complex password.

    I thought that the permissions on a schema applied to everything in that schema. Specifically, if the schema has Select permission, and not Insert or Update, I thought everything in that schema (specifically, this view) would have only that level of access to the underlying table. That seems to not be the case, and it's far from the first thing that I have completely misunderstood about security and permissions.

    Since the schema does not seem to have the effect I thought it had, what is the point of putting a view into a schema? Does it actually do anything? Does putting a stored procedure into a schema do anything? It seems to me that I have previously fixed permission problems by putting views or procedures into the correct schema, although I do not recall exact details at the moment. But I routinely overlook permissions, test something, then discover that users do not have the necessary permission to execute an action that worked for me as dbo. I always fix it by fiddling things into the proper schema, one that has necessary permissions for a role, such as Update or Insert. I thought I knew what I was doing, because I've always managed to get things working that way, but now I seem to be up against a wall again.

    The schema will have the effect that you think it does. You can manage security with the use of schemas but you need to consider permissions at all levels.
    Permissions are cumulative so you also need to take into account database permissions, membership in any other roles or groups, public permissions, etc. So you usually want to check effective permissions - meaning permissions from all of the above. Using fn_my_permissions is a good start to check what your permissions are. I'd probably start by checking database level permissions - it's nothing other than:
    SELECT *
    FROM sys.fn_my_permissions(null, 'database');

    Check the documentation for this function as well as has_perms_by_name since they both deal with effective permissions:
    sys.fn_my_permissions

    Sue

  • Aha. It may be that -I- was able to write to read-only schema, since I have dbo permission on my login. I thought that the more restrictive permission would apply, and that the RO schema would prevent anyone from writing through it, even if they actually had permission to access the underlying table. I seem to remember something about the more restrictive permission has priority, in my readings on permissions, but I don't remember exactly how it went. It may have been Windows file and folder permissions, which I've also been gnashing my teeth over.

    I suppose I need to test these things with logins privileged the same way as the users. But that's a PIA, since I have several levels of user privilege. I thought these schema restrictions would keep control over what the app could do where, regardless of who logged in, and save me the work of running multiple tests. I already can't keep up with all the stuff I should test before every deployment of a new version. There are too many functions to test them all, and testing my own work is not as effective as having someone else test it. But there isn't anyone else.

    Anyway, thank you for the tip. I'll have a look at those functions and see if I can clear away some of my confusion.

  • pdanes - Wednesday, May 30, 2018 1:14 AM

    Aha. It may be that -I- was able to write to read-only schema, since I have dbo permission on my login. I thought that the more restrictive permission would apply, and that the RO schema would prevent anyone from writing through it, even if they actually had permission to access the underlying table. I seem to remember something about the more restrictive permission has priority, in my readings on permissions, but I don't remember exactly how it went. It may have been Windows file and folder permissions, which I've also been gnashing my teeth over.

    I suppose I need to test these things with logins privileged the same way as the users. But that's a PIA, since I have several levels of user privilege. I thought these schema restrictions would keep control over what the app could do where, regardless of who logged in, and save me the work of running multiple tests. I already can't keep up with all the stuff I should test before every deployment of a new version. There are too many functions to test them all, and testing my own work is not as effective as having someone else test it. But there isn't anyone else.

    Anyway, thank you for the tip. I'll have a look at those functions and see if I can clear away some of my confusion.

    Permissions are cumulative with deny taking precedence rather than the least restrictive set of permissions. It's the deny that matters. If you are in a group that can read from all tables and another group has only create table permissions, you would have all of those permissions - read from all tables and create table.
    I never commented on the dbo permissions as that could mean different things. There is db_owner database role and dbo as in database owner.
    dbo and db_owner aren't the same.

    Sue

  • pdanes - Wednesday, May 30, 2018 1:14 AM

    Aha. It may be that -I- was able to write to read-only schema, since I have dbo permission on my login. I thought that the more restrictive permission would apply, and that the RO schema would prevent anyone from writing through it, even if they actually had permission to access the underlying table. I seem to remember something about the more restrictive permission has priority, in my readings on permissions, but I don't remember exactly how it went. It may have been Windows file and folder permissions, which I've also been gnashing my teeth over.

    I suppose I need to test these things with logins privileged the same way as the users. But that's a PIA, since I have several levels of user privilege. I thought these schema restrictions would keep control over what the app could do where, regardless of who logged in, and save me the work of running multiple tests. I already can't keep up with all the stuff I should test before every deployment of a new version. There are too many functions to test them all, and testing my own work is not as effective as having someone else test it. But there isn't anyone else.

    Anyway, thank you for the tip. I'll have a look at those functions and see if I can clear away some of my confusion.

    Did you select all objects belongs to the schema when you set permissions OR just the schema itself?

  • Just the schema, then I put object like views and stored procedures into the schema.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply