Re: Row-Level Pseudo -Security
From: Steve (steve_at_netlineit.co.uk)
Date: 09/21/04
- Next message: Jan Schoenrock: "Temp Tables, Transaction and Loops"
- Previous message: Tony C: "RE: Trigger Will Not Work Due To Constraint"
- In reply to: Andrew J. Kelly: "Re: Row-Level Pseudo -Security"
- Next in thread: Andrew J. Kelly: "Re: Row-Level Pseudo -Security"
- Reply: Andrew J. Kelly: "Re: Row-Level Pseudo -Security"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 21 Sep 2004 07:13:45 +0000 (UTC)
Hi Andy
That's not quite what I was hoping for. It's not the table I need to
control, but the records it contains. This isn't something that SQL2K
supports.
Each record in the table is 'owned' by a user identified by their login
name. For the records they 'own', they should have complete access, but must
have read-only access to the remaining records. A non-owner is permitted to
view any other record
So initially for any user they would have read-only access to all the
records. This was easily achieved by creating roles etc. A column within the
record records the login name of the original creator, and that record is
deemed updateable only by that user.
In some kind of pseudo-code I want to do this
CREATE TRIGGER [TRIGGER NAME] ON [dbo].[MY_TABLE]
FOR UPDATE
AS
BEGIN
check user on MY_TABLE.OWNER_ID
if same
GRANT UPDATE ON MY_TABLE
else
DENY UPDATE ON MY_TABLE
END
Is this getting near, or should I look for another tree to bark up?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:%23SDo9y1nEHA.556@tk2msftngp13.phx.gbl...
> What Login and Owner ID are you referring to? I don't see where you are
> going with this. Either you let someone in the database or you don't. If
> you let them in you can certainly control their actions with roles and
> rights. How can you supply a database where you don't control how people
> access it and expect to maintain security? It's like this. If you want
> Joe
> to be able to update a certain table you give Joe the proper rights and he
> can update the table. If you don't want him to update the table then don't
> give Joe rights to do so. I don't understand why you want to do this ina
> trigger instead of using the security already built in?
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Steve" <google@netlineit.co.uk> wrote in message
> news:65295adf.0409201208.39e96c9f@posting.google.com...
>> I agree that would be better, but what if the application that uses
>> the database is not one of your own, and therefore cannot be changed
>> to access via stored procedures, views or whatever.
>> Basically, I have tables and need to impose some kind of security that
>> isn't offered by the client application nor is likely to be.
>> That's why I thought a trigger could verify the userid and allow
>> update access to that particular record, but select only to all others
>>
>> Is a trigger no good for that? Can you deny update for the table that
>> a trigger is on? Can you deny update privilege for any record? I'm not
>> a DBA, just a SQL dabbler hence my ignorance! Any advice on how to
>> write this trigger would be much appreciated (SQL2K)
>>
>> tia
>
>
- Next message: Jan Schoenrock: "Temp Tables, Transaction and Loops"
- Previous message: Tony C: "RE: Trigger Will Not Work Due To Constraint"
- In reply to: Andrew J. Kelly: "Re: Row-Level Pseudo -Security"
- Next in thread: Andrew J. Kelly: "Re: Row-Level Pseudo -Security"
- Reply: Andrew J. Kelly: "Re: Row-Level Pseudo -Security"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
