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 1.1
edited by christoph_lechleitner@iteg_at
on 2013-01-18 02.49:21
Change comment: There is no comment for this version
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
... ... @@ -16,12 +16,18 @@
16 16  
17 17  === {{id name="org.clazzes.login.sql-Functionality"/}}Functionality ===
18 18  
19 -The SQL login service authenticates against users in a SQL database.
19 +The SQL login service authenticates against users in a SQL database, using configurable SQL queries.
20 20  
21 +There are no plans to support authentication domains, because typical standalone login systems do not care for domains.
22 +
21 21  === {{id name="org.clazzes.login.sql-Configuration"/}}Configuration ===
22 22  
23 -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.
24 24  
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 +
25 25  (% class="wiki-content" %)
26 26  (((
27 27  |=(((
... ... @@ -32,22 +32,94 @@
32 32  )))|=(((
33 33  (% class="tablesorter-header-inner" %)
34 34  (((
35 -Default Value
36 -)))
37 -)))|=(((
38 -(% class="tablesorter-header-inner" %)
39 -(((
40 40  Description
41 41  )))
42 42  )))
43 43  |(((
44 44  {{code language="none"}}
46 +deactivateUserStatement
47 +{{/code}}
48 +)))|(((
49 +Required non-empty for //deactivateUser// feature.
50 +
51 +SQL template for a prepared statement to deactivate a user.
52 +
53 +Default, appropriate for SDS' tables:
54 +{{code language="none"}}UPDATE USERS SET PASSWORD='{disabled}' WHERE USERID=?{{/code}}
55 +)))
56 +|(((
57 +{{code language="none"}}
45 45  defaultDomain
46 46  {{/code}}
47 47  )))|(((
48 -(% style="font-family: monospace;" %)-
61 +Optional. Defaults to an empty string.
62 +
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.
68 +)))
69 +|(((
70 +{{code language="none"}}
71 +groupsByUserIdQuery
72 +{{/code}}
49 49  )))|(((
50 -The login domain to use for principals, which do not contain a domain.
51 -May be left null for domain-less databases
74 +Required non-empty for //getGroups// feature.
75 +
76 +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.
77 +
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}}
52 52  )))
81 +|(((
82 +{{code language="none"}}
83 +defaultPasswordAlgorithm
84 +{{/code}}
85 +)))|(((
86 +Optional. Defaults to {{code language="none"}}crypt{{/code}}
87 +
88 +Values supported so far: {{code language="none"}}crypt{{/code}}, {{code language="none"}}ssha1{{/code}}, {{code language="none"}}plain{{/code}}.
89 +
90 +Password fields may contain:
91 +
92 +* the password encrypted using the default password algorithm, or
93 +* a LDAP style algorithm prefix and the password encrypted with the algorithm specified in the prefix. Example: {{code language="none"}}{PLAIN}badPassword{{/code}}
53 53  )))
95 +|(((
96 +{{code language="none"}}
97 +setUserPasswordStatement
98 +{{/code}}
99 +)))|(((
100 +Required non-empty for //changePassword// feature.
101 +
102 +SQL template for a prepared statement to set a new password for the user.
103 +
104 +Default, appropriate for SDS' tables:
105 +{{code language="none"}}UPDATE USERS SET PASSWORD=? WHERE USERID=?{{/code}}
106 +)))
107 +|(((
108 +{{code language="none"}}
109 +userByUserIdQuery
110 +{{/code}}
111 +)))|(((
112 +SQL template for a prepared statement to query userId, encrypted password, pretty name and e-mail address of a user specified by a userId.
113 +If the pretty name is not part of the database, reuse the userId field.
114 +If the e-mail address is not part of the database, use a constant like '' or null.
115 +
116 +Example:
117 +{{code language="none"}}SELECT USERID, PASSWORD, USERNAME, EMAIL FROM USERS WHERE USERID=?{{/code}}
118 +)))
119 +|(((
120 +{{code language="none"}}
121 +usersByGroupIdQuery
122 +{{/code}}
123 +)))|(((
124 +Required non-empty for //getGroupMembers// feature.
125 +
126 +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.
127 +
128 +Example:
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}}
130 +)))
131 +)))
Confluence.Code.ConfluencePageClass[0]
Id
... ... @@ -1,1 +1,1 @@
1 -688785
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/688785/org.clazzes.login.sql (work in progress)
1 +https://clazzes.atlassian.net/wiki/spaces/LOGIN/pages/688797/org.clazzes.login.sql