0

Our company inherited some software that runs on C# Visual Studio 2010, Windows 7 and Oracle 11g. After some effort we got the software working and got a stable database (schema) set up.

We are now starting the process of migrating some data from an old system to this "new" system. However, I don't want to mess up our working schema as I expect a bit of trial and error work will be needed with our data import.

I wanted to do the following: Let's say our existing schema is called PROD. I wanted to create a second schema called TEST that we can use for the imported data. Then, in the C# code I can just switch the name of the datasource when switching between our two database schemas. The catch is that the username and password for this connection appears in a multitude of places scattered in the code. To avoid having to change user credentials in multiple places every time we switch between "db environments", I wanted to create a single user to have access to PROD and to TEST.

However, how to grant user privilege on specific schema? suggests this is not possible. Correct way to give users access to additional schemas in Oracle suggests a method for granting access on an object level, but this is insufficient: I basically want one single user to have access to two identical schemas (PROD and TEST). Once I've achieved this, I want to start modifying TEST to start with our data import.

I have also tried creating TEST as a separate Oracle Database installation on a different port, but when trying to create my user on this new instance I still get a conflict that the user already exists (since it was created for PROD in the original database installation).

My user already exists and has access to PROD. How do I give him access to TEST as well? Or how would one solve the more general problem of having a PROD and TEST database defined in an application that uses Oracle?

In MySQL this would be trivial, but I don't have any idea how to do this in Oracle. I am very new to Oracle.

Community
  • 1
  • 1
Stanley
  • 5,261
  • 9
  • 38
  • 55
  • What do you mean by `one single user to have access to two identical schemas` ? in oracle db a user is pretty much the same as schema. Which user `has access to PROD` and what does that mean ? – A.B.Cade Mar 21 '12 at 12:55
  • Clarification: in the code we have a username and password defined for connecting to the oracle database. This username and password is hard-coded in code in various places. This username/password is used to connect to a schema, say A (which is, correctly, defined in only one place). Now I want to create a second schema, say B, on which to do some tests of a data import. B must use the same username/password as A so I don't have to change those details throughout application. Then I can simply switch between A and B in the application by changing the schema name in the one place. – Stanley Mar 21 '12 at 20:25
  • In oracle DB schema=db user. You can read about the difference in semantics here http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6162110256950. Now, what do you mean when you say `connect to schema A`? do you mean you connect to the DB as user A from your application ? or is it a different DB user which have privileges to objects in schema A (= objects owned by user A) ? – A.B.Cade Mar 21 '12 at 21:21
  • Thanks for asktom link - it explains why I'm having difficulty. I want same user to have access (or be owner) of two different databases (schemas). In MySQL I would have simply created one user with two databases and given the user privileges on both databases. Then I would have referred to correct database by name. – Stanley Mar 22 '12 at 06:23

3 Answers3

1

The question of giving permissions has already been answered.

Now to your question, as a whole: Am I reading correctly that you want to update the database schema, but you want to keep it in the same database as another schema and run both in what appears to be a production database? If so, read that again to let it sink in how extremely dangerous that is.

When migrating from one "schema" to another, as a software update, it is safer to create a new database and migrate the data. This gives you plenty of shots, as you can blow away the new database as you tweak scripts.

