Hello,
Have a wonderful new year!
I have oracle 9.2 on XP.
There is a table T1 created by user U1 in U1's schema,
a synonym S1 created also by U1 to point to T1 in U1's schema,
U1 granted S1 to public for select.
Procedure P2 created by user U2 in U2's schema,
in P2, the S1 is referenced for select in a cursor.
Problem and question:
when P2 is compiled, got an error message saying that not sufficient
privilege. I tried a set of privileges which worked on oracle 8.1.6,
but not
on oracle 9.2 I memtioned above.
What is the privilege needed to be granted or something else needs to
be done other than just granting privileges?
Thanks in advance.
Ping 9 20958
Ping wrote: Hello,
Have a wonderful new year!
I have oracle 9.2 on XP.
There is a table T1 created by user U1 in U1's schema, a synonym S1 created also by U1 to point to T1 in U1's schema, U1 granted S1 to public for select.
Procedure P2 created by user U2 in U2's schema, in P2, the S1 is referenced for select in a cursor.
Problem and question: when P2 is compiled, got an error message saying that not sufficient privilege. I tried a set of privileges which worked on oracle 8.1.6, but not on oracle 9.2 I memtioned above.
What is the privilege needed to be granted or something else needs to be done other than just granting privileges?
Thanks in advance.
Ping
What other tests have you performed? Can U2 select data from S1? How
about from U1.T1?
Please post the exact message, especially the error number.
First thought is that perhaps U2 does not have capability to compile.
However, insufficient info provided to verify.
/Pong
Hans Forbrich <fo******@yahoo.net> wrote in message news:<3F***************@yahoo.net>... Ping wrote: Hello,
Have a wonderful new year!
I have oracle 9.2 on XP.
There is a table T1 created by user U1 in U1's schema, a synonym S1 created also by U1 to point to T1 in U1's schema, U1 granted S1 to public for select.
Procedure P2 created by user U2 in U2's schema, in P2, the S1 is referenced for select in a cursor.
Problem and question: when P2 is compiled, got an error message saying that not sufficient privilege. I tried a set of privileges which worked on oracle 8.1.6, but not on oracle 9.2 I memtioned above.
What is the privilege needed to be granted or something else needs to be done other than just granting privileges?
Thanks in advance.
Ping
What other tests have you performed? Can U2 select data from S1? How about from U1.T1?
Please post the exact message, especially the error number.
First thought is that perhaps U2 does not have capability to compile. However, insufficient info provided to verify.
/Pong
Thanks Hans.
Sorry, some modification to the problem description: the T1 in fact is
a view and T1's base tables are all owned by sys. The T1 is
successfully compiled in U1's schema.
U2 has 'create procedure' privilege granted in addition to some other
privileges.
I also have U1 granted select specifically to U2, still got same error
message as below when compiled:
PL/SQL: Declaration ignored
PLS-00904: insufficient privilege to access object U1.T1
Any thoughts and help would be appreciated.
Ping
Ping wrote: Sorry, some modification to the problem description: the T1 in fact is a view and T1's base tables are all owned by sys. The T1 is successfully compiled in U1's schema.
U2 has 'create procedure' privilege granted in addition to some other privileges.
I also have U1 granted select specifically to U2, still got same error message as below when compiled:
PL/SQL: Declaration ignored PLS-00904: insufficient privilege to access object U1.T1
Any thoughts and help would be appreciated.
I'm still wondering whether U2 can do a plain and simple 'SELECT
COUNT(*) FROM U1.T1' and 'SELECT COUNT(*) FROM S1' in SQL*Plus.
I wouldn't even worry about the PL/SQL part of it until the basic access
for the SELECT is set up properly.
/Hans
Ping wrote: Hello,
Have a wonderful new year!
I have oracle 9.2 on XP.
There is a table T1 created by user U1 in U1's schema, a synonym S1 created also by U1 to point to T1 in U1's schema, U1 granted S1 to public for select.
Procedure P2 created by user U2 in U2's schema, in P2, the S1 is referenced for select in a cursor.
Problem and question: when P2 is compiled, got an error message saying that not sufficient privilege. I tried a set of privileges which worked on oracle 8.1.6, but not on oracle 9.2 I memtioned above.
What is the privilege needed to be granted or something else needs to be done other than just granting privileges?
Thanks in advance.
Ping
Sigh... Roles are *not* active during PL/SQL execution, as mentioned
many times in this ng.
For you to search the archives (or the documentation) for the solution
--
A prosperous 2004,
Regards,
Frank van Bortel
In schema U2, the select count(*) from U1.T1 gives following error message:
ERROR at line 1:
ORA-01031: insufficient privileges
Ping wrote: In schema U2, the select count(*) from U1.T1 gives following error message:
ERROR at line 1: ORA-01031: insufficient privileges
Have you done a 'grant select' from U1 on T1 to U2?
What ROLES do U1 and U2 have?
Ping wrote: In schema U2, the select count(*) from U1.T1 gives following error message:
ERROR at line 1: ORA-01031: insufficient privileges
Your problem is therefore NOT in the PL/SQL routine. It is a basic
accessibility problem.
Now might be a good time to reread the following documentation:
Oracle9i Database Concepts: Chapter 23
Oracle9i Database Administrator's Guide: Chapters 25, 23 & 24
both being available at http://docs.oracle.com
( http://otn.oracle.com/pls/db92/db92....emark=homepage)
Hans Forbrich wrote: Ping wrote:
In schema U2, the select count(*) from U1.T1 gives following error message:
ERROR at line 1: ORA-01031: insufficient privileges
Your problem is therefore NOT in the PL/SQL routine. It is a basic accessibility problem.
Now might be a good time to reread the following documentation:
Oracle9i Database Concepts: Chapter 23 Oracle9i Database Administrator's Guide: Chapters 25, 23 & 24
both being available at http://docs.oracle.com (http://otn.oracle.com/pls/db92/db92....emark=homepage)
Even worse, then - wonder what the "set of privileges which worked on
oracle 8.1.6" were?
--
A prosperous 2004,
Regards,
Frank van Bortel
> Even worse, then - wonder what the "set of privileges which worked on oracle 8.1.6" were?
Frank, I have the following set for the schema U2 as mentioned in the
above conversation:
ALTER ANY PROCEDURE
ALTER ANY TRIGGER
ALTER SYSTEM
CREATE ANY PROCEDURE
CREATE ANY TRIGGER
CREATE PUBLIC SYNONYM
DROP ANY PROCEDURE
DROP ANY TRIGGER
DROP PUBLIC SYNONYM
EXECUTE ANY PROCEDURE
GRANT ANY PRIVILEGE
SELECT ANY TABLE
UNLIMITED TABLESPACE
Just got oracle 9.2 on XP working after granting the basetables of T1
with grant option, as stated in the Oracle 9i concept:
Additionally, in order to grant other users access to your view, you
must have received object privileges to the base objects with the
GRANT OPTION clause or appropriate system privileges with the ADMIN
OPTION clause. If you have not, grantees cannot access your view.
Thanks, Hans, for pointing out the excact chapters in the documents.
Ping This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Keith K |
last post by:
Having developed with VB since 1992, I am now VERY
interested in C#. I've written several applications with
C# and I do enjoy the language.
What C# Needs:
There are a few things that I do...
|
by: David Greenberg |
last post by:
Hi
We use Sql 2000.
I have a user that I want to be able to run a bat file with the DTSRUN
command in it on a remote computer.
What happens now is that she runs the bat file , reads the DTSRUN...
|
by: Ping |
last post by:
Hello,
Have a wonderful new year!
I have oracle 9.2 on XP.
There is a table T1 created by user U1 in U1's schema,
a synonym S1 created also by U1 to point to T1 in U1's schema,
U1 granted...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |