Skip to Content

Changes for page org.clazzes.login.sql

Last modified by christoph_lechleitner@iteg_at on 2013-07-15 01.30:42
From version 3.1
edited by christoph_lechleitner@iteg_at
on 2013-01-18 05.27:29
Change comment: Added deactivateUserStatement, renamted and extended group members query
To version 8.1
edited by christoph_lechleitner@iteg_at
on 2013-02-12 05.00:28
Change comment: documenting query string defaults as of upcoming 1.1.0 release

Summary

Details

Page properties
Title
... ... @@ -1,1 +1,1 @@
1 -org.clazzes.login.sql (work in progress)
1 +org.clazzes.login.sql
Content
... ... @@ -22,12 +22,14 @@
22 22  
23 23  === {{id name="org.clazzes.login.sql-Configuration"/}}Configuration ===
24 24  
25 -The SQL login service may be configured using the OSGi configuration PID {{code language="none"}}org.clazzes.login.sql{{/code}} using these configuration values:
25 +The SQL login service may be configured using the OSGi configuration PID {{code language="none"}}org.clazzes.login.sql{{/code}} using the configuration values shown in the table below.
26 26  
27 +Beginning with the upcoming 1.1.0 release, all query strings default to the database structure used by the upcoming [[SDS (SQL Directory Service)>>confluencePage:page:LOGIN.SDS (SQL Directory Service) (work in progress)]] bundle. When using another database structure that does not allow some of the queries, it is important set those configuration values to empty strings; deleting them will not help because default values would kick in right away.
28 +
29 +1.1.0 will also start to "naturally" order resulting lists, so there is no need to use ORDER BY clauses which often provoke temporary tables and filesort.
30 +
27 27  (% class="wiki-content" %)
28 28  (((
29 -
30 -
31 31  |=(((
32 32  (% class="tablesorter-header-inner" %)
33 33  (((
... ... @@ -44,12 +44,12 @@
44 44  deactivateUserStatement
45 45  {{/code}}
46 46  )))|(((
47 -Not implemented yet. Optional. Required for //deactivateUser// feature.
49 +Required non-empty for //deactivateUser// feature.
48 48  
49 49  SQL template for a prepared statement to deactivate a user.
50 50  
51 -Example:
52 -{{code language="none"}}UPDATE users SET encryptedPassword='{disabled}' WHERE userId=?{{/code}}
53 +Default, appropriate for SDS' tables:
54 +{{code language="none"}}UPDATE USERS SET PASSWORD='{disabled}' WHERE USERID=?{{/code}}
53 53  )))
54 54  |(((
55 55  {{code language="none"}}
... ... @@ -58,7 +58,11 @@
58 58  )))|(((
59 59  Optional. Defaults to an empty string.
60 60  
61 -The login domain to use for principals when the request did not contain a domain.
63 +If set to an empty string, the domain parameter of request queries does not get checked but is filled in in returned principals.
64 +
65 +If set to a non-empty string, requests for other domains are refused.
66 +
67 +Support for databases maintaining multiple authentication domains might be added in the future, but I do not believe multi-domain setups even exist outside the LDAP/ADS world.
62 62  )))
63 63  |(((
64 64  {{code language="none"}}
... ... @@ -65,16 +65,12 @@
65 65  groupsByUserIdQuery
66 66  {{/code}}
67 67  )))|(((
68 -Optional. Required for //getGroups// feature.
74 +Required non-empty for //getGroups// feature.
69 69  
70 70  SQL template for a prepared statement to query the group IDs and group names of the groups of which the user specified by a userId is a member.
71 71  
72 -Example:
73 -{{code language="none"}}SELECT g.groupId, g.groupName FROM groups AS g, users AS u, memberships AS mgroupName
74 - WHERE u.userId=?
75 -AND m.userId = u.id
76 - AND g.id = m.groupId
77 - ORDER BY g.groupId {{/code}}
78 +Default, appropriate for SDS' tables:
79 +{{code language="none"}}SELECT g.GROUPID, g.GROUPNAME FROM GROUPS AS g, USERS AS u, GROUPMEMBERSHIPS AS m WHERE u.USERID='?' AND m.USER_ID = u.ID AND g.ID = m.GROUP_ID{{/code}}
78 78  )))
79 79  |(((
80 80  {{code language="none"}}
... ... @@ -95,12 +95,12 @@
95 95  setUserPasswordStatement
96 96  {{/code}}
97 97  )))|(((
98 -Optional. Required for //changePassword// feature.
100 +Required non-empty for //changePassword// feature.
99 99  
100 100  SQL template for a prepared statement to set a new password for the user.
101 101  
102 -Example:
103 -{{code language="none"}}UPDATE users SET encryptedPassword=? WHERE userId=?{{/code}}
104 +Default, appropriate for SDS' tables:
105 +{{code language="none"}}UPDATE USERS SET PASSWORD=? WHERE USERID=?{{/code}}
104 104  )))
105 105  |(((
106 106  {{code language="none"}}
... ... @@ -112,7 +112,7 @@
112 112  If the e-mail address is not part of the database, use a constant like '' or null.
113 113  
114 114  Example:
115 -{{code language="none"}}SELECT userId, encryptedPassword, userName, mailAdr FROM users WHERE userId=?{{/code}}
117 +{{code language="none"}}SELECT USERID, PASSWORD, USERNAME, EMAIL FROM USERS WHERE USERID=?{{/code}}
116 116  )))
117 117  |(((
118 118  {{code language="none"}}
... ... @@ -119,16 +119,11 @@
119 119  usersByGroupIdQuery
120 120  {{/code}}
121 121  )))|(((
122 -Optional. Required for //getGroupMembers// feature.
124 +Required non-empty for //getGroupMembers// feature.
123 123  
124 124  SQL template for a prepared statement to query the user IDs, user names and e-mail-addresses of the members of the group specified by a groupId.
125 125  
126 126  Example:
127 -{{code language="none"}}SELECT u.userId, u.userName, u.mailAdr
128 - FROM groups AS g, users AS u, memberships AS mgroupName
129 - WHERE g.groupId=?
130 -AND m.groupId = g.id
131 - AND u.id = m.userId
132 - ORDER BY g.userId {{/code}}
129 +{{code language="none"}}SELECT u.USERID, u.USERNAME, u.EMAIL FROM GROUPS AS g, USERS AS u, GROUPMEMBERSHIPS AS m WHERE g.GROUPID=? AND m.GROUP_ID = g.ID AND u.ID = m.USER_ID{{/code}}
133 133  )))
134 134  )))
Confluence.Code.ConfluencePageClass[0]
Id
... ... @@ -1,1 +1,1 @@
1 -688787
1 +688797
Title
... ... @@ -1,1 +1,1 @@
1 -org.clazzes.login.sql (work in progress)
1 +org.clazzes.login.sql
URL
... ... @@ -1,1 +1,1 @@
1 -https://clazzes.atlassian.net/wiki/spaces/LOGIN/pages/688787/org.clazzes.login.sql (work in progress)
1 +https://clazzes.atlassian.net/wiki/spaces/LOGIN/pages/688797/org.clazzes.login.sql