/*
  Enhanced security database (isc4.gdb) - version B
   -it allows users to change their own passwords,
   -it hides other users names
    (unless you are SYSDBA, you will see only 1 row in "table" USERS)

  ! Before running this script do not forget to do _physical copy_ backup of isc4.gdb !

  Written by: Ivan Prenosil, 2001
 */



CONNECT    'C:\Program Files\Borland\InterBase\isc4.gdb'
  USER     'SYSDBA'
  PASSWORD 'masterkey';


/** Rename existing USERS table to USERS2. **/

CREATE TABLE USERS2 (
  USER_NAME     USER_NAME,
  SYS_USER_NAME USER_NAME,
  GROUP_NAME    USER_NAME,
  UID           UID,
  GID           GID,
  PASSWD        PASSWD,
  PRIVILEGE     PRIVILEGE,
  COMMENT       COMMENT,
  FIRST_NAME    NAME_PART,
  MIDDLE_NAME   NAME_PART,
  LAST_NAME     NAME_PART,
  FULL_NAME COMPUTED BY (first_name || _UNICODE_FSS ' ' || middle_name || _UNICODE_FSS ' ' || last_name ));

INSERT INTO USERS2
  (USER_NAME, SYS_USER_NAME, GROUP_NAME, UID, GID, PASSWD, PRIVILEGE, COMMENT, FIRST_NAME, MIDDLE_NAME, LAST_NAME)
SELECT
   USER_NAME, SYS_USER_NAME, GROUP_NAME, UID, GID, PASSWD, PRIVILEGE, COMMENT, FIRST_NAME, MIDDLE_NAME, LAST_NAME
  FROM USERS;

COMMIT;

DROP TABLE USERS;

CREATE UNIQUE INDEX USER_NAME_INDEX2 ON USERS2(USER_NAME);


/** Create view that will be used instead of original USERS table. **/

CREATE VIEW USERS AS
  SELECT *
    FROM USERS2
   WHERE USER = ''
      OR USER = 'SYSDBA'
      OR USER = USER_NAME;


/** Grants. **/

GRANT SELECT ON USERS TO PUBLIC;

GRANT UPDATE(PASSWD, GROUP_NAME, UID, GID, FIRST_NAME, MIDDLE_NAME, LAST_NAME)
   ON USERS
   TO PUBLIC;