12 Replies Latest reply: Dec 17, 2009 9:42 AM by TexasApexDeveloper RSS

    apex users vs database users

    Tigerwapa
      Hi
      I am in the progress of learning about apex and try to migrate our Oracle forms (10g and 6i) and reports to APEX. I have some confusions between database users and apex users. Our environment for APEX is using database 11g with plsql gateway.

      Every table in our database is configured with trigger to update "Date of Changed" and "Changed By" to use "sysdate" and "user". When I use APEX to develop a simple form to do update --> submit. The "Changed By" become "Anonymous," not the login "user". "user" is the database user. I use database account to authenticate the application. I have made worked is to use 'APP_USER' not 'user'. I have to change the trigger in the table to use 'APP_USER'. It is not good for our environment.

      Our environment is also using three methods to update our database: Oracle forms/reports (6i), Oracle Application Server (10g R2), Apex (is developing) . "user" works for AS and Oracle forms/reports (6i), but not in APEX. The trigger locates in database. Why does not use "user" in Apex? What is the alternative I can back to use "user" and not "APP_USER". I really don't want to change all our triggers in the database into "APP_USER."

      Greatly appreciate any advise. Have a nice holiday to everyone.

      Candice
        • 1. Re: apex users vs database users
          Hari_639
          Candice,

          One work around for this would be to use NVL.
          NVL(v('APP_USER'),USER)
          So that 'Changed by' will get values of APEX user when you update from APEX and still works fine for your Oracle forms and reports

          Cheers,
          Hari
          • 2. Re: apex users vs database users
            Tyler-Oracle
            Candice,

            I've been meaning to blog about this, but for now... run the following query from the SQL Commands section of the APEX SQL Workshop:
            select sys_context('USERENV', 'MODULE') MODULE, sys_context('USERENV', 'ACTION') ACTION,
                   sys_context('USERENV', 'CLIENT_IDENTIFIER') CLIENT_IDENTIFIER, sys_context('USERENV', 'CLIENT_INFO') CLIENT_INFO, 
                   sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') SESSION_USER,
                   sys_context('USERENV', 'IP_ADDRESS') IP_ADDRESS,sys_context('USERENV', 'HOST') HOST,sys_context('USERENV', 'TERMINAL') TERMINAL,
                   sys_context('USERENV', 'SERVER_HOST') SERVER_HOST,
                   v('APP_USER') app_user,v('APP_ID') app_id,v('APP_PAGE_ID') app_page_id,v('APP_SESSION') app_session
              from dual 
            Then run it from sqlplus

            Here's some formatting code for sqlplus:
            set linesize 250
            column MODULE            format a15
            column ACTION            format a15
            column CLIENT_IDENTIFIER format a15
            column CLIENT_INFO       format a15
            column CURRENT_SCHEMA    format a15
            column SESSION_USER      format a15
            column IP_ADDRESS        format a15
            column HOST              format a15
            column TERMINAL          format a15
            column SERVER_HOST       format a15
            column APP_USER          format a15
            column APP_ID            format a15
            column APP_PAGE_ID       format a15
            column APP_SESSION       format a15
            Pay close attention to CURRENT_SCHEMA and SESSION_USER as they should be different.

            Here's my output from APEX:
            MODULE                   ACTION       CLIENT_IDENTIFIER         CLIENT_INFO  CURRENT_SCHEMA  SESSION_USER        IP_ADDRESS   HOST                   TERMINAL    SERVER_HOST  APP_USER    APP_ID  APP_PAGE_ID APP_SESSION
            ------                   ------       -----------------         -----------  --------------  ------------        ----------   ----                   --------    -----------  --------    ------  ----------- -----------
            APEX:APPLICATION 4500    PAGE 1204    TMUTH:2607364228111977    TMUTH        DEMO            APEX_PUBLIC_USER    127.0.0.1    WORKGROUP\TMUTH-LAP    TMUTH-LAP   tmuth-lap    TMUTH       4500    1204        2607364228111970
            and sqlplus:
            MODULE          ACTION          CLIENT_IDENTIFI CLIENT_INFO     CURRENT_SCHEMA  SESSION_USER    IP_ADDRESS      HOST            TERMINAL        SERVER_HOST     APP_USER        APP_ID          APP_PAGE_ID     APP_SESSION
            --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------
            SQL*Plus                                                        DEMO            DEMO            192.168.174.1   WORKGROUP\TMUTH TMUTH-LAP       tmuth-lap
                                                                                                                            -LAP
            Tyler Muth
            http://tylermuth.wordpress.com
            [Applied Oracle Security: Developing Secure Database and Middleware Environments|http://sn.im/aos.book]
            • 3. Re: apex users vs database users
              Tigerwapa
              Tyler,

              Thank you for the help and patient. Greatly appreciated. I am still new to database, apex, as well SqlPlus. I ran your commands via my machine (GLOBAL-HP001), not directly on the apex server as well as database server (bigbertha). I will have some dumb questions in the followings:
              1) The result via SQL Commands section of the APEX SQL Workshop:
              MODULE: APEX:APPLICATION 4500
              ACTION: PAGE 1204
              CLIENT_IDENTIFIER: CLIU:4807108431496672
              CLIENT_INFO: CLIU
              CURRENT_SCHEMA: CLIU
              SESSION_USER: ANOMYMOUS
              IP_ADDRESS: 192.168.1.106
              HOST:
              TERMINAL:
              SERVER_HOST: bigbertha
              APP_USER: CLIU
              APP_ID: 4500
              APP_PAGE_ID: 1204
              APP_SESSION: 4807108431496670

              2) How do you login into workspace via sqlplus?

              3) I ran the sqlplus result as regular database login:
              CURRENT_SCHEMA: CLIU
              SESSION_USER: CLIU
              IP_ADDRESS: 192.168.1.106
              HOST: GDINC/GLOBAL-HP001
              TERMINAL: GLOBAL-HP001
              SERVER_HOST: bigbertha

              4) CLIU is a database user as well as APEX user. Am I messing up some kind of settings in APEX?

              Any advise is greatly appreciately.

              Candice
              • 4. Re: apex users vs database users
                Tigerwapa
                Thank you Hari

                I want to make sure I understand what your suggestion is. Do you mean to change default value of that item: Changed By via Apex application? I tried, but no change. Not even show ANONYMOUS. I change Column Attributes --> Changed By --> Default Type to PL/SQL Expression or Function --> Default into NVL(v('APP_USER'),USER).

                I appreciate all of your inputs. Let me know.

                Candice
                • 5. Re: apex users vs database users
                  TexasApexDeveloper
                  Candice,
                  Is the trigger coded as such that it only updates the changed_by if the updating method does not provide a value of is it doing it no matter what? If the trigger is coded to just look at the action being provided (inserting, updating) then there is not much you can do, since the trigger will have to be updated to allow APEX style users to update and insert data into your application data tables..


                  Thank you,

                  Tony Miller
                  Webster, TX
                  • 6. Re: apex users vs database users
                    Tyler-Oracle
                    All APEX applications run or parse as one and one database schema. You've made things a bit confusing for yourself by naming your schema, workspace, and APEX user "CLIU". It might be easier if you create a new workspace called "Development", then map the CLIU schema to that workspace. Then create a new APEX user called "candice". That way you distinguish between the components.

                    Tyler Muth
                    http://tylermuth.wordpress.com
                    [Applied Oracle Security: Developing Secure Database and Middleware Environments|http://sn.im/aos.book]
                    • 7. Re: apex users vs database users
                      Hari_639
                      Candice,

                      As I understood your issue is, you have trigger which contains following (or similar) piece of code
                      :new.changed_by := USER;
                      When you are doing INSERT or UPDATE using APEX, changed_by column is not getting updated by APEX User instead it's getting updated as 'Anonymous'. To get the APEX user you should use APEX function v like
                      :new.changed_by := NVL(v('APP_USER'),USER);
                      So if you are updating from APEX, then v('APP_USER') will return APEX user id who has logged in to application. If you are updating data from other environments like Oracle Forms or SQL plus, then v('APP_USER') will return NULL so changed_by will get updated with database user name.

                      Hope it's clear now. :-)

                      Cheers,
                      Hari
                      • 8. Re: apex users vs database users
                        Tigerwapa
                        Hari

                        Thank you again. I have to change thinking of various developing method. I have to change in the database triggers which relating to our tables. In our environment, we use Forms/Reports 6i, Forms/Reports 10g, and Apex (new project) to update Database 11g. 6i and 10g will not go away soon in our environment.

                        Even though new record for the fields, DateAdded and Addedby, have to change. I was trying to see whatelse can work around. You are right.

                        :new.changed_by := NVL(v('APP_USER'),USER);

                        This will work. Thank you and happy new year to you.

                        Sincerely,
                        Candice
                        • 9. Re: apex users vs database users
                          Tigerwapa
                          Tyler,

                          Thank you for all the inputs and clarification. I have to change in our triggers NVL(v('APP_USER'),USER);

                          I greatly appreciate your inputs and help. I learn a lot everyday.

                          Thank you again and have a happy new year.

                          Sincerely,
                          Candice
                          • 10. Re: apex users vs database users
                            Tigerwapa
                            Hi Tony

                            You are right. Yes, currently our triggers was coded for updates to use ":NEW.CHANGEDBY := USER;". My new project is migration oracle forms/reports to APEX. I have to change our triggers into ":new.changed_by := NVL(v('APP_USER'),USER);". Even for inserting as well. We set up the default value in our tables of fields: Added_By and DateAdded as user and sysdate.

                            Thank you very much for the help and suggestion. Have a nice holiday.

                            Sincerely,
                            Candice
                            • 11. Re: apex users vs database users
                              Hari_639
                              You are welcome Candice and wish you the same!!

                              Cheers,
                              Hari
                              • 12. Re: apex users vs database users
                                TexasApexDeveloper
                                Candice,
                                Glad to have been of assistance! Come back often and ask away.. That is what we are all here for!!

                                Thank you,

                                Tony Miller
                                Webster, TX