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

Name	Description
APPLICATION	All applications listed in Crowd.
APPLICATIONADDRESSES	Remote addresses currently assigned to each application.
APPLICATIONCREDENTIALS	Hashed version of passwords used for each application. This should match the non-hashed version in the application's `crowd.properties` file.
APPLICATIONDIRECTORIES	Directories assigned to each application.
APPLICATIONGROUPS	Groups assigned to each application.
ATTRIBUTES	Attributes of directories, applications and users.
ATTRIBUTEVALUES	Actual values of directory, application and user attributes.
DIRECTORY	All directories listed in Crowd.
DIRECTORYPERMISSIONS	Permissions for adding, modifying and removing users, groups and roles from a directory.
PRINCIPALCREDENTIALHISTORY
REMOTEGROUP	Groups from internal directories.
REMOTEGROUPMEMBERS	Group members from internal directories.
REMOTEPRINCIPAL	Users from internal directories.
REMOTEPRINCIPALCREDENTIALS	Hashed passwords for each user.
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	Application-level permissions for adding, modifying and removing users, groups and roles from a directory.
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.