{**************************************************************************}
{*                                                                        *}
{*  Licensed Materials - Property of IBM and/or HCL                                  *}
{*                                                                        *}
{*  IBM Informix Dynamic Server                                           *}
{*  (c) Copyright IBM Corporation 1996, 2011 All rights reserved.         *}
{*  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.          *}
{*                                                                        *}
{**************************************************************************}
{                                          }
{   Title:       sysuser.sql               }
{   Description: create sysuser database   }

create database sysuser with log;

create table informix.sysauth (
	username	char(32) not null,	{ User login id }
	groupname	char(32),		{ Group name - unused }
	servers		varchar(128) not null,	{ Informix server }
	hosts		varchar(128) not null,  { Host for Informix server }
	check (groupname is null)
) LOCK MODE ROW;

create unique index informix.sysauth_idx on sysauth(username, servers, hosts) in table;
revoke all on sysauth from public as informix;
grant select on sysauth to public as informix;

create table informix.sysdbsecadmauth (
        grantee		nchar(32),	{ DBSECADM role grantee }
        database	nvarchar(128)	{ Database name - unused }
) LOCK MODE ROW;

create unique index informix.sysdbsecadmauth_idx on sysdbsecadmauth(grantee) in table;
revoke all on sysdbsecadmauth from public as informix;
grant select on sysdbsecadmauth to public as informix;

create table informix.syssurrogates
(
        surrogate_id    serial,
        os_username     nchar(32),
        uid             int,
        gid             int,
        groupname       nchar(32),
        homedir         nvarchar(255),
        userauth        char(10)
) LOCK MODE ROW;

create unique index informix.syssurrogates_idx_sid on syssurrogates (surrogate_id) in table;
create index informix.syssurrogates_idx_uname_uauth on syssurrogates (os_username, userauth) in table;
create index informix.syssurrogates_idx_uid_gid_uauth on syssurrogates (uid, gid, userauth) in table;
create index informix.syssurrogates_idx_uid_gname_uauth on syssurrogates (uid, groupname, userauth) in table;
create index informix.syssurrogates_idx_uname_gid_uauth on syssurrogates (os_username, gid, userauth) in table;
create index informix.syssurrogates_idx_uname_gname_uauth on syssurrogates (os_username, groupname, userauth) in table;

revoke all on syssurrogates from public as informix;
grant select on syssurrogates to public as informix;


create table informix.sysusermap
(
        username        nchar(32),
        surrogate_id    int
) LOCK MODE ROW;

create unique index informix.sysusermap_idx_username on sysusermap(username) in table;
create index informix.sysusermap_idx_surrogateid on sysusermap(surrogate_id) in table;
revoke all on sysusermap from public as informix;
grant select on sysusermap to public as informix;


create table informix.syssurrogategroups
(
	surrogate_id    int,
	gid		int,
        groupname       nchar(32),
	groupseq	smallint
) LOCK MODE ROW;

create unique index informix.syssurrogategroups_idx_sid_grpseq on syssurrogategroups(surrogate_id, groupseq) in table;
revoke all on syssurrogategroups from public as informix;
grant select on syssurrogategroups to public as informix;

create table informix.sysintauthusers
(
    username          NCHAR(32),
    salt              BIGINT,
    hashed_password   VARCHAR(128),  {Hex encoded: allow expansion to 512-bit hashes, SHA-256 needs just 64, SHA-1 needs just 40}
    hash_type         CHAR(16),
    updated           DATETIME YEAR TO SECOND {TIMESTAMP},
    flags             INTEGER,
    min_change        INTERVAL DAY(7) TO SECOND,
    max_change        INTERVAL DAY(7) TO SECOND,
    inactive          INTERVAL DAY(7) TO SECOND,
    ac_expire         DATETIME YEAR TO SECOND {TIMESTAMP - when a/c cannot be used any longer}
) lock mode row;

create unique index informix.sysintauthusers_idx_username on sysintauthusers(username) in table;
revoke all on sysintauthusers from public as informix;
grant select on sysintauthusers to public as informix;

/****** Trusted Context ******/

create table informix.systrustedcontext
(
	contextid	serial,
	contextname	varchar(128),
	database	char(128),
	authid		char(32),
	defaultrole	char(32),
	enabled		char(1) not null,
	encryption	char(1)
) LOCK MODE ROW;

create unique index informix.systcx_ctxid on systrustedcontext(contextid) in table;
create unique index informix.systcx_ctxname on systrustedcontext(contextname) in table;
create unique index informix.systcx_authid on systrustedcontext(authid) in table;
revoke all on systrustedcontext from public as informix;
grant select on systrustedcontext to public as informix;

create table informix.systcxattributes
(
	contextid	integer not null,
	address		char(64),
	encryption      char(1)
) LOCK MODE ROW;

create index informix.systcxatt_ctxid on systcxattributes(contextid) in table;
create unique index informix.systcxatt_ctxaddr on systcxattributes(contextid, address) in table;
revoke all on systcxattributes from public as informix;
grant select on systcxattributes to public as informix;

create table informix.systcxusers
(
	contextid       integer not null,
	username	char(32),
	usertype	char(1),
	userrole	char(32),
	authreq		char(1)
) LOCK MODE ROW;

create index informix.systcxusr_ctxid on systcxusers(contextid) in table;
create unique index informix.systcxusr_username on systcxusers(contextid, username) in table;
revoke all on systcxusers from public as informix;
grant select on systcxusers to public as informix;

grant connect to public;
close database;