9 Replies Latest reply on Sep 10, 2009 7:09 AM by 681400

    What is the best practice for connecting to different schemas?

    681400
      Hi all,

      We are porting an application from SQL Server to oracle and would like to know what the best practices are in oracle for user connections to an Oracle instance.

      More or less the question could be put like this:
      1) The equivalent of a SQL Server Database in Oracle is a Schema. (more or less)
      2) A specific application has it's own schema where it keeps all related objects (Tables, etc)
      3) In SQL Server you grant access to the Database and its objects (Tables, etc) to all users of the application.
      4) In Oracle do you grant access to the Schema and its objects (Tables, etc) to all users of the application also? Or do all users log
      in as the schema owner?

      So in Oracle if there existed [SchemaApplication].[table1], how would [userChris] and [userDave] query [SchemaApplication].[table1]?
      Would Chris and Dave log in as [userChris] and [userDave], or would they normally log in as [userApplication]?

      finally, is it good practice to log in as a unique user eg [userChris] and then issue the

      alter session set current_schema = shemaApplication;

      command to change the way references to tables are interpreted?
        • 1. Re: What is the best practice for connecting to different schemas?
          asifkabirdba
          You can go through this previous discussed topic:

          Re: Read only database


          Regards
          Asif Kabir
          • 2. Re: What is the best practice for connecting to different schemas?
            539769
            We are porting an application from SQL Server to oracle and would like to know what the best practices are in oracle for user connections to an Oracle instance.

            More or less the question could be put like this:
            1) The equivalent of a SQL Server Database in Oracle is a Schema. (more or less)
            2) A specific application has it's own schema where it keeps all related objects (Tables, etc)
            3) In SQL Server you grant access to the Database and its objects (Tables, etc) to all users of the application.
            4) In Oracle do you grant access to the Schema and its objects (Tables, etc) to all users of the application also? Or do all users log
            in as the schema owner?
            There are ways to implement the same.
            Case 1.
            Create different roles, such as APP_ROLE, READONLY_ROLE. Create public synonym for objects in SchemaApplication user. Grant these role to single user say appUser this is different from you SchemaApplication user. Use appUser to connect to application and for different user like userChris, userDave provide another layer of security. Say userDave is allowed only to deal with cash related transaction, so allow him to open only those screens which are related to cash transaction only.
            Case 2.
            Create public synonym and grant privilege on tables from SchemaApplication to different users (say userChris, userDave).
            So in Oracle if there existed [SchemaApplication].[table1], how would [userChris] and [userDave] query [SchemaApplication].[table1]?
            This is resolved by public synonym. There are private synonym as well, you can create this also but in this case you have to create private synonym for each of the users.
            Would Chris and Dave log in as [userChris] and [userDave], or would they normally log in as [userApplication]?
            I would suggest you to connect either using a new user(Case 1) or the user itself has account in the database(Case2).
            finally, is it good practice to log in as a unique user eg [userChris] and then issue the

            alter session set current_schema = shemaApplication;
            No. It is not a good practice to allow the user to login to database using the application owner.
            command to change the way references to tables are interpreted?
            The public/private synonym can be used to resolve the schema.object value. For example, if SchemaApplication has table T, then you can create public synonym as 'CREATE PUBLIC SYNONYM T FOR SchemaApplication.T'; and now you can refer this table as T from any other schema(user).

            HTH
            Virendra
            1 person found this helpful
            • 3. Re: What is the best practice for connecting to different schemas?
              681400
              Thanks Asif,

              That post explains how a user can access data owned by another schema, suggesting
              1) To grant select to the new user on the orginal tables OR
              2) To create a synonym and grant select on that.

              I am aware that this can be done, as in my previous post, however I am looking for a guide to best practice. Specifically if it is common or reccommended practice in oracle to have the user of an application log in directly to the schema dedicated to storing assets owned and used by the application.

              Edited by: user2588424 on 9/09/2009 22:26
              • 4. Re: What is the best practice for connecting to different schemas?
                asifkabirdba
                Welcome.

                No, if your schema name is Applicatoin which owned all the objects for your database. You must create different users to access data from the applicatoin schema owner. In our organization we follow the mentioned procedure. Main schema user (application) password is totally restricted. Other database users can access the table using other users which granted the object of main schema owner.

                Regards
                Asif Kabir

                -- Mark the answer as correct/helpful.
                1 person found this helpful
                • 5. Re: What is the best practice for connecting to different schemas?
                  681400
                  Thanks to the both of you.

                  Virendra, you wrote that issuing the
                  alter session set current_schema = schemaApplication;
                  would end up with the user logging in as the Application owner, however I do not believe this is the case, as in this thread:

                  ALTER SESSION SET CURRENT_SCHEMA =<user> vs. public synonyms....

                  To me the question is more likely whether it is reasonable to expect that a user will be granted alter session privaleges, and once again whether it acceptable to use that over a public synonym (these are ruled out due to application requirements) - or just fully qualifying the object name in the calling SQL.

                  I am very happy with the quick responses to this question, however I would still prefer a link to some authoritative documentation. If nothing is forthcoming I will mark this question as answered within the next 48 hours

                  Thanks everyone!
                  • 6. Re: What is the best practice for connecting to different schemas?
                    539769
                    Virendra, you wrote that issuing the
                    alter session set current_schema = schemaApplication;
                    would end up with the user logging in as the Application owner, however I do not believe this is the case, as in this thread:

                    ALTER SESSION SET CURRENT_SCHEMA =<user> vs. public synonyms....

                    To me the question is more likely whether it is reasonable to expect that a user will be granted alter session privaleges, and once again whether it acceptable to use that over a public synonym (these are ruled out due to application requirements) - or just fully qualifying the object name in the calling SQL.

                    I am very happy with the quick responses to this question, however I would still prefer a link to some authoritative documentation. If nothing is forthcoming I will mark this question as answered within the next 48 hours
                    I looked at the the thread. It seems me same as I was stating earlier. See the last post of John Spencer.

                    Regards.
                    • 7. Re: What is the best practice for connecting to different schemas?
                      681400
                      As John Spencer said, the alter schema operation changes the default schema that oracle looks in to determine how to resolve a table name. As such if I log in as user1 and then change current_Schema to user2, then Select * from table1 would resolve to user2.table1. However the alter current_session command does not affect the privaleges of the logged in user -- if user1 has no permissions on user2.table1, then the select command would fail.

                      This is very different from logging in as user2. As such, in my example, changing current_schema to applicationSchema would not be the same as logging on as the applicationSchema owner.
                      • 8. Re: What is the best practice for connecting to different schemas?
                        539769
                        Thanks dear. I was not aware of this fact. Thanks again.

                        Regards.
                        • 9. Re: What is the best practice for connecting to different schemas?
                          681400
                          No problems. Thanks again for your help.