Tuesday, April 28, 2015

WSO2 Identity Server Data Dictionary [Introduction to Database in WSO2 Identity Server]

The database scripts for WSO2 Identity Server can be found in <IS_HOME>/descripts directory. Following databases are currently supported and for each type of database, a separate file is included in dbscripts that contains SQL commands for database and table creation.

  • IBM DB2
  • Oracle
  • Oracle RAC
  • MySQL
  • H2
  • Microsoft SQL Server
  • Informix
  • PostgreSQL
  • OpenEdge

Following is the folder structure of the <IS_HOME>/dbscripts directory where generic database table creation scripts are directly located inside the dbscripts directory and identity specific table creation scripts are located inside <IS_HOME>/dbscripts/identity directory. Further, the table creation database scripts related to the identity related application management are located inside <IS_HOME>/dbscripts/identity/application-mgt directory.

dbscripts/
├── db2.sql
├── derby.sql
├── h2.sql
├── identity
│   ├── application-mgt
│   │   ├── db2.sql
│   │   ├── h2.sql
│   │   ├── informix.sql
│   │   ├── mssql.sql
│   │   ├── mysql.sql
│   │   ├── oracle_rac.sql
│   │   ├── oracle.sql
│   │   └── postgresql.sql
│   ├── db2.sql
│   ├── h2.sql
│   ├── informix.sql
│   ├── mssql.sql
│   ├── mysql.sql
│   ├── oracle_rac.sql
│   ├── oracle.sql
│   └── postgresql.sql
├── informix.sql
├── mssql.sql
├── mysql_cluster.sql
├── mysql.sql
├── openedge.sql
├── oracle_rac.sql
├── oracle.sql
└── postgresql.sql

The data tables in Identity Server can be categorized to several groups. They are listed below with diagrams showing how the relationships are spread among the tables.


Registry Related Tables

  • REG_CLUSTER_LOCK
If you are using Registry separation implementation and sharing configuration and governance registries among cluster nodes of any WSO2 product, when a node is restarting locks can be defined to avoid database schema creation and data population from multiple nodes for JDBC-base databases.These locks are defined in this table.
REG_LOCK_NAME  
REG_LOCK_STATUS
REG_LOCKED_TIME
REG_TENANT_ID  

  • REG_LOG
All registry activities are logged in this table. Details such as the registry resource path, the user who did the modification, the action performed, timestamp and tenant ID of the user are recorded in this table. Following are the columns in the table.
REG_LOG_ID    
REG_PATH      
REG_USER_ID    
REG_LOGGED_TIME
REG_ACTION    
REG_ACTION_DATA
REG_TENANT_ID  


  • REG_PATH
The paths of all registry resources are stored in this table along with an ID for the registry path. From other tables when referring a resource path, this ID is used.

REG_PATH_ID      
REG_PATH_VALUE   
REG_PATH_PARENT_ID
REG_TENANT_ID    

  • REG_CONTENT
Actual content of the registry resources are stored as binary objects in this table. Following are the columns of the table.
REG_CONTENT_ID  
REG_CONTENT_DATA
REG_TENANT_ID

  • REG_CONTENT_HISTORY
History of the actual content of registry resource is maintained in this table. Following are the columns of the table.
REG_CONTENT_ID  
REG_CONTENT_DATA
REG_DELETED    
REG_TENANT_ID

  • REG_RESOURCE
The details about the registry resource are stored in this table. Following are the columns of the table.
REG_PATH_ID        
REG_NAME            
REG_VERSION         
REG_MEDIA_TYPE     
REG_CREATOR         
REG_CREATED_TIME   
REG_LAST_UPDATOR    
REG_LAST_UPDATED_TIME
REG_DESCRIPTION     
REG_CONTENT_ID    
REG_TENANT_ID                    
REG_UUID           

  • REG_RESOURCE_HISTORY
Upon modifying details of the registry resource (not the actual content), history for those details are maintained in this table. Following are the columns of the table.
REG_PATH_ID    
REG_NAME            
REG_VERSION        
REG_MEDIA_TYPE     
REG_CREATOR       
REG_CREATED_TIME    
REG_LAST_UPDATOR   
REG_LAST_UPDATED_TIME
REG_DESCRIPTION    
REG_CONTENT_ID      
REG_DELETED         
REG_TENANT_ID      
REG_UUID


  • REG_COMMENT
The details of a comment  added for a registry resource are stored in this table. Comments in this table are mapped with the records in REG_RESOURCE_COMMENT table. Following are the columns of the table.
REG_ID        
REG_COMMENT_TEXT  
REG_USER_ID    
REG_COMMENTED_TIME
REG_TENANT_ID

  • REG_RESOURCE_COMMENT
For a registry resource, we can add multiple comments for describing the resource. Those comments are stored in this table along with the registry path ID. Following are the columns of the table.
REG_COMMENT_ID   
REG_VERSION     
REG_PATH_ID     
REG_RESOURCE_NAME
REG_TENANT_ID    

  • REG_RATING
Ratings for the registry resources are stored in this table. Following are the columns of the table.
REG_ID       
REG_RATING    
REG_USER_ID    
REG_RATED_TIME
REG_TENANT_ID

  • REG_RESOURCE_RATING
The resources in the registry can be given a rating based on a scale 1-5. Actual rating value is stored in the table REG_RATING and records in this table acts as mappings to the records in REG_RATING.
REG_RATING_ID   
REG_VERSION    
REG_PATH_ID     
REG_RESOURCE_NAME
REG_TENANT_ID

  • REG_TAG
Tags of a registry resource are stored in this table. Following are the columns of the table.
REG_ID        
REG_TAG_NAME    
REG_USER_ID    
REG_TAGGED_TIME
REG_TENANT_ID

  • REG_RESOURCE_TAG
Registry resources can be tagged with a name. Actual tag name is stored in the REG_TAG table. This table is mapping records with the registry path and the records in REG_TAG. Following are the columns of the table.
REG_TAG_ID     
REG_VERSION     
REG_PATH_ID     
REG_RESOURCE_NAME
REG_TENANT_ID


  • REG_PROPERTY
Properties for a registry resource are stored as key value pairs in this table. Following are the columns of the table.
REG_ID     
REG_NAME    
REG_VALUE    
REG_TENANT_ID

  • REG_RESOURCE_PROPERTY
Properties can be added for a registry resource. Actual key value pairs of the properties are stored in REG_PROPERTY table. This table is providing the mapping for the records in REG_PROPERTY table with the resource registry path. Following are the columns of the table.
REG_PROPERTY_ID   
REG_VERSION    
REG_PATH_ID     
REG_RESOURCE_NAME
REG_TENANT_ID

  • REG_ASSOCIATION
An association can be created from any resource (or collection) on the registry to another resource (or collection) on the registry or an external resource which can be referred to by a URL. While the source should be a resource existing on the registry, the destination resource can be anything for which a URL can be given. Association has a type associated.
eg: Document A that confirms to a standard B, You can create an association between those two by stating A "confirms to" B, where association name is "confirms to". The registry associations are stored in this table.

REG_ASSOCIATION_ID   
REG_SOURCEPATH    
REG_TARGETPATH    
REG_ASSOCIATION_TYPE
REG_TENANT_ID

  • REG_SNAPSHOT
This table is used to create track of revisions that you create for a specific resource. Data will be populated upon creating a revision for a resource from the Registry Browser (Creating a checkpoint in the meta data section of a resource). Apart from that, versioning can be done per each change made for a resource which can be an extremely expensive operation at runtime (especially if there are many resources that change). By setting the <versionResourcesOnChange> parameter in <IS_HOME>/repository/conf/registry.xml to true/false, you can enable/disable versioning resources on change. If it is enabled, upon modification to a resource, a new version will be created from the resource and also a new record is added to this table. Following are the columns of the table.
REG_SNAPSHOT_ID   
REG_PATH_ID     
REG_RESOURCE_NAME
REG_RESOURCE_VIDS
REG_TENANT_ID



User Manager Related Tables

  • UM_TENANT
When creating a tenant, the details of the tenant are stored in this table. UM_ID is the auto generated tenant ID. Following are the columns of the table.
UM_ID         
UM_DOMAIN_NAME  
UM_EMAIL      
UM_ACTIVE    
UM_CREATED_DATE
UM_USER_CONFIG

  • UM_DOMAIN
The user store domains of all tenants are stored in this table. By default for a tenant, there are three domains as PRIMARY, SYSTEM and INTERNAL. If a secondary user store is added for a tenant, the user store domain details for that also will get stored in this table.
UM_DOMAIN_ID  
UM_DOMAIN_NAME
UM_TENANT_ID

  • UM_USER
When a JDBC user store is used as a primary or secondary user store, the user details will be stored in this table upon user creation. Following are the columns of the table.
UM_ID           
UM_USER_NAME    
UM_USER_PASSWORD
UM_SALT_VALUE    
UM_REQUIRE_CHANGE
UM_CHANGED_TIME   
UM_TENANT_ID

  • UM_ROLE
When a JDBC user store is used as a primary or secondary user store, the user role details will be stored in this table upon creation of a role. Following are the columns of the table.
UM_ID        
UM_ROLE_NAME  
UM_TENANT_ID   
UM_SHARED_ROLE

  • UM_MODULE
This table is not being used in Identity Server latest version.
UM_ID      
UM_MODULE_NAME

  • UM_MODULE_ACTIONS
This table is not being used in Identity Server latest version.
UM_ACTION   
UM_MODULE_ID

  • UM_PERMISSION
The permission tree is stored in this table. These are the permissions to be assigned for user roles. Following are the columns of the table.
UM_ID        
UM_RESOURCE_ID
UM_ACTION   
UM_TENANT_ID   
UM_MODULE_ID

  • UM_ROLE_PERMISSION
All the permissions assigned to a role are stored in this table. A role can have multiple records in this table and each record is associated with a particular permission defined by UM_PERMISSION_ID. This permission ID is linked to UM_PERMISSION table. Following are the columns of the table.  
UM_ID         
UM_PERMISSION_ID
UM_ROLE_NAME   
UM_IS_ALLOWED    
UM_TENANT_ID   
UM_DOMAIN_ID

  • UM_USER_PERMISSION
This table is not used in the latest Identity Server version. Following are the columns of the table.
UM_ID          
UM_PERMISSION_ID
UM_USER_NAME   
UM_IS_ALLOWED    
UM_TENANT_ID


  • UM_USER_ROLE
The relationship with users and roles is stored in this table. One user can have multiple roles assigned and similarly, one role can have multiple users assigned into it. User is mapped with UM_USER_ID and the role is mapped with UM_ROLE_ID. Following are the columns of the table.
UM_ID    
UM_ROLE_ID  
UM_USER_ID  
UM_TENANT_ID

  • UM_SHARED_USER_ROLE
This table is not being used in the latest version of the Identity Server.
UM_ROLE_ID      
UM_USER_ID      
UM_USER_TENANT_ID
UM_ROLE_TENANT_ID

  • UM_ACCOUNT_MAPPING
This table is not being used in the latest version of the Identity Server.
UM_ID              
UM_USER_NAME    
UM_TENANT_ID       
UM_USER_STORE_DOMAIN
UM_ACC_LINK_ID


  • UM_USER_ATTRIBUTE
When a JDBC user store is used and a user is created in that user store, attributes can be added for the user profile. Each attribute will have a record in the table as key value pairs where UM_ATTR_NAME and UM_ATTR_VALUE columns contain the attribute name and value respectively. UM_USER_ID column contains the ID of the user which points to the UM_ID column of the UM_USER table. If a JDBC user store is used, multiple profiles can be created for a user. The profile that the user attribute belongs is given in UM_PROFILE_ID column. Following are the columns of the table.
UM_ID      
UM_ATTR_NAME  
UM_ATTR_VALUE
UM_PROFILE_ID
UM_USER_ID    
UM_TENANT_ID

  • UM_DIALECT
In claim management, all the claims are grouped into dialects. A claim dialect is a group of claims. One claim dialect can have multiple claims. Those claims are stored in UM_CLAIM table. The claim dialects are stored in this table. Following are the columns of the table.
UM_ID        
UM_DIALECT_URI
UM_TENANT_ID

  • UM_CLAIM
All the claims of all claim dialects are stored in this table. Following are the columns of the table.
UM_ID                 
UM_DIALECT_ID            
UM_CLAIM_URI         
UM_DISPLAY_TAG          
UM_DESCRIPTION           
UM_MAPPED_ATTRIBUTE_DOMAIN
UM_MAPPED_ATTRIBUTE     
UM_REG_EX                
UM_SUPPORTED            
UM_REQUIRED             
UM_DISPLAY_ORDER         
UM_CHECKED_ATTRIBUTE     
UM_READ_ONLY            
UM_TENANT_ID

  • UM_PROFILE_CONFIG
This table is not being used in the Identity Server latest version. Following are the columns in the table.
UM_ID        
UM_DIALECT_ID   
UM_PROFILE_NAME
UM_TENANT_ID

  • UM_HYBRID_ROLE
All the “internal” roles are stored in this table. By default “everyone” role is there for each tenant. Apart from that, for each Service Provider created, a role will be added to this table with the same name as the Service Provider name. Following are the columns of the table.
UM_ID   
UM_ROLE_NAME
UM_TENANT_ID

  • UM_HYBRID_USER_ROLE
When a user is assigned an “internal” role, that is recorded in this table. The “internal” roles are stored in UM_HYBRID_ROLE table and from this table, the users are mapped to those roles. Following are the columns of the table.
UM_ID     
UM_USER_NAME
UM_ROLE_ID   
UM_TENANT_ID
UM_DOMAIN_ID

  • UM_SYSTEM_USER
System users of the Identity Server are stored in this table. The “wso2.anonymous.user” user is by default created. Following are the columns of the table.
UM_ID           
UM_USER_NAME    
UM_USER_PASSWORD
UM_SALT_VALUE  
UM_REQUIRE_CHANGE
UM_CHANGED_TIME   
UM_TENANT_ID

  • UM_SYSTEM_ROLE
System roles of the Identity Server are stored in this table. The “wso2.anonymous.role” is created by default which is a special role that represents a user that has not logged into the Identity Server Management Console. Granting "Read" access to resources for this role would mean that you do not require authentication to access resources using the respective permalinks. Following are the columns of the table.
UM_ID   
UM_ROLE_NAME
UM_TENANT_ID

  • UM_SYSTEM_USER_ROLE
Storing the system roles assigned to the the system users is done with this table. UM_USER_NAME contains the username of the system user. UM_ROLE_ID column contains the ID of the system role which points to the UM_ID column of the UM_SYSTEM_ROLE table. “wso2.anonymous.role” system role is by default assigned to the “wso2.anonymous.user” system user in this table. Following are the columns of the table.
UM_ID     
UM_USER_NAME
UM_ROLE_ID   
UM_TENANT_ID

  • UM_HYBRID_REMEMBER_ME
When login to the management console of the Identity Server, user can select the ‘Remember Me’ option. Upon successful login, a record will be added to this table. UM_USER_NAME column contains the username of the user. UM_CREATED_TIME is the date and time of the login. UM_COOKIE_VALUE column contains the wso2.carbon.rememberme cookie value which is created upon login.
UM_ID         
UM_USER_NAME    
UM_COOKIE_VALUE
UM_CREATED_TIME
UM_TENANT_ID

Identity Related Tables

  • IDN_BASE_TABLE
This is a table used for checking whether the database structure is already created or not when starting the server with -Dsetup option. It has only one column PRODUCT_NAME which contains a row with the value “WSO2 Identity Server”.

  • IDN_OAUTH_CONSUMER_APPS
When adding OAuth/OpenID Connect Configuration as Inbound Authentication Configuration for a Service Provider, this table is used. CONSUMER_KEY and CONSUMER_SECRET columns contain the OAuth client key and the secret respectively. USERNAME column contains the username of the user who created the application. APP_NAME column contains the name of the Service Provider where this configuration is added. OAUTH_VERSION column contains the supported OAuth version of the application. CALLBACK_URL contains the URL to be redirected upon authorization is complete. GRANT_TYPES column contains all the allowed grant types for the application.
CONSUMER_KEY    
CONSUMER_SECRET
USERNAME        
TENANT_ID    
APP_NAME      
OAUTH_VERSION   
CALLBACK_URL   
GRANT_TYPES

  • IDN_OAUTH1A_REQUEST_TOKEN
When using OAuth 1.0a, OAuth clients need to send consumer key, consumer secret and scope to the OAuth service and obtain a request token. When a the Identity Server returns a request token to such a client, it adds a record to this table. REQUEST_TOKEN and REQUEST_TOKEN_SECRET columns contain the generated request token value and the request token secret. CONSUMER_KEY column contains the consumer key of the OAuth application inside the Service Provider. CALLBACK_URL column contains the callback url (redirect url) specified for the client inside the OAuth application in the Service Provider. SCOPE column contains the provided scope in the received request. AUTHORIZED column contains true/false to indicate whether the resource owner authorized the request. Initially this will be false. OAUTH_VERIFIER column value also will be NULL initially. The client application then receives the request token and the request token secret. The client application can now authorize the request token where the user needs to enter credentials and authorize. The username of the user who authorized the request token will be added to the AUTHZ_USER column. Then the AUTHORIZED column value will be true. OAUTH_VERIFIER column will be inserted a random number for verification purpose. Now the client will receive the request token and the OAuth verifier. The client can send these values back to the service and obtain the OAuth access token. Then the record in this table will be deleted and a new row will be added to the IDN_OAUTH1A_ACCESS_TOKEN table. Following are the columns of the table.
REQUEST_TOKEN   
REQUEST_TOKEN_SECRET
CONSUMER_KEY       
CALLBACK_URL      
SCOPE             
AUTHORIZED        
OAUTH_VERIFIER    
AUTHZ_USER

  • IDN_OAUTH1A_ACCESS_TOKEN
When using OAuth 1.0a and receiving a OAuth 1.0a request token, the client application can obtain the access token by authorizing the request token. When the Identity Server returns an access token, a row will be added to this table. ACCESS_TOKEN column and the ACCESS_TOKEN_SECRET column contain the access token and the secret which are randomly generated values. CONSUMER_KEY column has the consumer key of the OAuth application created in the Service Provider. SCOPE column contains the authorized scope and the AUTHZ_USER column contains the username of the user who authorized the request token for obtaining the access token. Following are the columns of the table.
ACCESS_TOKEN     
ACCESS_TOKEN_SECRET
CONSUMER_KEY   
SCOPE            
AUTHZ_USER       


  • IDN_OAUTH2_AUTHORIZATION_CODE
When a client application is used with OAuth 2 Authorization Code grant type and after the authentication, Identity Server returns the authorization code to the client. At this time a record will be added to this table. AUTHORIZATION_CODE column will have the generated authorization code value. CONSUMER_KEY column contains the particular consumer key for which the authorization code was generated  that is used to identify the OAuth application. CALLBACK_URL column contains the redirect url of the client for returning the authorization code. SCOPE column contains the approved OAuth scope and the AUTHZ_USER contains the fully qualified username (with the tenant domain) who authorized the application. TIME_CREATED column contains the date and time where the authorization code was generated and the VALIDITY_PERIOD column contains the validity time period for the authorization code (default value is 300000 ms). Using the authorized code, the client application can then request the OAuth Access Token. When the Access Token is returned, the record with that authorization code will be deleted from this table. Following are the columns of the table.
AUTHORIZATION_CODE
CONSUMER_KEY     
CALLBACK_URL    
SCOPE            
AUTHZ_USER       
TIME_CREATED     
VALIDITY_PERIOD


  • IDN_OAUTH2_ACCESS_TOKEN
When an OAuth2 access token is returned to a client, a record will be added to this table. A record contains the access token, refresh token, the consumer key of the OAuth application, the fully qualified username (with tenant domain) of the user who authorized the application, the type of the user, the date time value which specifies when the access token is generated, the validity period of the token (default is 3600000 ms), the scope of the access token and the state of the access token (i.e ACTIVE). Following are the columns of the table.
ACCESS_TOKEN
REFRESH_TOKEN   
CONSUMER_KEY
AUTHZ_USER   
USER_TYPE     
TIME_CREATED    
VALIDITY_PERIOD
TOKEN_SCOPE   
TOKEN_STATE   
TOKEN_STATE_ID

  • IDN_OAUTH2_SCOPE
When Identity Server is used with a product like API Manager, custom OAuth scopes can be defined. When adding a new scope, a key, name for the scope, description and a list of roles should be given. In a standalone Identity Server instance this table will not get populated with data. Following are the columns of the table.
SCOPE_ID    
SCOPE_KEY   
NAME    
DESCRIPTION
TENANT_ID   
ROLES

  • IDN_OAUTH2_RESOURCE_SCOPE
When Identity Server is used with a product like API Manager, the custom scopes defined for the APIs will be stored in IDN_OAUTH2_SCOPE table. These scopes can be mapped with the resources in the API. The resources and scopes mapping is stored in this table where the RESOURCE_PATH column contains the path to the resource and the SCOPE_ID column contains the ID of the  scope which points to the SCOPE_ID column of the IDN_OAUTH2_SCOPE table. In a standalone Identity Server instance this table will not get populated with data. Following are the columns of the table.
RESOURCE_PATH
SCOPE_ID

  • IDN_SCIM_GROUP
In the user store configuration, if the “SCIMEnabled” property is set to true, when creating a new role in the user store, SCIM attributes for the created role are stored in this table. ROLE_NAME column contains the name of the role. ATTR_NAME and ATTR_VALUE contain the name and the value of the SCIM attributes respectively. For one role created, there are multiple rows stored as multiple SCIM attributes are associated with a role (GROUP). Following are the columns of the table.
ID     
TENANT_ID  
ROLE_NAME
ATTR_NAME  
ATTR_VALUE

  • IDN_SCIM_PROVIDER
This table is not used in the latest version of the Identity Server.
CONSUMER_ID   
PROVIDER_ID  
USER_NAME   
USER_PASSWORD
USER_URL   
GROUP_URL   
BULK_URL

  • IDN_OPENID_REMEMBER_ME
This table is not being used in the latest version of Identity Server as ‘Remember Me’ is handled from the authentication framework.
USER_NAME  
TENANT_ID    
COOKIE_VALUE
CREATED_TIME

  • IDN_OPENID_USER_RPS
When users login to OpenID relying party applications where the OpenID authentication is provided by the Identity Server, the login details are stored in this table. USER_NAME column contains the username of the logged in user. RP_URL contains the URL of the relying party where it should be redirected upon successful login. TRUSTED_ALWAYS column contains the value of TRUE or FALSE which indicates whether the user has given the  “Approve Always” or “Approve” options of the application for authentication. LAST_VISIT column contains the date of the last login of the user. VISIT_COUNT is the number of successful login attempts for the user. Following are the columns of the table.
USER_NAME          
TENANT_ID         
RP_URL             
TRUSTED_ALWAYS    
LAST_VISIT         
VISIT_COUNT       
DEFAULT_PROFILE_NAME

  • IDN_OPENID_ASSOCIATIONS
The OpenID associations are stored in this table. HANDLE column contains the association handle and the ASSOC_TYPE column contains the OpenID association type. EXPIRES_IN column contains the date time value of the expiry of the association. MAC_KEY column contains the Message Authentication Code of the association. Following are the columns of the table.
HANDLE    
ASSOC_TYPE  
EXPIRE_IN   
MAC_KEY
ASSOC_STORE

  • IDN_STS_STORE
When the Identity Server is used as a Security Token Service which issues access tokens, such records are stored in following table. Following are the columns of the table.
ID          
TOKEN_ID    
TOKEN_CONTENT
CREATE_DATE  
EXPIRE_DATE   
STATE

  • IDN_IDENTITY_USER_DATA
When using a JDBC user store, the user attributes of a user are stored in the UM_USER_ATTRIBUTE table for the supported claims. There are some claims used for Identity Management features that should be handled specifically. Claims used for Identity Management feature will be stored in the usestore which is specified in the <IS_HOME>/repository/conf/security/identity-mgt.properties file. In that property file, the property "Identity.Mgt.User.Data.Store" is used to define what kind of store it will be using for storing those reserved claims.

