Hi,
HR database has a table storing employee data, if a new hire comes along we need to create a user in the backend by going to Tools SQL and run CREATE USER “max” PASSWORD “groucho” ADMIN or similar.
Could I get both name as well as initial password from the table, store them to a variable and then run the CREATE USER bit as an sqlstring in a macro instead ?
Likewise if the hire leaves could I have a drop user button in the employee form ?
Thanks for your thoughts.
Hello,
Yes this can be done but it seems terrible to me - storing and retrieving passwords that are plain text???
There are many posts here which describe running SQL within a macro. Used this one for a test:
Using your example, the sSQL statement in the example would be replaced with:
Rem Create User
sSQL = "CREATE USER ""max"" PASSWORD ""groucho"" ADMIN"
Rem Drop user
sSQL = "DROP USER ""max"""
The actual text can be replaced with variables. For example:
Rem Create User
sSQL = "CREATE USER " & sName & " PASSWORD " & sPassword & " ADMIN"
Rem Drop user
sSQL = "DROP USER " & sName
which you have retrieved from your source.
For the form you can have one list box to select the employee and have a button to Create or Drop that employee. Add code to retrieve the selected item in the list box and use that in an SQL statement to retrieve all information. Some links to examples for SQL can be found in my answer on this post → In base Convert Query to macro
You can also list the current users with this Query:
SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_USERS"
Tests were done on Ubuntu 18.04 using LO v6.4.0.3 and HSQLDB split v2.3.2
Works like a treat ! just noted when creating user manually from Tools, SQL I get lower case, using above code with variable defaults to UPPER case, but that ok.