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);Databas
e=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
> 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);Databas
e=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);Databas
e=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
>|||Thank you very very very very very much, Dan, for your help. I am now
able to do what I wanted to do. I really appreciate the effort you have
put in to enlighten me on the "Permissions" issue. You have indeed
given me quite a detailed explanation.
Yeah, you are correct......using 'sa' & a blank password does
compromise on security. Henceforth I will ensure that I don't repeat
this again.
You have suggested using 'Integrated Security=SSPI' instead of login &
password for Windows authorization. Can you please show me what should
the ConnectionString look like in that case?
Lastly I have been told by a SQL expert that it is not possible to
assign column permissions in SQL Server to control column data
visibility but you are saying otherwise. Can you please give me some
idea on how to go about it or maybe suggest a few sites which deals
with assigning column permissions?
Thanks once again for your help.
Regards,
Arpan|||> You have suggested using 'Integrated Security=SSPI' instead of login &
> password for Windows authorization. Can you please show me what should
> the ConnectionString look like in that case?
"Provider=SQLOLEDB;Data Source=(local);Initial Catalog=ARPAN;Integrated
Security=SSPI"
> Lastly I have been told by a SQL expert that it is not possible to
> assign column permissions in SQL Server to control column data
> visibility but you are saying otherwise. Can you please give me some
> idea on how to go about it or maybe suggest a few sites which deals
> with assigning column permissions?
You can control access to specific columns using column permissions but this
doesn't control visibility. For example, 'SELECT *' will fail if the user
doesn't have SELECT permissions on all table columns. A column list needs
to be specified for those columns the user has permissions to access.
IMHO, views are better for controlling access to specific columns since this
allows columns to be made invisible. This technique is described on the
Books Online under 'Using Views as Security Mechanisms'
<adminsql.chm::/ad_security_5whf.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1125937401.336692.78350@.f14g2000cwb.googlegroups.com...
> Thank you very very very very very much, Dan, for your help. I am now
> able to do what I wanted to do. I really appreciate the effort you have
> put in to enlighten me on the "Permissions" issue. You have indeed
> given me quite a detailed explanation.
> Yeah, you are correct......using 'sa' & a blank password does
> compromise on security. Henceforth I will ensure that I don't repeat
> this again.
> You have suggested using 'Integrated Security=SSPI' instead of login &
> password for Windows authorization. Can you please show me what should
> the ConnectionString look like in that case?
> Lastly I have been told by a SQL expert that it is not possible to
> assign column permissions in SQL Server to control column data
> visibility but you are saying otherwise. Can you please give me some
> idea on how to go about it or maybe suggest a few sites which deals
> with assigning column permissions?
> Thanks once again for your help.
> Regards,
> Arpan
>|||Your suggestions have indeed been very helpful, Dan. Thanks for the
same.
I tried using the ConnectionString you have cited in an ASP application
but it throws the following error:
---
Login failed for user 'ARPAN\IUSR_ARPAN'.
---
Why this error?
There's another question I would like to ask you regarding Permissions
in SQL Server. After executing the queries you had provided, I am now
able to restrict users' access to the different columns but any user
can access the different views & play with the records by viewing the
records in Enterprise Manager. Is there any way to overcome this?
BTW, what is IMHO?
Thanks once again,
Regards,
Arpan|||Sorry Dan....forgot to ask another question.
Assume that instead of 3 users, there are, say, 100 users. Does that
mean that 100 views have to be created - one for each user? If yes,
then this means that the ASP application that will be accessing the
columns must have 100 If...Else statements with the ConnectionString
having different UIDs & Passwords something like this (assuming that
each user has to enter a password in the ASP application first before
proceeding further):
---
Dim strPassword
strPassword=3DRequest.Form("pwd") 'collecting the password the user has
entered
If(strPassword=3D"pwd1") Then
" Provider=3DSQLOLEDB;Server=3D(local);Dat
abase=3DARPAN;UID=3Duid1;PWD=3Dpas=
s1=AD"
ElseIf(strPassword=3D"pwd2") Then
" Provider=3DSQLOLEDB;Server=3D(local);Dat
abase=3DARPAN;UID=3Duid2;PWD=3Dpas=
s2"
ElseIf(strPassword=3D"pwd3") Then
" Provider=3DSQLOLEDB;Server=3D(local);Dat
abase=3DARPAN;UID=3Duid3;PWD=3Dpas=
s3"
.=2E...........
.=2E...........
.=2E...........
End If
---
This will be a tedious & monotonous approach! Would you suggest some
other workaround to this?
Also the ConnectionString that uses 'Integrated Security=3DSSPI' doesn't
include any UserID & Password; so how will SQL Server know which user
is trying to access data & restrict data access accordingly?
Thanks once again,
Regards,
Arpan|||> Login failed for user 'ARPAN\IUSR_ARPAN'.
The 'ARPAN\IUSR_ARPAN' login referenced in the error message is the Windows
account used for anonymous access. This account is used because your web
application is configured for anonymous access instead of integrated
security. Is this an intranet app? The details on how to use Windows
authentication under the end user's security context depend on the version
of IIS and whether you are running AD. You can search for relevant MSDN
articles at http://msdn.microsoft.com/
> There's another question I would like to ask you regarding Permissions
> in SQL Server. After executing the queries you had provided, I am now
> able to restrict users' access to the different columns but any user
> can access the different views & play with the records by viewing the
> records in Enterprise Manager. Is there any way to overcome this?
Access to the data is limited to the permissions you have granted regardless
of the method used to access the data.
- user must have a valid SQL Login
- user must have been granted database access
- user must have permssions, direct or via role membership, on the objects
> BTW, what is IMHO?
http://www.answers.com/topic/imho
Hope this helps.
Dan Guzman
SQL Server MVP
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1125949593.398597.234660@.g44g2000cwa.googlegroups.com...
> Your suggestions have indeed been very helpful, Dan. Thanks for the
> same.
> I tried using the ConnectionString you have cited in an ASP application
> but it throws the following error:
> ---
> Login failed for user 'ARPAN\IUSR_ARPAN'.
> ---
> Why this error?
> There's another question I would like to ask you regarding Permissions
> in SQL Server. After executing the queries you had provided, I am now
> able to restrict users' access to the different columns but any user
> can access the different views & play with the records by viewing the
> records in Enterprise Manager. Is there any way to overcome this?
> BTW, what is IMHO?
> Thanks once again,
> Regards,
> Arpan
>|||It seems you want to display different data based on the password the user
entered. In this case, you can connect using a common login (unknown to the
end user). You can then either build SQL differently depending on the
password or render data differently depending on the password. You only
need to grant SQL permissions to the application login and users won't be
able to access the data outside your application.
Hope this helps.
Dan Guzman
SQL Server MVP
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1125952217.382925.241690@.g14g2000cwa.googlegroups.com...
Sorry Dan....forgot to ask another question.
Assume that instead of 3 users, there are, say, 100 users. Does that
mean that 100 views have to be created - one for each user? If yes,
then this means that the ASP application that will be accessing the
columns must have 100 If...Else statements with the ConnectionString
having different UIDs & Passwords something like this (assuming that
each user has to enter a password in the ASP application first before
proceeding further):
---
Dim strPassword
strPassword=Request.Form("pwd") 'collecting the password the user has
entered
If(strPassword="pwd1") Then
" Provider=SQLOLEDB;Server=(local);Databas
e=ARPAN;UID=uid1;PWD=pass1_"
ElseIf(strPassword="pwd2") Then
" Provider=SQLOLEDB;Server=(local);Databas
e=ARPAN;UID=uid2;PWD=pass2"
ElseIf(strPassword="pwd3") Then
" Provider=SQLOLEDB;Server=(local);Databas
e=ARPAN;UID=uid3;PWD=pass3"
............
............
............
End If
---
This will be a tedious & monotonous approach! Would you suggest some
other workaround to this?
Also the ConnectionString that uses 'Integrated Security=SSPI' doesn't
include any UserID & Password; so how will SQL Server know which user
is trying to access data & restrict data access accordingly?
Thanks once again,
Regards,
Arpan|||Yes Dan you are absolutely correct....I want to display different data
depending upon the password the user entered. You have suggested either
building SQL differently depending on the password or rendering data
differently depending on the password. Now if I am not mistaken, this
means that the ASP application must have 100 If....Else statements for
100 users & that is exactly what I want a workaround for. Please
correct me if I have misinterpreted your advice.
I believe citing an example as what you have done earlier would be
highly beneficial.
Sorry for all the trouble I am giving you.
Thanks once again for the co-operation you have extended towards me,
Regards,
Arpan|||> Now if I am not mistaken, this
> means that the ASP application must have 100 If....Else statements for
> 100 users & that is exactly what I want a workaround for.
Rather than hard-code the list in your app, a better approach would be to
store this data externally and then build the appropriate SQL statement
depending on the user's identity. The simplistic example below shows how
one could store this user configuration data in SQL Server.
CREATE TABLE Users
(
UserId varchar(30) NOT NULL
CONSTRAINT PK_Users PRIMARY KEY,
Password varchar(30) NOT NULL
)
INSERT INTO Users VALUES('User1', 'Passwrd1')
INSERT INTO Users VALUES('User2', 'Passwrd2')
INSERT INTO Users VALUES('User3', 'Passwrd3')
CREATE TABLE UserViews
(
UserId varchar(30) NOT NULL,
TableName sysname NOT NULL,
SqlStatement varchar(4000),
CONSTRAINT PK_UserViews
PRIMARY KEY(UserId, TableName),
CONSTRAINT FK_UserViews_Users
FOREIGN KEY(UserId)
REFERENCES Users(UserId)
)
INSERT INTO UserViews VALUES('User1', 'Table1', 'SELECT Col1, Col2 FROM
Table1')
INSERT INTO UserViews VALUES('User2', 'Table1', 'SELECT Col3, Col4 FROM
Table1')
INSERT INTO UserViews VALUES('User3', 'Table1', 'SELECT Col5, Col6 FROM
Table1')
GO
Your application can then retrieve the desired SQL Statement based on the
current user identity and then execute query:
SELECT SqlStatement FROM UserViews WHERE UserId = ? AND TableName = ?
Note that you don't need different SQL logins with this method, assuming
individual users access SQL Server only from your application.
Hope this helps.
Dan Guzman
SQL Server MVP
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1126046628.322404.42890@.g44g2000cwa.googlegroups.com...
> Yes Dan you are absolutely correct....I want to display different data
> depending upon the password the user entered. You have suggested either
> building SQL differently depending on the password or rendering data
> differently depending on the password. Now if I am not mistaken, this
> means that the ASP application must have 100 If....Else statements for
> 100 users & that is exactly what I want a workaround for. Please
> correct me if I have misinterpreted your advice.
> I believe citing an example as what you have done earlier would be
> highly beneficial.
> Sorry for all the trouble I am giving you.
> Thanks once again for the co-operation you have extended towards me,
> Regards,
> Arpan
>
" <hari_pra
BoulderBum: