صلاحيات فى الاوراكل

:angry:

إليكم الجداول كاملة لعمل الصلاحيات وهي كما ذكرت لكم من أقوى الصلاحيات

وهو كما مرفق يتم بناءه كما هو مرتب

 

 

 

CREATE TABLE COMMON_USERS

(

USER_NO NUMBER(7) DEFAULT '0 ' NOT NULL,

EMPLOYEE_NO NUMBER(7) DEFAULT '0',

USER_NAME VARCHAR2(40) NOT NULL,

PASSWORD VARCHAR2(6) NOT NULL,

DEPT_NO VARCHAR2(10),

FREEEZE_UNFREEZE NUMBER(1) DEFAULT '0' NOT NULL,

PRIVILEGE_FLAG NUMBER(1) DEFAULT '0' NOT NULL,

SYSTEM_EXP_DATE DATE,

COUNT_EXIT_NON_SYS NUMBER(2) DEFAULT '0' NOT NULL,

FLAG NUMBER(1)

)

/

COMMENT ON COLUMN COMMON_USERS.USER_NO IS 'رمز المستخدم';

/

COMMENT ON COLUMN COMMON_USERS.EMPLOYEE_NO IS 'رقم الموظف';

/

COMMENT ON COLUMN COMMON_USERS.USER_NAME IS 'اسم المستخدم';

/

COMMENT ON COLUMN COMMON_USERS.PASSWORD IS 'كلمة السر';

/

COMMENT ON COLUMN COMMON_USERS.DEPT_NO IS 'رقم الادراة';

/

COMMENT ON COLUMN COMMON_USERS.FREEEZE_UNFREEZE IS 'المستخدم موقوف/غير موقوف';

/

COMMENT ON COLUMN COMMON_USERS.PRIVILEGE_FLAG IS 'صلاحيات المستخدم';

/

COMMENT ON COLUMN COMMON_USERS.SYSTEM_EXP_DATE IS 'اخر تاريخ تعديل';

/

COMMENT ON COLUMN COMMON_USERS.COUNT_EXIT_NON_SYS IS 'الخروج الطبيعي';

/

ALTER TABLE COMMON_USERS ADD (CONSTRAINT CU_PK PRIMARY KEY (USER_NO))

/

CREATE TABLE COMMON_SYSTEMS

(

SYSTEM_NO NUMBER(2) DEFAULT 0 NOT NULL,

SYSTEM_SHORT_NAME VARCHAR2(3) NOT NULL,

SYSTEM_DESC VARCHAR2(30) NOT NULL,

FREEZE_UNFREEZE NUMBER(1) DEFAULT 0 NOT NULL,

SYSTEM_PATH VARCHAR2(100) NOT NULL,

PRV_PRV_TYPE NUMBER(2) NOT NULL

)

/

COMMENT ON COLUMN COMMON_SYSTEMS.SYSTEM_NO IS 'رمز النظام';

/

COMMENT ON COLUMN COMMON_SYSTEMS.SYSTEM_SHORT_NAME IS 'الرمز المختصر';

/

COMMENT ON COLUMN COMMON_SYSTEMS.SYSTEM_DESC IS 'اسم النظام';

/

COMMENT ON COLUMN COMMON_SYSTEMS.FREEZE_UNFREEZE IS 'تجميدالنظام';

/

COMMENT ON COLUMN COMMON_SYSTEMS.SYSTEM_PATH IS 'المسار في المحث';

/

COMMENT ON COLUMN COMMON_SYSTEMS.PRV_PRV_TYPE IS 'رمز الصلاحية';

/

ALTER TABLE COMMON_SYSTEMS ADD (CONSTRAINT CS_PK PRIMARY KEY (SYSTEM_NO))

/

ALTER TABLE COMMON_SYSTEMS ADD (CONSTRAINT CS_PRV_FK FOREIGN KEY (PRV_PRV_TYPE)

REFERENCES PRIVILAGES_TYPES (PRV_TYPE))

/

CREATE TABLE FORMS_REPORTS_INFOS

(

FRM_REP_TYPE NUMBER(1) DEFAULT 0 NOT NULL,

FRM_REP_NO NUMBER(5) DEFAULT 0 NOT NULL,

FRM_REP_ALTERNATE_NO NUMBER(5) DEFAULT 0 NOT NULL,

FRM_REP_DESC VARCHAR2(60) NOT NULL,

SYS_SHORT_ALTERNATE_NAME VARCHAR2(3) NOT NULL,

FRM_REP_CLASS NUMBER(2) DEFAULT 0 NOT NULL,

FREEZE_UNFREEZE NUMBER(1) DEFAULT 0 NOT NULL,

CS_SYSTEM_NO NUMBER(2) NOT NULL

)

/

COMMENT ON COLUMN FORMS_REPORTS_INFOS.FRM_REP_TYPE IS 'النوع';

/

COMMENT ON COLUMN FORMS_REPORTS_INFOS.FRM_REP_NO IS 'رمز الشاشة أوالتقرير';

/

COMMENT ON COLUMN FORMS_REPORTS_INFOS.FRM_REP_ALTERNATE_NO IS 'الرقم البديل';

/

COMMENT ON COLUMN FORMS_REPORTS_INFOS.FRM_REP_DESC IS 'اسم الشاشة';

/

COMMENT ON COLUMN FORMS_REPORTS_INFOS.SYS_SHORT_ALTERNATE_NAME IS 'اسم المختصر لنظام البديل';

/

COMMENT ON COLUMN FORMS_REPORTS_INFOS.FRM_REP_CLASS IS 'موقع الشاشة في القائمة';

/

COMMENT ON COLUMN FORMS_REPORTS_INFOS.FREEZE_UNFREEZE IS 'مجمد';

/

COMMENT ON COLUMN FORMS_REPORTS_INFOS.CS_SYSTEM_NO IS 'رمز النظام';

/

ALTER TABLE FORMS_REPORTS_INFOS ADD (CONSTRAINT FRI_PK PRIMARY KEY (CS_SYSTEM_NO, FRM_REP_NO, FRM_REP_TYPE))

/

ALTER TABLE FORMS_REPORTS_INFOS ADD ( CONSTRAINT FRI_CS_FK FOREIGN KEY (CS_SYSTEM_NO)

REFERENCES COMMON_SYSTEMS (SYSTEM_NO))

/

CREATE TABLE COMMON_GROUPS

(

GROUP_NO NUMBER(4) DEFAULT 0 NOT NULL,

GROUP_TYPE NUMBER(1) DEFAULT 0 NOT NULL,

GROUP_NAME VARCHAR2(60) NOT NULL,

FREEZE_UNFREEZE NUMBER(1) DEFAULT 0 NOT NULL

)

/

COMMENT ON COLUMN COMMON_GROUPS.GROUP_NO IS 'رمز المجموعة';

/

COMMENT ON COLUMN COMMON_GROUPS.GROUP_TYPE IS 'نوع المجموعة (اضافة،استبعاد)';

/

COMMENT ON COLUMN COMMON_GROUPS.GROUP_NAME IS 'اسم المجموعة';

/

COMMENT ON COLUMN COMMON_GROUPS.FREEZE_UNFREEZE IS 'تجميد المجموعة';

/

ALTER TABLE COMMON_GROUPS ADD (CONSTRAINT CG_PK PRIMARY KEY (GROUP_NO))

/

CREATE TABLE COMMON_SYS_PRVS

(

NOTES VARCHAR2(30),

CS_SYSTEM_NO NUMBER(2) NOT NULL,

CG_GROUP_NO NUMBER(4) NOT NULL,

PRV_PRV_TYPE NUMBER(2) NOT NULL

)

/

COMMENT ON COLUMN COMMON_SYS_PRVS.NOTES IS 'ملاحظات';

/

COMMENT ON COLUMN COMMON_SYS_PRVS.CS_SYSTEM_NO IS 'رمز النظام';

/

COMMENT ON COLUMN COMMON_SYS_PRVS.CG_GROUP_NO IS 'رمز المجموعة';

/

COMMENT ON COLUMN COMMON_SYS_PRVS.PRV_PRV_TYPE IS 'رمز الصلاحية';

/

ALTER TABLE COMMON_SYS_PRVS ADD (CONSTRAINT CSP_PK PRIMARY KEY (CS_SYSTEM_NO, CG_GROUP_NO))

/

ALTER TABLE COMMON_SYS_PRVS ADD (CONSTRAINT CSP_CG_FK FOREIGN KEY (CG_GROUP_NO)

REFERENCES COMMON_GROUPS (GROUP_NO))

/

ALTER TABLE COMMON_SYS_PRVS ADD (CONSTRAINT CSP_CS_FK FOREIGN KEY (CS_SYSTEM_NO)

REFERENCES COMMON_SYSTEMS (SYSTEM_NO))

/

ALTER TABLE COMMON_SYS_PRVS ADD (CONSTRAINT CSP_PRV_FK FOREIGN KEY (PRV_PRV_TYPE)

REFERENCES PRIVILAGES_TYPES (PRV_TYPE))

/

CREATE TABLE FORMS_REPORTS_PRVS

(

NOTES VARCHAR2(30),

FRI_CS_SYSTEM_NO NUMBER(2) NOT NULL,

FRI_FRM_REP_NO NUMBER(5) NOT NULL,

FRI_FRM_REP_TYPE NUMBER(1) NOT NULL,

CG_GROUP_NO NUMBER(4) NOT NULL,

PRV_PRV_TYPE NUMBER(2) NOT NULL

)

/

COMMENT ON COLUMN FORMS_REPORTS_PRVS.NOTES IS 'ملاحظات';

/

COMMENT ON COLUMN FORMS_REPORTS_PRVS.FRI_CS_SYSTEM_NO IS 'رمز النظام';

/

COMMENT ON COLUMN FORMS_REPORTS_PRVS.FRI_FRM_REP_NO IS 'رمز الشاشة أوالتقرير';

/

COMMENT ON COLUMN FORMS_REPORTS_PRVS.FRI_FRM_REP_TYPE IS 'النوع';

/

COMMENT ON COLUMN FORMS_REPORTS_PRVS.CG_GROUP_NO IS 'رمز المجموعة';

/

COMMENT ON COLUMN FORMS_REPORTS_PRVS.PRV_PRV_TYPE IS 'رمز الصلاحية';

/

ALTER TABLE FORMS_REPORTS_PRVS ADD (CONSTRAINT FN_PK PRIMARY KEY (FRI_CS_SYSTEM_NO, FRI_FRM_REP_NO, FRI_FRM_REP_TYPE, CG_GROUP_NO))

/

ALTER TABLE FORMS_REPORTS_PRVS ADD (CONSTRAINT FN_CG_FK FOREIGN KEY (CG_GROUP_NO)

REFERENCES COMMON_GROUPS (GROUP_NO))

/

ALTER TABLE FORMS_REPORTS_PRVS ADD (CONSTRAINT FN_FRI_FK FOREIGN KEY (FRI_CS_SYSTEM_NO, FRI_FRM_REP_NO, FRI_FRM_REP_TYPE)

REFERENCES FORMS_REPORTS_INFOS (CS_SYSTEM_NO,FRM_REP_NO,FRM_REP_TYPE))

/

ALTER TABLE FORMS_REPORTS_PRVS ADD (CONSTRAINT FN_PRV_FK FOREIGN KEY (PRV_PRV_TYPE)

REFERENCES PRIVILAGES_TYPES (PRV_TYPE))

/

CREATE TABLE COMMON_USER_GROUPS

(

NOTES VARCHAR2(10) NOT NULL,

CU_USER_NO NUMBER(7) NOT NULL,

CG_GROUP_NO NUMBER(4) NOT NULL

)

/

COMMENT ON COLUMN COMMON_USER_GROUPS.NOTES IS 'ملاحظات';

/

COMMENT ON COLUMN COMMON_USER_GROUPS.CU_USER_NO IS 'رمز المستخدم';

/

COMMENT ON COLUMN COMMON_USER_GROUPS.CG_GROUP_NO IS 'رمز المجموعة';

/

ALTER TABLE COMMON_USER_GROUPS ADD (CONSTRAINT CUG_PK PRIMARY KEY (CU_USER_NO, CG_GROUP_NO))

/

ALTER TABLE COMMON_USER_GROUPS ADD (CONSTRAINT CUG_CG_FK FOREIGN KEY (CG_GROUP_NO)

REFERENCES COMMON_GROUPS (GROUP_NO))

/

ALTER TABLE COMMON_USER_GROUPS ADD (CONSTRAINT CUG_CU_FK FOREIGN KEY (CU_USER_NO)

REFERENCES COMMON_USERS (USER_NO))

/

CREATE TABLE SYSTEM_MAILS

(

MAIL_TYPE NUMBER(3) NOT NULL,

MAIL_PRIMARY NUMBER(6),

MAIL_SCNDARY NUMBER(6) NOT NULL,

MAIL_NAME VARCHAR2(50) NOT NULL,

MAIL_SHORT_NAME VARCHAR2(20),

MAIL_FLAG NUMBER(1) NOT NULL

)

/

CREATE TABLE NUMBERS

(

NO NUMBER(10),

NAME VARCHAR2(50)

)

/

CREATE TABLE GREG_HIJ_BASE

(

GREG_DATE DATE NOT NULL,

HIJRI_DATE DATE NOT NULL

)

/

insert into privilages_types values (20,'11111','NULL');

insert into privilages_types values (19,'11110','NULL');

insert into privilages_types values (18 ,'11011','NULL');

insert into privilages_types values (17 ,'11010','NULL');

insert into privilages_types values (16 ,'10111','NULL');

insert into privilages_types values (15 ,'10110','NULL');

insert into privilages_types values (14 ,'10011','NULL');

insert into privilages_types values (13 ,'10001','NULL');

insert into privilages_types values (12 ,'10010','NULL');

insert into privilages_types values (11 ,'10000','NULL');

insert into privilages_types values (10 ,'01111','NULL');

insert into privilages_types values (9,'01110','NULL');

insert into privilages_types values (8,'01011','NULL');

insert into privilages_types values (7,'01010','NULL');

insert into privilages_types values (6,'00111','NULL');

insert into privilages_types values (5,'00110','NULL');

insert into privilages_types values (4,'00011','NULL');

insert into privilages_types values (3,'00001','NULL');

insert into privilages_types values (2,'00010','NULL');

insert into privilages_types values (1,'00000','NULL')

/

CREATE OR REPLACE FORCE VIEW MAIN.COMMON_SYSTEM_V1

(CU_USER_NO, CG_GROUP_NO, CS_SYSTEM_NO, FRM_REP_NO, FRM_REP_TYPE,

SYS_SHORT_ALTERNATE_NAME, FREEZE_UNFREEZE, PRV_PRV_TYPE)

AS

(SELECT

A.CU_USER_NO ,

B.CG_GROUP_NO ,

C.CS_SYSTEM_NO,

C.FRM_REP_NO,

C.FRM_REP_TYPE,

C.SYS_SHORT_ALTERNATE_NAME,

C.FREEZE_UNFREEZE,

B.PRV_PRV_TYPE

FROM COMMON_USER_GROUPS A,COMMON_SYS_PRVS B,FORMS_REPORTS_INFOS C

WHERE A.CG_GROUP_NO =B.CG_GROUP_NO AND

B.CS_SYSTEM_NO=C.CS_SYSTEM_NO AND EXISTS

(SELECT F.GROUP_NO FROM COMMON_GROUPS F WHERE

B.CG_GROUP_NO=F.GROUP_NO AND F.GROUP_TYPE=1)

UNION

SELECT

A.CU_USER_NO ,

B.CG_GROUP_NO ,

B.FRI_CS_SYSTEM_NO ,

B.FRI_FRM_REP_NO ,

B.FRI_FRM_REP_TYPE ,

'' ,

0 ,

B.PRV_PRV_TYPE

FROM COMMON_USER_GROUPS A,FORMS_REPORTS_PRVS B

WHERE A.CG_GROUP_NO =B.CG_GROUP_NO AND EXISTS

(SELECT F.GROUP_NO FROM COMMON_GROUPS F WHERE

B.CG_GROUP_NO=F.GROUP_NO AND F.GROUP_TYPE=2));

/

 

CREATE OR REPLACE FORCE VIEW MAIN.COMMON_SYS_MIN_FRM

(CU_USER_NO, CS_SYSTEM_NO, FRM_REP_NO, FRM_REP_TYPE, PRV_PRV_TYPE)

AS

(SELECT

G.CU_USER_NO ,

G.CS_SYSTEM_NO ,

G.FRM_REP_NO ,

G.FRM_REP_TYPE ,

MIN(G.PRV_PRV_TYPE) PRV_PRV_TYPE

FROM COMMON_SYSTEM_V1 G

GROUP BY

G.CU_USER_NO ,

G.CS_SYSTEM_NO ,

G.FRM_REP_NO ,

G.FRM_REP_TYPE );

/

CREATE OR REPLACE FORCE VIEW MAIN.COMMON_SYSTEM_V2

(CU_USER_NO, CG_GROUP_NO, CS_SYSTEM_NO, FRM_REP_NO, FRM_REP_TYPE,

PRV_PRV_TYPE)

AS

(SELECT

G.CU_USER_NO ,

0 CG_GROUP_NO ,

G.CS_SYSTEM_NO ,

G.FRM_REP_NO ,

G.FRM_REP_TYPE ,

G.PRV_PRV_TYPE

FROM COMMON_SYS_MIN_FRM G

UNION

SELECT

A.CU_USER_NO ,

B.CG_GROUP_NO ,

B.FRI_CS_SYSTEM_NO ,

B.FRI_FRM_REP_NO ,

B.FRI_FRM_REP_TYPE ,

B.PRV_PRV_TYPE

FROM COMMON_USER_GROUPS A,FORMS_REPORTS_PRVS B

WHERE A.CG_GROUP_NO =B.CG_GROUP_NO AND EXISTS

(SELECT F.GROUP_NO FROM COMMON_GROUPS F WHERE

B.CG_GROUP_NO=F.GROUP_NO AND F.GROUP_TYPE IN (2,3)));

/

CREATE OR REPLACE FORCE VIEW MAIN.COMMON_SYS_MAX_FRM

(CU_USER_NO, CS_SYSTEM_NO, FRM_REP_NO, FRM_REP_TYPE, PRV_PRV_TYPE)

AS

(SELECT

G.CU_USER_NO ,

G.CS_SYSTEM_NO ,

G.FRM_REP_NO ,

G.FRM_REP_TYPE ,

MAX(G.PRV_PRV_TYPE) PRV_PRV_TYPE

FROM COMMON_SYSTEM_V2 G

GROUP BY

G.CU_USER_NO ,

G.CS_SYSTEM_NO ,

G.FRM_REP_NO ,

G.FRM_REP_TYPE );

/

CREATE OR REPLACE FORCE VIEW MAIN.COMMON_SYS_FRM_PRV

(CU_USER_NO, CS_SYSTEM_NO, FRM_REP_NO, FRM_REP_TYPE, FRM_REP_ALTERNATE_NO,

SYS_SHORT_ALTERNATE_NAME, FREEZE_UNFREEZE, FRM_REP_DESC, FRM_REP_CLASS, SYSTEM_SHORT_NAME,

SYSTEM_DESC, SYS_FREEZE_UNFREEZE, SYSTEM_PATH, PRV_ACT, PRV_PRV_TYPE)

AS

(SELECT

A.CU_USER_NO ,

A.CS_SYSTEM_NO ,

A.FRM_REP_NO ,

A.FRM_REP_TYPE ,

B.FRM_REP_ALTERNATE_NO,

B.SYS_SHORT_ALTERNATE_NAME,

B.FREEZE_UNFREEZE ,

B.FRM_REP_DESC ,

B.FRM_REP_CLASS ,

C.SYSTEM_SHORT_NAME ,

C.SYSTEM_DESC ,

C.FREEZE_UNFREEZE SYS_FREEZE_UNFREEZE,

C.SYSTEM_PATH ,

D.PRV_ACT ,

A.PRV_PRV_TYPE

FROM COMMON_SYS_MAX_FRM A,FORMS_REPORTS_INFOS B,

COMMON_SYSTEMS C ,PRIVILAGES_TYPES D

WHERE

A.CS_SYSTEM_NO=B.CS_SYSTEM_NO AND

A.FRM_REP_NO =B.FRM_REP_NO AND

A.FRM_REP_TYPE=B.FRM_REP_TYPE AND

A.PRV_PRV_TYPE=D.PRV_TYPE AND

A.CS_SYSTEM_NO=C.SYSTEM_NO AND

(A.PRV_PRV_TYPE>1 OR A.FRM_REP_TYPE<>1));

/