SQL table permissions

Discussion in 'OT Technology' started by Murph, Nov 30, 2007.

  1. Murph

    Murph New Member

    Joined:
    Jun 18, 2003
    Messages:
    872
    Likes Received:
    0
    Location:
    Columbia, SC
    I've got a large legacy SQL 2005 database I'm working on (reworking the table is not an option). There is a user that currently has READ and WRITE. There is a stored procedure that requires the user to have ALTER for a certain table. The stored procedure is run very infrequently perhapse once or twice a year, and it is not desireable for the user to have ALTER on the table for the entire year. It is also not desirable to have the admin change the permissions manually or have to preform some task when the user wants to run the stored procedure. Is there a programatic way to give the user this permission and remove it afterward?
     
  2. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Run that procedure as a separate user with only the required permissions?
     
  3. Murph

    Murph New Member

    Joined:
    Jun 18, 2003
    Messages:
    872
    Likes Received:
    0
    Location:
    Columbia, SC
    This approach would also complicate the process for the user, who as most people who have dealt with users know, tend to be stupid. We want it to be invisible to the user (they just have to click a button).
     
  4. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    I don't mean run it as a different user in the application. I mean run it as a different user in the DB. The user should never know. Create a DB user with ALTER access to your table, and then when you need to run that PROC just use a different database connection logged in as this new user.
     
  5. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    12
    Location:
    Toronto
    or run a cron job at regular intervals (once or twice a year) to give that user the proper privileges.
     
  6. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    But it doesn't sound like he knows when the proc will be run.
     
  7. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    12
    Location:
    Toronto
    So wait, the proc is ran 1-2 a year, at random times. During those times, he has to have permission, and during all others he doesnt?

    Seems kinda stupid to me.
     
  8. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Its a strange question and approach.
     
  9. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yeah I didn't get that either to be honest - if you don't know when it's going to run, and you don't want to give the permissions permantely, how will you know when to invoke and revoke them?
     
  10. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    And if you could grant permission FROM your program... isn't that worse than ALTER permissions?

    So, best I can see is you do that as a separate user so the rest of the operations don't have that permission.
     
  11. br0wer

    br0wer New Member

    Joined:
    Nov 29, 2007
    Messages:
    66
    Likes Received:
    0
    I agree. The elegant solution is to have a separate DB user with the correct permissions, and just run the procedure as the new user. Honestly though, if this stored procedure is only run once or twice a year, I don't see why there's a need for a programmatic solution. It takes one command to grant the privileges, one to run the stored procedure, and one to revoke the privileges. Does your DBA have one hand or something?
     
  12. Pork Chop Sandwiches

    Pork Chop Sandwiches Extraordinary Member

    Joined:
    Feb 25, 2005
    Messages:
    7,217
    Likes Received:
    0
    Location:
    TGP
    You probably wasted more time making this thread than it would take to modify permissions every time this needs to be run for the next 10 years.
     
  13. Murph

    Murph New Member

    Joined:
    Jun 18, 2003
    Messages:
    872
    Likes Received:
    0
    Location:
    Columbia, SC
    Yah, I know its a really crappy retarted situation this is. However, the database was built several years before I came, and it would take months to go back and do it right (an effort schedualed several years in the future).

    The one thing I've learned from this job is follow the process, don't do cutestsy solutions no one can maintain, and document the hell out of everything.
     

Share This Page