Use @variable in a create view statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Use @variable in a create view statement?

Author

Topic

krsk
Starting Member

21 Posts

Posted - 2003-02-13 : 06:46:31

Hi!

This is what I would like to do:

DECLARE @idag smalldatetime,
@user varchar(10)
SET @idag = (CONVERT(varchar, getdate(), 101))
SET @user = SUSER_SNAME()

create view dbo.BALI_AFSAETNING_MONTH
as
SELECT *
FROM dbo.BALI_AFSAETNING A
WHERE A.BEREGNET_KUNDETYPE IN
(
SELECT authValue AS BEREGNET_KUNDETYPE
FROM dbo.getUserRowAccessRights(@user,'BALI',@idag)
)

Perhaps it is backwards but what i'am trying to do is to create a view that enables me to controlle user access on row level (the function does a table lookup based on the userid)

Any suggestions?

/Kristian :-)

Edited by - krsk on 02/13/2003 07:01:26

krsk
Starting Member

21 Posts

Posted - 2003-02-13 : 06:49:27

Sorry: This is the error I get:

"'CREATE VIEW' must be the first statement in a query batch."

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-13 : 12:53:00

The first problem is exactly what the error states. If you're running this from QA either the create view needs to be the first statement in the batch or you need to put a GO prior to the CREATE VIEW statement. Outside of that, I see you're attempting to execute a function inside the view utlizing parameters. I'm not sure that is the approach I would take seeing as you can't pass parameters to views. Rather, create the view and wrap a stored procedure to implement the subquery.

CREATE VIEW dbo.vw_BALI_AFSAETNING_MONTH
AS
SELECT A.*
FROM dbo.BALI_AFSAETNING AS A
GO

CREATE PROCEDURE dbo.usp_sel_BALI_AFSAETNING
AS

DECLARE @idag SMALLDATETIME
DECLARE @user VARCHAR(10)

SET @idag = CONVERT(VARCHAR(10), GETDATE(), 101)
SET @user = SUSER_SNAME()

SELECT *
FROM dbo.vw_BALI_AFSAETNING AS A
WHERE A.BEREGNET_KUNDETYPE IN
(
SELECT authValue
FROM dbo.getUserRowAccessRights(@user, 'BALI', @idag)
)
GO

Or something along those lines.

Edited by - tfountain on 02/13/2003 12:54:38

krsk
Starting Member

21 Posts

Posted - 2003-02-17 : 03:51:28

Hi

Thank you! Good point :-)

One more noob question :-)

When i've created what you suggest - how do I enforce the stored procedure when users select from the view?

(BTW - Are there a better way to enforce user access control on row level?)

/Kristian :-)

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-18 : 10:57:48

To enforce who can select from the view, create a role and assign users to that role. Then grant the necessary permissions on that view for that role. As far as row by row security, I'm not the one to ask ;).

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-18 : 14:55:34

I would imagine it would have to be part of the database design. For example, for any gice key of a row, you might want to restrict it to a particular user or group. To that end the db would need to identify Users, Groups and Profiles. Just like SQL Server Security, except you have to build in to your application, and authenticate through the app front end. We use connection pooling with 1 SQL Server login. We then have all of the users login with their id's (as assigned by an application administrator) and autheticated though an enterprise wide single sign on api. Once they're in, we have a user_work_profile table that identifies who can see what (actually they only see what they're allowed). This is because the application is only allowed to call stored procedures. We prevent the developers from making an ad-hoc calls.

Hope this helps

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources