public fixed DB role - a pain?

Question

public fixed DB role - a pain?

tulcanla

SSCommitted

Points: 1541
More actions
June 26, 2003 at 12:00 pm

#59684

I am in the process of checking and anulating the power of public role.That is because ALL logins by default get very important and dangereous rights through public role.
Just try and see.
Now, my problem is that even if I make public role with no permissions in my user database in master and in msdb I do not have the guts to do it...I am afraid there are specific permissions that have to stay there.
Could someone tell me what are the minimal permissions for public in master and msdb?
Thank you all
LMT

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

A lot depends on your application. I have an application (third-party screwup) that runs using PUBLIC! It give PUBLIC access to just about everything. Can I change it? No way...the product will then be unsupported.

-SQLBill

Shas3 Hall of Fame Points: 3671 More actions · Answer 2

By default public role user can create/ Delete/ Execute the DTS packages. You need to explicitly remove the permissions to block them. Revoke the permissions on sp_add_dtspackage, sp_enum_dts_packages and sp_get_dtspackage also remove the guest login.

forgot to mention these SP's are in msdb 😉

Edited by - Shas3 on 06/26/2003 2:46:42 PM

Shas3

tulcanla SSCommitted Points: 1541 More actions · Answer 3

Thank you.

Is there an article regarding the public group ?

lmt

sswords SSCrazy Eights Points: 8207 More actions · Answer 4

We're in a similar situation to SQLBill's with one of our applications. Virtually every update that the vendor sends has a script at the end that grants rights for all objects to the Public role. It's impossible to lock this down without breaking the application.

Another web integrated app that we installed was found by one of our developers to be vulnerable to SQL injection attacks by which a hacker could have easily gained access to admin rights from the login page. Downright SCARY. When we contacted the vendor with our findings, they were not aware of the problem but patched it quickly (which is to their credit - but it seemed odd to me that they had no previous knowledge of the problem).

Maybe this is a subject for another thread, but IMHO vendors and developers need to be held to a much higher standard when it comes to security. Good, solid security is much easier to implement in the testing and development phase rather than overworked DBA's running behind them and attempting to secure things once an application is already in production. I'm not a programmer or developer, just a lowly Sys Admin and it's virtually impossible for me to determine how to implement good SQL Server security without breaking their applications. Oh well...sorry for the rant but thanks for a chance to vent .

My hovercraft is full of eels.

Shas3 Hall of Fame Points: 3671 More actions · Answer 5

Here is a good Article form Brian

http://www.sqlservercentral.com/columnists/bknight/dtslockdown.asp

Shas3

tulcanla SSCommitted Points: 1541 More actions · Answer 6

I can be in control over this.

So I plan to dissipate ALL public power especially after I read "lockdown script", very inspiring from http://www.sqlsecurity.com

read the script (that is free to download)it is interesting and lets you block the most important sp rights for public.

Regards

Colin Leversuch-Roberts SSC Veteran Points: 285 More actions · Answer 7

I usually create a users role and script out all the permissions from public to users .. then I move into master and msdb and remove permissions. Where users need permissions in master etc. I create a role in there too .. once it's all done I auto script the permissions and document them for future use. You need to check the public permissions to the system objects in user databases too .. I usually stop users viewing system tables as a matter of course.