Privileges Problem

Question

Post reply

Privileges Problem

chileu17

Hall of Fame

Points: 3093
More actions
October 15, 2008 at 12:09 pm

#199779

Hi everyone, I have a little problem with the public role from my server.
I have a db and a user that has only the public role assign to it. The user has privileges to execute a sp, the sp makes a select to the table which the user is not supposed to have access to. But when the user executes the sp, he gets access to the table, do you guys know which is the permit I have to remove from this role so not even the sp has access to the table?
Thanks in advance.

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

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

RP_DBA SSCertifiable Points: 5116 More actions · Answer 1

You could deny rights on this table to the user/role.

DENY SELECT ON [tblname] TO Public

_____________________________________________________________________
- Nate

@nate_hughes

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 2

Why does the user have access to the SP then? That doesn't make sense.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Since you cross-posted this to the 2005 Forums also, perhaps you should tell us what version of SQL Server you are running?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Giving a user/role access to a stored procedure is a means to allow users/applications to access database tables without giving them direct access to the tables. If you don't want users with only public permissions to access the data in a table using a procedure, don't grant public access to that procedure.

😎

chileu17 Hall of Fame Points: 3093 More actions · Answer 5

I am running this on SQL 7.0 on my dev enviroment as on my prod enviroment. We are doing a migration to SQL Server 2005. The thing is that this sp is actually on another database referencing this table which my user doesn't have access to it.

So it's something like this:

Use BD1

GO;

Create table tab1(cod int,dsc varchar(20));

GO;

Use BD2

GO;

Create Procedure selTab1 as

select * from BD1.dbo.tab1

GO;

The user doesn't have access to tab1, but he has access to other tables of 'BD1'. We have deny the access to this table for the public role as suggested before but still, the user can have access to the table though the SP. Help :ermm:

chileu17 Hall of Fame Points: 3093 More actions · Answer 6

Lynn Pettis (10/15/2008)
Giving a user/role access to a stored procedure is a means to allow users/applications to access database tables without giving them direct access to the tables. If you don't want users with only public permissions to access the data in a table using a procedure, don't grant public access to that procedure.

so it means I can't restrict my public role to have access to just certain tables? and I was told that this problem, I mean the public role having access to all tables, was a bug in SQL Server 7.0, is that so?

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 7

You should never assign permissions to public. Always create another role and assign permissions there.

If you give the user the rights through to the proc, they can access the table. That's the idea. You give them the ability to get data without accessing the table. Remove rights for the stored procedure.

If this is in 7, can't remember how the cross database chaining works. In 2000/2005, you can set this at an instance level to allow permissions to propagate across databases.

chileu17 Hall of Fame Points: 3093 More actions · Answer 8

Steve Jones - Editor (10/15/2008)
You should never assign permissions to public. Always create another role and assign permissions there.
If you give the user the rights through to the proc, they can access the table. That's the idea. You give them the ability to get data without accessing the table. Remove rights for the stored procedure.
If this is in 7, can't remember how the cross database chaining works. In 2000/2005, you can set this at an instance level to allow permissions to propagate across databases.

Well that is the weird thing, my certification env has sql server 7.0 service pack 4 and my dev doesn't have any service packs. On my dev enviroment this worked, I mean having access to the table through the stored procedure, but on me certificaction enviroment I got a level access error, any ideas about the privileges I need to take away from the user would be highly appreciated.Thanks!