Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Posts
    19

    Unanswered: Revoking permissions on system tables to the public role.

    We have an audit issue that is requiring me to revoke the select permissions from the public role to the system tables. Has anyone had to do this?? What problems did you experience? Are their any tables that you were not able to change the permissions on? Any help is greatly appreciated.
    Last edited by rpeoplesjr; 10-14-04 at 10:48.

  2. #2
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    ..when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...

    ...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)

    ...as to what affect revoking system table access from the public role will have, you would first have to answer the above questions...

    ...also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?

  3. #3
    Join Date
    Jun 2002
    Posts
    19
    ..when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...

    --I mean all. From the master, msdb, and user databases. My concerns are mostly with the master and msdb databases.

    ...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)

    --We use several types of applications. Everything from web pages( We are a web company), to PeopleSoft, Siebel, and Access.

    also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?
    -- I am not sure, but I believe that they will. This is related to the new Sarbanes-Oxley act.

  4. #4
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Unhappy

    ...we are working on Sarbanes as well...but there has been nothing to compell us to look at this type of restriction...

    ...I am not sure you can restrict this access and still have application functionality...

    ...if this is still something you would like to pursue, I would start at the database level, create a test database and userid's and a method of access.
    First you would have to test to ensure the access method works for the objects created in the database. Once that was proven, I would try the revoke against the databases system tables. To obtain a list of system tables:

    select name from sysobjects where type = 'S'

    you can then use the "sp_helprotect" stored procedure to obtain the current security settings.

    then use the REVOKE command to revoke public access from these objects...

    ...then the access methid would have to be tested again...

    now as far as the system databases goes, i'm sure that revoking public access would allow funtionality to remain...but you'd have to follow a similiar test...good luck...

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Why on earth would SOX be interested in the system tables? Are you storing SOX-sensitive data in the system tables? Using system tables for storing user data is a really bad idea!

    -PatP

  6. #6
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    ...we discussed SOX at lunch and agree that everyone seems to be going overboard in the name of SOX...including auditors who are just guessing at how to comply with the new expectations...(not that they ever knew!)...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •