RE: Revoking public permissions

From: Randy Dyess (anonymous_at_discussions.microsoft.com)
Date: 02/29/04

  • Next message: Randy Dyess: "RE: Encrypt SQL Command to any SQL Server" 
    Date: Sun, 29 Feb 2004 12:46:09 -0800

    Best practices often tell you to remove PUBLIC role from all user objects and this should be done after a careful review of the permission required by your application.

    Some experts also go as far as to recommend that you remove PUBLIC role permissions from system stored procedures and extended stored procedures to prevent attacks on your server. This should also be done, but only after careful testing as removing PUBLIC permissions to all stored procedures can often break some functionality in Enterprise Manager. I do not know of a article/whitepaper that has studied this and listed what exactly will be broken based on each system stored procedure.

    Brian Kelley recently released a nice whitepaper on the PUBLIC role permissions to system tables that you should read to see if it is any help.

    SQL Server 2000: Permissions on System Tables Granted to Logins Due to the Public Role
    http://www.giac.org./practical/GSEC/KBrian_Kelley_GSEC.pdf

    Randy Dyess
    www.Database-Security.Info

  • Next message: Randy Dyess: "RE: Encrypt SQL Command to any SQL Server"

    Relevant Pages

    • Re: change user1 access changes user2 access
      ... Using the public role is your problem. ... All users, whether it be User1, ... permission you are granting All users the permissions. ... > name, a password, I select a database to login to, then go ...
      (microsoft.public.sqlserver.security)
    • Re: SMS View Tables access through ODBC - Help!
      ... > view tables is required for a feed to a third party asset management tool. ... Is public role necessary to access view tables? ... with a standard user account to make sure that nothing bad happened. ... enable the select permissions to the objects you want them to view. ...
      (microsoft.public.sms.admin)
    • Re: change user1 access changes user2 access
      ... > Using the public role is your problem. ... >permission you are granting All users the permissions. ... I click on New Login. ... >> name, a password, I select a database to login to, then ...
      (microsoft.public.sqlserver.security)
    • SQL user permissions problem
      ... I have run into an unusual error for one specific user who's ... permissions have been granted via the public role. ... and permissions to all relevant stored procedures have been set. ...
      (microsoft.public.sqlserver)
    • Re: Public Role in SQL Server 2000 SP3
      ... The default permissions of the public role allow SELECT from sysobjects and ... other meta-data objects. ... A security best practice is to create your own roles and grant object ... permissions to those roles as needed. ...
      (microsoft.public.sqlserver.server)