The Carbon RDBMS User Security Module

Purpose

Provides an implemenation of the User Manager that can run against JDBC stores.

Who needs it and why

Applications that have a User Store in an RDBMS system.

Dependencies

• SQL Service

Limitations

The default implemenation contains configurable SQL statements which allows it to work against most user stores without update. Highly complicated databases or databases with specific requirements that do not match assumptions made by this service may require.

When creating a user, the user management service only fills in a principal name and credential field. If this fields are stored in the same table as other user information (which may not by NULLABLE), a work around will need to be implemented. The suggested solution is to place login information in it's own table with a foreign key relation to the main user table. Another solution is to first create the complete user information without credential, and than update the "create" query to make an update call to that table.

Primary Key

The primary key in the main principal table is a surrogate key to the principal name. This allows changing of the principal name without cascading the change into other tables. The recommended solution is to use a database sequence (Oracle Sequence, Access Auto Increment, mySQL auto_number, etc) to hold this value.

Database Setup

Because the store runs against a JDBC datasource, a database must be setup to contain the information required by the service. This table strucutre may be replaced to meet a projects requirements. Minor changes to the table structure may be handled by properly configuring the SQL queries used by the service. Major changes may require subclassing the current implementation and various methods to deal with more complicated queries and logic.

The reference service runs against a database using tables setup by the following script:

CREATE SEQUENCE principal_id_sequence;

CREATE TABLE carbon_principal (
    principal_id INTEGER UNIQUE NOT NULL,
    principal_name  VARCHAR(200) UNIQUE NOT NULL,
    principal_type SMALLINT NOT NULL,
    PRIMARY KEY (principal_id)
);

CREATE TABLE carbon_credential (
    principal_id INTEGER UNIQUE NOT NULL,
    credential VARCHAR(200) NOT NULL,
    FOREIGN KEY (principal_id) REFERENCES carbon_principal(principal_id)
);

CREATE TABLE carbon_principal_to_group (
    principal_id INTEGER NOT NULL,
    group_id INTEGER NOT NULL,
    PRIMARY KEY (principal_id,group_id),
    FOREIGN KEY (principal_id) REFERENCES carbon_principal(principal_id),
    FOREIGN KEY (group_id) REFERENCES carbon_principal(principal_id)
);

Component Configuration

The major component configuration occurs with a Statement Factory class that contains all the queries used to manipulate the user store.

Configuration Example

