Re: View Security

From: Denny (mrdenny@gamespy.com)
Date: 03/05/03


From: "Denny" <mrdenny@gamespy.com>
Date: Wed, 5 Mar 2003 11:46:32 -0800

You can remove a users select access to sysobjects, which will stop them from being able to use EM to view the database, but will allow them to do a select from the view. The CRM group at my last company did this to me. Still not sure why, but it did work, and was very annoying.

-- 
Denny Cherry
Database Administrator
GameSpy Industries
  "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:OrBrn804CHA.2396@TK2MSFTNGP11.phx.gbl...
  By default, you can examine the system objects.  I imagine you could revoke SELECT permission on the various tables, procs, etc.  Metadata is generally not considered sensitive.
  -- 
  Tom
  ---------------------------------------------------------------
  Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
  SQL Server MVP
  Columnist, SQL Server Professional
  Toronto, ON Canada   tom@cips.ca
  www.pinnaclepublishing.com/sql
    "A.M" <hate-spam@spam.com> wrote in message news:#RQ7P204CHA.1676@TK2MSFTNGP12.phx.gbl...
    So you mean if we give small access to a user then the user will be able to see all database structure ? That is terrible vulnerability.
    We have SCHEMA in Oracle so i can efficiently hide any part of database from user. Do we have similar thing in SQL server? I know SqlServer 2000 has scema capability. Can it solve my problem ?
    Thanks,
    Ali
      "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:#xtfKY04CHA.2408@TK2MSFTNGP09.phx.gbl...
      Well, yes and no.  You can create a view, proc or function and specify the WITH ENCRYPTION option.  However, it is quite difficult to prevent users from seeing a list of objects.
      -- 
      Tom
      ---------------------------------------------------------------
      Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
      SQL Server MVP
      Columnist, SQL Server Professional
      Toronto, ON Canada   tom@cips.ca
      www.pinnaclepublishing.com/sql
        "A.M" <hate-spam@spam.com> wrote in message news:O$KCE2z4CHA.2408@TK2MSFTNGP09.phx.gbl...
        Hi,
        Can i limit a user to just run a select statemet on a view, but do not allow
        him see the view definition or list of other database objects?
        If i make a login member of database public role, then that user will be
        able to see all database structue such as view/table/sp definitions.
        We need to restrict a user and hide database structure from him. All we want
        him to do is run a select statement on a view, but we don't want him to see
        view structure.
        Any help would be appreciated,
        Ali