Hello,
I have Three Tables in My Schema,
CREATE TABLE "DB_USER_PRIVILEGES"
(
"USER_ID" NUMBER(15,0),
"FORM_ID" NUMBER(15,0),
"VIEW_TO" VARCHAR2(200 CHAR),
"CREATE_TO" VARCHAR2(200 CHAR),
"UPDATE_TO" VARCHAR2(200 CHAR),
"DELETE_TO" VARCHAR2(200 CHAR),
"PRINT_TO" VARCHAR2(200 CHAR),
"RESEND_SMS" VARCHAR2(200 CHAR),
"RESEND_EMAIL" VARCHAR2(200 CHAR),
"STATUS" VARCHAR2(20 CHAR),
"EX" VARCHAR2(200 CHAR),
"EX1" VARCHAR2(200 CHAR),
"EX2" NUMBER(15,3),
"EX3" NUMBER(15,3),
CONSTRAINT "DB_USER_PRIVILEGES_DB_USE_FK1" FOREIGN KEY ("USER_ID") REFERENCES DB_USERS" ("SRNO") ENABLE,
CONSTRAINT "DB_USER_PRIVILEGES_FORM_M_FK1" FOREIGN KEY ("FORM_ID") REFERENCES FORM_MASTER" ("FORM_ID") ENABLE
)
CREATE TABLE DB_USERS"
(
"SRNO" NUMBER(15,0) NOT NULL ENABLE,
"DB_USER" VARCHAR2(20 BYTE),
"DB_PASSWORD" VARCHAR2(20 BYTE),
"DB_STATUS" VARCHAR2(20 BYTE),
"DB_CREATION_DATE" DATE,
"USER_NAME" VARCHAR2(200 CHAR),
"USER_DEPT_ID" NUMBER(15,0),
"USER_COMP_IPADD" VARCHAR2(200 CHAR),
"EX" VARCHAR2(200 CHAR),
"EX1" VARCHAR2(200 CHAR),
"EX2" NUMBER(15,3),
"EX3" NUMBER(15,3),
CONSTRAINT "DB_USERS_PK" PRIMARY KEY ("SRNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE,
CONSTRAINT FOREIGN KEY ("USER_DEPT_ID") REFERENCES DEPARTMENT_MASTER" ("SRNO") ENABLE
)
CREATE TABLE FORM_MASTER"
(
"FORM_ID" NUMBER(15,0) NOT NULL ENABLE,
"FORM_NAME" VARCHAR2(200 CHAR),
"FORM_SECTION" VARCHAR2(200 CHAR),
"STATUS" VARCHAR2(20 CHAR),
"EX" VARCHAR2(200 CHAR),
"EX1" VARCHAR2(200 CHAR),
"EX2" NUMBER(15,3),
"EX3" NUMBER(15,3),
CONSTRAINT PRIMARY KEY ("FORM_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE
)
i am Using C#.net Forms to Grant Privileges to User FOr Accessing Form and its Features.
Now I need to Design a Form In WHich Administrator will be able to list of All FOrms, Then He will select User name to Grant Access, Select Specific Privileges for him and Save!
I have written a Right outer Join Query but It does not Give Results as Expected,
Can you Please Suggest me?
Query :
SELECT FORM_MASTER.FORM_NAME, FORM_MASTER.FORM_ID, DB_USER_PRIVILEGES.VIEW_TO, DB_USER_PRIVILEGES.CREATE_TO, DB_USER_PRIVILEGES.UPDATE_TO, DB_USER_PRIVILEGES.DELETE_TO, DB_USER_PRIVILEGES.PRINT_TO, DB_USER_PRIVILEGES.RESEND_SMS, DB_USER_PRIVILEGES.RESEND_EMAIL FROM DB_USERS INNER JOIN DB_USER_PRIVILEGES ON DB_USERS.SRNO = DB_USER_PRIVILEGES.USER_ID RIGHT JOIN FORM_MASTER ON FORM_MASTER.FORM_ID = DB_USER_PRIVILEGES.FORM_ID where(DB_USER_PRIVILEGES.USER_ID=6) OR (DB_USER_PRIVILEGES.USER_ID IS NULL)