Description: Hi all, Would it not be possible to add to Base the management of the Roles of the underlying database? This new graphical interface should be very similar to the existing one for user management... and would allow a more complete management of databases. Thank you for your comments. Actual Results: Enhancement Expected Results: Enhancement Reproducible: Didn't try User Profile Reset: No Additional Info: Enhancement
(In reply to prrvchr from comment #0) > > This new graphical interface should be very similar to the existing one for > user management... and would allow a more complete management of databases. Could you add a screenshot of the user management you see for databases? I only see username and password for external databases.
Exactly what and where to add?
(In reply to Timur from comment #2) > Exactly what and where to add? In first post you have written: "This new graphical interface should be very similar to the existing one for user management". I don't know where you see a "user management". Don't think it is only asking for username and password. Add a screenshot of this "user management" to this bug as attachment: https://bugs.documentfoundation.org/attachment.cgi?bugid=149926&action=enter
(In reply to prrvchr from comment #0) > Would it not be possible to add to Base the management of the Roles of the > underlying database? > > This new graphical interface should be very similar to the existing one for > user management... and would allow a more complete management of databases. > Under which circumstances would you want the role management to appear in the UI? More particularly, which database backend would you want role management for ? Most server backends require admin specific rights to be awarded in order to change or create roles. These would not normally be accessible to a non-privileged user. How do you then decide which parts of the role management to make available to whom, and in what way ? How would you make that applicable to all of the database backends supported by LO ? Far from being a simple solution, this could turn out to be quite complicated, depending on how this might be implemented. For example, if I have DBA privileges on a mysql backend, I can do pretty much what I like to the "users" table apart from apply functions that are specific to role management, e.g. encryption of passwords, and which are not supported via the UI by the native connector driver or the corresponding JDBC driver (maybe possible via the Tools > SQL menu using DDL). By default, I can also see all of the tables of the mysql schema in the UI, and modify via the UI certain elements of those tables. It isn't entirely clear to me what you would like to see, or how you would like to see it within the UI.
@Alex: I have had this problem with ROLE when switching from internal Firebird to external Firebird and then to Firebird server. After logging in I have to start through Tools → SQL with SET ROLE RDB$ADMIN; to get all permissions for all tables, views, procedures. Something like GRANT ALL seems only be possible for tables, not for the whole database.
I've just updated the jdbcDriverOOo documentation to show where to manage Users and Privileges. https://prrvchr.github.io/jdbcDriverOOo/#managing-users-and-privileges-in-base > How would you make that applicable to all of the database backends supported by LO ? The driver must provide the com.sun.star.sdbcx.Driver service and more precisely the services: https://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbcx/User.html https://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbcx/Group.html But it seems that Base never calls the Groups service and there is no entry in the menu to access the Groups management...
(In reply to prrvchr from comment #6) > I've just updated the jdbcDriverOOo documentation to show where to manage > Users and Privileges. > > https://prrvchr.github.io/jdbcDriverOOo/#managing-users-and-privileges-in- > base > Thanks for the link. Never seen this dialog before, because it isn't available for internal databases. Could be it is only working with external HSQLDB and Firebird. Doesn't work with PostgreSQL and also MySQL/MariaDB. External Firebird will show the dialog.
Seems the dialog Tools → User Administration… couldn't size automatically and couldn't be resized by user. I have tried to get a screenshot here, but only a part of the button "Delete User" is visible in German GUI. Will report a new bug for this.
Nice dialog ! Confirming. Setting to RFE. Definitely nice to have, IMHO. Some would no doubt argue that it is not the role of an office productivity app to provide administrative functions for the database module, when there are specialized tools that already provide that functionality. No idea whether this would be an easy hack or not. The UI you have created with the extension shows that it can be done.
It seems that the UI elements are already there, but clearly, the underlying connections to the various different driver code have not been implemented: core/blob/master/dbaccess/uiconfig/ui/useradmindialog.ui core/blob/master/dbaccess/uiconfig/ui/useradminpage.ui core/blob/master/dbaccess/uiconfig/ui/userdetailspage.ui As I recall, OpenOffice.org (and StarOffice prior to that) had a user administration dialog for use with Adabas which was integrated into and shipped at the time with the office suite.
> User Administration… couldn't size automatically and couldn't be resized by user I have same issue an French UI: https://prrvchr.github.io/jdbcDriverOOo/README_fr#la-gestion-des-utilisateurs-et-des-privil%C3%A8ges-dans-base > The UI you have created with the extension shows that it can be done. This UI comes from Base which displays it if the driver supports the com.sun.star.sdbcx.User service, otherwise it displays an error message. Would be really nice to have the same for roles (i.e.: com.sun.star.sdbcx.Group), with the ability to add and/or remove users from the role. jdbcDriverOOo has com.sun.star.sdbcx.Group service already implemented. Although in the user administration the Privileges is read-only for the moment, the display takes into account the privileges coming from the roles of the user.
> Base which displays it if the driver supports the com.sun.star.sdbcx.User service Maybe Base only checks for the presence of the com.sun.star.sdbcx.XUsersSupplier interface?
With the latest snapshot, jdbcDriver manages now read and write privileges (I just tested with HsqlDB and need to test with other) Any database administrator will tell you that privileges should not be assigned to users but to roles, which must be assigned to users. So I repeat my question, can we expect to see role management in Base. I would clearly like an answer to know what to expect. Thanks again.
(In reply to prrvchr from comment #13) > > So I repeat my question, can we expect to see role management in Base. I > would clearly like an answer to know what to expect. Thanks again. Who should answer here? If you find a developer, who will implement this - no problem.
Hi Robert, I hope this message, and all the others, should be read by the group that maintains Base. I think the code needed for group management should be very similar to the one already in place for user management. That's all that made me come here, but maybe I'm wrong?
(In reply to prrvchr from comment #15) > Hi Robert, > > I hope this message, and all the others, should be read by the group that > maintains Base. There is no "group, that maintains Base". There are some people, who fix bugs. There hasn't been changed much in Base since LO exists. Firebird has been added as (experimental) internal database. Not much more … > I think the code needed for group management should be very similar to the > one already in place for user management. > > That's all that made me come here, but maybe I'm wrong? I don't know who created the user management. Could be it is part of LO and AOO for a long time. I didn't notice this management, because it isn't working with internal databases and isn't working with MySQL/MariaDB and also PostgreSQL. This bugtracker is the right place to ask for an enhancement. Some bugs will be fixed in a very short time, but an enhancement in Base …
@prrvchr: Basically, if you want something done in Base, you stand more of a chance of getting it if you do it yourself, or else hire someone to do it for you. Currently, I think it is fair to say that the database code is in a state of gradual semi-decline, with the occasional regression-fix made by some very kind volunteers. It is barely "hangin' on in there". Past experience of being a member of Base QA has shown that programming for the database module is a complex, and mostly, thankless task, as pretty much everyone who has been involved at some stage rapidly moves away to other (sexier?) subjects - as a case in point, the development to provide a functional embedded Firebird ODB and corresponding migration tool, which has essentially stalled. All coding contributors and efforts would be most welcome.
That's what I wanted to know, it's better to say things clearly than to leave hope. Otherwise in the end, this bug tracker will lose all credibility and usefulness. If we want to solve this problem, does anyone have information concerning: - How to add a menu entry in Base under User Administration. - How to find the connection in the protocol handler which will manage this new url. Thanks.
(In reply to prrvchr from comment #18) > If we want to solve this problem, does anyone have information concerning: > - How to add a menu entry in Base under User Administration. > - How to find the connection in the protocol handler which will manage this > new url. I see something for hsqldb, here: core/connectivity/source/drivers/hsqldb/HUser.cxx core/connectivity/source/drivers/hsqldb/HUsers.cxx might be the place to start, to try and understand. Presumably, this would have to be implemented/adapted for each of the supported drivers in their respective areas. For the UI part, the UserAdmin dialog seems to be defined here: https://github.com/LibreOffice/core/blob/master/dbaccess/uiconfig/ui/useradminpage.ui and possibly in part here: https://github.com/LibreOffice/core/blob/master/dbaccess/uiconfig/ui/useradmindialog.ui although I'm not really sure how the second UI definition relates to the first (it seems to have a "reset" command, which I don't recall having seen anywhere in the UI).
(In reply to Alex Thurgood from comment #19) To follow on from my last post, if I look at what's available for mysql, for example: core/connectivity/source/drivers/mysqlc/mysqlc_user.cxx it seems that pretty much any and all user/privilege/role functionality requires implementation, as pointed out in the stubs in the code.
(In reply to Alex Thurgood from comment #20) > > To follow on from my last post, if I look at what's available for mysql, for > example: > > core/connectivity/source/drivers/mysqlc/mysqlc_user.cxx > > it seems that pretty much any and all user/privilege/role functionality > requires implementation, as pointed out in the stubs in the code. But: The database does not support user administration. at /home/buildslave/source/libo-core/dbaccess/source/ui/dlg/UserAdminDlg.cxx:94 This message appears with direct connection to MariaDB.
I have just finished an implementation in Base of the management of roles and users of the underlying database. https://prrvchr.github.io/jdbcDriverOOo/#libreofficeopenoffice-base-improvement This has only been tested with HsqlDB, malfunctions are to be expected with H2 and Derby or any other JDBC driver, this remains to be tested... Voila, I hope this can be useful...
I have just released version 1.3.1 of jdbcDriverOOo. This version is capable of managing users and roles for 6 different databases. Modifications will be necessary if we want the list of privileges managed by Base to correspond to the privileges supported by the underlying database driver. I will propose a Fix to make this possible.