January 27, 2009 at 2:40 pm
Hi I have a doubt, maybe someone does know how to work this out.
I have found a particular behavior on SQL 7.0 and the next releases such as 2K5 and 2008.
The thing is that if I give permissions to a user to execute an SP, he could read, and write on the database objects, regarding whether he has or not privileges on them. I know this is because of the public role which has permissions on the database objects, but this seems to me like a security issue. I don't think this is intented to be that way.
When we were working on SQL 7.0, we had the same problem with the builds:
Version del servidor XXXX01: Microsoft SQL Server 7.00 - 7.00.623 (Intel X86) Nov 27 1998 22:20:07
Versión del servidor YYYYH01: Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86) Apr 9 2002 14:18:16
The first one is SP1 and the second one is SP4. The first one presented this problem, but the second one asked me for the privileges to the tables or views.
Does anyone know if there is like a specific service pack I would have to install? or a microsoft web page explaining why they did this?
Any feedback is highly appreciated. Thanks!!!
January 27, 2009 at 2:41 pm
I'm not familiar with SQL 7, but I can definitely say that in 2005/8, you can give someone execute permission without also giving them the ability to view/edit code in the database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2009 at 2:47 pm
With SQL Server 7, every database user is a member of the public role. If the public role has permissions against the tables, everyone that's a user of the database has permissions against the tables. The EXECUTE permissions on the stored procedures have no effect. The security issue is the permissions that have been assigned to the public role. This is NOT the default behavior. This had to have been done by someone explicitly running GRANT statements.
K. Brian Kelley
@kbriankelley
January 27, 2009 at 2:57 pm
GSquared (1/27/2009)
I'm not familiar with SQL 7, but I can definitely say that in 2005/8, you can give someone execute permission without also giving them the ability to view/edit code in the database.
yeah that is exactly what I mean, but is this supposed to work this way? Wouldn't that be a security flaw? :hehe:
January 27, 2009 at 3:29 pm
K. Brian Kelley (1/27/2009)
With SQL Server 7, every database user is a member of the public role. If the public role has permissions against the tables, everyone that's a user of the database has permissions against the tables. The EXECUTE permissions on the stored procedures have no effect. The security issue is the permissions that have been assigned to the public role. This is NOT the default behavior. This had to have been done by someone explicitly running GRANT statements.
What you say about SQL Server 7.0 is what I think is correct, but it does not work that way. Once you apply the service packs until service pack 4 then you get the expected behavior.
In sql 2k5, I tried the same and got exactly the same thing. I have a
Microsoft SQL Server 2005 - 9.00.3228.00 (X64) Feb 9 2008 09:33:32 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I use this script to generate my login.
CREATE LOGIN [r1e1n1a1t1o1]
WITH PASSWORD=N'X1X1X1X1',
GO
and for the user
CREATE USER [r1e1n1a1t1o1] FOR LOGIN [r1e1n1a1t1o1] WITH DEFAULT_SCHEMA=[dbo]
That is all I did :hehe: please any suggestions are welcome 🙁
January 27, 2009 at 3:35 pm
What behavior are you expecting and not getting?
K. Brian Kelley
@kbriankelley
January 27, 2009 at 3:46 pm
What I am expecting to get is that a user without any privileges on tables, but only on a exec of a sp, gets an error trying to execute the sp because this user doesn't have privileges on the tables.
What happens is that the user can see and modify the tables.
January 27, 2009 at 4:13 pm
You said the public role has permissions, right? Every database user is a member of the public role. There are no exceptions. Therefore, if the public role has permissions, every user has permissions. The only way to remove permissions is to take them away from the public role unless you want to use explicit DENY to block access. Correcting the permissions on public is the preferred means.
K. Brian Kelley
@kbriankelley
January 28, 2009 at 6:57 am
chileu17 (1/27/2009)
What I am expecting to get is that a user without any privileges on tables, but only on a exec of a sp, gets an error trying to execute the sp because this user doesn't have privileges on the tables.What happens is that the user can see and modify the tables.
If a user is granted execute permission on a proc, but does not have read/write permissions on the table the proc affects, they will still be able to run the proc. That's not a problem, it's necessary for the security to work at all. Otherwise, what would be the point of granting execute rights on procs?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2009 at 7:55 am
The thing is that I haven't given any permissions to the public role and it still can query all my tables, and I don't think that would be ok because if there would be this specific user who can access only some tables, why could he use the other tables if he doesn't have the privileges to do so?
Is there a way to disable this public role? or to revoke the permission it has on the rest of the tables?
And what GSquared says is totally right, the user should only have permissions to the tables that he's using, but why would he have to gain access to tables, through the public role, on which he hasn't got access to? So I guess I would have to remove every user I create from the public role every time I am creating one right?
Thank you very much again for your replies.:D
January 28, 2009 at 1:39 pm
In some way, someone, somehow granted permissions to the public role. At least, that's the only thing I can think of that would do what you're describing. May not have been you, but it was someone. Maybe a prior DBA, or a developer who shouldn't have been messing around with it anyway.
But do check if that role has permissions it shouldn't have.
You can't remove everyone from public. Just double-check and make sure public (and other accounts) only have the rights they are supposed to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2009 at 4:53 pm
GSquared (1/28/2009)
In some way, someone, somehow granted permissions to the public role... At least, that's the only thing I can think of that would do what you're describing. May not have been you, but it was someone. Maybe a prior DBA, or a developer who shouldn't have been messing around with it anyway.But do check if that role has permissions it shouldn't have.
You can't remove everyone from public. Just double-check and make sure public (and other accounts) only have the rights they are supposed to.
The weird thing is that I am using servers just installed without any modifications or anybody working on them. The public role has no permissions on any tables. This is as bizarre for me as it's probably for you. How do I fixe this problem then if I can't remove the users from the public role? Would I have to deny them all the privileges to all the tables first and then asigning them permissions?
January 28, 2009 at 8:27 pm
Check to make sure the user is not a member of the db_datareader or db_datawriter roles. Those give implicit permissions and so you don't see it unless you check the role. Ensure the user is not the database owner. If none of those are true, check to see if the user has the permissions at the schema or database level.
K. Brian Kelley
@kbriankelley
January 28, 2009 at 9:39 pm
Everything you have described is exactly the way SQL Server Security is designed, and is the same in SQL 7, 2000 , 2005, and 2008, regardless of the SP.
If you have execute permission on a dbo stored procedure, the procedure will be able to access all tables, even those that the user does not have access to, except for dynamic SQL. If the procedure executes dynamic SQL, the user must have access to the tables. In 2005 and later, a stored procedure can be modified with the EXECUTE AS clause to allow access to tables in dynamic SQL via the account of another user.
If you don’t want users to be able to access those tables via stored procedures, do not grant them access to the stored procedure.
All database users, except for guest, are members of the public role and cannot be removed. The guest account can be added to public role, but is not there by default.
It is better to setup security on database objects via roles, and then add or remove users from those roles as needed to grant users access to objects. Membership in the fixed db_owner role gives a user access to all database objects, so you should avoid adding users to that role.
January 29, 2009 at 3:56 pm
Michael Valentine Jones (1/28/2009)
Everything you have described is exactly the way SQL Server Security is designed, and is the same in SQL 7, 2000 , 2005, and 2008, regardless of the SP.If you have execute permission on a dbo stored procedure, the procedure will be able to access all tables, even those that the user does not have access to, except for dynamic SQL. If the procedure executes dynamic SQL, the user must have access to the tables. In 2005 and later, a stored procedure can be modified with the EXECUTE AS clause to allow access to tables in dynamic SQL via the account of another user.
If you don’t want users to be able to access those tables via stored procedures, do not grant them access to the stored procedure.
All database users, except for guest, are members of the public role and cannot be removed. The guest account can be added to public role, but is not there by default.
It is better to setup security on database objects via roles, and then add or remove users from those roles as needed to grant users access to objects. Membership in the fixed db_owner role gives a user access to all database objects, so you should avoid adding users to that role.
hhhhmmmmmmm so you are saying that if I grant the execute permission on a [dbo].[sp_XYZ] to a user, this user will be able
to execute any DML sentence on it without a problem? Now, the execute clause only works for dynamic SQL, right? Because I tried executing my [dbo].[sp_XYZ] with the 'execute as 'UsrBD_XYZ'' statement but I could still access the tables eventhough this user hasn't got privileges on them. How could I avoid this user access to tables which I don't want him to?
You said I can't remove public role, that I can't disable this? You said that if I use roles, this could be solved, but how could this if every user is always added to the public role?If I create a new user and assign them to a new roles, they will still be part of the public role.
Please 🙁 help me out with this. Thanks in advance!!!
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply