Message-Id: <10643.118742@fatcity.com> From: Mohammad Shuja Date: Mon, 09 Oct 2000 02:36:34 +0500 Subject: create user > grant > create synonym ? --------------E4FD35E2C1EF9333C6CE57FF Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have a problem in accessing tables through synonyms. I have a requirement to creat user, then grant create session, then create synonyms in his schema, all through forms (forms_ddl). I am using Forms 2 on NT. main is the user, that owns tables, and can create user and grant create session privilege, and create any synonym, but is not dba. Main has created role 'full', and 'student' that have access to his tables through views. I used double quotes in creating user name, as they include special characters. It is all working fine for user names with special characters, like MCS-1 But now i am unble to work with regular user names. Every thing works, like normal, ie user is created, role granted and synonyms created in his schema. But after connecting with that user, when i select *from synonym, it says, table or view does not exist. This user even can directly reference main's table like mail.teacher. So, If i drop that synonym and recreate it, it starts working fine. What could be wrong ? i have copied three procedures (creat user, grant create session - role full and create synonyms) The initial lines are executed from when-button-pressed trigger. TIA Shuja if condition then > CREATE_USER('OTHER'); > GRANT_PRIVILEGE('OTHER'); > CREATE_SYNONYM('TEACHER', 'FULL_TEACHER', 'OTHER'); > else > CREATE_USER('STD'); > GRANT_PRIVILEGE('STD'); > CREATE_SYNONYM('TEACHER', 'STD_TEACHER', 'STD'); > end if; > PROCEDURE CREATE_USER (NAME_TYPE IN VARCHAR2) IS > BEGIN > IF NAME_TYPE = 'STD' THEN > FORMS_DDL ('CREATE USER "'||UPPER(:STD_ID)||:USER_NAME||'" IDENTIFIED BY "'||:NEW_PASSWORD||'"'); > ELSIF NAME_TYPE = 'OTHER' THEN > FORMS_DDL ('CREATE USER '||UPPER(:STD_ID)||:USER_NAME||' IDENTIFIED BY '||:NEW_PASSWORD); > END IF; > IF NOT FORM_SUCCESS THEN > MESSAGE (DBMS_ERROR_CODE||'-'||DBMS_ERROR_TEXT); > RAISE FORM_TRIGGER_FAILURE; > END IF; > END; > > PROCEDURE GRANT_PRIVILEGE (NAME_TYPE IN VARCHAR2) IS > BEGIN > IF NAME_TYPE = 'STD' THEN > FORMS_DDL ('GRANT CREATE SESSION, '||:ROLE_SELECTED||' TO "'||UPPER(:STD_ID)||'"'); > ELSIF NAME_TYPE = 'OTHER' THEN > FORMS_DDL ('GRANT CREATE SESSION, '||:ROLE_SELECTED||' TO '||UPPER(:STD_ID)||:USER_NAME); > IF NOT FORM_SUCCESS THEN > MESSAGE (DBMS_ERROR_CODE||'-'||DBMS_ERROR_TEXT); > MESSAGE ('Error granting privilege'); > RAISE FORM_TRIGGER_FAILURE; > END IF; > END IF; > END; > > PROCEDURE CREATE_SYNONYM (SYN_NAME IN VARCHAR2, VIEW_NAME IN VARCHAR2, NAME_TYPE IN VARCHAR2)IS > BEGIN > IF NAME_TYPE = 'STD' THEN > FORMS_DDL('CREATE SYNONYM "'||UPPER(:STD_ID)||'".'||SYN_NAME||' FOR main.'||VIEW_NAME); > ELSIF NAME_TYPE = 'OTHER' THEN > FORMS_DDL('CREATE SYNONYM '||:USER_NAME||'.'||SYN_NAME||' FOR main.'||VIEW_NAME); > END IF; > IF NOT FORM_SUCCESS THEN > MESSAGE (DBMS_ERROR_CODE||'-'||DBMS_ERROR_TEXT); > MESSAGE ('Error creating required synonyms.'); > RAISE FORM_TRIGGER_FAILURE; > END IF; > END; > --------------E4FD35E2C1EF9333C6CE57FF Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit I have a problem in accessing tables through synonyms.
I have a requirement to creat user, then grant create session,
then create synonyms in his schema, all through
forms (forms_ddl). I am using Forms 2 on NT.
main is the user, that owns tables, and can create user and grant create session
privilege, and create any synonym, but is not dba. Main has created role 'full',
and 'student' that have access to his tables through views.

