471,350 Members | 1,450 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,350 software developers and data experts.

Prequalifying table names with the user name in reusable components

Hi,

I have a data driven application which has some generalized components. So,
for reuse, I am building the components so they can be reused in other
projects ... it takes almost no extra effort, just a bit of planning.

Following the security convention of only giving the user as much access as
required, I always create at least 2 users for each app; owner & user. And
recently I've had a cool idea for handling multiple sites with one DB; you
make the table owner the app owner user instead of dbo. This is useful with
a webhost, I don't have to pay extra for multiple databases OR have some
funky naming convention. You can have a separate 'users' table for each app
without name collisions. I'm sure others have done this, but I was pretty
happy with my little epiphany.

However, I have run into the following glitch:
My reusable components need to have the table owner coded into every SQL
statement! Damn it! How is this going to be reusable if every app that
uses it needs to have the same user names? Right?

Now the options as I see them are :
1. Adding an extra method 'username' parameter everywhere my connection
passed in.
2. Add the user name to the web.config file and use it when building every
sql statement.
3. Use my components as the table owner user.
4. Require an updatable view with the user as the owner, for every table
used by the component. In other words, have app_owner.users (table) as well
as app_user.users (view), and reference it like 'select * from users'.

Oracle has something called a synonym, which would allow me to remove the
prequalifying user name from all references, but SQLServer doesn't seem to
have anything comparable.

Personally, I don't like any of the options that I immediately recognize.
I'm guessing any reusable component accessing data must have the same
problem.

Is there a commonly used way to get around this?

Or does anybody see an option which I am missing?

Thanks in advance.
--
Regards,
John MacIntyre
http://www.johnmacintyre.ca
Specializing in; Database, Web-Applications, and Windows Software
Jul 21 '05 #1
2 1940
Have you looked at Microsoft's Enterprise library? They use a series
of .config files that contain information about the sql connections,
including user, etc. These files are edited using a config app that is
provided.

Here is the link:

http://tinyurl.com/5ga86

Jul 21 '05 #2
"Chris Dunaway" <du******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Have you looked at Microsoft's Enterprise library? They use a series
of .config files that contain information about the sql connections,
including user, etc. These files are edited using a config app that is
provided.

Here is the link:

http://tinyurl.com/5ga86


Thanks Chris,

I will look into that.

Regards,
John
Jul 21 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

36 posts views Thread by toedipper | last post: by
2 posts views Thread by DaleMan | last post: by
9 posts views Thread by john | last post: by
1 post views Thread by Fix_Metal | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.