473,325 Members | 2,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

What privilege needs to be granted?

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
Jul 19 '05 #1
9 20943
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
Jul 19 '05 #2
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
Jul 19 '05 #3
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
Jul 19 '05 #4
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

Jul 19 '05 #5
In schema U2, the select count(*) from U1.T1 gives following error message:

ERROR at line 1:
ORA-01031: insufficient privileges
Jul 19 '05 #6
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?
Jul 19 '05 #7
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)
Jul 19 '05 #8
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

Jul 19 '05 #9
> 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
Jul 19 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

46
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...
0
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...
8
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.