I don't hate databases, but...

Tom Dunne

...they confuse me a bit.

I have a table, PERSONS, in a schema and have granted the role READ_ACCESS select on this table (it's a read-only role).

In another schema, REPORTS, I have a package that selects from the PERSONS table. I have granted the REPORTS schema the READ_ACCESS role, so it should be able to read from the PERSONS table in the other schema.

Basically, what is happening is that the SELECT statement works outside a package, but won't work inside a package, giving a "table or view does not exist".

To fix it, I can grant SELECT on the PERSONS table to the REPORTS schema and it works. But I shouldn't have to do that as the role (READ_ACCESS) already has the required privilege.

Any ideas as to what might be going wrong?

Edit: Doh! Forgot to mention, in case it wasn't obvious, it's Oracle 10g

Seth Brundle

first Q: did you give access to SHCEMA_name.tablename, e.g. SCOTT.READ_ACCESS?

Tom Dunne

I don't think I understand what you are asking.

Here's what I did, logged in to the PERSONS schema:

GRANT select on PERSONS to READ_ACCESS

followed by

GRANT READ_ACCESS to REPORTS

So a SELECT * from PERSONS works from the REPORTS schema, but it doesn't work in the REPORTS schema from inside a package.

I am wondering if it has something to do with the invoker vs. definer rights and AUTHID?

Seth Brundle

is PERSONS a table, schema or both?

Anyhow, what happens when you change your code to
SELECT * from SCHEMA_NAME.PERSONS

Tom Dunne

kbannon wrote:

is PERSONS a table, schema or both?

Apologies. It's a table.

kbannon wrote:

Anyhow, what happens when you change your code to
SELECT * from SCHEMA_NAME.PERSONS

I tried that, it still gives table or view does not exist inside the package.

I'm using Oracle SQL developer and when I put in SCHEMA_NAME. it pops up a list of the objects owned by SCHEMA_NAME, with PERSONS listed. So, I enter PERSONS but it still reports that the table or view does not exist when I compile the package.

Edit: My IT manager has kindly forked out for Toad, so I have better tools at my disposal to figure out what is going on (or what I am doing wrong).

bonkey

tom dunne wrote:

...they confuse me a bit.

Oracle terminology always has that effect on me. I'm not sure I understand some of what you're describing here, but it could be that I'm (still) misunderstanding Oracle terminology...so I'll talk my way through this and hopefully you'll correct me if I go wrong in my understanding...

Anyway...

I have a table, PERSONS, in a schema and have granted the role READ_ACCESS select on this table (it's a read-only role).

OK.

In another schema, REPORTS, I have a package that selects from the PERSONS table. I have granted the REPORTS schema the READ_ACCESS role, so it should be able to read from the PERSONS table in the other schema.

I think you mean you have granted the REPORTS user the READ_ACCESS role. A Schema, in my understanding, is a fancy name for the objects owned by a given user. Privs are granted to users.

The reason I make this distinction is because when it comes to a package, its not a question of the privs of the schema the package exists in, but rather its a question of the privs of the user who is creating / running the package.

Basically, what is happening is that the SELECT statement works outside a package, but won't work inside a package, giving a "table or view does not exist".

I'm not sure what you mean by "won't work inside a package". A package is a set of code. When executed, it runs with the privileges of the user who is running it. When being created, it is verified against the priveleges of the user who is creating it.

To fix it, I can grant SELECT on the PERSONS table to the REPORTS schema and it works. But I shouldn't have to do that as the role (READ_ACCESS) already has the required privilege.

You've never clarified what schema the PERSONS table is in, but I'm gonna assume its *not* the REPORTS schema.

So...if I'm understanding correctly, what you're saying is that while logged in as REPORTS, the following works :

SELECT * FROM some_schema.PERSONS

But this doesn't :

CREATE OR REPLACE PACKAGE BODY myPackage
  PROCEDURE myProcedure IS
    BEGIN
      SELECT x INTO somevariable
        FROM some_schema.PERSONS 
      ...
  END
END

Note - in both cases the PERSONS table is referenced by the qualified name (schema.object).

Again....in both cases, the user logged in trying to execute the code is user REPORTS, correct?

Any ideas as to what might be going wrong?

Not if what I've described above is accurate.

If some of it is inaccurate (e.g. user X is trying to create REPORTS.MyPackate), then the problem is mostly liekly related to where my description is inaccurate.

By the way, there is a system-level role called "SELECT ANY TABLE". It sounds to me like your READ_ACCESS role is trying to duplicate this functionality...unless you don't want to give read access to *everything*, you could use the system role.

Tom Dunne

bonkey wrote:

I think you mean you have granted the REPORTS user the READ_ACCESS role. A Schema, in my understanding, is a fancy name for the objects owned by a given user. Privs are granted to users.

Yes, that is correct, on all fronts.

bonkey wrote:

I'm not sure what you mean by "won't work inside a package".

What I mean is exactly as you have described...

bonkey wrote:

So...if I'm understanding correctly, what you're saying is that while logged in as REPORTS, the following works :

SELECT * FROM some_schema.PERSONS

But this doesn't :

CREATE OR REPLACE PACKAGE BODY myPackage
  PROCEDURE myProcedure IS
    BEGIN
      SELECT x INTO somevariable
        FROM some_schema.PERSONS 
      ...
  END
END

Note - in both cases the PERSONS table is referenced by the qualified name (schema.object).

bonkey wrote:

Again....in both cases, the user logged in trying to execute the code is user REPORTS, correct?

Correct.

bonkey wrote:

Not if what I've described above is accurate.

Damn.

bonkey wrote:

By the way, there is a system-level role called "SELECT ANY TABLE". It sounds to me like your READ_ACCESS role is trying to duplicate this functionality...unless you don't want to give read access to *everything*, you could use the system role.

Yeah, I am aware of that role, but the READ_ACCESS role is limited to specific tables, which is what the designer wanted.

Either way, I have granted SELECT on the PERSONS table to REPORTS and it's working fine, it's just bugging the livin' bejaney out of me why I can't figure out what is going on. I'm only getting back into Oracle in the last couple of months, and it's amazing how much you forget.

Ah well, it's going to have to wait until after Christmas at this stage. I've now got Toad installed, so I have some better tools at my disposal.

Thanks for the input.

bonkey

Wow.

Having read your above response, I did the unthinkable and actually checked whether or not it works for me here in work (cause all privs work through roles).

To my amazement, it didn't (and I feel kinda embarrassed for being so cocksure it should).

So I pulled the ORA error number (00942) in this case, googled on it, and found the following :

http://www.adp-gmbh.ch/ora/err/ora_00942.html

Its worth reading, but the horrifying (for me) sentence in there is :

The problem is that procedures don't respect roles; only directly granted rights are respected.

In other words, the behaviour you are experiencing can be described as "broken by design".

It don't work cause it ain't supposed to.

Stupid Oracle. Stupid, STUPID Oracle.

Tom Dunne

bonkey wrote:

Wow.

I'll see your Wow and raise you three. :eek:

You know, I didn't google it because I thought it was something straight forward that I was missing. I've seen that error before and I've fixed it, so I was just as cocksure.

bonkey wrote:

Stupid Oracle. Stupid, STUPID Oracle.

You said it.

I really appreicate the time you put into it, thanks. It's good to know that I am not going mad.

Now after I master Oracle, I'll try mastering google.....