|
Hello everybody, I have a question that you have just read in the title. In greater details, I want to know best practices regarding the user that the MobiLink server uses for own connections. E.g. every example uses the line that creates system tables for MobiLink using the DBA user:
However, I assume that it would be better to create a separate "server user" with own schema and access rights. Is it a good practice to do what I am asking? If yes, could you please recommend any reference, where I can read about it? I know that it is possible to decouple DB spaces (e.g. have one for transactional data, and one for MobiLink scripts and tables), however the help says that the performance will be reduced in this case. Thank you in advance. Kind regards, Comment Text Removed
|
|
AFAIK when mlsrv16.exe connects to a SQL Anywhere 16 consolidated database, there is no mechanism to tell it to use some other user id to reference the ml* tables, other than the user id that mlsrv16.exe uses to connect to the consolidated database. Generally speaking, that means most shops use the same user id for mlsrv16.exe as they did to run the syncasa.sql script. It is usually DBA, but it can certainly be something different; e.g., a user id that is only used for MobiLink purposes. Some shops, particularly Oracle, have draconian security rules that require different user ids be used to run syncora.sql and mlsrv16.exe. That creates interesting challenges because mlsrv16.exe refers to ml* tables as "SELECT ... FROM ml_whatever ..." with no owner name. With Oracle, you can use synonyms to deal with this challenge. In SQL Anywhere, you can use group membership... if you want more details, just ask. To answer the question you DID ask, there is no reason to use a user id other than DBA, other than someone telling you to do it... then, if you MUST do it, try to use the SAME user id for both syncasa.sql and mlsrv16.exe. Thank you, Breck, for your detailed answer. Ok, I will use the DBA user instead of a separate user for the MobiLink server. I was thinking about real scenarios, when acustomer has a database with business data. And should the MobiLink scripts be installed on the DB server explicitely, or it is better to create a separate user, or maybe just to create a stand alone SA database and replicate only necessary transactional tables from the production DB? E.g. SUP gives a good approach, when you can take the data from the backend server, and store it in the CDB. But if I do not use SUP, should I create CDB by myself, or the backend only :) For the last question, I assume that it is a bad idea to do development things on the backend.
(22 Jul '14, 10:46)
Vlad
1
I would see this from a different point of view: For me, a ML server is just another client application to the consolidated database (though a particular one). If the "normal" applications connect with DBA (or RESOURCE) privilege (or some other "high" privilege that is allowed to modify the schema), then there's no reason for the ML server to behave differently. In contrast, if you disallow application users to modify the schema or to do other possibly "dangerous" activities by forcing them to connect as a user with restricted privileges ("principle of least privilege"), then I would think the ML server should follow that notion, too. And if you use a particular user for the ML server, it might be easier to track modifications lateron. Just my 2 cents, obviously. And apparently, this scenario assumes that the consolidated database is not only used with remote clients via ML but also with "local" clients (connected directly or via web services, whatever).
(23 Jul '14, 03:10)
Volker Barth
|
|
Do you ask for a separate UID
Thank you for the reply, Volker. Yes, I am talking about the separate UID. E.g. I have decided to create the user mlServer, and use it for the mlsrv process. However, I had to give SYS_AUTH_DBA_ROLE and SYS_AUTH_RESOURCE_ROLE to it, because I was tired with adding separate System Privileges. I do not know what is a proper way to setup MobiLink schema. Should I run the script from the DBA user, but them remove "create XXX" privileges for mlServer, or I have to do in another way? I am just wondering how enterprise projects are usually configured, because I am starting to learn this process.
(22 Jul '14, 04:19)
Vlad
Replies hidden
Hm, I think you have not really answered my question (as "owner" vs. as "connected user")... FWIW, if you want to restrict the connected user (i.e. the 2nd choice), you would usually use an UID with DBA or RESOURCE authority to create the ML schema and then use a separate script to create a "ML server user" and grant that user the accordings permissions. Besides the fact that this user will need write access to the according tables, it's quite similar to the method Breck has described here: how to best give someone read-only access to database? Note, as the ML server connects to the consolidated, the actual USER management and GRANT statements will have to fit to that DBMS. I guess you are using SA as consolidated, so Breck's sample should fit if you enhance "SELECT" to "SELECT, INSERT, UPDATE, DELETE" and the like.
(22 Jul '14, 05:45)
Volker Barth
Oh, I am sorry, Volker. Maybe I didn't understand the question first. To be honest, I do not know a proper way of MobiLink initialization: My idea is to create the connection user for MobiLink server. And I do not know, if the owner of all ML tables should be exactly THIS user, or DBA. I assumed that MobiLink user for the connection, should be the owner of tables/triggers and all sync. scripts.
(22 Jul '14, 09:04)
Vlad
1
Well, you ask for a suggestion, so that's just my point of view: I would generally separate the owner of database objects (who must obviously have the permission to create and alter those) from the "user" of such objects (who may select and modify the data but should usually be not allowed to modify the schema). And in that guideline, you might create the ML system tables and the like as DBA but would create a particular user that only has permissions to select from and fill the data. The latter one would then be used for the connections from the ML server.
IMHO, they should be different. That is what I'm trying to say:)
(22 Jul '14, 09:34)
Volker Barth
Thanks. Or, no - many thanks! If you can, please copy and paste your comment to the answer, so I can approve it.
(22 Jul '14, 10:12)
Vlad
1
FWIW, I have turned my intial comment into an answer, in case that's what you asked me for:)
(23 Jul '14, 03:12)
Volker Barth
|
