Click here to Skip to main content
15,175,962 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,
I have Three Tables in My Schema,

SQL
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
  )



SQL
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
  )


SQL
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 :
SQL
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)
Posted
Comments
walterhevedeich 11-Aug-11 2:17am
   
Would you mind to give your expected result and why you said that it does not give results as expected.

1 solution

Why not have a couple of lists... one that lists the current forms/permissions for the selected user, and one that lists the available forms.

Two queries, one with an inner joing (to get the forms they are already assigned) and one with a simple left join to get the forms that they aren't assigned... Write a bit of code to react to clicking something (a button perhaps) to move a form from one list to the other...
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900