February 25, 2010 at 1:25 am
Hallo all together:
Here is what I tried to do with Login, User + database roles
Environment and requirements:
- MSSQL Server 2005 Express
- SQL Server Authentication Mode
- want to assign user rights explicit on every table, view, procedure or function
- don't want to assign the permissions in the USERs, because they can change.
I want to assign permissions in roles and map the USERS to these roles like shown
below.
--> ..."sa" access needed for login creation!
USE [master]
GO
--> Create the Logins on the Server level
CREATE LOGIN [DBViewer] WITH PASSWORD=N'xyzxyzxyz', DEFAULT_DATABASE=[my_database], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
--> Change to database level (login as db_owner)
USE [my_database]
GO
--> Create the User with the appropriate Login on DB level
--> (login and user has the same name here)
CREATE USER [DBViewer] FOR LOGIN [DBViewer]
GO
--> Create the required Database Role
CREATE ROLE [db_dbreader_role] AUTHORIZATION [DBViewer]
GO
--> Map the User to the desired Database Roles
EXEC sp_addrolemember N'db_viewer_role', N'DBViewer'
GO
--> assign user rights on a view (or a table) level via the role
--> just SELECT is granted
DENY ALTERON [dbo].[vw_my_view] TO [db_viewer_role]
DENY CONTROLON [dbo].[vw_my_view] TO [db_viewer_role]
DENY DELETEON [dbo].[vw_my_view] TO [db_viewer_role]
DENY INSERTON [dbo].[vw_my_view] TO [db_viewer_role]
DENY REFERENCESON [dbo].[vw_my_view] TO [db_viewer_role]
GRANT SELECTON [dbo].[vw_my_view] TO [db_viewer_role]
DENY TAKE OWNERSHIPON [dbo].[vw_my_view] TO [db_viewer_role]
DENY UPDATEON [dbo].[vw_my_view] TO [db_viewer_role]
DENY VIEW DEFINITIONON [dbo].[vw_my_view] TO [db_viewer_role]
GO
Problem:
When I login as user 'DBViewer'. I cannot execute a 'SELECT' command, although the SELECT rights are granted via the role.
e.g. SELECT * FROM dbo.vw_my_view;
delivers the error message:
The SELECT permission was denied on the object 'vw_my_view', database 'my_database', schema 'dbo'.
I tried a lot adjustments, e.g. when I change the rights in the role for 'CONTROL' with
GRANT CONTROL ON [dbo].[vw_my_view] TO [db_viewer_role]
then is is possible to execute the SELECT command, but then this user can do, e.g. a DROP of the view, too, and so GRANTING the CONTROL rights is not a solution for me.
Question:
Has anybody an idea why GRANTING the SELECT in the role doesn't work here?
Thanks in Advance
Marc
February 25, 2010 at 1:51 am
In case that there is a conflict between permissions, the DENY permission will be the one that will be imposed by the server. In your case there is a permission conflict because you specifically denied the control permission. Part of the control permission is the select permission, so as you found out your user can not select from the view. You have to remember that by default your user doesn’t have any permission at all on the view, so you don’t need to deny all the permissions that you have denied. You should just grant the user permission to run select statement wit out denying any other operation. This will limit his option to run only select statement on the view
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 25, 2010 at 5:11 am
Hi Adi,
thanks very much. This was the solution. When I just do:
GRANT SELECT ON [dbo].[vw_my_view] TO [db_viewer_role]
without DENYING the other permissions, I get exactly what I need.
A SELECT works and the user cannot DROP the view.
You wrote:
>> You have to remember that by default your user doesn’t have any
>> permission at all on the view, so you don’t need to deny all the permissions that you have denied.
So if I am right it is not exactly the same behaviour to have
A) The default scenario: User doesn't have any permission at all.
or
B) explicitely DENY all the permissions (as I did it)
This seams not really logical or intuitive to me.
It is hard to find detailed informations about that and a detailed description
about the relations between the permissions (which combinations are possible and which are not)
Do you have a good link to a manual or a HowTo regarding this theme.
Thanks for the great reply and
Best Regards from Germany
Marc 🙂
February 25, 2010 at 9:25 am
I don’t have a link, but I can try and explain. First of all you are correct when you say that there is a difference between setting deny permission to a user and not setting permission at all. If user A have no permission to do a specific operation and User B has deny permission on this operation, then both users won’t be able to do this operation. The difference is that if you’ll grant permission to both users on this operation, only user A will be able to do this operation. The reason for that is that User A didn’t have deny permission, so there is no conflict between deny and grant permissions, but User B does has a permission conflict (he has both deny and grant on the same operation). In case that there is a permission conflict, the Deny permission will be the effective one.
When you understand how it works, you can use it. For example, if you have a database user that should be able to select from all tables and views except for 2 tables, you can add this user to db_datareader role and then just deny select permissions from those 2 tables. Another way to do it is to grant select specifically on each table and view in the database except for those 2 tables. Notice that there is also one more difference between those 2 approaches. In the first approach (where you add the user to db_datareader role), the user will automatically have select permissions on every new table or view that will be created on the database. In the second approach each time a new table or view will be created, you’ll have to grant this user permission to select from it.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 26, 2010 at 12:24 am
Hi Adi,
thanks very much for this detailed and very useful information. Now I get it. I think your post will be helpful for many other visitors of this forum, too.
Have a nice day and thanks again.
Marc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply