web user security management UX to a SQL server

[jrodder]

OK, so I am going to ask for some direction from this wonderful group once more. I am struggling to understand what I need to be looking at in order to get a UX component created to deal with user management from within the application itself. I have implemented web security, and it works, and SQL is holding the relevant tables: webSecurityUsers, Members and Groups. From looking at the tables in SQL Studio I can see the 'relationships' exist as members contains the GUIDs of the Users and Groups tables. I have tried opening up the UX component template for SecurityFramework-CreateNewUserAccount_and_AddUserInformationRelatedToTable. It looks like I have to then redo the binding to correctly match what exists on SQL, right?

This is where my ignorance comes in, I am basically trying to learn T-SQL, JavaScript, and Alpha all at the same time coming from no real previous experience in any of them. When looking at the data binding, I cannot select multiple tables to bind to unless I also link them with related fields. I thought I could do that after discovering how the tables are related (even though they don't show relations within SQL) but when trying to do a one-to-many Alpha tells me I need to add repeating sections.

Can anyone give me a few minutes of time and tell me how I should be attacking this? I have done some searching around and browsing videos, but this is one of those times I wish AA had an actual manual and not 942 hours of 5 minute video chunks as reference materials. I suppose it's not as big of a deal for actual developers though, I'm not exactly the target audience for the platform I would presume.

[RichCPT]

Re: web user security management UX to a SQL server

I don't know if this will be any help, I might lead you down a path that takes a lot more work. What I did was to break things down into separate pieces where I mostly use plain xbasic and AlphaDAO to manipulate the DB instead of any action scripting. The one exception is for editing WebSecurityMembers table.

1. I placed a simple LIST control on a UX. The LIST uses a SQL datasource against the WebSecurityUsers table and one additional companion table that I use to store a little extra information about each user. Trying to update this companion table as well as Alpha's table at the same time is probably why I avoided action scripting.

2. I placed buttons for manipulating the LIST on the bottom of the UX to include functions like the following: 1) Add User, 2) Change User ID, 3) Change User Name, 4) Change User Password, etc. These buttons all open a container window within this same UX. There's a button on each container window for calling an xbasic Ajax Callback function that validates the data and executes the necessary AlphaDAO SQL to affect the change. No databinding to worry about, just plain xbasic.

3. I thought implementing the WebSecurityMembers table would be the most difficult, but using action scripting in a separate UX made things work easily. To access this UX, I placed a "Security Permissions" button on the main UX. The button opens the UX and displays the security permissions for the selected user from the main UX. Action scripting takes care of setting the initial checkboxes of the assigned groups for the user. After submitting any changes, action scripting takes care of deleting and inserting records into the WebSecurityMembers table as needed based on the checkbox control.

The UX is bound to my companion user login table that has the user's firstname, lastname, last sign-on time, etc. which is displayed on the top of the UX.

There is a checkbox control for displaying the user's security group assignments. It has these properties:

Variable Name: SECGRPS
Choices: variable = UserRoles
Orientation: Columns  ; flow: TBLR;   Row Count 18;
Not in list rule: Add
Table Alias: *unbound

I do the following in the Server Side OnDialogInitialize Event for the UX:

	'... set alphaDAO connection info for cn, tbl, fltr, rtnFld fields ...
	e.rtc.UserRoles = sql_get_values(cn,tbl,fltr,rtnFld,.t.)
	'-- use action script to load user login display values, such as name and ID
	ExecuteServerSideAction("Populate Dialog from Tables::Sec_Access_Load_UL")
	'-- use action script to load the checkbox control for security group assignments
	ExecuteServerSideAction("Load Web Security Values::Load_Web_Security_Values")

Server side dialogValidate event:

	'... code to validate security group assignments; in my system I have a safety check to ensure some security tags can only be granted by certain users.

Server side afterDialogValidate event:

	ExecuteServerSideAction("Save Web Security Values::Save_Web_Security_Values")

[jrodder]

Re: web user security management UX to a SQL server

That does seem like a valid way to attack it, I guess since I had seen the template containing a template for doing multiple table entries at once I was assuming it wasn't that big of a deal to implement, and I was just ignorant of how to set data bindings properly. Your solution also seems likely above my abilities at present. This leaves me with a question as to how to populate the GUID, to which it seems is the common linkage between the webSecurity tables. I appreciate your help in any case!

*edit*

Ah well I did find this video that looks like it should help me with the GUID. https://www.youtube.com/watch?v=UxVREFQcjLg
If I can manage to at least alter the individual tables with popup UXes linked to buttons having the GUID created and linked, that *should* accomplish what I'm trying to do. More involved than I originally imagined but at least seeing the light at the end of the tunnel.

The last thing I may have to change if it can't be answered here, is how to use the UX to store the password encrypted as happens when making the users from within the AA platform. It's not a huge deal since this app wont exactly be hypersensitive but would be nice to know.

[RichCPT]

Re: web user security management UX to a SQL server

You can use
a5_decrypt_string(Text as C, Key as C [, Algorithm as C [, Initializer as C ]] )
a5_encrypt_string(Text as C, Key as C [, Algorithm as C [, Initializer as C ]] ).

