Dynamic TSQL in trigger using values from inserted
-
How to build dynamic TSQL statement in an instead of trigger for inserting values from inserted to base table?
A view with instead of trigger is great tool for security. But there are some problems. For example if the instead of trigger defined on a view inserts all columns to base table, the base table defaults doesn't take effect. I want to build insert statement for just an updated columns.
A little difficult work around:
--drop table [dbo].[Sample_table]
--GO
CREATE TABLE [dbo].[Sample_table] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[char_column] [char] (10) NOT NULL ,
[date_column] [datetime] NULL ,
[money_column] [money] NULL ,
[timestamp_column] [timestamp] NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Sample_table ADD CONSTRAINT
DF_Sample_table_date_column DEFAULT getdate() FOR date_column
GO
ALTER TABLE dbo.Sample_table ADD CONSTRAINT
DF_Sample_table_money_column DEFAULT 1000 FOR money_column
GO
-- sample rows
INSERT INTO sample_table (char_column) VALUES ('test1')
INSERT INTO sample_table (char_column,date_column) VALUES ('test2',getdate()-70)
INSERT INTO sample_table (char_column,money_column) VALUES ('test3',3000)
GO
-- DROP VIEW sample_view
-- GO
CREATE VIEW sample_view AS
SELECT ID, char_column, date_column,
money_column=case is_member('leader')
WHEN 1 THEN money_column
ELSE null
END, timestamp_column
FROM dbo.Sample_table
WHERE NOT is_member('h_restricted')=1 OR datediff(day, date_column, getdate())<60
-- really secure, isn't it ? 🙂
go
CREATE TRIGGER tr_sample_view_i ON sample_view INSTEAD OF INSERT
AS
INSERT INTO sample_table (char_column, date_column, money_column)
SELECT char_column, date_column, money_column FROM inserted
go
-- sample row, must put some dummy value to ID, because it is not NULLable in view
INSERT INTO sample_view (ID, char_column) VALUES (0,'test5')
-- puts NULL value to the date_column,
-- because the trigger inserts all columns, default getdate() doesn't take effect
go
DROP TRIGGER tr_sample_view_i
go
CREATE TRIGGER tr_sample_view_i ON sample_view INSTEAD OF INSERT
AS
BEGIN
DECLARE @clist nvarchar(200), @tsqls nvarchar(200)
SET @clist=''
--creating updated column list
if update(char_column)
SET @clist='char_column,'
IF update(date_column)
SET @clist=@clist+'date_column,'
IF update(money_column)
SET @clist=@clist+'money_column,'
IF len(@clist)=0
EXEC sp_sqlexec N'insert into dbo.sample_table default values'
ELSE
BEGIN
-- cutting trailing comma
SET @clist=left(@clist,len(@clist)-1)
SET @tsqls='insert into dbo.sample_table ('+@clist+') select '+@clist+' from inserted'
EXEC sp_sqlexec @tsqls
-- it wont work, because sqlexec fires another batch, and inserted isn't visible out there
-- but how to make it useable ?
END
END
GO
-
why don't you take the values from the inserted table into variables and build the insert statement with these variables instead of selecting from inserted
-
Because it is not too smart solution. Declare all the variables, declare cursor on inserted table, fetch records from inserted table, set values,...
I have >10 views in my database.
-
I havent worked through all of it, but usually the way you avoid the EXEC() scoping isssue is through a ##temp table.
Its an interesting problem, but I'd really want a better way than having all that code in a trigger. How can you avoid a cursor? If you have a transaction of more than one record...you'll have to do something different for each row...potentially?
Another question. Why are you using INSTEAD OF? If you have 10 views that Im guessing have joins that require its use, maybe you're taking the wrong approach? Flattening relationships out to reduce programming...should be a fairly special case in my view!
Andy
-
Thank You GRN, Andy!
I will try temporary table.
The original question: How to create a solution with the following security requirements: Restrict access to historical data (datdiff>60 days) if user is member of group1, restrict acces to other data - for example several accounting info - for the user member of group2, etc...
The well known way: code this logic in a client application or in middle tier.
I've chosen another way: the database should be secure itself. Every user gets the data - threw views - intended for him, even if he uses the client application or other query tools to reach the database.
It works, and I like it
, even if I loose default capabilities then. I rather code defaults on client than security. -
Interesting. Worth doing even! So let's see what other methods we can think of to arrive at the same place.
One would be create a view on each table that belongs to that user - as long as they connect using that login they would access data via that view, which could be customized to restrict data as needed. You could go as granular as a unique view per user if you had to.
Another way would be to use an app role, not allow any access not through std logins. You COULD combine this with middle tier security.
Anyone have other ideas?
Andy
, even if I loose default capabilities then. I rather code defaults on client than security.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply