How to work with multiple users accessing own database in Oracle with .NET?_问答_开发者

My and 2 of my friends finished working on a .NET and Oracle based application for university but there's a little problem. We only noticed about it now and it's not really relevant that we fix it but we would like to anyway.

The thing is, the university provides an Oracle server and each student has 开发者_如何学Goan account with it's own database. Since we were on the same developing team we decided to use the same password for simplification. We would only need to change the username to work on our own database and each one could debug whatever he was working on without messing up the others database.

We thought this was just a matter of changing the username in the connection string. The problem is that we are using DataSets and it seems these also store the connection string which doesn't help us at all.

Isn't there a way to simply have ONE connection with ONE username/password pair store in a SINGLE place and be done with it?

Dunno if it's relevant but we are using the Oracle Developer Tools for Visual Studio.

Based off your comment it looks like you're looking for "Proxy Users". So, your web application connects to the database as user webapp with some password. By itself, it doesn't have anything to do. The, the web user flynn comes along and wants to use your web application. The thing is, the data is in flynn's schema. What would happen is that webapp says "Hey, I'm going to be flynn for a while" and the database says "Yeah, I know you, you can be flynn for a bit." And from then on webapp works (by default) in flynn's schema.

More information about proxy authentication is available in the ODP documentation.

Edit: This is not what the asker was talking about. Sorry!

Congratulations! You've met a "real world" coding issue in school. Man, I wish this happened to me more often while attending University.

You've got a couple options:

Pick one user's schema to work with. That is, every developer will login with their own username/password. However, once they login they will set their session's default schema using this command: ALTER SESSION SET CURRENT_SCHEMA = SCHEMA_NAME_HERE That way when you refer to table T it is without the schema name in front for all developers. The trick is to not clobber each other's changes when working simultaneously on the project. Coordination will be necessary. Depending upon the number of people this may be feasible.
Use an outside source control tool to synchronize changes. This includes things like Subversion (SVN), Git, Mercurial, etc. You will need to use some form of this once you get into the workplace, so it might be useful to learn about this stuff now.

Where to store connection strings? Either in web.config or app.config Retrieve using ConfigurationManager or WebConfigurationManager

Example from a web.config

<connectionStrings>
    <add name="DB:PM" connectionString="Persist Security Info=False;User Id=**UserName**;Password=**PASSWORD**;Data Source=**DataBaseName**" providerName="Oracle.DataAccess.Client"/>
</connectionStrings>

Example of retrieval

m_strConnectionsString = WebConfigurationManager.ConnectionStrings["DB:PM"].ConnectionString;

1) I am guessing, but I think you each have your own schema, not database. The schemas probably all exist in the same database. Schema names and usernames are usually the same.

What you are seeing that preceeds the table name is the schema name. This is often referred to as a fully qualified name. Fred can have a table named girlfriends, Tom can have a table named girlfriends. The table Fred.girlfriends is not the same table as the table Tom.girlfriends.

2) create one or more database roles (roles are database objects, not schema objects) give the role permissions to the tables you want, using fully qualified names.

Grant the roles to the users (you and your friends) Now you each have rights to select (and possibly modify) data in the tables. You will still have to select the data from tables using fully qualified names. If you do not use fully qualified names, when you select from girlfriends, you will get the data that is your own schema, which might be what you want, but you are always better to use fully qualified names. If someone else uses your application, they will still get the correct data if you use fully qualified names.

Hope I haven't completely missed the mark here, and I hope you can use this information anyway
Harv Sather