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 9.1
edited by christoph_lechleitner@iteg_at
on 2013-02-13 11.31:05
Change comment: 1.1.0 released
To version 10.1
edited by christoph_lechleitner@iteg_at
on 2013-07-15 01.30:42
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -1,5 +1,7 @@
1 -The jaas-login-service is provided as an OSGi bundle, which may be activated by
1 += {{id name="org.clazzes.login.sql-Introduction"/}}Introduction =
2 2  
3 +The {{code language="none"}}sql-login-service{{/code}} is provided as an OSGi bundle, which may be activated by
4 +
3 3  (% class="code" %)
4 4  (((
5 5  obr:addurl http:~/~/maven.clazzes.org/repository.xml
... ... @@ -14,118 +14,18 @@
14 14  <artifactId>sql-login-service</artifactId>
15 15  )))
16 16  
17 -=== {{id name="org.clazzes.login.sql-Functionality"/}}Functionality ===
19 += {{id name="org.clazzes.login.sql-Functionality"/}}Functionality =
18 18  
19 19  The SQL login service authenticates against users in a SQL database, using configurable SQL queries.
20 20  
21 21  There are no plans to support authentication domains, because typical standalone login systems do not care for domains.
22 22  
23 -=== {{id name="org.clazzes.login.sql-Configuration"/}}Configuration ===
25 += {{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 the configuration values shown in the table below.
27 +With version 1.2 we introducted a new feature (support for multiple authentication domains covered by distinct databases) and switched to [[JDBC-Provider>>doc:UTIL.Multi-DataSource pooling with JDBC-Provider.WebHome]] as DataSource pool.
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.
29 +Therefor the configuration depends on the version of {{code language="none"}}sql-login-service{{/code}} used:
28 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 -
31 -(% class="wiki-content" %)
32 -(((
33 -|=(((
34 -(% class="tablesorter-header-inner" %)
35 -(((
36 -Key
37 -)))
38 -)))|=(((
39 -(% class="tablesorter-header-inner" %)
40 -(((
41 -Description
42 -)))
43 -)))
44 -|(((
45 -{{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"}}
58 -defaultDomain
59 -{{/code}}
60 -)))|(((
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}}
73 -)))|(((
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}}
80 -)))
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}}
94 -)))
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 -)))
31 +(% style="list-style-type: square;" %)
32 +* [[doc:LOGIN.DomainPasswordLoginService implementations and backends.org\.clazzes\.login\.sql.Configuring org\.clazzes\.login\.sql 1\.0 to 1\.1.WebHome]]
33 +* [[doc:LOGIN.DomainPasswordLoginService implementations and backends.org\.clazzes\.login\.sql.Configuring org\.clazzes\.login\.sql 1\.2+.WebHome]]
Confluence.Code.ConfluencePageClass[0]
Id
... ... @@ -1,1 +1,1 @@
1 -688781
1 +688660
URL
... ... @@ -1,1 +1,1 @@
1 -https://clazzes.atlassian.net/wiki/spaces/LOGIN/pages/688781/org.clazzes.login.sql
1 +https://clazzes.atlassian.net/wiki/spaces/LOGIN/pages/688660/org.clazzes.login.sql