Restricting access to the USERS table

Any user can log in to ISC4.GDB and manipulate data in the USERS table. Here is how to prevent this.

/*
  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;

 

Share this article!

Follow us!

Find more helpful articles: