select permission to public

Question

select permission to public

HBK_4700

SSCrazy Eights

Points: 8932
More actions
May 5, 2011 at 12:09 am

#237660

we got two vulnerabilities as part of our security scan.
[Object:dm_exec_sessions] [Granted To:public] [Database:master]
[Permission:SELECT]
[Object:sysprotects] [Granted To:public] [Database:master]
[Permission:SELECT]
Just want to know if I revoke the select perimission from public, is there any issue regular SQL server activity? thank you

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

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

sachnam SSCrazy Points: 2317 More actions · Answer 1

I don't think it is a vulnerability because a user will have only select access to the the objects in subject.

Moreover public is default server role every login is by default added into this role .this role give a user the ability to view the system related information only or system view only.

It does not allow anyone to access user objects or even view the user objects.

For e.g he will be able to run Sp_tables command but will not be able to view any user object listed it. quite safe.

So, personally speaking it is not a problem that you should worry about.

Regards,

Sachin

matthew.lowe 19651 Ten Centuries Points: 1192 More actions · Answer 2

matthew.lowe 19651

Ten Centuries

Points: 1192

May 5, 2011 at 3:06 am

#1320265

So is :-

select * from syslogins

Not an issue then?

sachnam SSCrazy Points: 2317 More actions · Answer 3

matthew.lowe 19651 (5/5/2011)
So is :-
select * from syslogins
Not an issue then?

As i mentioned earlier even if you have select permisions on the system views which by default a public role provides. you will not be able to view any user objects.

Similarly, if a user with only public role runs this above query will get only two records, one is 'SA' for sure because it is sql server' default login accout and the one using which you are connected currently.

So, if you think this can cause a security issue which i suspect because we anyway know 'SA' is always present unless it is renamed it for some security reasons.

Regards,

Sachin

matthew.lowe 19651 Ten Centuries Points: 1192 More actions · Answer 4

Hi,

Surely if you can extract the SA Hash from the syslogins table and then crack it , this is a security hole.

not being pedantic , just an overthought

Matt

sachnam SSCrazy Points: 2317 More actions · Answer 5

matthew.lowe 19651 (5/5/2011)
Hi,
Surely if you can extract the SA Hash from the syslogins table and then crack it , this is a security hole.
not being pedantic , just an overthought
Matt

Just for your information Password_hash in never in Syslogins these days. Syslogins only has SID which is always same for 'SA' .

But if you realy want to talk about breaking a password hash for 'SA' then for your information the query i mentioned below will always return NULL for user with public Role so you are stuck .

SELECT password_hash FROM sys.sql_logins where name='sa'

Better you prove your points through example. Sothat everybody could learn something may be i am missing something which you know better.

REgards,

Sachin

matthew.lowe 19651 Ten Centuries Points: 1192 More actions · Answer 6

I set up a user test , assigned it to Public role in SQL SMS and mapped it to the Master database.

Note , this is done on SQL 2000

Then i simply assigned a simple Select Permission to the sysxlogins table for the User test (only in the public role)

I then ran the Select * from sysxlogins.

this is what i got:-

NULL0x01182000-08-06 01:27:52.6872011-03-17 13:18:12.690sa0x010MY HASHC5F4EC81CCBE44DA851D2CBDBDF5E4BBCCD717EF95368120AC958265CC53C0CFA9204F285A0561NULL000

I replaced MY HASH with some of my hash 😉

So my point is , that if you have SELECT permission on the master database to the public role , and set up a new user , which defaults to the Public role , they can pull this information.

im struggling to upload the Images of my progress however

matt

sachnam SSCrazy Points: 2317 More actions · Answer 7

Sorry . but this is not a SQL SERVER 2000 Forums and with no wrong intentions. I myself know the security loop holes in sql server 2000 .

I provided all the information keeping in mind we are talking about sql server 2005 or 2008.

There is no sysxlogins table in sql server 2005 onwards and i did not refer any system table they all are system views .

I don't know why you brought sql server 2000 into picture the OP submitted the problem in SQL SERVER 2008 Forum so answer is also expected according to that.

Steve Pl. correct me if i am wrong in making the above statement..

Regards,

Sachin Sharma

matthew.lowe 19651 Ten Centuries Points: 1192 More actions · Answer 8

Ok good point , and it returns NULL in SQL 2008

Is this because of the security enhancments in SQL 2008 and it doesnt include the GRANT OBJECT privs?

IE:-

USE AdventureWorks2008R2;

GRANT SELECT ON sys.sql_logins TO Sylvester1;

GO

Sorry , just would like to understand this between SQL 2000 , amd SQl 2008

Thanks

matt

hbk_sql Old Hand Points: 365 More actions · Answer 9

Thanks Sachnam!

But our security team has rejected the request. They are saying revoke the select permission

[Object:dm_exec_sessions] [Granted To:public] [Database:master]

[Permission:SELECT]

[Object:sysprotects] [Granted To:public] [Database:master]

[Permission:SELECT]

FIx recommendation is

Grant audit file and database audit object access to authorized DBA's and auditors. revoke audit file and database audit object access from unauthorized database accounts.

I just want if we revoke these select perimission from public, will application breaks? Can we apply the fix without affecting the application?

Thank you very much.