If you specify org.wso2.carbon.identity.mgt.store.UserStoreBasedIdentityDataStore for the Identity.Mgt.User.Data.Store property which is the default store, it will use the same user store where the user resides for storing these special attributes.

If you specify org.wso2.carbon.identity.mgt.store.JDBCIdentityDataStore for the Identity.Mgt.User.Data.Store property, it will not use the user's user store but instead it will use Identity Server's internal JDBC database to store those claims.
The reserved claims as follows,
  • Any claim contains the part : "http://wso2.org/claims/challengeQuestion" for its claim URI
http://wso2.org/claims/challengeQuestion1        
http://wso2.org/claims/challengeQuestion2         
http://wso2.org/claims/challengeQuestionUris

  • Any claim contains part "http://wso2.org/claims/identity" for its claim URI
http://wso2.org/claims/identity/accountLocked     
http://wso2.org/claims/identity/failedLoginAttempts
http://wso2.org/claims/identity/unlockTime 
http://wso2.org/claims/identity/passwordTimestamp

Upon updating the user profile, these claims and their mapped attribute values will be inserted to this table.

The TENANT_ID column contains the ID of the tenant where the user belongs. USER_NAME column contains the username of the user. DATA_KEY column contains the Claim URI of the supported claim and the DATA_VALUE column contains the user attribute value of the particular claim. Following are the columns of the table.
Following are the columns of the table.
TENANT_ID  
USER_NAME  
DATA_KEY   
DATA_VALUE
   

  • IDN_IDENTITY_META_DATA
USER_NAME    
TENANT_ID   
METADATA_TYPE
METADATA   
VALID

  • IDN_THRIFT_SESSION
This table is used to store the authenticated Thrift session.  Once the user is authenticated to the thrift authenticator,  it would create a thrift session.  It stores the authenticated USERNAME ,  SESSION_ID, CREATED_TIME of the session the LAST_MODIFIED_TIME in this table. This is mainly used in the XACML feature in WSO2 Identity Server. Entitlement Service of the Identity Server is exposed via Thrift transport.  In order to access this admin service, it must be authenticated. Following are the columns of the table.
SESSION_ID       
USER_NAME       
CREATED_TIME    
LAST_MODIFIED_TIME
  • IDN_ASSOCIATED_ID
In the Identity Server Dashboard, users can associate their social identity (i.e. Facebook, Yahoo, Google, Microsoft ) with the user account created in the Identity Server. The IDP_USER_ID column contains the user’s username in the social account (i.e Facebook username). TENANT_ID column contains the ID of the tenant where the user is created. IDP_ID column contains the ID of the Identity Provider which contains the federated authenticator. USER_NAME column contains the username of the user in Identity Server where this social identity is associated with.
ID        
IDP_USER_ID
TENANT_ID   
IDP_ID    
USER_NAME

  • IDN_AUTH_SESSION_STORE
When the remember me option is selected when logging into the either a service provider or the Identity Server, session data is persisted provided that the session data persistence is enabled from configuration. When a new session is created, the time of the session creation is stored in TIME_CREATED column. Following are the columns of the table.
SESSION_ID   
SESSION_TYPE  
SESSION_OBJECT
TIME_CREATED



IDN_AUTH_SESSION_STORE table is not defined with the fresh pack of Identity Server 5.0, but included in the Service Pack 1. Command for Creating the table is below.

CREATE TABLE IDN_AUTH_SESSION_STORE (
          SESSION_ID varchar(100) default NULL,
          SESSION_TYPE varchar(100) default NULL,
          SESSION_OBJECT BLOB,
          TIME_CREATED TIMESTAMP,
          PRIMARY KEY (SESSION_ID, SESSION_TYPE),
          UNIQUE(SESSION_ID, SESSION_TYPE)
   )

Identity Application Management - Service Provider Related Tables

  • SP_APP
When a Service Provider is added, the details are stored in this table. The APP_NAME column represents the Service Provider name. Following are the columns of the table.
ID                        
TENANT_ID                
APP_NAME                   
USER_STORE                 
USERNAME                   
DESCRIPTION               
ROLE_CLAIM                
AUTH_TYPE                 
PROVISIONING_USERSTORE_DOMAIN
IS_LOCAL_CLAIM_DIALECT     
IS_SEND_LOCAL_SUBJECT_ID    
IS_SEND_AUTH_LIST_OF_IDPS    
SUBJECT_CLAIM_URI          
IS_SAAS_APP

  • SP_INBOUND_AUTH
Inbound authentication configuration details of a Service Provider are stored in this table. For each type of associated inbound authentication configuration for a Service Provider, there will be a separate record (i.e. OpenID, PassiveSTS, SAMLSSO). If the inbound authentication configuration is SAMLSSO (value in INBOUND_AUTH_TYPE is ‘samlsso’), it will have a property named “Attribute Consuming Service Index” in the column PROP_NAME and the value is a random integer stored in PROP_VALUE column. Following are the columns of the table.
ID             
TENANT_ID       
INBOUND_AUTH_KEY
INBOUND_AUTH_TYPE
PROP_NAME       
PROP_VALUE    
APP_ID


  • SP_AUTH_STEP
