CREATE_USER

Creates a new database user and assigns a SCURTY user type (GU, HU, TU) to the user.

This procedure supports authentication by a database password as well as external and global authentication using LDAP, KERBEROS or RADIUS. To use external or global authentication the password field must contain the respective strings. See parameter p_password in the procedure description below for details.

For HU and GU type users a default permanent tablespace needs to be provided either in the procedure call (see also parameter description below) or be defined as SCURTY default permanent tablespace. A quota on the tablespace and additional custom roles may be defined. The user may optionally be expired immediately after creation. A database profile may be provided for all three types of users.

This procedure can only be executed by a user who has been granted the ‘manage_users’ privilege, as defined in table ADM_ADMIN_PRIVS. By default, this is granted to the SCURTY OPR role.

After creating, the user status information will be listed in view REP_ALL_USERS.

Parameter Name

Required

DefaultValue

Values

Description

p_username

yes

The username for an Oracle database user to be created.

Caution: the username is case-sensitive. If you provide a lower case username, you will have to logon using doublequotes (e.g. “wklinger”) as the Oracle database otherwise automatically converts the name to upper case.

p_password

yes

A password for the database user that will be created or the authentication string for the global or external authentication method.

Supported global or external authentication methods are LDAP, KERBEROS, RADIUS.

The corresponding strings for the these methods to be entered in p_password are:

  • For LDAP: enter the string “@LDAP:” followed by the user DN (distinguished name). For example “@LDAP:CN=architect, OU=consulting, O=sphinx, C=AT”

  • For KERBEROS: enter the string “@KRB:” followed by the KERBEROS ID. The KERBEROS ID is the principal name in the form <user>/<instance>@<REALM>. Examples for the complete string are “@KRB:wklinger@sphinx.at” or “@KRB:wklinger/consulting@sphinx.at”.

  • For RADIUS: enter the string “@RADIUS:” without any further string following. If a user tries to log in, the RADIUS server will take control of the authentication process.

p_user_type

yes

  • HU

  • GU

  • TU

The type of user role.

Possible values:

  • ‘HU’ for a human user

  • ‘GU’ for a generic user (application user)

  • ‘TU’ for a technical user (3-tier application user)

The database roles behind these users roles are:

  • R_SCURTY_HU for HU

  • R_SCURTY_GU for GU

  • R_SCURTY_TU for TU

p_expired

no

  • TRUE

Defines if the user-account should expire immediately after being created.

Possible values:

  • TRUE to expire the user-account

  • FALSE to not expire the user-account

The default value depends on the user type defined by parameter p_user_type:

  • TRUE if p_user_type is HU

  • FALSE if P_user_type is GU or TU

p_profile

no

The user profile of the Oracle database that should be used.

If no value is provided the value will be taken from the default value defined for the user type provided (defined by parameter p_user_tryp) and stored in table adm_params.

If there is no value stored in table adm_params the Oracle database default profile (DEFAULT) will be used.

p_tablespace

no

The default permanent tablespace for the user that will be created. If a tablespace name is provided the tablespace must exist in the database.

A user of type ‘TU’ will not be allocated a default permanent tablespace, regardless of the value defined in parameter p_tablespace.

Default value: if no tablespace name is provided it will default to the SCURTY default permanent tablespace (see table ADM_USER_TABLESPACES and view ADM_USER_TABLESPACE_DETAILS).

Execution of this procedure will return an error if parameter p_tablespace is set to NULL and no SCURTY user default permanent tablespace has been defined in table ADM_USER_TABLESPACES. To set a SCURTY user default permanent tablespace use procedure p_rep_admin.associate_user_ts.

p_quota

no

The quota in GB in the default permanent tablespace. A user of type ‘TU’ will not be provided quota on a tablespace.

Possible values: any positive integer or -1 for unlimited quota.

Default value: will be taken from table ADM_PARAMS:

  • GU users will receive the quota defined in the parameter ‘adm_gu_def_quota’, column VALUE_NUMERIC, or -1 (=unlimited) if this parameter is not defined in the table.

  • HU users will receive the quota defined in the parameter ‘adm_hu_def_quota’, column VALUE_NUMERIC, or 0 (quota set to zero) if this parameter is not defined in table ADM_PARAMS.

p_custom_role

no

An additional default custom role to be granted to the user. Additional default custom roles may be granted for users of type ‘GU’ or ‘HU’.

Possible values: the name of an existing database role.

Default value: will be taken from table ADM_PARAMS, dependent upon the user type:

  • GU users will receive the role defined by parameter ‘adm_gu_def_role’, column VALUE_TEXT.

  • HU users will receive the role defined by parameter ‘adm_hu_def_role’, column VALUE_TEXT.

If no value is provided, or can be determined by a parameter in ADM_PARAMS, no additional role will be granted.

Example

Create user ‘WKLINGER’ using password ‘change’, user type human user (‘HU’), do not expire the account immediately and without specifying a profile. Assign default permanent tablespace ‘USERS’ with 1 GB quota and do not grant an additional custom role:

EXEC p_rep_oper.create_user
    ( 'WKLINGER'
    , 'change'
    , 'HU'
    , FALSE
    , NULL
    , 'USERS'
    , 1
    , NULL );