Database security - user access

Question

Database security - user access

alark

Default port

Points: 1402
More actions
March 26, 2004 at 12:29 pm

#84060

I created a new database user and granted him "db_datawriter" role to the database. By some reason he could not do any work with database objects until I went to the Database Users-Permissions and explicitly granted him Select, Insert, Update, Delete permissions for every object.
Am I missing something here or this is how it supposed to work - every time I have manually click on every object?
Thank you in advance!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

Check if the user in the db is member of another role that deny access to any object and check the permissions in the public role, wich of course that user also belongs.

And Also Check in the Logins to wich that user maps, if it is member of another role member.

For permissions in public role, you should check excellent article of Brian Kelley in http://www.giac.org./practical/GSEC/KBrian_Kelley_GSEC.pdf

alark Default port Points: 1402 More actions · Answer 2

Well, I just created this user. By default it was granted public access, and I gave him a db_datawriter role. No other restrictions. It still doesn't have write access untill I individually mark all the permisions object by object.

Any other thoughts?

Thank you.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Windows user or SQL Server user? Verify using say Profiler the user is indeed the one you've assigned permissions to. Also be sure to log off all connections with said user and then back on. I know there was a knowledge base article on the last one.

K. Brian Kelley
@kbriankelley

alark Default port Points: 1402 More actions · Answer 4

Brian,

These are SQL Server users. As I said, after giving them db_datawriter role some of them assume it right away, but for some other I have to go into "Permissions" and manually click on each object to grant access.

By the way, if you assign db_datawriter role to a user and then go to the User Properties - Permissions screen, should the boxes for some objects be already marked (since the given role has granted those permissions)? For me, whatever role I give those boxes always stay unchecked. I have SQL-2000 on Win2000.

Thank you.

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 5

The boxes should not be marked. These show explicit individual permissions. If they are marked, then I suspect that you or someone has actually set something which may be part of your issue.

If some users are not getting the permissions, I'd troubleshoot that without granting them explicit permissions. If you add a new SQL Authenticated user and only grant them dbwriter role, can they not write to a table? Are you sure public doesn't have some restrictions set?

mybid2003 SSCertifiable Points: 6620 More actions · Answer 6

I did have similar issue recently, when I created a user (SQL user) and did grant db_datawriter. This user could not SELECT any data. So I gave db_datareader as well... and it worked fine....

Strange though... Because on rest of the boxes, I could select using db_datawriter role alone.

Not sure what is the problem.... :-((

.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

db_datawriter gives INSERT, UPDATE, DELETE implicitly on all tables and views.

db_datareader gives SELECT implicitly on all tables and views.

You may want to go back and check those other servers because giving db_datawriter alone won't allow someone to SELECT against the tables and views.

K. Brian Kelley
@kbriankelley

alark Default port Points: 1402 More actions · Answer 8

Brian you are great!

This was the problem. I gave a user db_datawriter role assuming that it should have SELECT permission as well.

Still, I can not imagine a scenario when user is given INSERT,UPDATE,DELETE rights but not SELECT. Why didn't they include SELECT into the db_datawriter role?

Thanks everybody for help!

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 9

There are cases where you'd only want someone inserting data, not being able to read. For instance, if you have a device that's taking measurements, you'd want it to be able to insert data and that's it. Should the account somehow get compromised, no one could use it to read previously stored data, though admittedly they could post bogus results.

Now for the soapbox! My personal preference on this is to NOT use db_datareader and db_datawriter... I'll explain.

These roles have implicit permissions to all tables and views with the database. Under SQL Server 2000 that also means the system tables. Now with db_datawriter things aren't so bad. Unless the setting is enabled to allow direct updates to a system table, a user isn't going to be able to affect things there. But with SELECT, oh ho! I can see every object, every owner, every security permission, and if the stored procedures aren't encrypted every stored procedure text. Not good.

Instead, build a user-defined role and give it the requisite permissions on the proper tables. In development with tables and views coming and going this may not be feasible, but for most apps in production it is. It's a bit more work (not much, mind you, because it's fairly easy to write a script to do this for only user-defined tables and views), but you get the idea.

Then again, as long as the sp_help stored procedures are around, users with access to the database can still get an enormous amount of info about the SQL Server. That's another story.

K. Brian Kelley
@kbriankelley