Post reply

Few question about security

  • March 18, 2005 at 3:08 am

    #87863

    Hello to all,

    I have few question about security.

    - I noticed that public role has rights on some system procedures and tables. Do I need this or I can remove rights? Basically I have created my role where I assigned rights to user-definied stored procedures and tables and I do not use any of those system procedure and tables.

    - I want that users of my system connect to DB only over stored procedures (select, insert, update, delete). I gave them rights for my SP's but if I do not give them right for appropriate tables I am receiving errors that they do not have right to connect to tables. Why this happens? I do not want that they have direct right on tables.

    - Can I remove system tables/SP's from my DB? I do not use them.

    Thanks in advance for all comments,

    Oliver

  • March 18, 2005 at 5:55 am

    #547754

    1) Any changes to system objects will result in an unsupported configuration from MS. Yes, you could remove permissions, and Microsoft did so with their OpenHack 4 configuration. There are two objects that public must retain permissions to. More here:

    SQL Server 2000: Permissions on System Tables Granted to Logins Due to the Public Role

    2) Check ownership between the stored procedures and the tables. If the owners are the same (such as both owned by dbo), ownership chaining can be established. If the two have different owners, chaining cannot. If you can't change ownership on one or the other, explicit permissions will need to be granted on the tables.

    Also, if you are using dynamic SQL within these stored procedures and executing them with EXEC or sp_executesql, the problem there is when the dynamic SQL query is executed, SQL Server executes it in a separate batch from the original stored procedure. That means it rechecks permissions. Ownership chaining is not possible. If you are using dynamic SQL in your stored procedures you will need to grant permissions on the tables. Read Robert Marda's work on this site for more on this subject. He has several excellent articles explaining the issue.

    3) Tables no, stored procedures potentially yes. However, you're back in that unsupported state again. If the public role permissions are revoked the stored procedures will still be present and the sysadmin role will still have access to them, but no one else will. This is probably preferable from a Microsoft supportability perspective. It'll be easier to script the existing permissions out, revoke permissions and then if you have a problem, run the script to restore permissions, than it would to script out permissions AND object definitions and do the same in the case of an issue. It is important to note on the OpenHack 4 config, Microsoft did not drop any stored procedures.

    K. Brian Kelley
    @kbriankelley

Viewing 2 posts - 1 through 2 (of 2 total)

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