Database Schema and Example SQL for Crowd

Changed:

There were significant changes to the Crowd database schema in Crowd 2.0. The information below applies to Crowd 2.0 and later. The earlier schema documentation applies to Crowd 1.6 and earlier.

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

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

1
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

1
2
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

1
2
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

1
2
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

1
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.

``` sql update cwd_user set credential='x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==' where user_name=''; ```

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