This page last changed on Jul 29, 2009 by smaddox.

This page contains information about the Crowd database tables and some example SQL queries.

On this page:

Crowd Database Schema

Please refer to the diagram of the Crowd database schema.

Crowd Database Table Information

Name Description
cwd_application All applications listed in Crowd.
cwd_application_address Remote addresses currently assigned to each application.
cwd_application_alias Alias information for a user, see the alias documentation for more information
cwd_app_dir_operation Application-level permissions for adding, modifying and removing users, groups and roles from a directory.
cwd_application_attribute Attributes for an application.
cwd_app_dir_mapping Directories assigned to each application.
cwd_app_dir_group_mapping Groups assigned to each application.
cwd_directory All directories listed in Crowd.
cwd_directory_attribute Attributes for a directory.
cwd_directory_operation Permissions for adding, modifying and removing users, groups and roles from a directory.
cwd_group Groups from internal directories.
cwd_group_attribute Attributes for a group.
cwd_user Users from internal directories.
cwd_user_attribute Attributes for a user.
cwd_user_credential_record Hashed passwords for each user.
cwd_membership Group members from internal directories.
cwd_token User and application session tokens.
cwd_property Various server properties. Names are stored as long (L) values.
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

select child_name from cwd_membership where parent_name = '<group-name>' and membership_type='GROUP_USER' and group_type='GROUP';

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

Finding Attributes for a Specific Directory

select directory_name, attribute_name, attribute_value from cwd_directory, cwd_directory_attribute where cwd_directory.id=cwd_directory_attribute.directory_id
and directory_name='<directory_name>';

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

Finding Attributes for a Specific User

select user_name, attribute_name, attribute_value from cwd_user, cwd_user_attribute where cwd_user.id=cwd_user_attribute.user_id
and user_name = '<username>';

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

Finding Attributes for a Specific Application

select application_name, attribute_name, attribute_value from cwd_application, cwd_application_attribute where cwd_application.id=cwd_application_attribute.application_id
and application_name = '<application_name>'

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

Finding the Groups which have Administrative Access to Crowd

select group_name from cwd_app_dir_group_mapping where application_id = (select id from cwd_application where application_name='crowd')

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.

Examples in this section:

Resetting a User's Password

The example below resets a user's password to "admin" (no quotes):

The hashed password below is using the Atlassian-SHA1 algorithm. Please make sure you are using the same algorithm before running this SQL on your 'admin' user.
update cwd_user set credential='x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='
where user_name='<username>';

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

RELATED TOPICS
Crowd Database Schema (Crowd 2.0)
Resetting the Domain Cookie Value (Crowd 2.0)
Recovering your Console application password (Crowd 2.0)
Database Schema and Example SQL for Crowd (Crowd 2.0)
Document generated by Confluence on Jul 30, 2009 01:30