Do NOT use ENCRYPT_STRING() or DECRYPT_STRING (). There is something weird about the 'key' parameter. The Alpha documentation doesn't explain what characters are allowed or its expected length. It may have a lot to do with which Algorithm is used. I had success using 64 character length string comprised of lower case letters (a - f) and digits. The 'key', of course, must also be set in the Web Security project settings on the "User ID and Password options" tab.

[RichCPT]

Re: web user security management UX to a SQL server

Use "api_uuidcreate()" to create your GUIDs.

[jrodder]

Re: web user security management UX to a SQL server

Use "api_uuidcreate()" to create your GUIDs.

I'm playing with that now, but no love as of yet. I need to see how to make debug(1) produce some output. GUID row in table webSecurityUsers is named 'Guid', and I've inserted DataSubmitted.Guid = api_uuidcreate() into CanInsertRecord but nothing being written to the guid field on a record save. (in a grid component). Looks like I am more in the tinkering stage than totally lost stage, which is progress.

[RichCPT]

Re: web user security management UX to a SQL server

Is this on the Grid part or Grid Detail part?

Make sure the control type for your guid on the update form is either of type "Hidden" or "Textbox".

Try setting the "Field Update Setting" to "All Fields" or maybe you have to add the guid field to a "dirty field" list, if you have the update setting as "Dirty Fields Only".

Turn on "Show Update Command" if you are using Grid Detail for this update.

When using debug(1) in Working Preview (or Live Preview) you can use the watch window to look at any variable. So, I don't know what you are looking for when you said, "I need to see how to make debug(1) produce some output".

[jrodder]

Re: web user security management UX to a SQL server

When using debug(1) in Working Preview (or Live Preview) you can use the watch window to look at any variable. So, I don't know what you are looking for when you said, "I need to see how to make debug(1) produce some output".

Apologies, as I had stated before I am highly ignorant of much of the workings of AA, kind of trying to tread water so I struggle when posting to ensure that I don't waste anyone's time, yet it still happens. :) I understood that to try and see why I was getting no output when I should be into the Guid field, I was looking to do a debug to see what was going on. I did figure that out, threw the debug command somewhere else and could bust out what was being sent to the server, of course it was nothing. Not NULL, but blank.

As far as the rest, I will try what you have recommended and see if that helps. I was inserting DataSubmitted.Guid = api_uuidcreate() into the server side event section. (assuming that I needed to append the .fieldname to the DataSubmitted) The Guid field is set to textbox. I am looking for the Field Update Setting in both Grid and Detail part, but not seeing it. I'll keep nosing around. "Show Update Command" was exactly the type of 'debugging' I was looking for. I was just trying to see if I could turn over any rocks.

Am I even putting the api_uuidcreate() in properly you think?

pg1.pngpg2.pngpg3.png

[RichCPT]

Re: web user security management UX to a SQL server

I just tested this in a Updateable Grid based on a SQL statement and this all all I had to do:

function CanInsertRecord as v (DataSubmitted as P, Args as p, PageVariables as p, Result as p)
with PageVariables
Result.Cancel = .f.
Result.ErrorHTML = ""
end with

if DataSubmitted.GUID = ""
	DataSubmitted.GUID = api_uuidcreate()
end if


end function

[RichCPT]

Re: web user security management UX to a SQL server

Try starting over with a new updateable Grid.

[jrodder]

Re: web user security management UX to a SQL server

I just tested this in a Updateable Grid based on a SQL statement and this all all I had to do:

function CanInsertRecord as v (DataSubmitted as P, Args as p, PageVariables as p, Result as p)
with PageVariables
Result.Cancel = .f.
Result.ErrorHTML = ""
end with

if DataSubmitted.GUID = ""
	DataSubmitted.GUID = api_uuidcreate()
end if


end function

Yep, that worked wonderfully. This is why I get frustrated sometimes. Looking at your IF, it now makes total sense. However, I was going off the video pasted earlier in the thread, and that one appeared to be working without any "If blank then fill with this function output" logic. https://youtu.be/UxVREFQcjLg?t=167

So I am assuming that part should have been good, you know? I appreciate your patience with something that is a simple fix. It can be difficult to figure out what code can run where, which variables are passed where, where to best stuff code, etc. I am slowly learning especially after getting working examples from people like yourself and David K. Thanks!

[RichCPT]

Re: web user security management UX to a SQL server

Oh, I'm sure that it wasn't the 'if DataSubmitted.GUID = ""' that made it work. It wouldn't surprise me though, if it was the fact that my code occurs after the "end with". I've had nothing but trouble putting code inside that "with" block, so I just ignore it and put all my code after it.

One thing I have to keep reminding myself: when something isn't working right in Alpha, always try another way. When something fails in Working Preview then try running the application against the development server. When a component doesn't work right, start over with a brand new component and built up just the part that doesn't work from the original. There is a "disconnect" between the Alpha component designers and the server, such that properties that have become hidden in the component (based on other property values) can influence what happens when running the component. Hours can be spent trying to figure why a component is doing something that it shouldn't be only to discover there is some property in the designer that has become hidden that has to be unhidden and then changed!