If you want as little friction as possible in your software, you need to do a couple of things:

  1. Refactor out the code from the moron who decided to hard code connection information in multiple places. You need to get the strings in one place and make sure you extract out the Data access layer (DAL) code into its own class.
  2. Consider creating domain objects that do not rely on the database schema(s). I consider this mandatory, but you could get away without doing this. I would still create domain objects, even if they match the PROD schema tables, as you should not be using data constructs if you are moving from one schema to another.
  3. Create an interface for your data access layer (DAL)
  4. Map the current data schema, through the current DAL, to the domain objects, using the interface.
  5. Map the new schema, through a new DAL, to the domain objects, using the interface.
  6. Create a factory (or use the provider pattern) to determine which DAL object you are going to use (this makes the application configurable to old or new "schema"
Community
  • 1
  • 1
Gregory A Beamer
  • 16,870
  • 3
  • 25
  • 32
  • System is not in prod yet. PROD and TEST are just examples of two different database (schema) names. This sounds like the ultimate, eventual solution. However, code refactoring is not our mandate at the moment. We simply need to do some work on the data as a start. So I simply want to be able to use same username/password to connect to PROD database if I need to and to connect to TEST database if I need to. And I need to switch between the two easily. – Stanley Mar 22 '12 at 06:17
0

I'm assuming that you have a schema PROD and a DBUSER which have some privileges to objects in this schema.
DBUSER's name and password is hardcoded all over the application.
You've created a new schema TEST which looks the same as PROD (including grants to DBUSER).
You want that wherever the application does something like: 

UPDATE some_table set ...

It will update some_table table in TEST and not in PROD.
My suggestion is to use and change SYNONYMS, ie-
When you want to update some_table in PROD, do:

CREATE OR REPLACE PUBLIC SYNONYM some_table for prod.some_table;

and when you want to update some_table in TEST, do:

CREATE OR REPLACE PUBLIC SYNONYM some_table for test.some_table;
A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • Do synonyms mean I don't have to change my calling (c#) code? So "UPDATE some_table" will update PROD if synonym for some_table is PROD.some_table and will update TEST if synonym is TEST.some_table at the specific time? (The supplied link suggests that if synonym name is same as schema object, then object name must be prefaced with schema name) 1) So to switch: to use TEST I set up synonym for TEST and to use PROD again I delete synonym for TEST and create synonym for PROD? 2) How can I create synonyms for ALL schema objects so that my_object will point to schema.my_object for all objects? – Stanley Mar 22 '12 at 06:08
  • Lets say you have 3 schemas A,B and C. Both A and B have a table name T1. In both A and B T1 is granted select to C. If you connect to the DB to schema C (as user C) and you want to select form T1 you should say T1 of which schema, syntax is `A.T1` or `B.T1` (depends which T1 you want). If C has a synonym, lets say "TT1", for A.T1, or if there is a public synonym "TT1" for A.T1, then C can select from TT1 which is really `A.T1`. If you'll change TT1 to be a synonym for `B.T1` then selecting from TT1 will give results from T1 of B. – A.B.Cade Mar 22 '12 at 16:44
  • Ok. But that means I would have to change SQL commands everywhere in the code since both A and B will have the same table names and the table name (without any prefixes) is being referenced in SQL code. Example: both A and B will contain table T1 and C# code contains statements such as "select * from T1". I cannot use T1 as a synonym (without a prefix), and to add a prefix or change name of T1 for each table or view referenced from code is an invasive exercise. It's not in our scope to change code yet. So I don't think synonym will work. – Stanley Mar 23 '12 at 05:46
  • You can have a public synonym "T1" for "A.T1" (I used "TT1" in the example just to make it more clear). BTW, why do you mess with all this ? Why not just have 2 instances of the DB both with the exact same schemas and then change the connection strings in your c# code ? – A.B.Cade Mar 23 '12 at 06:47
  • The connections is defined in multiple places in C# code - code actually needs some refactoring as Gregory suggests. Also uses different connection methods in terms of connection pool, odbc, etc. We are not yet mandated to make any real code changes. First need to get DB up. – Stanley Mar 26 '12 at 19:09
0

The connection to Oracle is not handled correctly in the C# code and this is what is causing difficulty.

If the Data Access Layer were defined separately as Gregory suggests or if a more generic naming convention were used in SQL statements as A.B's answer points to, then it would be much simpler to switch between two databases.

Since our mandate currently doesn't involve making any changes/refactoring of code, I am using a backup and recovery approach:

I create a backup of the working database. Then I do the necessary tests and changes on the database. If I need to revert back to the working database, I create a backup of the "testing" database again and restore the original working database, using the appropriate flag to replace existing tables in the case of a restore. This enables me to switch back and forth between the "working" database and the "test" database.

This is not ideal as it does take some time to execute the backups and restores, but works without affecting the C# code and gives the ability to do work on a "testing" database without affecting the working one. Since this is a temporary scenario until the "testing" database becomes working, this is the approach I'll be following.

As the other answers point out - there is a more generic need to fix/refactor/generalize the connection code - I believe that is the best approach and the only reason I'm not doing that immediately is because we are not yet mandated to change the code.

Stanley
  • 5,261
  • 9
  • 38
  • 55