Post reply

User permissions

  • July 20, 2005 at 7:46 pm

    #93526

    Hi!

    I've got a a question about permissions...

    First some background. I'm developing a software that will be used by the employees of a company to report how much time has been put on different projects. All tasks regarding a project are saved in a 'Task' table. In the same way information about a project are stored in a 'Project' table.

    I use stored procedures to insert, update or delete records.

    There are two roles a user can be member of. Number one is 'Reporter' and this role doesn't have grant execute on the insert, update and delete SP's used for the project information. Number two is 'Manager' and this role have grant execute.

    Now to the question, how should I do to test if the logged in user have permission to add, edit or delete a project before executing the code that insert new data to my dataset!??

    I do not want to use: If UserRole = Manager...

    I guess this is very simple but newbie as I am...

    Best Wishes,
    Stefan Johansson

  • July 21, 2005 at 7:43 am

    #576072

    Instead of checking permissions, you might simply handle the error (if it occurs) within the application itself.

  • July 21, 2005 at 7:56 am

    #576079

    To be able to check permissions you need to establish a connection (other than the actual user's connection) to the database via a user with the role "db_securityadmin" or be a system administrator and read the system tables directly.

    This complicates matters unnecessarily and I would stick with what bellis said : Write an approriate error handler for permission violations.

    HABIB.

    Kindest Regards,

    Habib Zmerli (MVP)

  • July 21, 2005 at 12:25 pm

    #576194

    >> I do not want to use: If UserRole = Manager... << why ?

    Other than that you can call sp_helprotect like:

    EXEC sp_helprotect NULL, NULL, 'UserName'

    this seems a bit overkill but it may answer your question


    * Noel

  • July 21, 2005 at 12:48 pm

    #576204

    Getting to what I think might be a biggr issue... so all managers have the ability to alter all projects? How are you segregating permissions at this level?

    Also when you say role, is that role a user-defined database role or is it something stored in a database table?

    K. Brian Kelley
    @kbriankelley

  • July 21, 2005 at 5:06 pm

    #576269

    Thank you for all answers!

    noeld: I don't think the IF...END IF is the "right" way to do it. What if I in a later time would like to alter the name of the role, or maybe add more roles?!? In that way I would have to rewrite, recompile and redistribute a "new" application rather than just updating the database...

    belis: In my application I have a mdi-child called Project, and to this I have a toolbar with the buttons for new, edit and delete. When a user click one of these buttons I would like the program to directly show a messagebox with the access denied message if a user is member of the reporter role... Your suggestion works for the delete button, but if the user adds or edit a current record he doesn't get this message until he has written or edited all data and pressed OK...

    bkelley: Yup, so far all users who is a member of the user-defined database role 'Manager' can alter all projects. For the moment I don't know how to segrate permissions at this level, so please, if you could give me a tip, or maybe point me to some good reading I would appreciate it alot!

    To all of you. Why not use the sp_helpprotect as noeld suggested?!?

    And As I said I'm very new to this. I have only been working with the .NET architecture and SQL Server for like 3 months, and I have very little experience of database programming... so maybe I see things in a wrong way?!?

    Thanks alot!

    Best Wishes,
    Stefan Johansson

Viewing 6 posts - 1 through 5 (of 5 total)

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