Database Schema and Example SQL for Crowd

On this page:

Crowd Database Schema

Please refer to the diagram of the Crowd database schema.

Crowd Database Table Information

名前	説明
アプリケーション	All applications listed in Crowd.
APPLICATIONADDRESSES	各アプリに現在割り当てられているリモートアドレス。
APPLICATIONCREDENTIALS	Hashed version of passwords used for each application. This should match the non-hashed version in the application's `crowd.properties` file.
APPLICATIONDIRECTORIES	各アプリに割り当てられているディレクトリ。
APPLICATIONGROUPS	各アプリに割り当てられているグループ。
ATTRIBUTES	Attributes of directories, applications and users.
ATTRIBUTEVALUES	Actual values of directory, application and user attributes.
ディレクトリ	All directories listed in Crowd.
DIRECTORYPERMISSIONS	ディレクトリからユーザー、グループ、ロールを追加、変更、削除する権限。
PRINCIPALCREDENTIALHISTORY
REMOTEGROUP	Groups from internal directories.
REMOTEGROUPMEMBERS	Group members from internal directories.
REMOTEPRINCIPAL	Users from internal directories.
REMOTEPRINCIPALCREDENTIALS	各ユーザーのハッシュ化されたパスワード。
REMOTEROLE	Roles from internal directories.
REMOTEROLEMEMBERS	Role members from internal directories.
SERVERPROPERTY	Various server properties. Names are stored as long (L) values.
TOKEN	User and application session tokens.
applicationdirectorypermission	ディレクトリからユーザー、グループ、ロールを追加、変更、削除するためのアプリレベルの権限。
hibernate_unique_key	Values for ResettableTableHiLoGenerator.

Example SQL Queries

Examples based on PostgreSQL

The following examples are written for a PostgreSQL database. SQL syntax may vary for other databases.

Examples in this section:

Finding Users that are Members of a Group
Finding Attributes for a Specific Directory
Finding Attributes for a Specific User
Finding Attributes for a Specific Application
Finding the Users which Belong to a Group
Finding the Groups which have Administrative Access to Crowd

Finding Users that are Members of a Group

select REMOTEPRINCIPALNAME from REMOTEGROUPMEMBERS where REMOTEGROUPNAME = '<group-name>';

Where <group-name> is the name of the desired group, e.g. crowd-administrators.

Finding Attributes for a Specific Directory

select directory."NAME" as directory,"ATTRIBUTES"."ATTRIBUTE" as attribute, "ATTRIBUTEVALUES"."VALUE" as value 
from "ATTRIBUTEVALUES", "ATTRIBUTES", (select * from "DIRECTORY" where "NAME" = '<directory_name>') AS directory 
where "ATTRIBUTES"."ID" = "ATTRIBUTEVALUES"."ATTRIBUTEVALUEID" 
AND "ATTRIBUTES"."DIRECTORYID"= directory."ID";

Where <directory_name> is the name of the desired directory.

Finding Attributes for a Specific User

select "REMOTEPRINCIPALNAME" as user,"ATTRIBUTES"."ATTRIBUTE" as attribute, "ATTRIBUTEVALUES"."VALUE" as value 
from "ATTRIBUTEVALUES", "ATTRIBUTES"
where "ATTRIBUTES"."ID" = "ATTRIBUTEVALUES"."ATTRIBUTEVALUEID" 
AND "REMOTEPRINCIPALNAME" = '<username>';

Where <username> is the account name of the desired user.

Finding Attributes for a Specific Application

select application."NAME" as application_name, "ATTRIBUTES"."ATTRIBUTE" as attribute, "ATTRIBUTEVALUES"."VALUE" as value
from "ATTRIBUTEVALUES", "ATTRIBUTES", (select * from "APPLICATION" where "NAME" = '<application_name>') as application 
where "ATTRIBUTES"."ID" = "ATTRIBUTEVALUES"."ATTRIBUTEVALUEID" 
AND "ATTRIBUTES"."APPLICATIONID"= application."ID";

Where <application_name> is the name of the desired application.

Finding the Users which Belong to a Group

select "REMOTEPRINCIPALNAME", "REMOTEGROUPNAME" from "REMOTEGROUPMEMBERS"
where "REMOTEGROUPNAME" = '<group_name>';

Where <group_name> is the name of the desired group, e.g. crowd-administrators.

Finding the Groups which have Administrative Access to Crowd

select "APPLICATIONGROUPS"."NAME" 
from "APPLICATIONGROUPS", (select * from "APPLICATION" where "NAME" = 'crowd') as application 
where "APPLICATIONGROUPS"."APPLICATIONID" = application."ID";

Other Useful SQL Commands

Important --- Back Up your Database!

Before making changes to the Crowd database via SQL, please ensure you have an immediate backup of the Crowd database.