| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
I have a problem in accessing tables through synonyms.
<br>I have a requirement to creat user, then grant create session, <br>then create synonyms in his schema, all through <br>forms (forms_ddl). I am using Forms 2 on NT. <br>main is the user, that owns tables, and can create user and grant createsession
<br>schema. But after connecting with that user, when i <br>select *from synonym, it says, table or view does not exist. This user <br>even can directly reference main's table like mail.teacher. So, Ifi drop that
<br>The initial lines are executed from when-button-pressed trigger. <p>TIA <br>Shuja <blockquote TYPE=CITE> <pre></pre> </blockquote>
<blockquote TYPE=CITE> <pre></pre> </blockquote>
<blockquote TYPE=CITE>
<pre>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;
<blockquote TYPE=CITE>
<pre>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;
<blockquote TYPE=CITE>
<pre>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;
Received on Sun Oct 08 2000 - 16:36:34 CDT
 |
 |