Need a GRANT kludge - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need a GRANT kludge

Author

Topic

Moussie
Starting Member

20 Posts

Posted - 2002-03-25 : 20:46:44

Hello!

First of all, yes, the following question could be solved by better table design, but that is, as you know, not always an
option...:(

So, in order not to aspire for Celko's wrath
here is a _simplified_ DDL

create table T (
someint int NOT NULL PRIMARY KEY
)

So to the problem:
The users in Role 1 needs to have
delete permission to all rows in T where someint > 0.
The users in Role 2 should only be able to delete
the rows in T where someint == -1.

Is that possible without hassling with application logic
in the client ?
What I mean is, can I use GRANT to do that ?

----
Don't use your head - and you'll walk a lot

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-25 : 20:57:35

Not really. It may not seem a better way to do it, but having separate views for each role that allow them to DELETE would be about as close as you can get. Otherwise there will always be some kind of loophole that a user could find that would let them DELETE rows they're not supposed to.

You *could* perhaps write a DELETE trigger that tests for SUSER_SNAME() and rolls back a DELETE if the login name isn't the correct one, but that doesn't entirely provide the row-level security you're looking for. If 100 rows were OK to delete, but 1 wasn't, all of them will be rolled back.

You really can't do this properly WITHOUT redesigning the table or using role-specific views. Permissions should never rely on a column's value; you either have permissions to the entire table, or you don't. Data that requires that kind of security should not be kept in one table.

Moussie
Starting Member

20 Posts

Posted - 2002-03-26 : 00:45:43

>You *could* perhaps write a DELETE trigger
>that tests for SUSER_SNAME() and rolls
>back a DELETE if the login name isn't the
>correct one, but that doesn't entirely provide
>the row-level security you're looking for.
>If 100 rows were OK to delete, but 1 wasn't,
>all of them will be rolled back.

I might go for that one. There will (because
of the application design) never be more than
one row at a time to delete for that specific
role.

So, is there any faster way (except for hardcoding
the logins into the trigger, which is not an option)
to see if the user has enough rights than
cursoring through the output of sp_helprolemember
(on the given role) and see if the user exists
there ?

Thanks for your time.

------
Don't use your head - and you'll walk alot

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-26 : 02:18:48

quote:

So, is there any faster way (except for hardcoding
the logins into the trigger, which is not an option)
to see if the user has enough rights than
cursoring through the output of sp_helprolemember
(on the given role) and see if the user exists
there ?

Faster than a cursor?

- not on this site! - Just ask nr!

...
OK that was a joke. I don't think you'll need a cursor...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-26 : 07:40:53

quote:
There will (because of the application design) never be more than one row at a time to delete for that specific role.

What exactly does this application do, and this particular table? You say you can't change the design, but you can add a trigger? Do these users have actual SQL Server permissions, or are the permissions being simulated? I can't figure out the bigger picture from these in order to say whether this will work or not.

Without more info (maybe I'm missing something), I can't help but think that this is never gonna work right, and even if it does it is far less than an optimal way to do it. Cursors or no cursors. It makes more sense to me that a stored procedure should be in place for deleting rows, and it would identify and enforce the proper rows to be deleted. But either you can't create one, or you can't guarantee everyone uses it, so you need something in place to catch any stragglers. If that's true, then a trigger will not solve the problem.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-03-26 : 09:07:09

quote:

So, is there any faster way (except for hardcoding
the logins into the trigger, which is not an option)
to see if the user has enough rights than
cursoring through the output of sp_helprolemember
(on the given role) and see if the user exists
there ?

IS_MEMBER ( )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources