lesson 1

                                 بسم الله الرحمن الرحيم 

اللهم علمنا ما جهلنا و زدنا علما نافعا

الدرس الاول يا اخوان هيكون كا التالي :-
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)