Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Posts
    33

    Unanswered: Database security

    Hi,
    I'm trying to implement some security on our more sensitive tables in a database.
    The database is used by all for read/write via Web pages (IIS).
    Is there any way to restrict users from accessing a table other than from a specific application (i.e. IIS or Crystal Reports)?
    Am I looking in the wrong direction?


    Thanks
    Motty

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: Database security

    Yes, you can do that by implementing application security(application role).
    For more details see "application roles" in BOL.

    Originally posted by mseal1
    Hi,
    I'm trying to implement some security on our more sensitive tables in a database.
    The database is used by all for read/write via Web pages (IIS).
    Is there any way to restrict users from accessing a table other than from a specific application (i.e. IIS or Crystal Reports)?
    Am I looking in the wrong direction?


    Thanks
    Motty
    Steve

  3. #3
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185
    How is the access to the tables controlled?Thru Stored procedure ,roles???

  4. #4
    Join Date
    Nov 2002
    Posts
    33
    I have no control at this time as to how users access the Db.
    Security is using NT logons, and domain users can read/write to all tables.
    (Hope I don't sound too naive about administrating my database (SQL 7.0)

    Thanks
    Motty

  5. #5
    Join Date
    Nov 2002
    Posts
    33
    What if I have no control over the application that accesses SQL, then I can't run the sp_setapprole to gain access?

  6. #6
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117
    Once the app role in place, you won`t need to keep NT logons , so this it would be the only way to connect to the database for the users. (supposing of course that guest acc. don`t exists in the current DB)

    Originally posted by mseal1
    What if I have no control over the application that accesses SQL, then I can't run the sp_setapprole to gain access?
    Steve

  7. #7
    Join Date
    Nov 2002
    Posts
    33
    I know I'm sounding a little thick today…
    I have several applications (off the shelf) such as Crystal reporting, Access, Excel…
    I want to be able to limit access to a table based on the application name the users are coming from.
    If I use Profiler, I have a column called 'Application Name' that identifies the type of application.
    Can I use that information? At times I don't have a way to 'send' the sp_setapprole command.

    Thanks for all your help!

  8. #8
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117
    No you don't because SQL implements the security based on accounts and roles. The only way to restrict the access is to declare a custom role in your DB for each app., then set the privileges according to your policy, and map your users to these roles.



    Originally posted by mseal1
    I know I'm sounding a little thick today…
    I have several applications (off the shelf) such as Crystal reporting, Access, Excel…
    I want to be able to limit access to a table based on the application name the users are coming from.
    If I use Profiler, I have a column called 'Application Name' that identifies the type of application.
    Can I use that information? At times I don't have a way to 'send' the sp_setapprole command.

    Thanks for all your help!
    Steve

  9. #9
    Join Date
    Nov 2002
    Posts
    33
    Thanks,
    I think I have enough to start

Posting Permissions

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