Курсовая работа: Захист інформації в інформаційних системах

ACCESS_LEVEL INTEGER REFERENCES

ACCESS_LEVELS(ACCESS_LEVEL_ID));

REVOKE ALL ON GROUPS_ACCESS_LEVEL FROM GROUP USERS;

GRANT SELECT ON GROUPS_ACCESS_LEVEL TO GROUP USERS;

INSERT INTO GROUPS_ACCESS_LEVEL VALUES ('users',2);

ALTER TABLE PERSONS

ADD COLUMN SPOT_CONF INTEGER DEFAULT 1

REFERENCES ACCESS_LEVELS(ACCESS_LEVEL_ID);

CREATE OR REPLACE VIEW PERSONS_LIST AS

SELECT PERSON_ID, NAME, SEX, BIRTHDAY

FROM PG_GROUP G, PG_USER U, PERSONS P

GROUPS_ACCESS_LEVEL L

WHERE

USENAME = CURRENT_USER AND

U.USESYSID = ANY (G.GROLIST) AND

L.GROUP_NAME = G.GRONAME AND

P.SPOT_CONF <= L.ACCESS_LEVEL;

REVOKE ALL ON PERSONS FROM GROUP USERS;

GRANT SELECT ON PERSONS_LIST TO GROUP USERS;

INSERT INTO PERSONS_LIST VALUES (1,'Tkachuk','M','23-02-1986');

UPDATE PERSONS SET SPOT_CONF = 4 WHERE PERSON_ID = 1;

INSERT INTO PERSONS_LIST VALUES (1,'Ivanov','M','15-03-1987');

UPDATE PERSONS SET SPOT_CONF = 1 WHERE PERSON_ID = 2;

DROP RULE PERSONS_LIST_INSERT ON PERSONS_LIST;

CREATE RULE PERSONS_LIST_INSERT AS ON INSERT TO PERSONS_LIST DO INSTEAD

INSERT INTO PERSONS

SELECT CASE WHEN NEW.PERSON_ID IS NULL THEN NEXTVAL('PERSON_ID') ELSE NEW.PERSON_ID END

NEW.NAME, NEW.SEX, NEW.BIRTHDAY, L.ACCESS_LEVEL

FROM PG_GROUP G, PG_USER U, GROUPS_ACCESS_LEVEL L