Permissions on Objects


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Permissions on Objects

  1. #1
    Xin Li Guest

    Permissions on Objects


    Hi,

    I am a relative newbe here, and a newbe to SQL Server in general. So be
    gentle I was wondering if there was anyway I could use a stored procedure
    (or by any other means) to see if a particular user has a particular permission
    on a certain object. The kind of tasks I'd want to perform would be something
    like does the user "jane2" have EXECUTE permission on sp_password, or does
    "tom" have SELECT permission on CustomerList table.

    The primary motivation behind this painful excercise is to implement security
    on a website. The site is driven by a DB, and certain people play certain
    roles in administrating the site. So for every backend page, the ASP code
    has to check to see if the user should be able to access the site. The way
    I thought to do this is to have the ASP code some how check to see if the
    user has a certain set of permissions on a certain set of stored procedures
    and views and possibly tables. This way, I can use SQL server's own security
    infrastructure to manage everything without having to design a virtual security
    system with my own tables and such.

    Normally, this would be a relatively simple task, if I were only checking
    on the particular user account. However, I also need to take into consideration
    that the user could be a member in different database roles, and those roles
    may have been granted certain permission sets on different objects. And
    thus, the user's effective permission set would be the union of all permissions
    from all roles to which the user belongs (Note: I am using SQL Server 7.0).
    And of course if the user is denied a cerain permission at any juncture,
    then the user is entirely denied.

    I know that SQL Server internally grabs all roles in which a user belongs
    from the sysmembers table, and from then on, it does a whole bunch of stuff
    with the syspermissions table to see if a user has permissions to a certain
    object. But I am not clear exactly what is involved. And I'd rather not
    have to reinvent the wheel if I don't have to.

    Any tips or pointers you can give would be greatly appreciated. Thank you
    very much in advance.

    Xin Li


  2. #2
    Andrew Prosser Guest

    Re: Permissions on Objects


    "Xin Li" <xinli1@uiuc.edu> wrote:
    >
    >Hi,
    >
    >I am a relative newbe here, and a newbe to SQL Server in general. So be
    >gentle I was wondering if there was anyway I could use a stored procedure
    >(or by any other means) to see if a particular user has a particular permission
    >on a certain object. The kind of tasks I'd want to perform would be something
    >like does the user "jane2" have EXECUTE permission on sp_password, or does
    >"tom" have SELECT permission on CustomerList table.
    >
    >The primary motivation behind this painful excercise is to implement security
    >on a website. The site is driven by a DB, and certain people play certain
    >roles in administrating the site. So for every backend page, the ASP code
    >has to check to see if the user should be able to access the site. The

    way
    >I thought to do this is to have the ASP code some how check to see if the
    >user has a certain set of permissions on a certain set of stored procedures
    >and views and possibly tables. This way, I can use SQL server's own security
    >infrastructure to manage everything without having to design a virtual security
    >system with my own tables and such.
    >
    >Normally, this would be a relatively simple task, if I were only checking
    >on the particular user account. However, I also need to take into consideration
    >that the user could be a member in different database roles, and those roles
    >may have been granted certain permission sets on different objects. And
    >thus, the user's effective permission set would be the union of all permissions
    >from all roles to which the user belongs (Note: I am using SQL Server 7.0).
    > And of course if the user is denied a cerain permission at any juncture,
    >then the user is entirely denied.
    >
    >I know that SQL Server internally grabs all roles in which a user belongs
    >from the sysmembers table, and from then on, it does a whole bunch of stuff
    >with the syspermissions table to see if a user has permissions to a certain
    >object. But I am not clear exactly what is involved. And I'd rather not
    >have to reinvent the wheel if I don't have to.
    >
    >Any tips or pointers you can give would be greatly appreciated. Thank you
    >very much in advance.
    >
    >Xin Li
    >


    You would make your life easier if you just checked the users role, but to
    check explicit permissions you can execute sp_helprotect or you can use the
    system function 'select permissions(object_id)' for example,

    Regards,

    Andrew

  3. #3
    Xin Li Guest

    Re: Permissions on Objects


    >You would make your life easier if you just checked the users >role, but

    to check explicit permissions you can execute >sp_helprotect or you can use
    the system function 'select >permissions(object_id)' for example,

    Thanks for the tip. Checking role membership would be much easier. I was
    wondering if you could point me to a list of all the stored procedures concerned
    with security, and what parameters they take, and which return values they
    give back. Thanks..

    BTW, how would I get the objectID of something in the DB? Thanks again.

    Xin Li

  4. #4
    DaveSatz Guest

    Re: Permissions on Objects

    In BOL there is a categorized listing of system SP's under "System Stored
    Procedures (T-SQL)" including links to SP like sp_helprotect .

    "Xin Li" <xinli1@uiuc.edu> wrote in message news:3a50f740$1@news.devx.com...
    >
    > >You would make your life easier if you just checked the users >role, but

    > to check explicit permissions you can execute >sp_helprotect or you can

    use
    > the system function 'select >permissions(object_id)' for example,
    >
    > Thanks for the tip. Checking role membership would be much easier. I was
    > wondering if you could point me to a list of all the stored procedures

    concerned
    > with security, and what parameters they take, and which return values they
    > give back. Thanks..
    >
    > BTW, how would I get the objectID of something in the DB? Thanks again.
    >
    > Xin Li




  5. #5
    Xin Li Guest

    Re: Permissions on Objects


    "DaveSatz" <davidsatz@yahoo.com> wrote:
    >In BOL there is a categorized listing of system SP's under "System Stored
    >Procedures (T-SQL)" including links to SP like sp_helprotect .


    Thanks, I found a similar documentation on Microsoft's support site. For
    anyone interested, it's at:

    http://support.microsoft.com/support...ures_syssp.asp



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center