<Configuration ConfigurationInterface="org.sape.carbon.services.security.management.rdbms.RdbmsUserManagerConfiguration">

    <FunctionalInterface>org.sape.carbon.services.security.management.UserManager</FunctionalInterface>
    <FunctionalImplementationClass>org.sape.carbon.services.security.management.rdbms.RdbmsUserManagerImpl</FunctionalImplementationClass>

    <ConnectionFactory>ref:///sql/connection/test/StandaloneConnectionFactory</ConnectionFactory>

    <StatementFactory ConfigurationInterface="org.sape.carbon.services.sql.StatementFactoryConfiguration">
        <FunctionalInterface>org.sape.carbon.services.sql.StatementFactory</FunctionalInterface>
        <FunctionalImplementationClass>org.sape.carbon.services.sql.DefaultStatementFactoryImpl</FunctionalImplementationClass>
        <ConnectionFactory>ref:///sql/connection/test/StandaloneConnectionFactory</ConnectionFactory>

        <Statement>
            <QueryName>retreiveUserQuery</QueryName>
            <Query>
                select principal_name
                from carbon_principal
                where principal_name = ?
                  and principal_type = 1
            </Query>
        </Statement>

        <Statement>
            <QueryName>authenticateUserQuery</QueryName>
            <Query>
                select principal_name
                from carbon_principal,
                     carbon_credential
                where carbon_principal.principal_id = carbon_credential.principal_id
                  and carbon_principal.principal_type = 1
                  and carbon_principal.principal_name = ?
                  and carbon_credential.credential = ?
            </Query>
        </Statement>


        <Statement>
            <QueryName>retreiveGroupQuery</QueryName>
            <Query>
                select principal_name
                from carbon_principal
                where principal_name = ?
                  and principal_type = 2
            </Query>
        </Statement>

        <Statement>
            <QueryName>retreiveGroupsForUserQuery</QueryName>
            <Query>
                select group_table.principal_name
                  from carbon_principal user_table,
                       carbon_principal_to_group,
                       carbon_principal group_table
                 where user_table.principal_type = 1
                   and group_table.principal_type = 2
                   and user_table.principal_name = ?
                   and user_table.principal_id = carbon_principal_to_group.principal_id
                   and carbon_principal_to_group.group_id = group_table.principal_id
            </Query>
        </Statement>

        <Statement>
            <QueryName>retreiveGroupsForGroupsQuery</QueryName>
            <Query>
                select parentgroups.principal_name
                  from carbon_principal parentgroups,
                       carbon_principal_to_group,
                       carbon_principal childgroup
                 where parentgroups.principal_type = 2
                   and childgroup.principal_type = 2
                   and childgroup.principal_name = ?
                   and childgroup.principal_id = carbon_principal_to_group.principal_id
                   and carbon_principal_to_group.group_id = parentgroups.principal_id
            </Query>
        </Statement>

        <Statement>
            <QueryName>createUserQuery</QueryName>
            <Query>
                insert
                into carbon_principal (principal_id, principal_name, principal_type)
                values (principal_id_sequence.nextval, ?, 1)
            </Query>
        </Statement>

        <Statement>
            <QueryName>createCredentialQuery</QueryName>
            <Query>
                insert
                into carbon_credential (principal_id, credential)
                values (?, ?)
            </Query>
        </Statement>

        <Statement>
            <QueryName>removeCredentialQuery</QueryName>
            <Query>
                delete
                from carbon_credential
                where principal_id = ?
            </Query>
        </Statement>


        <Statement>
            <QueryName>retreiveUserPrimaryKeyQuery</QueryName>
            <Query>
                select principal_id
                from carbon_principal
                where principal_name = ?
                  and principal_type = 1
            </Query>
        </Statement>

        <Statement>
            <QueryName>retreiveGroupPrimaryKeyQuery</QueryName>
            <Query>
                select principal_id
                from carbon_principal
                where principal_name = ?
                  and principal_type = 2
            </Query>
        </Statement>



        <Statement>
            <QueryName>removeUserQuery</QueryName>
            <Query>
                delete
                from carbon_principal
                where principal_id = ?
            </Query>
        </Statement>

        <Statement>
            <QueryName>updateCredentialQuery</QueryName>
            <Query>
                update carbon_credential
                set credential = ?
                where principal_id = ?
            </Query>
        </Statement>

        <Statement>
            <QueryName>createGroupQuery</QueryName>
            <Query>
                insert
                into carbon_principal (principal_id, principal_name, principal_type)
                values (principal_id_sequence.nextval, ?, 2)
            </Query>
        </Statement>

        <Statement>
            <QueryName>removeGroupQuery</QueryName>
            <Query>
                delete
                from carbon_principal
                where principal_id = ?
                  and principal_type = 2
            </Query>
        </Statement>

        <Statement>
            <QueryName>addPrincipalToGroupQuery</QueryName>
            <Query>
                insert
                into carbon_principal_to_group (principal_id, group_id)
                values (?, ?)
            </Query>
        </Statement>

        <Statement>
            <QueryName>retreiveMemebersQuery</QueryName>
            <Query>
                select members.principal_name, members.principal_type
                from carbon_principal parent,
                     carbon_principal members,
                     carbon_principal_to_group
                where parent.principal_name = ?
                  and parent.principal_id = carbon_principal_to_group.group_id
                  and carbon_principal_to_group.principal_id = members.principal_id
            </Query>
        </Statement>

        <Statement>
            <QueryName>removePrincipalFromGroupQuery</QueryName>
            <Query>
                delete
                from carbon_principal_to_group
                where principal_id = ?
                  and group_id = ?
            </Query>
        </Statement>

        <Statement>
            <QueryName>removeAllRelationshipsQuery</QueryName>
            <Query>
                delete
                from carbon_principal_to_group
                where principal_id = ?
                   or group_id = ?
            </Query>
        </Statement>

        <Statement>
            <QueryName>retreiveAllUsersQuery</QueryName>
            <Query>
                select principal_name
                from carbon_principal
                where principal_type = 1
            </Query>
        </Statement>

        <Statement>
            <QueryName>retreiveAllGroupsQuery</QueryName>
            <Query>
                select principal_name
                from carbon_principal
                where principal_type = 2
            </Query>
        </Statement>

    </StatementFactory>
</Configuration>