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

    Unanswered: Security in SQL 7.0

    Hi,
    Im new to DBA administration and security.
    Just got a request for column and row (I think) security.
    The row security is based on data in the tables.
    Can it be done with SQL 7.0?
    Can anyone point me to the right direction?

    Thanks

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What exactly are you trying to secure ?

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Re: Security in SQL 7.0

    RE:
    Hi, Im new to DBA administration and security.
    Just got a request for column and row (I think) security.
    The row security is based on data in the tables.
    Q1 Can row security be done with SQL 7.0? Can anyone point me to the right direction?
    Thanks
    A1 Sql Server 2k and before do not support row permissions directly at the DBMS level (the next version is rumored to). I've seen a variety of implementations of what may be considered private "row security" schemes. Such implementations may be created in 2k, 7.0, 6.x, etc., what exactly is your application / need?

  4. #4
    Join Date
    Nov 2002
    Posts
    33

    Re: Security in SQL 7.0

    I'm trying to limit what users can see.
    In this case there are no updates to the dB only viewing.

    Thanks

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    This sounds like a job for views. Check out "Scenarios for Using Views" in your bol. If you need additional help, let me know.

  6. #6
    Join Date
    Oct 2002
    Posts
    369

    Post Re: Security in SQL 7.0

    Originally posted by mseal1
    I'm trying to limit what users can see.
    In this case there are no updates to the dB only viewing.

    Thanks
    -- This example may help get you started:

    Use Pubs
    Go
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vAuthors]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[vAuthors]
    GO


    -- Create vAuthors, which will restrict row viewing to logins with the same lastname as in the au_lname column
    CREATE VIEW dbo.vAuthors
    AS
    SELECT
    [au_id],
    [au_lname],
    [au_fname],
    [phone],
    [address],
    [city],
    [state],
    [zip],
    [contract]
    FROM
    [pubs].[dbo].[authors]
    Where CURRENT_USER = au_lname

    -- Add a login and user to Pubs and test vAuthors
    Go
    -- sp_droplogin @loginame = 'White'
    if not exists (select * from master.dbo.syslogins where loginname = N'White')
    BEGIN
    exec sp_addlogin @loginame = 'White', @passwd = null, @defdb = 'Pubs'
    exec sp_grantdbaccess @loginame = 'White', @name_in_db = 'White'
    exec sp_addrolemember @rolename = 'db_datareader' , @membername = 'White'
    Deny SELECT, INSERT, UPDATE, DELETE
    ON Authors
    TO White
    END
    GO

    -- now connect and login as White in QA (with no password) and execute a select from the view:

    -- The following should suceed:
    Select * From vAuthors
    -- Returning:
    -- au_id au_lname au_fname phone address city state zip contract
    -- 172-32-1176 White Johnson 408 496-7223 10932 Bigge Rd. Menlo Park CA 94025 1
    Go
    -- While the following statement should fail:
    Select * From Authors
    -- Returning:
    -- Server: Msg 229, Level 14, State 5, Line 1
    -- SELECT permission denied on object 'authors', database 'pubs', owner 'dbo'.
    Go

  7. #7
    Join Date
    Oct 2002
    Posts
    369

    Re: Security in SQL 7.0

    RE: I'm trying to limit what users can see.
    In this case there are no updates to the dB only viewing.
    Thanks
    -- Once the concept is clear this is a more
    -- generalized scheme:

    Use Pubs
    GO

    -- Create a table to track authorized row viewers in Pubs DB views
    CREATE TABLE [Authorized] (
    [TargetTable] [nvarchar] (128) NOT NULL ,
    [DataRowPrimaryKey] [nvarchar] (128) NOT NULL ,
    [PermittedSuser_Sname] [nvarchar] (128) NOT NULL ,
    CONSTRAINT [PK_Authorized] PRIMARY KEY CLUSTERED
    (
    [TargetTable],
    [DataRowPrimaryKey],
    [PermittedSuser_Sname]
    )
    )
    GO

    -- Populate the Authorized pubs table with some sample row security data
    INSERT INTO Authorized
    (TargetTable, DataRowPrimaryKey, PermittedSuser_Sname)
    SELECT
    'Authors' AS TargetTable,
    au_id AS DataRowPrimaryKey,
    suser_sname() AS 'PermittedSuser_Sname'
    FROM
    Authors
    WHERE (state = 'CA')
    Go

    -- Create a view that uses the Authorized pubs table data to determine who sees what rows in the view
    CREATE VIEW dbo.vAuthorsRowSecurityData AS
    SELECT
    [au_id],
    [au_lname],
    [au_fname],
    [phone],
    [address],
    [city],
    [state],
    [zip],
    [contract]
    FROM
    dbo.Authors AS Target
    INNER JOIN
    dbo.Authorized AS OK
    ON
    (
    Target.au_id = OK.DataRowPrimaryKey
    AND
    OK.PermittedSuser_Sname = suser_sname()
    AND
    TargetTable = 'Authors'
    )
    Go

    -- Test the row security scheme
    Select Count(*) As 'Authors Count' From Authors
    Select Count (*) As 'vAuthorsRowSecurityData Count' From vAuthorsRowSecurityData
    Select * From vAuthorsRowSecurityData

    -- Naturally one also needs to secure the base tables
    -- as previously demonstrated in the "get you started" example
    -- presented to demonstrate the concept:

Posting Permissions

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