Creating Repository to Enable a Shared Environment

[kriding]

I recently started looking at this product after the developers had been using it independently for a while.  Now I am evaluating the Corporate Edition to determine how to set it up for the rest of our site -up to 30 users on multiple applications/projects.

I am an Oracle DBA, so my focus has been on setting up the repository in an Oracle Database.  I downloaded the Oracle_BaseModel.sql script, but there was little information on the requirements for the user setup.

Q1: Is there more detailed information about how to set up the user that will eventually run the Oracle_BaseModel.sql script?  I granted the privileges I thought appropriate based on reviewing the script, but this process seems like guesswork to me.  

Q2: Are there recommendations for how to set up the repository user as far as security goes?  I wanted to create an EADBA user who owned the tables and then an EAUSER user who had privileges to modify the data.  However, when I tried to use EAUSER, I got a bunch of table not found errors.  EADBA worked just fine.

Q3: Am I correct in assuming that separate repository database users are needed for separate projects?  I was able to do a "Project Data Transfer" of a test project into the repository.  Then when I tried to load a second project, it informed me that the target would be erased first. I am familiar with Oracle Designer Repository which is a single database containing multiple projects under one owner.  With EA, I am guessing I need to create a new user and run the Oracle_BaseModel.sql script for each application project.

Q4: How do I manage security if I am giving out the userid and password so the developers can connect to the repository database?

Please forgive me if this is documented somewhere more explicitly.  I am reviewing all documentation now, but these questions did not seem to be answered clearly.

Thanks!
Kristin
 

[Geert Bellekens]

Kristin,

I went through the same process a few years ago, I'll try to respond to your questions with what I still remember from that period.

Q1 and Q2: The database users setup for EA need to have quite a lot of rights. I think they certainly need the rights to create "sequences" if that means something in an Oracle context. We started with only modify rights and added rights until it all seemed to work.

Q3: No, you can use one database to host multiple models (the root level object that you see in EA that looks like multiple grey packages). The problem you are probably facing is that you have multiple models each in their own eap (ms-access) file and that you want to transfer them to one database in Oracle. The project transfer will only allow you to add one project to the Oracle database. Choose the biggest model around.
For the other models you'll have to work with xmi export/import. You can export a whole model to xmi (right click on the root model object) and then import it into the oracle model. (answer yes to the question whether it should be imported as a root model).
There are pro's and con's for the solution to host multiple models in one database.
The main con is that you cannot restrict some model for some users unless you use an integration with a version control system.
The main pro's are less maintenance, easy to share common components among models, and the ability to have reference data and configuration for all models in one place.

Q4. There is an option to create a shortcut eap file from a model in a database. (Menu/Save As). This will basically create a textfile with an eap extension that contains the connection string to the database (and some other stuff). There is also an option somewhere to encrypt the user/password. That should allow all your users to connect to the database without knowing the password of the database user.

I think there have been some questions, and useful answers about how to setup oracle in this forum. Use the search button on the top left, and set the range to "(all posts)" for the best results.

Geert