December 6, 2016 at 2:47 am
Hi all
I have a table "test" in my "testdb" database. There is a user "user1"
The user has access to all tables in the database.
For this particular table "test", I executed "DENY insert on test to user1"
But still i am able to insert!!
The user has membership in db_datareader, db_datawriter and db_owner roles
Thanks
KRS
December 6, 2016 at 2:56 am
krishnaroopa (12/6/2016)
The user has membership in db_datareader, db_datawriter and db_owner roles
There's your problem.
db_owner's can do what every they want, whenever they want, in that database.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 6, 2016 at 2:57 am
Check if he is an administrator of the whole server. Anyone that is authenticated as an administrator, can not be limited at all.
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/
December 6, 2016 at 3:01 am
Further Question, why ARE they a member of the db_datareader and db_datawriter roles when they are a member of db_owner role? These roles serve no purpose, as db_owner grants all of this and more.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 6, 2016 at 3:25 am
Thom A (12/6/2016)
Further Question, why ARE they a member of the db_datareader and db_datawriter roles when they are a member of db_owner role? These roles serve no purpose, as db_owner grants all of this and more.
They are members of all those roles because someone added them as members. Consider a situation that I add you as a member to db_datareader role and after that to db_owner role. The fact that I added you to db_owner role should not change your status as a member of db_datareader role. If I'll realize that I made a mistake and wanted to add someone else to the db_owner role and take you out from this role, I wouldn't want you not to be part of the db_datareader role.
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/
December 6, 2016 at 3:31 am
Adi Cohn-120898 (12/6/2016)
Thom A (12/6/2016)
Further Question, why ARE they a member of the db_datareader and db_datawriter roles when they are a member of db_owner role? These roles serve no purpose, as db_owner grants all of this and more.They are members of all those roles because someone added them as members. Consider a situation that I add you as a member to db_datareader role and after that to db_owner role. The fact that I added you to db_owner role should not change your status as a member of db_datareader role. If I'll realize that I made a mistake and wanted to add someone else to the db_owner role and take you out from this role, I wouldn't want you not to be part of the db_datareader role.
Adi
perhaps it's just me who's prefers to keep things clean then 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 6, 2016 at 4:46 am
Thom A (12/6/2016)
krishnaroopa (12/6/2016)
The user has membership in db_datareader, db_datawriter and db_owner rolesThere's your problem.
db_owner's can do what every they want, whenever they want, in that database.
Slight but IMHO important correction.
db_owner can do anything to that database. Such as drop it, take a backup of it and steal your data, grant access to other users, change recovery model drop or truncate your orders table. One thing that far too many fail to understand is, do_owner is not there for end user database access. db_owner is there as a database administrator access level, and for that only. Are your users supposed to be doing any of this? I very much doubt it. So this means you have explicitly chosen to add a security hole to your system.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
December 6, 2016 at 10:06 am
Thom A (12/6/2016)
Adi Cohn-120898 (12/6/2016)
Thom A (12/6/2016)
Further Question, why ARE they a member of the db_datareader and db_datawriter roles when they are a member of db_owner role? These roles serve no purpose, as db_owner grants all of this and more.They are members of all those roles because someone added them as members. Consider a situation that I add you as a member to db_datareader role and after that to db_owner role. The fact that I added you to db_owner role should not change your status as a member of db_datareader role. If I'll realize that I made a mistake and wanted to add someone else to the db_owner role and take you out from this role, I wouldn't want you not to be part of the db_datareader role.
Adi
perhaps it's just me who's prefers to keep things clean then 🙂
No, it isn't just you. 😉
Also, see what Andrew says below. They can do other things as well. I would take what he said seriously.
December 6, 2016 at 10:37 am
krishnaroopa (12/6/2016)
Hi allI have a table "test" in my "testdb" database. There is a user "user1"
The user has access to all tables in the database.
For this particular table "test", I executed "DENY insert on test to user1"
But still i am able to insert!!
The user has membership in db_datareader, db_datawriter and db_owner roles
Thanks
KRS
You said: "But still i am able to insert!!" but I assume it's the user who's inserting. DB_OWNER can read, write, and alter without limits. They literally OWN your database and can do whatever they want with it, so don't make them angry.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 7, 2016 at 1:42 am
I think that you can deny an insert from a dbo. The dbo can change the permissions so he'll be able to use the insert statement but as long as someone denied him the permissions and he didn't modify the permission, he won't be able to run an insert statement. The code bellow illustrates it.
CREATE DATABASE Test
go
USE Test
go
CREATE TABLE T (I INT)
go
DENY INSERT ON T TO PUBLIC
go
--run an insert statement as admin. This should work
INSERT INTO T (I) VALUES (1)
GO
--See that the record was inserted
SELECT * FROM T
GO
--Creating a new login and user and adding him to the db_owner role
CREATE LOGIN DBOwnerTest WITH PASSWORD = '1QAZ@wsx'
go
CREATE USER DBOwnerTest FOR LOGIN DBOwnerTest
go
EXEC sp_addrolemember db_owner, DBOwnerTest
go
--Executing as the user DBOwnerTest
EXECUTE AS USER = 'DBOwnerTest'
--make sure that I'm running as DBOnwerTest and that I'm part of the db_owner role
SELECT USER_NAME(), IS_ROLEMEMBER('db_owner')
--Trying to insert a record into the table. I'm getting an error
INSERT INTO T (I) VALUES (2)
go
--Modifying the permissions so the dbo will be able to insert data
REVOKE INSERT ON T TO PUBLIC
go
--Running the insert again. This should work
INSERT INTO T (I) VALUES (2)
go
--running the script again as admin and clean up the mess:-)
REVERT
USE master
go
DROP DATABASE Test
go
DROP LOGIN DBOwnerTest
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/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply