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.
Please refer to the diagram of the Crowd database schema.
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. |
Examples based on PostgreSQL
The following examples are written for a PostgreSQL database. SQL syntax may vary for other databases.
1 2select 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
.
1 2select 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.
1 2select 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.
1 2select 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.
1 2select group_name from cwd_app_dir_group_mapping where application_id = (select id from cwd_application where application_name='crowd')
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.
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.
Where <username>
is the account name of the desired user.
Rate this page: