| Synonym [message #49973] |
Thu, 21 February 2002 05:01  |
Giuseppe
Messages: 11
Registered: December 2001
|
Junior Member |
|
|
I have more user with few grant, i'd like to create for them a role with all necessary grant. I've the problem when i try to create a synonym ,to the role, for view of another user;
example:
** as user system
create role cia;
grant create session to cia;
create user cia1 identified by ****;
grant cia to cia1;
** as user fbi
create view v_fbi as select * from fbi.departement;
** as user system
create synonym cia.v_fbi for fbi.v_fbi;
but i have this error:
ERROR at line 1:
ORA-01917: user or role '' does not exist
The question is can i create the synonym in the role ??
|
|
|
|
| Re: Synonym [message #49976 is a reply to message #49973] |
Thu, 21 February 2002 06:19  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
You using the wrong syntax for synonym. The purpose of a synonym is to not have to preface it with the schema name. You also have to grant select on the view to the role.
For example:
SQL> connect system
Enter password:
Connected.
SQL> create user fbi identified by fbi;
User created.
SQL> grant connect,resource to fbi;
Grant succeeded.
SQL> create user cia1 identified by cia1;
User created.
SQL> grant connect to cia1;
Grant succeeded.
SQL> create role cia;
Role created.
SQL> connect fbi
Enter password:
Connected.
SQL> create table department(test varchar(20));
Table created.
SQL> create view v_fbi as select * from department;
View created.
SQL> grant select on v_fbi to cia;
Grant succeeded.
SQL> connect system;
Enter password:
Connected.
SQL> grant cia to cia1;
Grant succeeded.
SQL> connect cia1;
Enter password:
Connected.
SQL> desc fbi.v_fbi;
Name Null? Type
----------------------------------------- -------- ----------------------------
TEST VARCHAR2(20)
SQL> desc v_fbi;
ERROR:
ORA-04043: object v_fbi does not exist
SQL> create synonym v_fbi for fbi.v_fbi;
Synonym created.
SQL> desc v_fbi
Name Null? Type
----------------------------------------- -------- ----------------------------
TEST VARCHAR2(20)
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
