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 9.1
edited by christoph_lechleitner@iteg_at
on 2013-02-13 11.31:05
Change comment: 1.1.0 released

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 version 1.1.0 (released 2013-02-13), 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 +Results of list queries (group memberships, group members) are sorted naturally in the Java layer, so there is no need to use ORDER BY clauses. ORDER BY clauses often provoke temporary tables and filesort, which is quite expensive for queries used quite often.
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 +688781
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/688781/org.clazzes.login.sql