Technical user sufficient for MySQL connection?

[Pascada Consulting GmbH]

Hello,

I have successfully set up my EA to connect to a MySQL DBMS via ODBC following the instructions at http://www.sparxsystems.com/enterprise_architect_user_guide/10/projects_and_teams/upsizingtomysql.html. Thus, I can now share the EA projects through this DBMS.

Unfortunately, the MySQL instance is of a somewhat older version (5.1) which does not allow authentication via LDAP (corporate Active Directory). Therefore, I see three options how my fellow colleagues may connect their EA to the DBMS:

• Everyone connects with the same technical user
• Create one MySQL user ID for every colleague using EA. (The admin has to do that and also set a password.)
• Upgrade to MySQL 5.5 or higher (which supports LDAP authentication)

As options 2 and 3 impose some effort, I wonder whether option 1 might be sufficient. That is:
Is one technical user ID sufficient in an ODBC connection to a MySQL database for multiple concurrently working EA users?

Notes:

• As I understand, MySQL differentiates connections by user ID and source host. Therefore, if both colleagues A and B connect with the same technical_user, MySQL would still see them as technical_user@computer-of-A and technical_user@computer-of-B, respectively.
• As EA requires a separate database per project (as I understood it), this case is only relevant for users working concurrently on the same EA project.

Best regards,
Björn

EA 10.0.1009

[qwerty]

I don't see any issue using a technical user except you can not meaningful use security/locking. For me that would be a killer argument. YMMV

q.

[Uffe]

First off, you are correct in that one database houses one EA project.

Yes, you can use a single technical DBMS user and Yes, you can still assign separate project user IDs for the different individuals (qwerty is wrong here).

The DBMS user is used to check database access. Read and write access is required for EA to work, even if you intend for the person to only be allowed read access to the models; this is because EA stores a lot more than just the models in the database.

In-project permissions, such as the permission to change models, generate documents, as well as locking of models to prevent concurrent-update issues, is done using EA's project user IDs. These are stored in the project and are not related to the DBMS user. You can import users from Active Directory, but all management of permissions is in-project (permissions are not stored in the AD).

Separately from the user import, you can configure the project to Accept Windows Authentication. If you do not, each user must authenticate with user name and password when they open the project. (This is the project user, not the DBMS user.)

If the project is configured for Windows Authentication, no user name / password prompt appears, provided that the logged-on Windows user is found among the in-project users.

The main thing in order for your option 1 to work is to ensure that EA uses the technical user when accessing the database. Having to do this every time you open a project (which you can do in the Connection tab of the Data Link Properties dialog, which opens after you select Connect to Server in the Open Project dialog), you can create an ODBC data source and distribute that.

As an alternative, I believe you can include it in the connection string which is stored in an .EAP shortcut to a database project. The .EAP file is in this case simply a text file with the ODBC connection string, wrapped in a little EA stuff. This might be less secure (the password might be stored in there), but if this is acceptable you can simply place such a shortcut .EAP file on a file share.

However, if you have the option of upgrading the DBMS I would do that, it's the least painful solution long-term. It should be noted here that you can transfer an EA project from one database to another, which transfers all the in-project users (since they are stored in the database) but not the DBMS user. So you could start with option 1 and then some while later switch to option 3 with a minimum of work (slightly more if you have used ODBC data sources).

HTH,


/Uffe

[Geert Bellekens]

First off, you are correct in that one database houses one EA project.

Yes, you can use a single technical DBMS user and Yes, you can still assign separate project user IDs for the different individuals (qwerty is wrong here).

The DBMS user is used to check database access. Read and write access is required for EA to work, even if you intend for the person to only be allowed read access to the models; this is because EA stores a lot more than just the models in the database.

In-project permissions, such as the permission to change models, generate documents, as well as locking of models to prevent concurrent-update issues, is done using EA's project user IDs. These are stored in the project and are not related to the DBMS user. You can import users from Active Directory, but all management of permissions is in-project (permissions are not stored in the AD).

Separately from the user import, you can configure the project to Accept Windows Authentication. If you do not, each user must authenticate with user name and password when they open the project. (This is the project user, not the DBMS user.)

If the project is configured for Windows Authentication, no user name / password prompt appears, provided that the logged-on Windows user is found among the in-project users.

The main thing in order for your option 1 to work is to ensure that EA uses the technical user when accessing the database. Having to do this every time you open a project (which you can do in the Connection tab of the Data Link Properties dialog, which opens after you select Connect to Server in the Open Project dialog), you can create an ODBC data source and distribute that.

As an alternative, I believe you can include it in the connection string which is stored in an .EAP shortcut to a database project. The .EAP file is in this case simply a text file with the ODBC connection string, wrapped in a little EA stuff. This might be less secure (the password might be stored in there), but if this is acceptable you can simply place such a shortcut .EAP file on a file share.

However, if you have the option of upgrading the DBMS I would do that, it's the least painful solution long-term. It should be noted here that you can transfer an EA project from one database to another, which transfers all the in-project users (since they are stored in the database) but not the DBMS user. So you could start with option 1 and then some while later switch to option 3 with a minimum of work (slightly more if you have used ODBC data sources).

HTH,


/Uffe

As an addition to that, you can encrypt the .eap shortcut files so the database password isn't readable.

Geert

[qwerty]

Yes, you can use a single technical DBMS user and Yes, you can still assign separate project user IDs for the different individuals (qwerty is wrong here).

Looks like I miss some coffee  

q.

[Pascada Consulting GmbH]

Wow,

four answers within 100 minutes including an outstanding comprehensive one.

Thanks to all participants but especially to Uffe for those details.

Björn