May 18, 2006 at 3:29 pm
Hi, I was wondering if you guys can clear something up for me.
I have two sql standard roles (rwxrole and rxrole). The rwxrole is part of the datawriter fixed role. rxrole is part of the datareader. I know that datareader can only read data from tables and views while the write can perform inserts, updates, and deletes.
created a domain user called sql.test and added it to the datareader, attempted to perform an insert, failed with an error. moved sql.test to datawriter, attempt the insert, completed sucessfully. exactly what I was looking for.
created 3 sprocs: usp_insert, delete, update.
moved sql.test from datawriter to rxrole (standard role)
granted exec to rxrole (part of the datareader fixed role)
ran the above sprocs and surpisingly, instead of getting errors stating that I can't perform an insert, delete, or updates, the sprocs executed without any error.
So I am confused. SQL permissions accumulate. rxrole is part of datareader which can only read from tables and views, shouldn't be able to insert, delete, or update. yet when I grant exec to the sprocs for rxrole, it ignored the limitation... can someone explain this to me?
Thanks
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
May 19, 2006 at 7:18 am
IIRC, datareader doesn't deny write access to the tables. It simply doesn't grant it. Which is a very different thing, especially in terms of accumulation. Once you granted the execute permission on the stored procs, you gave them the ability to modify the data. But, ONLY through those stored procs. I'd bet that if you tried to insert data from that test user any other way, you would be denied.
That's one of the reasons that the standard practice is to grant standard users NO permissions on the base tables, and only grant them permissions on views and stored procs. You can more precisely limit what they can see, and what they can do.
May 23, 2006 at 11:13 am
Marshall,
Thank you for your response, it all makes much better sense now.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply