8 Replies Latest reply: Jul 27, 2009 6:38 AM by 628675 RSS

    Non-accessible workbooks

    444303
      Some workbooks appearing in the list were made by the EUL owner user (USER_A), this user is a DB user. Since then, we moved to an Oracle Applications Discoverer. We want to get rid of those old workbooks, but it's impossible to login as USER_A to suppress them.

      We tried with Desktop as a "regular" user, but the message "You don't have access to any table" shows up.

      Is there a way to get rid of the workbooks, I'm thinking using the Discoverer inner tables or else.

      Thanks
        • 1. Re: Non-accessible workbooks
          Michael Armstrong-Smith
          Hi
          Desperate time call for desperate measures and what I am going to describe below should be done with caution. I am only describing it here for you so that you have a way to get rid of those workbooks you otherwise cannot access. For anyone else reading this, please use Discoverer's built-in method for deleting workbooks.

          The trick is to change the owner of the workbook to an Apps user then log in as that user and delete the workbooks. I recommend doing this in Desktop.

          The workbooks are stored in a table called EUL5_DOCUMENTS and the column that contains the ID of the owner is called DOC_EU_ID. This is what we will change.

          Step 1:
          Log in to SQL as the owner of the EUL and run this:

          SELECT DOC_EU_ID, DOC_NAME FROM EUL5_DOCUMENTS WHERE DOC_CREATED_BY = 'eul_owner';

          obviously changing eul_owner to be the EUL owner you will see the list of workbooks that you want to delete. Make a note of the DOC_EU_ID.

          Step 2:
          Let's get a count.

          SELECT COUNT(*) FROM EUL5_DOCUMENTS WHERE DOC_CREATED_BY = 'eul_owner';

          obviously again changing eul_owner to be the EUL owner. Make a note of the COUNT

          Ok so far?

          Step 3:
          Next, let's find the name and ID of a real Apps user who will become a temporary owner for your workbooks. The following code will list all Apps users who currently own workbooks in the database:

          SELECT DISTINCT
          USERS.USER_NAME APPS_USER,
          DISCO_USERS.EU_USERNAME,
          DISCO_USERS.EU_ID,
          DISCO_DOCS.DOC_CREATED_BY
          FROM
          EUL5_DOCUMENTS DISCO_DOCS,
          EUL5_EUL_USERS DISCO_USERS,
          APPS.FND_USER USERS
          WHERE
          SUBSTR(DISCO_DOCS.DOC_CREATED_BY, 2)) = USERS.USER_ID AND
          DISCO_USERS.EU_ID = DISCO_DOCS.DOC_EU_ID;


          Pick the Apps user you want to log in. Make a note of the DISCO_USERS.EU_ID. You will need this later.

          Note: the reason I suggest using someone who already owns workbooks is this saves you from having to create an entry in the EUL5_EUL_USERS table. All users who own workbooks have an entry in this table.

          Step 4:
          Now let's check whether the original owner shared any workbooks. These will need deleting too.

          First let's check if any sharing has been done.

          SELECT COUNT(*) FROM
          EUL5_DOCUMENTS, EUL5_ACCESS_PRIVS
          WHERE
          EUL5_DOCUMENTS.DOC_CREATED_BY = 'eul_owner' AND
          EUL5_DOCUMENTS.DOC_ID = EUL5_ACCESS_PRIVS.GD_DOC_ID;


          Once again replace eul_owner with real owner name. If this returns zero then no sharing has taken place.

          If this returns a number greater than zero then we need to note that these records have to be removed. We'll let the system do it for us and recheck later. For now make a note of the number of shares.

          Step 5:
          Next we'll update the owner of the workbook(s) to the DISCO_ID you figured out earlier.

          UPDATE EUL5_DOCUMENTS
          SET DOC_EU_ID = the_ID_from_step_3
          WHERE DOC_CREATED_BY = 'eul_owner';


          You should get the same number updated as you got earlier when you did the count in Step 2. If this is true then COMMIT the change.

          Step 6:
          Now log in to Desktop as the Apps user that you picked and delete the workbooks. When you log in you will be informed that the workbook was created under another account and asked if you want to open it under that account or the account you are logged in as. You should open it using the account you are logged in as.

          Use Desktop's normal delete mechanism for deleting the workbooks. From the toolbar use File | Manage Workbooks | Delete. You should see and delete the same workbooks you saw in Steps 1 and 2

          Step 7:
          Having deleted the workbooks, if there were any shares, let's make sure that all of these have gone too. You will need the DOC_EU_ID I told you to take a note of in Step 1.

          SELECT COUNT(*) FROM
          EUL5_ACCESS_PRIVS
          WHERE
          EUL5_ACCESS_PRIVS.AP_TYPE = 'GD' AND
          EUL5_ACCESS_PRIVS.AP_EU_ID = SAVED_DOC_EU_ID;


          If this now returns zero then all sharing has been removed.

          If this still returns a number greater than zero then we need to manually delete those records.

          Before you do this, for safety take a backup of EUL5_ACCESS_PRIVS then run this:

          DELETE FROM EUL5_ACCESS_PRIVS
          WHERE
          EUL5_ACCESS_PRIVS.AP_TYPE = 'GD' AND
          EUL5_ACCESS_PRIVS.AP_EU_ID = SAVED_DOC_EU_ID;


          You should get a message that X rows have been deleted. If this count equals what you got earlier a few moments ago then COMMIT the delete. If you get a different count then use ROLLBACK and recheck your code.

          If by mistake you commited and deleted too many rows then restore the table from the backup I advised you to take.

          I know there's a lot of steps. Let me know how you get on.

          Best wishes
          Michael
          • 2. Re: Non-accessible workbooks
            444303
            Thank you very much for your prompt reply, Michael.

            Of course, I'll be doing this with the DBA...and since there's much security issues involved, it may take a while to get back to you, but be assured that I'll do it.
            • 3. Re: Non-accessible workbooks
              Michael Armstrong-Smith
              Hi
              Something else for you to take into consideration is the names of the workbooks because if the new user already owns workbooks named the same as the one you are trying to reassign you will run into issues.

              You therefore should check that there are no conflicts in the workbook names and pick another user if need be. One possible way of guaranteeing this is to log in to Discoverer as a user who has never owned a workbook and create a temporary one. This will make Discoverer insert a row into the the users table. Now you can find that user's ID and reassign the workbooks to be owned by that user.

              Alos, if you find any errors in the scripts please let me know so I can update my documentation.

              Best wishes
              Michael
              • 4. Re: Non-accessible workbooks
                444303
                Hi Michael,

                «Chose promise, chose dûe», like we say in French.

                Your solution was applied and it worked flawlessly. We intend to spread it to all our environments (DEV, PREPROD, PROD, FORMATION, and SANDBOXES) soon.

                Once again, many thanks.
                • 5. Re: Non-accessible workbooks
                  628675
                  Hi Michael,

                  When we are creating workbook in desktop it is giving "unable to connect to databse".

                  We are creating workbook using application user.Can you any suggesttion please.


                  Thanks in advance.

                  Regards,
                  Kamlesh
                  • 6. Re: Non-accessible workbooks
                    Tamir Lazover
                    Hi,
                    In the desktop tool menu go to : "Tools->Options" to the connection tab and select the 3rd option:

                    "connect to both..."

                    Then try to login again.
                    • 7. Re: Non-accessible workbooks
                      628675
                      Hi ,

                      Thanks for response !

                      I tried that also provided the gateway user_id and FND name but still the problem persist.

                      We are able to connect with EULD owner but not with any of the application user now.

                      I also assign administrative priviledges to the sysadmin application user.

                      Please suggest next.

                      regards,
                      Kamlesh
                      • 8. Re: Non-accessible workbooks
                        628675
                        Is this the tnsnames or secure folder problem.