Re: Permissions!

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/04/05

  • Next message: Sophie Guo [MSFT]: "RE: 0x80004005 - you need permission to view its data" 
    Date: Sun, 4 Sep 2005 11:11:45 -0500

    > DENY SELECT ON tblTry(FName,LName) TO dbo

    First, be aware that 'dbo' is a special user that has full permissions.
    Database permissions are not checked for the 'dbo' user so it serves no
    purpose to assign permissions to 'dbo'. Also, logins that are a member of
    the sysadmin fixed server role map to the 'dbo' user in all databases so
    permissions are not checked in any database for sysadmin role members.

    > objConn.Open
    > "Provider=SQLOLEDB;Server=(local);Database=ARPAN;UID=sa;PWD="

    Never use the 'sa' login for routine application access and assign a strong
    password to this login. A blank 'sa' password is a very bad thing.

    > I have a SQL Server 7.0 database table which has 6 columns. I want that
    > only 3 users should be allowed to access this table. Out of these 3
    > users, the first user should be allowed to access the records of only
    > the first 2 columns, the second user should be allowed to access the
    > records of the next 2 columns only & the third user should be allowed
    > to access the records of the last 2 columns. How do I implement this?
    > Do I have to create new Users/Roles to implement this & then GRANT,
    > REVOKE or DENY permissions to these 3 users? Does 3 users mean I have
    > to create 3 new Users?

    You will need individual logins/users so that SQL Server can identify users
    properly and check user permissions. These may be either standard SQL
    logins or Windows accounts. For SQL authentication, specify the
    individual's login and password in your connection string. For Windows
    auth, specify 'Integrated Security=SSPI' instead of the login and password.

    It's not required to use database roles to manage security but it's
    generally recommended because it simplifies security administration. With
    roles, you can setup security once and then control access through role
    membership.

    Although column permissions can be used to control column data visibility,
    you might find it easier to create views and grant permissions on views
    rather than to the underlying table. This technique allows you to control
    data access both vertically (columns) and horizontally (Rows). Below is a
    script that illustrates how you can implement this with both SQL and Windows
    authentication. See The Books Online for details and examples.

    USE MyDatabase
    GO

    CREATE TABLE dbo.tblTry
    (
        FName varchar(30),
        LName varchar(30),
        Col3 int,
        Col4 int,
        Col5 int,
        Col6 int
    )
    GO

    CREATE VIEW dbo.View1 AS
    SELECT FName, LName FROM dbo.tblTry
    GO

    CREATE VIEW dbo.View2 AS
    SELECT Col3, Col4 FROM dbo.tblTry
    GO

    CREATE VIEW dbo.View3 AS
    SELECT Col5, Col6 FROM dbo.tblTry
    GO

    --setup security
    EXEC sp_addrole 'Role1'
    EXEC sp_addrole 'Role2'
    EXEC sp_addrole 'Role3'
    GO

    GRANT SELECT ON View1 TO Role1
    GRANT SELECT ON View2 TO Role2
    GRANT SELECT ON View3 TO Role3
    GO

    --setup access (SQL authentication)
    EXEC sp_addlogin 'User1', 'User1Password'
    EXEC sp_addlogin 'User2', 'User2Password'
    EXEC sp_addlogin 'User3', 'User2Password'
    EXEC sp_grantdbaccess 'User1'
    EXEC sp_grantdbaccess 'User2'
    EXEC sp_grantdbaccess 'User3'
    EXEC sp_addrolemember 'Role1', 'User1'
    EXEC sp_addrolemember 'Role2', 'User2'
    EXEC sp_addrolemember 'Role3', 'User3'
    GO

    --setup access (Windows authentication)
    EXEC sp_grantlogin 'MyDomain\User1'
    EXEC sp_grantlogin 'MyDomain\User2'
    EXEC sp_grantlogin 'MyDomain\User3'
    EXEC sp_grantdbaccess 'MyDomain\User1'
    EXEC sp_grantdbaccess 'MyDomain\User2'
    EXEC sp_grantdbaccess 'MyDomain\User3'
    EXEC sp_addrolemember 'Role1', 'User1'
    EXEC sp_addrolemember 'Role2', 'User2'
    EXEC sp_addrolemember 'Role3', 'User3'
    GO 

    -- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Arpan" <arpan_de@hotmail.com> wrote in message 
news:1125803777.338446.195820@g44g2000cwa.googlegroups.com...
>I am an absolute newbie as far as granting, revoking & denying
> permissions to database objects are concerned. So please help me. I
> went through BOL but couldn't understand many aspects.
>
> This is what I want to do:
>
> I have a SQL Server 7.0 database table which has 6 columns. I want that
> only 3 users should be allowed to access this table. Out of these 3
> users, the first user should be allowed to access the records of only
> the first 2 columns, the second user should be allowed to access the
> records of the next 2 columns only & the third user should be allowed
> to access the records of the last 2 columns. How do I implement this?
> Do I have to create new Users/Roles to implement this & then GRANT,
> REVOKE or DENY permissions to these 3 users? Does 3 users mean I have
> to create 3 new Users?
>
> Now I access SQL Server from an ASP application using the following
> ConnectionString:
>
> objConn.Open
> "Provider=SQLOLEDB;Server=(local);Database=ARPAN;UID=sa;PWD="
>
> When I open Query Analyzer & connect, the SQL Server drop-down menu
> lists only 1 option which is 'ARPAN' (without the quotes). Under
> 'Connection Information', the 'Use SQL Server authentication' radio
> button is checked & the Login Name is sa. The Password field is blank.
>
> Please note that I login to my Windows 2000 Professional machine using
> Administrator as the login name & a password.
>
> This is what I did but failed:
>
> I executed the following query in Query Analyzer:
>
> DENY SELECT ON tblTry(FName,LName) TO dbo
>
> but after that when I executed SELECT * FROM tblTry in QA, the records
> under the columns named FName & LName were also retrieved along with
> the records of the rest of the columns. Even opening the table tblTry
> in Enterprise Manager displayed all the records under the columns FName
> & LName along with the records of the rest of the columns! So what did
> the DENY statement do?
>
> I would be highly obliged if someone could please explain me the steps
> required to do this in detail.
>
> Thanks,
>
> Arpan
>
  • Next message: Sophie Guo [MSFT]: "RE: 0x80004005 - you need permission to view its data"