6 Replies Latest reply on Aug 31, 2005 6:35 AM by sfrade

    Connect to a specific schema ('foo') using a user ('fee')

    sfrade
      Hi,

      Is it possible to connect a schema (foo for example) using an other user than foo (fee for example) using a specific dsn connection string ?

      or is there another way ?

      Thanks
        • 1. Re: Connect to a specific schema ('foo') using a user ('fee')
          JustinCave
          Can you explain what you want to accomplish?

          In Oracle, a schema is a collection of objects owned by a particular user. You don't really connect to a schema, you connect to a database as a particular user. Depending on what you are trying to accomplish, you may be able to use something like proxy authentication or an ALTER SESSION SET CURRENT_SCHEMA command once you have connected. If you can explain your goal, we may be able to assist.

          Justin
          Distributed Database Consulting, Inc.
          http://www.ddbcinc.com/askDDBC
          • 2. Re: Connect to a specific schema ('foo') using a user ('fee')
            sfrade
            Hi Justin,

            I've created a database TEST containing two schemas. let's call us LOCAL and CENTRAL. so respectively owned by LOCAL and CENTRAL user.
            I've also created an other user called SFRADE who have a role which give select, insert, delete and update privileges to all objects of LOCAL and CENTRAL.

            I'd like to be able to use SFRADE user to connect to TEST and be able to directly fetch data coming from both LOCAL and CENTRAL schema without using 'alter session set current_schema' statement or using the schema name as prefix of schema object in a sql statement.

            I've read that I can use a database trigger logon to change the default schema. I've tried it but it partially solved my problem. I can directly retrieve data from one of the two schemas, not both.

            I'm wondering if there is a parameter in ODBC in the DSN string to specify what is the default schema. I could use this parameter into the db logon trigger.
            • 3. Re: Connect to a specific schema ('foo') using a user ('fee')
              JustinCave
              If you want to be able to refer to objects in either schema without prefixing the object with the schema name, the easiest approach is to create a bunch of synonyms. For every table in either the LOCAL or the CENTRAL schema, create a private synonm for the user SFRADE. Assuming there are no objects with the same name in both schemas, each synonym will point at the "right" object.

              Imagine I have schemas A and B. In schema A there is a table T1. In schema B, there is a table T2. If I want to be able to refer to either T1 or T2 when I am logged in as JCAVE,

              As A
              GRANT ALL ON t1 TO jcave;
              As B
              GRANT ALL ON t2 TO jcave;
              As JCAVE
              CREATE SYNONYM t1 FOR a.t1;
              CREATE SYNONYM t2 FOR b.t2;
              Now, JCAVE can refer to t1 or t2 in a SQL statement and Oracle will use the synonyms to resolve the reference.

              Justin
              Distributed Database Consulting, Inc.
              http://www.ddbcinc.com/askDDBC
              • 4. Re: Connect to a specific schema ('foo') using a user ('fee')
                sfrade
                Thanks,

                That will help me.

                Is it possible to add parameter to the ODBC DSN string and use it in Oracle ?
                • 5. Re: Connect to a specific schema ('foo') using a user ('fee')
                  JustinCave
                  There is no DSN parameter that would control name resolution

                  Justin
                  Distributed Database Consulting, Inc.
                  http://www.ddbcinc.com/askDDBC
                  • 6. Re: Connect to a specific schema ('foo') using a user ('fee')
                    sfrade
                    That's pity.

                    Thanks for your help.