Home » RDBMS Server » Server Administration » problem accessing table
problem accessing table [message #234604] Wed, 02 May 2007 11:27 Go to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Hi,

I logged in as USER1:
CREATE TABLE TAB1

Logged as sys:
created a role called SELECT_ROLE;
grant select on USER1.TAB1 to SELECT_ROLE;
grant SELECT_ROLE to USER2;

Logged in as USER2:
create synony TAB1 for USER1.TAB1;
desc TAB1 --> I'm able to see the columns, but
when I try...select * from TAB1, I'm getting ORA-01031: insufficient privileges.

Can anyone tell me what could be the problem?

Thanks,
GK
Re: problem accessing table [message #234607 is a reply to message #234604] Wed, 02 May 2007 11:35 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Are you sure you did some like below
ORCL@TAJ>conn taj/taj
Connected.
ORCL@TAJ>create table ttt ( no number);

Table created.

ORCL@TAJ>insert into ttt values (1);

1 row created.

ORCL@TAJ>commit;

Commit complete.

ORCL@TAJ>create role select_role;

Role created.

ORCL@TAJ>grant select on ttt to select_role;

Grant succeeded.

ORCL@TAJ>grant select_role to scott;

Grant succeeded.

ORCL@TAJ>conn scott/tiger
Connected.
ORCL@TAJ>create synonym tt for taj.ttt;

Synonym created.

ORCL@TAJ>desc tt
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 NO                                                 NUMBER

ORCL@TAJ>select * from tt;

        NO
----------
         1

ORCL@TAJ>



Regards
Taj
Re: problem accessing table [message #234676 is a reply to message #234607] Wed, 02 May 2007 15:40 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you, perhaps, try to use this SELECT statement in PL/SQL procedure? If so, bad news for you: you'll have to grant privileges directly to the user (not through a role) because privileges acquired through roles do not apply in PL/SQL.
Previous Topic: Installing Oracle 10g and 9i on same systems
Next Topic: How can log as SYS in Interface EM
Goto Forum:
  


Current Time: Sat Dec 03 18:28:33 CST 2016

Total time taken to generate the page: 0.11688 seconds