Home » SQL & PL/SQL » SQL & PL/SQL » Can you tell me how to acess a table or procedure of another user in oracle.
Can you tell me how to acess a table or procedure of another user in oracle. [message #199542] Wed, 25 October 2006 01:28 Go to next message
mavericks
Messages: 4
Registered: October 2006
Junior Member
Can you tell me how to acess a table or procedure of another user in oracle.
Re: Can you tell me how to acess a table or procedure of another user in oracle. [message #199549 is a reply to message #199542] Wed, 25 October 2006 01:41 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> conn tester/tester
Connected.
SQL> create or replace procedure test_p
  2  is
  3  begin
  4  null;
  5  end;
  6  /

Procedure created.

SQL> create table test_t ( no number);

Table created.

SQL> conn scott/tiger
Connected.
SQL> desc tester.test_t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 NO                                                 NUMBER

SQL> exec tester.test_p;

PL/SQL procedure successfully completed.
Re: Can you tell me how to acess a table or procedure of another user in oracle. [message #199616 is a reply to message #199542] Wed, 25 October 2006 04:38 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

To access a procedure or a table of a particular user from another user he should have previleges to access that object. Previleges can be granted by the owner.

See the following statement for granting the prevs.

SQL> conn tester/tester
SQL> grant execute on test_p to scott;
SQL> conn scott/tiger
SQL> execute tester.test_p



thanks,
Ramesh.
Re: Can you tell me how to acess a table or procedure of another user in oracle. [message #199644 is a reply to message #199616] Wed, 25 October 2006 10:18 Go to previous messageGo to next message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
User52, your example doesn't show the real nature of Oracle security. It looks like everyone can access everyone else's objects. That isn't the case in real (Oracle) world.

It seems that Scott, in your database, is a privileged user. This is what happens to an ordinary one: First, create a procedure and a table (following your steps):
SQL> create or replace procedure test_p is
  2  begin
  3    null;
  4  end;
  5  /

Procedure created.

SQL> create table test_t (no number);

Table created.
Now connect as another user:
SQL> connect mike/lion
Connected.
SQL> desc scott.test_t
ERROR:
ORA-04043: object scott.test_t does not exist


SQL> exec scott.test_p;
BEGIN scott.test_p; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SCOTT.TEST_P' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
But, connected as SYS, everything is fine:
SQL> connect sys/syspwd as sysdba
Connected.
SQL> desc scott.test_t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------

 NO                                                 NUMBER

SQL> execute scott.test_p;

PL/SQL procedure successfully completed.

However, Scott might have granted required privileges to PUBLIC. Doing so, anyone could use those objects:
SQL> connect scott/tiger
Connected.
SQL> grant execute on test_p to public;

Grant succeeded.

SQL> grant select on test_t to public;

Grant succeeded.

SQL> connect mike/lion
Connected.
SQL> desc scott.test_t
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------

 NO                                                 NUMBER

SQL> execute scott.test_p;

PL/SQL procedure successfully completed.

SQL>

[Updated on: Wed, 25 October 2006 10:21]

Report message to a moderator

Re: Can you tell me how to acess a table or procedure of another user in oracle. [message #199652 is a reply to message #199644] Wed, 25 October 2006 10:38 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Yes , you're right.
i was missed complete demostration.

i am not grant "select on view , execute on procedure" privs to users.


sorry for my ignorance.
Re: Can you tell me how to acess a table or procedure of another user in oracle. [message #199654 is a reply to message #199652] Wed, 25 October 2006 10:39 Go to previous message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No problem ./fa/1626/0/
Previous Topic: History tables
Next Topic: CAST result of MONTHS_BETWEEN in to Integer
Goto Forum:
  


Current Time: Mon Dec 05 02:55:42 CST 2016

Total time taken to generate the page: 0.10060 seconds