Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Unanswered: SQL 2000: View vs table security.

    All,

    Can someone relate how I might be able to setup user security so that users can query our database via views, but be denied direct access to the underlying tables?

    Thx,

    Bovas

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Maybe I'm missing something here, but I would:

    1) create a role and grant that role access to the views (essentially SELECT permissions) and nothing else.
    2) Make users memebers of that role
    3) Grant no other permissions.

    Voila!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2009
    Posts
    5
    I tried that.

    When a user tries to select from the view (via being a member of the role I created) he gets an error saying "select permission denied on... TABLE NAME".

    I can only get around it by basically allowing access to the underlying table.

    btw - the views and tables are in two different databases.

    Bovas

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create your tables with dbo ownership.
    Create an application schema for your interface. The schema needs to be owned by dbo.
    Create all your interface views and sprocs in the application schema.
    Create a role for application users.
    Grant permissions on the application schema to to your application user role.

    Here is a script you can use to grant basic permissions on a schema to any user or role:
    Code:
    CREATE procedure [GrantSchemaUse]
        (@SchemaName varchar(128), --The schema to which rights are to be granted.
        @Principal varchar(128)) --The role or user to whom rights are to be granted.
    as
    begin
    --------------------------------------------------------------------------------
    --Procedure GrantSchemaUse
    --Description:    Grants basic usage rights to all the objects in a schema.
    --------------------------------------------------------------------------------
    --Revision history
    --------------------------------------------------------------------------------
    --blindman, 04/30/2009    Procedure created.
    --------------------------------------------------------------------------------
    
    set nocount on
    
    --Test Parameters
    -- declare    @SchemaName varchar(128)
    -- declare    @Principal varchar(128)
    -- set    @SchemaName = 'SCHEMA'
    -- set    @Principal = 'ROLE'
    
    --Procedure variables
    declare    @SQLString varchar(max) --Dynamic SQL String
    declare    @Priveleges table --Will define the basic priveleges to be granted by object
            (ObjectType varchar(10),
            PrivelegeString varchar(128))
    
    --Procedure code
        insert into    @Priveleges values ('U', 'DELETE, INSERT, REFERENCES, SELECT, UPDATE')
        insert into    @Priveleges values ('V', 'DELETE, INSERT, REFERENCES, SELECT, UPDATE')
        insert into    @Priveleges values ('P', 'EXECUTE')
    
        declare    CommandList cursor for
        select    'GRANT ' + Priveleges.PrivelegeString + ' ON ' + sys.schemas.name + '.' + sys.objects.name + ' TO ' + @Principal
        from    sys.objects
                inner join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
                inner join @Priveleges Priveleges on sys.objects.type = Priveleges.ObjectType COLLATE DATABASE_DEFAULT
        where    sys.schemas.name = @SchemaName
    
        open CommandList
        fetch next from CommandList into @SQLString
        if @SQLString is null print 'No objects in schema ' + @SchemaName
        while @@fetch_status = 0
            begin
                print    @SQLString
                exec    (@SQLString)
                fetch next from CommandList into @SQLString
            end
        close CommandList
        deallocate CommandList
    
    end
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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