بسم الله الرحمن الرحيم
اللهم علمنا ما جهلنا و زدنا علما نافعا
الدرس الاول يا اخوان هيكون كا التالي :-
1-عمل تحليل لنظام الصلاحيات
هيكون عبارة عن ملفات مرفقه
* Security ERD
*Create table
-----------------------------------------------------------------------------------------------------------------------
/* ---------------------------------------------------------------------- */
/* Tables */
/* ---------------------------------------------------------------------- */
/* ---------------------------------------------------------------------- */
/* Add table "USERS" */
/* ---------------------------------------------------------------------- */
CREATE TABLE SCS.USERS (
USER_ID NUMBER(9) CONSTRAINT NNUSERID NULL,
USER_NAME VARCHAR2(60) CONSTRAINT NN_USER_NAME NOT NULL,
FIRST_NAME VARCHAR2(40),
LAST_NAME VARCHAR2(40),
CARD_CODE NUMBER(10),
USER_TYPE NUMBER(1),
STATUS NUMBER(1),
PASSWORD VARCHAR2(50),
CONSTRAINT PK_USERS PRIMARY KEY (USERS)
);
/* ---------------------------------------------------------------------- */
/* Add table "SYSTEMS" */
/* ---------------------------------------------------------------------- */
CREATE TABLE SCS.SYSTEMS (
SYSTEM_CODE NUMBER(3) CONSTRAINT NN_SYSTEM_ID NOT NULL,
SYTEM_NAME VARCHAR2(150) CONSTRAINT NN_SYTEM_NAME NOT NULL,
CONSTRAINT PK_SYSTEMS PRIMARY KEY (SYSTEM_CODE)
);
/* ---------------------------------------------------------------------- */
/* Add table "SYSTEM_ROLLS" */
/* ---------------------------------------------------------------------- */
CREATE TABLE SCS.SYSTEM_ROLLS (
ROLLS_CODE NUMBER(6) CONSTRAINT NN_ROLLS_CODE NOT NULL,
ROLLS_NAME VARCHAR2(150),
SYSTEM_CODE NUMBER(3) CONSTRAINT NN_ROLLS_SYSTEM_CODE NOT NULL,
CONSTRAINT PK_SYSTEM_ROLLS PRIMARY KEY (ROLLS_CODE, SYSTEM_CODE)
);
/* ---------------------------------------------------------------------- */
/* Add table "ROLLS_CONTENT" */
/* ---------------------------------------------------------------------- */
CREATE TABLE SCS.ROLLS_CONTENT (
ROLLS_CODE NUMBER(6) CONSTRAINT NN_ROLLS_CONTENT_CODE NOT NULL,
PRIVILEGE_ACCESS NUMBER(1),
PRIVILEGE_INSERT NUMBER(1),
PRIVILEGE_DELETE NUMBER(1),
PRIVILEGE_UPDATE NUMBER(1),
PRIVILEGE_PRINT NUMBER(1),
PRIVILEGE_QUERY NUMBER(1),
ROLLS_TYPE NUMBER(1),
PAGE_CODE NUMBER(6) CONSTRAINT NN_ROLLS_CONTENT_PAGE_CODE NOT NULL,
SYSTEM_CODE NUMBER(3) CONSTRAINT NN_ROLLS_CONTENT_SYSTEM_CODE NOT NULL,
CONSTRAINT PK_ROLLS_CONTENT PRIMARY KEY (ROLLS_CODE, PAGE_CODE, SYSTEM_CODE)
);
/* ---------------------------------------------------------------------- */
/* Add table "PAGES" */
/* ---------------------------------------------------------------------- */
CREATE TABLE SCS.PAGES (
PAGE_CODE NUMBER(6) CONSTRAINT NN_PAGE_CODE NOT NULL,
PAGE_NAME VARCHAR2(50) CONSTRAINT NN_PAGE_NAME NOT NULL,
SYSTEM_CODE NUMBER(3) CONSTRAINT NN_PAGES_SYSTEM_CODE NOT NULL,
CONSTRAINT PK_PAGES PRIMARY KEY (PAGE_CODE, SYSTEM_CODE)
);
/* ---------------------------------------------------------------------- */
/* Add table "SYSTEM_ROLLS_USERS" */
/* ---------------------------------------------------------------------- */
CREATE TABLE SCS.SYSTEM_ROLLS_USERS (
ROLLS_CODE NUMBER(6) CONSTRAINT NN_1 NOT NULL,
SYSTEM_CODE NUMBER(3) CONSTRAINT NN_2 NOT NULL,
USER_ID NUMBER(9) CONSTRAINT NN_3 NOT NULL,
STATUS NUMBER(1),
CONSTRAINT PK_SYSTEM_ROLLS_USERS PRIMARY KEY (ROLLS_CODE, SYSTEM_CODE, USER_ID)
);
/* ---------------------------------------------------------------------- */
/* Foreign key constraints */
/* ---------------------------------------------------------------------- */
ALTER TABLE SCS.SYSTEM_ROLLS ADD CONSTRAINT FK_SYSTEM_ROLLS
FOREIGN KEY (SYSTEM_CODE) REFERENCES SCS.SYSTEMS (SYSTEM_CODE);
ALTER TABLE SCS.ROLLS_CONTENT ADD CONSTRAINT FK_ROLLS_CONTENT_PAGE
FOREIGN KEY (PAGE_CODE, SYSTEM_CODE) REFERENCES SCS.PAGES (PAGE_CODE,SYSTEM_CODE);
ALTER TABLE SCS.ROLLS_CONTENT ADD CONSTRAINT FK_ROLLS_CONTENT
FOREIGN KEY (ROLLS_CODE, SYSTEM_CODE) REFERENCES SCS.SYSTEM_ROLLS (ROLLS_CODE,SYSTEM_CODE);
ALTER TABLE SCS.PAGES ADD CONSTRAINT FK_SYSTEMS_PAGES
FOREIGN KEY (SYSTEM_CODE) REFERENCES SCS.SYSTEMS (SYSTEM_CODE);
ALTER TABLE SCS.SYSTEM_ROLLS_USERS ADD CONSTRAINT FK_ROLLS_SYSTEM_ROLLS_USERS
FOREIGN KEY (ROLLS_CODE, SYSTEM_CODE) REFERENCES SCS.SYSTEM_ROLLS (ROLLS_CODE,SYSTEM_CODE);
ALTER TABLE SCS.SYSTEM_ROLLS_USERS ADD CONSTRAINT FK_SYSTEM_ROLLS_USERS_ID
FOREIGN KEY (USER_ID) REFERENCES SCS.USERS (USER_ID);
-----------------------------------------------------------------------------------------------------------------------
2-انشاء مستخدم وليكن اسمه HOTELS بكلمة سرHOTELS هنعطيه صلاحيات DBA استخدم هذا CODE
CONN SYSTEM/SYS@XE
CREATE USER HOTELS IDENTIFIED BY HOTELS ;
-- ROLES
GRANT "DBA" TO HOTELS ;
3-انشاء مستخدم خاص بالنظام الصلاحيات وليكن اسمة SCS بكلمة سر SCS
هنعطيه صلاحيات
من خلال JDEVELOPER نعمل CONNECT WITH HOTELS و من ثم ننشأ SCS بصلاحيات
(RESOURCE- CONNECT)