The Service Providers can define how to authenticate users at the Identity Server, for authentication requests initiated by it. While doing that, each Service Provider can define multiple steps and for each step it can pick more than one authenticator. This is called as Multi-level (multi-factor) Authentication. This table stores each authentication step added to the Service Provider. Service Provider is represented by the APP_ID. If multiple authentication steps are added for one Service Provider, the order is maintained in the STEP_ORDER column. Following are the columns of the table.
ID             
TENANT_ID      
STEP_ORDER     
APP_ID        
IS_SUBJECT_STEP  
IS_ATTRIBUTE_STEP

  • SP_FEDERATED_IDP
For a Service Provider, Federated Identity Providers can be added for authentication. The Federated Identity Providers for a Service Provider are stored in this table. Here the ID column points to the ID column of the SP_AUTH_STEP table. Following are the columns of the table.
ID            
TENANT_ID    
AUTHENTICATOR_ID

  • SP_CLAIM_MAPPING
When the claims of the Identity Provider are different from the Service Provider, corresponding claims can be mapped from this table for each Service Provider. The Service Provider ID is given in APP_ID and the IDP_CLAIM and SP_CLAIM are the Identity Provider and Service Provider claims respectively. Following are the columns of the table.
ID        
TENANT_ID   
IDP_CLAIM  
SP_CLAIM    
APP_ID    
IS_REQUESTED  
DEFAULT_VALUE


  • SP_ROLE_MAPPING
When there are user roles defined in the Identity Provider side (here WSO2 Identity Server) and also in the client application side, the roles in both sides can be mapped together accordingly through the Service Provider. The Service Provider ID is given in APP_ID and the client application side role is given in SP_ROLE where the Identity Provider’s side role is given in IDP_ROLE. This way, the client application developers do not need to know the available roles in the Identity Provider as the roles can be mapped and linked together. Following are the columns of the table.
ID     
TENANT_ID
IDP_ROLE
SP_ROLE
APP_ID

  • SP_REQ_PATH_AUTHENTICATOR
If a RequestPathAuthenticator is added to a Service Provider, that is recorded in this table. Service Provider ID is given in APP_ID. AUTHENTICATOR_NAME column will contain the name of the request path authenticator.  Following are the columns of the table.
ID            
TENANT_ID       
AUTHENTICATOR_NAME
APP_ID

  • SP_PROVISIONING_CONNECTOR
Outbound Provisioning Connectors can be added to the Identity Providers for user provisioning. Those Provisioning Connectors can be linked to a Service Provider. For a Service Provider, the associated Provisioning Connectors are stored in this table. IDP_NAME column has the Identity Provider name and the CONNECTOR_NAME column has the name of the provisioning connector. APP_ID column has the ID of the Service Provider. Following are the columns of the table.  
ID        
TENANT_ID   
IDP_NAME    
CONNECTOR_NAME
APP_ID    
IS_JIT_ENABLED
BLOCKING




Identity Application Management - Identity Provider Related Tables

  • IDP
When an Identity Provider is added, the details are stored in this table. Following are the columns of the table.
ID                      
TENANT_ID              
NAME                    
IS_ENABLED           
IS_PRIMARY               
HOME_REALM_ID           
IMAGE                   
CERTIFICATE             
ALIAS                  
INBOUND_PROV_ENABLED     
INBOUND_PROV_USER_STORE_ID
USER_CLAIM_URI           
ROLE_CLAIM_URI           
DESCRIPTION              
DEFAULT_AUTHENTICATOR_NAME
DEFAULT_PRO_CONNECTOR_NAME
PROVISIONING_ROLE   
IS_FEDERATION_HUB   
IS_LOCAL_CLAIM_DIALECT
DISPLAY_NAME

  • IDP_ROLE
An Identity Provider may have different roles for authorization which are different from the local roles of the Identity Server. In such a situation, roles at the Identity Provider can be mapped to the local roles in the Identity Server. Such roles of the Identity Provider are stored in this table. ROLE column contains the name of the role. IDP_ID is the ID of the Identity Provider. Following are the columns of the table.
ID      
IDP_ID   
TENANT_ID
ROLE

  • IDP_ROLE_MAPPING
The mappings of local roles of the Identity Server to the roles of Identity Providers are stored in this table. LOCAL_ROLE column has the value of the role name of the local role. IDP_ROLE_ID column has the ID of the Identity Provider’s role which points to the ID column of the IDP_ROLE table.
ID          
IDP_ROLE_ID   
TENANT_ID   
USER_STORE_ID
LOCAL_ROLE

  • IDP_CLAIM
When an Identity Provider is having claims that are different from the local claims of the Identity Server, corresponding claims of the Identity Provider can be mapped to the local claims where the Identity Provider claims are stored in this table. The mapping details of the local claims are stored in IDP_CLAIM_MAPPING table. Following are the columns of the table.  
ID     
IDP_ID  
TENANT_ID
CLAIM

  • IDP_CLAIM_MAPPING
The mappings of the local claims with the Identity Provider claims are stored in this table.IDP_CLAIM_ID column has the Identity Provider’s claim ID which points to the ID column of the IDP_CLAIM table.  LOCAL_CLAIM column contains the claim value of the local claim in the mapping. Following are the columns of the table.
ID         
IDP_CLAIM_ID  
TENANT_ID    
LOCAL_CLAIM  
DEFAULT_VALUE
IS_REQUESTED

  • IDP_AUTHENTICATOR
The Local and Federated authenticators for each Identity Provider are stored in this table. The NAME column contains the name of the authenticator. IDP_ID is the Identity Provider’s ID which points to the ID column of the IDP table. Following are the columns of the table.
ID       
TENANT_ID    
IDP_ID    
NAME      
IS_ENABLED  
DISPLAY_NAME

  • IDP_AUTHENTICATOR_PROPERTY
The properties related to the authenticators stored in IDP_AUTHENTICATOR table are stored in this table. The properties are stored as key value pairs in PROPERTY_KEY and PROPERTY_VALUE tables respectively. The associated authenticator ID is given in the AUTHENTICATOR_ID column which points to the ID column of the IDP_AUTHENTICATOR table. Following are the columns of the table.
ID            
TENANT_ID      
AUTHENTICATOR_ID
PROPERTY_KEY    
PROPERTY_VALUE  
IS_SECRET

  • IDP_PROVISIONING_CONFIG
The Outbound Provisioning Connector details for each Identity Provider is stored in this table. The Identity Provider’s ID is given in the IDP_ID column which points to the ID column of the IDP. Provisioning Connector Type is given in the PROVISIONING_CONNECTOR_TYPE column. Detailed configuration for each type of provisioning connector is stored in IDP_PROV_CONFIG_PROPERTY table. Following are the columns of this table.
ID                       
TENANT_ID                 
IDP_ID                    
PROVISIONING_CONNECTOR_TYPE
IS_ENABLED                
IS_BLOCKING

  • IDP_PROV_CONFIG_PROPERTY
The properties for each Provisioning Connector are stored in this table as key value pairs in PROPERTY_KEY and PROPERTY_VALUE columns respectively. PROVISIONING_CONFIG_ID is the ID of the Provisioning Connector in IDP_PROVISIONING_CONFIG table. Data type of the property is stored in PROPERTY_TYPE column. Following are the columns of the table.
ID           
TENANT_ID           
PROVISIONING_CONFIG_ID
PROPERTY_KEY        
PROPERTY_VALUE       
PROPERTY_BLOB_VALUE    
PROPERTY_TYPE       
IS_SECRET

  • IDP_PROVISIONING_ENTITY
When Outbound Provisioning is enabled for an Identity Provider and a User or a Group is created inside Identity Server, this table is storing records such that the PROVISIONING_CONFIG_ID contains the ID of the Provisioning Config that points to the ID column of the IDP_PROVISIONING_CONFIG table. ENTITY_TYPE column contains the type of the entity which can be either USER or GROUP. The user store of the Identity Server where the user or group is created is added to ENTITY_LOCAL_USERSTORE column. ENTITY_NAME contains the name of the user or role created inside Identity Server. ENTITY_VALUE contains the unique identifier of the user or group created at the external provisioned Identity Provider.
ID                  
PROVISIONING_CONFIG_ID
ENTITY_TYPE        
ENTITY_LOCAL_USERSTORE
ENTITY_NAME        
ENTITY_VALUE     
TENANT_ID

  • IDP_LOCAL_CLAIM
This table is not used in the latest version of Identity Server.
ID         
TENANT_ID   
IDP_ID    
CLAIM_URI   
DEFAULT_VALUE
IS_REQUESTED






Tharindu Edirisinghe
Identity Server Team
WSO2


  

5 comments:

  1. Appreciate this article. I have a question about IDN_AUTH_SESSION_STORE especially the SESSION_OBJECT BLOB. Is there any information about what is stored there? For example the user the session belongs to? Tnx.

    ReplyDelete
    Replies
    1. Hello HOS,

      The BLOB contains the data in [1].

      [1] https://github.com/wso2/carbon-identity-framework/blob/v5.1.0/components/authentication-framework/org.wso2.carbon.identity.application.authentication.framework/src/main/java/org/wso2/carbon/identity/application/authentication/framework/context/AuthenticationContext.java

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. thank you, this is really informative ...

    ReplyDelete
  4. Нello thеrᥱ! Thiis article сouldn't be ԝritten any better!

    Going throuǥh tҺіѕ post reminds me of my preѵious
    roommate! He constаntly кept preaching аbout thiѕ.

    ӏ аm gоing tⲟ forward thіs post too him. Pretty sսгᥱ he's goіng to Һave a verү gߋod
    read. Thank you for sharing!

    ReplyDelete