I used double quotes in creating user name, as they include special characters.
It is all working fine for user names with special characters, like   MCS-1
   But now i am unble to work with regular user names. Every thing works,
like normal, ie user is created, role granted and synonyms created in his
schema. But after connecting with that user, when i
select *from synonym, it says, table or view does not exist. This user
even can directly reference main's table like mail.teacher. So, If i drop that
synonym and recreate it, it starts working fine. What could be wrong ?

i have copied three procedures (creat user, grant create session - role full and create synonyms)
The initial lines are executed from when-button-pressed trigger.

TIA
Shuja 




if condition then



CREATE_USER('OTHER');
GRANT_PRIVILEGE('OTHER');
CREATE_SYNONYM('TEACHER', 'FULL_TEACHER', 'OTHER');



else



CREATE_USER('STD');
GRANT_PRIVILEGE('STD');
CREATE_SYNONYM('TEACHER', 'STD_TEACHER', 'STD');



end if;










PROCEDURE CREATE_USER (NAME_TYPE IN VARCHAR2) IS
BEGIN
      IF NAME_TYPE = 'STD' THEN
          FORMS_DDL ('CREATE USER "'||UPPER(:STD_ID)||:USER_NAME||'" IDENTIFIED BY "'||:NEW_PASSWORD||'"');
      ELSIF NAME_TYPE = 'OTHER' THEN
          FORMS_DDL ('CREATE USER '||UPPER(:STD_ID)||:USER_NAME||' IDENTIFIED BY '||:NEW_PASSWORD);
      END IF;
      IF NOT FORM_SUCCESS THEN
            MESSAGE (DBMS_ERROR_CODE||'-'||DBMS_ERROR_TEXT);
            RAISE FORM_TRIGGER_FAILURE;
      END IF;
END;







PROCEDURE GRANT_PRIVILEGE (NAME_TYPE IN VARCHAR2) IS
BEGIN
    IF NAME_TYPE = 'STD' THEN
        FORMS_DDL ('GRANT CREATE SESSION, '||:ROLE_SELECTED||' TO "'||UPPER(:STD_ID)||'"');
    ELSIF NAME_TYPE = 'OTHER' THEN
        FORMS_DDL ('GRANT CREATE SESSION, '||:ROLE_SELECTED||' TO '||UPPER(:STD_ID)||:USER_NAME);
        IF NOT FORM_SUCCESS THEN
            MESSAGE (DBMS_ERROR_CODE||'-'||DBMS_ERROR_TEXT);
            MESSAGE ('Error granting privilege');
            RAISE FORM_TRIGGER_FAILURE;
        END IF;
    END IF;
END;







PROCEDURE CREATE_SYNONYM (SYN_NAME IN VARCHAR2, VIEW_NAME IN VARCHAR2, NAME_TYPE IN VARCHAR2)IS
BEGIN
    IF NAME_TYPE = 'STD' THEN
           FORMS_DDL('CREATE SYNONYM "'||UPPER(:STD_ID)||'".'||SYN_NAME||' FOR main.'||VIEW_NAME);
    ELSIF NAME_TYPE = 'OTHER' THEN
           FORMS_DDL('CREATE SYNONYM '||:USER_NAME||'.'||SYN_NAME||' FOR main.'||VIEW_NAME);
    END IF;
    IF NOT FORM_SUCCESS THEN
            MESSAGE (DBMS_ERROR_CODE||'-'||DBMS_ERROR_TEXT);
            MESSAGE ('Error creating required synonyms.');
            RAISE FORM_TRIGGER_FAILURE;