/******************************************************************************/
/****      Generated by IBExpert 2007.07.20 04/11/2007 09:31:11 a.m.       ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES NONE;

CREATE DATABASE 'ubandres:security'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET NONE;



/******************************************************************************/
/****                              Exceptions                              ****/
/******************************************************************************/

CREATE EXCEPTION UPDUSRNOPERMITIDO 'El usuario no tiene privilegios para hacer esa modificación';



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE RDB$USERS (
    RDB$USER_NAME      VARCHAR(128) CHARACTER SET UNICODE_FSS NOT NULL,
    RDB$SYS_USER_NAME  VARCHAR(128) CHARACTER SET UNICODE_FSS,
    RDB$GROUP_NAME     VARCHAR(128) CHARACTER SET UNICODE_FSS,
    RDB$UID            INTEGER,
    RDB$GID            INTEGER,
    RDB$PASSWD         VARCHAR(64) CHARACTER SET OCTETS NOT NULL,
    RDB$PRIVILEGE      INTEGER,
    RDB$COMMENT        BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UNICODE_FSS,
    RDB$FIRST_NAME     VARCHAR(32) CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS '',
    RDB$MIDDLE_NAME    VARCHAR(32) CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS '',
    RDB$LAST_NAME      VARCHAR(32) CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS ''
);




/******************************************************************************/
/****                                Views                                 ****/
/******************************************************************************/


/* View: USERS */
CREATE VIEW USERS(
    USER_NAME,
    SYS_USER_NAME,
    GROUP_NAME,
    UID,
    GID,
    PASSWD,
    PRIVILEGE,
    COMMENT,
    FIRST_NAME,
    MIDDLE_NAME,
    LAST_NAME,
    FULL_NAME)
AS
SELECT RDB$USER_NAME, RDB$SYS_USER_NAME, RDB$GROUP_NAME, RDB$UID, RDB$GID, RDB$PASSWD, 
		RDB$PRIVILEGE, RDB$COMMENT, RDB$FIRST_NAME, RDB$MIDDLE_NAME, RDB$LAST_NAME, 
		RDB$first_name || _UNICODE_FSS ' ' || RDB$middle_name || _UNICODE_FSS ' ' || RDB$last_name
	FROM RDB$USERS
	WHERE CURRENT_USER = 'SYSDBA'
	   OR CURRENT_USER = RDB$USERS.RDB$USER_NAME
;


INSERT INTO RDB$USERS (RDB$USER_NAME, RDB$SYS_USER_NAME, RDB$GROUP_NAME, RDB$UID, RDB$GID, RDB$PASSWD, RDB$PRIVILEGE, RDB$FIRST_NAME, RDB$MIDDLE_NAME, RDB$LAST_NAME) VALUES ('SYSDBA', NULL, NULL, NULL, NULL, 'NLtwcs9LrxLMOYhG0uGM9i6KS7mf3QAKvFVpmRg=', NULL, 'Sql', 'Server', 'Administrator');
INSERT INTO RDB$USERS (RDB$USER_NAME, RDB$SYS_USER_NAME, RDB$GROUP_NAME, RDB$UID, RDB$GID, RDB$PASSWD, RDB$PRIVILEGE, RDB$FIRST_NAME, RDB$MIDDLE_NAME, RDB$LAST_NAME) VALUES ('USR', NULL, NULL, 0, 0, 'oxHU1.k1WhI', NULL, NULL, NULL, NULL);
INSERT INTO RDB$USERS (RDB$USER_NAME, RDB$SYS_USER_NAME, RDB$GROUP_NAME, RDB$UID, RDB$GID, RDB$PASSWD, RDB$PRIVILEGE, RDB$FIRST_NAME, RDB$MIDDLE_NAME, RDB$LAST_NAME) VALUES ('ADMINCLAVES', NULL, NULL, 0, 0, 'oxHU1.k1WhI', NULL, NULL, NULL, NULL);
INSERT INTO RDB$USERS (RDB$USER_NAME, RDB$SYS_USER_NAME, RDB$GROUP_NAME, RDB$UID, RDB$GID, RDB$PASSWD, RDB$PRIVILEGE, RDB$FIRST_NAME, RDB$MIDDLE_NAME, RDB$LAST_NAME) VALUES ('CFG', NULL, NULL, 0, 0, 'oxHU1.k1WhI', NULL, NULL, NULL, NULL);

COMMIT WORK;



/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE RDB$USERS ADD PRIMARY KEY (RDB$USER_NAME);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/****                     Triggers for updatable views                     ****/
/******************************************************************************/



/* Trigger: SOLOADMINELIMINA */
CREATE TRIGGER SOLOADMINELIMINA FOR USERS
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
  POST_EVENT 'DUMMY_EVENT';
END
^


/* Trigger: SOLOADMININSERTA */
CREATE TRIGGER SOLOADMININSERTA FOR USERS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  POST_EVENT 'DUMMY_EVENT';
END
^


/* Trigger: SOLOELMISMOUSUARIO */
CREATE TRIGGER SOLOELMISMOUSUARIO FOR USERS
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
  POST_EVENT 'DUMMY_EVENT';
END
^


ALTER TRIGGER SOLOADMINELIMINA
AS
begin
     IF (((CURRENT_USER <> 'SYSDBA') and (CURRENT_USER <> 'ADMINCLAVES'))
       or (old.USER_NAME = 'SYSDBA') or (old.USER_NAME = 'ADMINCLAVES')) then begin
           exception UPDUSRNOPERMITIDO;
     end
end
^


ALTER TRIGGER SOLOADMININSERTA
AS
begin
     IF ((CURRENT_USER <> 'SYSDBA') and (CURRENT_USER <> 'ADMINCLAVES')) then begin
           exception UPDUSRNOPERMITIDO;
     end
end
^


ALTER TRIGGER SOLOELMISMOUSUARIO
AS begin
  IF (CURRENT_USER <> 'SYSDBA') then begin
     IF (
     (old.USER_NAME = 'SYSDBA') or
     ((CURRENT_USER <> 'ADMINCLAVES') and (old.USER_NAME <> CURRENT_USER)) or
     (old.USER_NAME <> new.USER_NAME)
     ) then begin
           exception UPDUSRNOPERMITIDO;
     end
  end
end
^


SET TERM ; ^



/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/


/* Privileges of users */
GRANT INSERT, UPDATE(GID,MIDDLE_NAME,UID,USER_NAME,PRIVILEGE,FULL_NAME,COMMENT,GROUP_NAME,FIRST_NAME,LAST_NAME,PASSWD,SYS_USER_NAME), DELETE ON USERS TO ADMINCLAVES;
GRANT SELECT ON RDB$ROLES TO PUBLIC;
GRANT SELECT, UPDATE(GID,MIDDLE_NAME,UID,USER_NAME,PRIVILEGE,FULL_NAME,COMMENT,GROUP_NAME,FIRST_NAME,LAST_NAME,PASSWD,SYS_USER_NAME) ON USERS TO PUBLIC;

/* Privileges of views */
GRANT ALL ON RDB$USERS TO VIEW USERS;