HELP! Dynamic binding of schema element names

Hi all,
Somehow I lost the thread where I originally posted this query. Thanks
Erland and GregO for your answers. Very helpful indeed.
Although I think I can solve my problem with what you guys suggested, I'd
really like to know your opinion and maybe offer solutions
of achieving my goal. Let me put my problem forth in more detail:
My application has all it's logic in a COM component and all SQL queries are
issued from there.
The application itself was never designed with security and access-control
in mind
(which I'm cursing it for and have to incorporate now :-(( ). So, now I have
a
few thousand queries that I don't want to affect drastically.
I have a bunch of users in a [USER] table and a bunch of resources in a
[RESOURCE] table. What ultimately should happen is that
every user should be able to see resources only entitled to her based on
some security policy.
Here is how I think it can be done.
A new entity [SEC_GROUP] can be introduced where each user is part of one or
more security groups. For each security group,
I will create a view on the [RESOURCE] table: [admingrp_resource],
[generaluser_resource] and so on.
So, when a user issues a query like,
SELECT * FROM [RESOURCE]
I will actually substitute [RESOURCE] with a function like
get_resource_view(userid)
EXEC( 'SELECT * FROM ' + get_resource_view(userid))
The get_resource_view( ) function would get the appropriate resource view
for the user based on her security group.
The above would be rather easy if the user is part of only one security
group. If there are more, I might have to do
some unions. This would impact the queries quite a bit, but I can't think of
another way to implement this.
Once again, thanks for your responses, I appreciate your help and looking
forward to more suggestions.
Best regards,
--Abhi
Abhijith Das (adas@.expeditevcs.com) writes:
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
>
> Here's a simple select statement
>
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
>
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
>
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition = > 1.
>
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however. Is there a way to
> accomplish this?
Yes. But it is unlikely that it is the right thing to do. Since I don't
know your underlying problem, I cannot suggest a solution here and now.
But this article on my web site, both describes on how you can achieve
this - and why you most probably should not do it anyway.
http://www.sommarskog.se/dynamic_sql.html.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.aspAbhijith Das (adas@.expeditevcs.com) writes:
> My application has all it's logic in a COM component and all SQL queries
> are issued from there. The application itself was never designed with
> security and access-control in mind (which I'm cursing it for and have
> to incorporate now :-(( ). So, now I have a few thousand queries that I
> don't want to affect drastically.
> I have a bunch of users in a [USER] table and a bunch of resources in a
> [RESOURCE] table. What ultimately should happen is that every user
> should be able to see resources only entitled to her based on some
> security policy. Here is how I think it can be done. A new entity
> [SEC_GROUP] can be introduced where each user is part of one or more
> security groups. For each security group, I will create a view on the
> [RESOURCE] table: [admingrp_resource], [generaluser_resource] and so
> on.
First of all, for this to be meaningful, you need to revoke access to
the tables from the users. Keep in mind that there are other means to
connecting to SQL Server, and a skilled user could for instance use
Query Analyzer to access the data.
> So, when a user issues a query like,
> SELECT * FROM [RESOURCE]
> I will actually substitute [RESOURCE] with a function like
> get_resource_view(userid)
> EXEC( 'SELECT * FROM ' + get_resource_view(userid))
But why do you want to have this function in SQL? Since you apparently
have all your logic client-side, why not stick to that? Depending on
the size of the data stored for these security groups, you could read
this data once, and keep it in memory. (May need some refresh mechanism
in case the security configuration is changed.)
From this follows that the user will need to have SELECT access to
the table what defines the security groups and the resources. (Unless
you use an application role.)
> The get_resource_view( ) function would get the appropriate resource
> view for the user based on her security group. The above would be rather
> easy if the user is part of only one security group. If there are more,
> I might have to do some unions. This would impact the queries quite a
> bit, but I can't think of another way to implement this.
A common approach to row-level security is to have views that includes
conditions like:
AND userid = SYSTEM_USER
Although one should be aware of that a skilled user with a query tool can
still be able to carve out glimpses of data he is not intended to see.
For a more complex security scheme, you could have table-valued
functions, but you would have to one for each base table.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

HELP! -> Stored procedure and ASP.NET

I've created a SP in MS SQL Server 2005 Express. In ASP.NET I want to use this SP, but somehow my data is not added...what am I doing wrong?
Also: how can I efficiently debug a SP?

Here's my SP:

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTERPROCEDURE [dbo].[sp_InsertNewUser]

-- Add the parameters for the stored procedure here

(@.UserNamenchar(20),

@.pwdnchar(15),

@.Emailnchar(35),

@.IsMalebit,

@.BirthDatedatetime,

@.Countrysmallint,

@.PerfectSexnchar(1),

@.HasHTMLbit,

@.HasOffersbit,

@.HasNewsLetterbit

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

INSERTINTO dbo.tblMember(UserName,pwd,Email,IsMale,BirthDate,Country,PerfectSex,HasHTML,HasOffers,HasNewsLetter)

VALUES(@.UserName,@.pwd,@.Email,@.IsMale,@.BirthDate,@.Country,@.PerfectSex,@.HasHTML,@.HasOffers,@.HasNewsLetter)

END

Here's my code:
Dim BirthDate As DateTime = CDate(ddlMonth.SelectedValue + "-" & ddlDay.SelectedValue & "-" & ddlYear.SelectedValue)
Dim ActivationCode As String = Session.SessionID
Randomize()
Dim sPwd As String = CStr(Int((1000000 * Rnd()) + 100000))
Dim MyCommand As New SqlCommand("sp_InsertNewUser", DBConnection)
MyCommand.CommandType = Data.CommandType.StoredProcedure
MyCommand.Parameters.Add(New SqlParameter("@.UserName", tbUserName.Text))
MyCommand.Parameters.Add(New SqlParameter("@.pwd", sPwd))
MyCommand.Parameters.Add(New SqlParameter("@.Email", tbEmail.Text))
MyCommand.Parameters.Add(New SqlParameter("@.IsMale", ddlSex.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.BirthDate", BirthDate))
MyCommand.Parameters.Add(New SqlParameter("@.Country", ddlCountry.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.PerfectSex", ddlPerfectSex.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.HasHTML", ddlEmailSupport.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.HasOffers", cbProdServices.Checked))
MyCommand.Parameters.Add(New SqlParameter("@.HasNewsLetter", True))

You've missed the pertinent part of your code. After you add the parameters, are you actually executing your MyCommand? And, where is this code running and are you sure its running?|||

Im sure its running, as I stepped through the code.
I execute the command with ExecuteNonQuery.
the db connection opens and the statement appears to be executed.
I've put everything in a try catch statement but no errors occur! I've checked the field definitions and they are good as well...

but I take it you dont see any syntax errors?
And do you know how I can debug a SP?

Thanks!

|||

Peter Smith wrote:

Im sure its running, as I stepped through the code.
I execute the command with ExecuteNonQuery.
the db connection opens and the statement appears to be executed.

I guess we'll have to trust you on this one.

Peter Smith wrote:

but I take it you dont see any syntax errors?

I don't see any obvious syntax errors. Have you tried running your stored procedure from Query Analyzer?

Also, I guess you are using ASP.NET 1.1? The syntax you are using for parameters has been deprecated for ASP.NET 2.0 (you would now use the AddWithValue method). Personally I would use the constructor that specifies the data type, e.g.:
MyCommand.Parameters.Add(New SqlParameter("@.UserName", SqlDbType.NChar,20)).Value = tbUserName.Text
or, more simply:
MyCommand.Parameters.Add("@.UserName", SqlDbType.NChar,20).Value = tbUserName.Text

Peter Smith wrote:

And do you know how I can debug a SP?

Which IDE are you using? Visual Studio 2003? I know this includes a stored procedure debugging capability from within Server Explorer but I have not really used it myself (seeHOW TO: Debug Stored Procedures in Visual Studio .NET). I know there is a Transact-SQL debugger available from within Query Analyzer (seeStarting the Debugger in BOL) and I've only played around with that a bit. Unfortunately I debug stored procedures using old-fashioned manual methods: running the stored procedure in Query Analyzer, examining the results, tweaking the code if there are errors/unexpected results. I don't use a formal debugging tool.|||

I dont know how to use that quote thing ;)

"Also, I guess you are using ASP.NET 1.1? The syntax you are using for parameters has been deprecated for ASP.NET 2.0 (you would now use the AddWithValue method). Personally I would use the constructor that specifies the data type, e.g.:
MyCommand.Parameters.Add(New SqlParameter("@.UserName", SqlDbType.NChar,20)).Value = tbUserName.Text
or, more simply:
MyCommand.Parameters.Add("@.UserName", SqlDbType.NChar,20).Value = tbUserName.Text"

actually I use ASP.NET 2.0, I just didnt know I could also do it otherwise.
I will now use that simple constructor you provided, although I dont really see the purpose of specifying the datatype...is that for better debugging, or faster code or...I dont know...

I use Visual Web Developer 2005 Express and SQL Server 2005 Express...can I then still use the tools (query analyzer and server explorer) you mentioned?

Thanks!