Access to views in another user’s schema (Oracle 11.2.0.3)

Posted: February 28, 2014 in Database Administration

Welcome back, Oracle Enthusiasts!!

Access to Database Views:

Sounds like a simple subject but, as I have come to accept, nothing is simple when I am behind the keyboard. An Oracle Development contractor sent me an email last evening requesting access to a group of views on a schema that he did not own.

GRANT SCHEMA_ROLE_RO  TO USER;

Easy peezy, right? Not so fast.

SELECT * FROM SCHEMA.VIEW_VW;

ORA01031: insufficient privileges. Cause: An attempt was made to change the current username or password without the appropriate privilege.

What?! That’s not what was expected. No one tried to change a username, etcetera…

1) Tried same query as SYS.  I got the data back.

2) Granted user exclusive select…

GRANT SELECT ON SCHEMA.VIEW TO USER;

Wrong again! Oracle returned the same error. Here’s where we decided to ask an old friend. “Oh, Google!”

Google suggests, (sorry for not providing the URL (lost it)), the owner of the referenced table in the view/s must possess administrative rights on the table. I didn’t believe it but it was worth a try.

SQL> select ‘grant select on ‘||referenced_owner||’.’||referenced_name||’ to ‘||owner||’ with grant option;’ cmd

2  from dba_dependencies

3  where type = ‘VIEW’

4  and owner = ‘VIEW_OWNER’ –view_owner refers to the owner of the view, not the referenced table

5  and name in (select view_name from dba_views where owner = ‘VIEW_OWNER’)

6  and referenced_owner <> ‘VIEW_OWNER’ and referenced_type = ‘TABLE’;

This query returns a dynamically created sql statement…

CMD

———————————————————————————————————————————-

grant select on SCHEMA.TABLE_NAME to VIEW_OWNER with grant option; –schema refers to the referenced table’s schema. Thank you, Mr. Obvious!


13 rows selected.

Success!!!

Questions? Comments?

Have a great weekend!!

Jason Brown

Oracle DBA

Advertisements
Comments
  1. bob kirby says:

    This SQL is Greek to me, but Google was my most important tool when I wrote C++ code with Visual Studio for Microsoft platforms. Early crowd sourcing, I guess.

    • Hey, Bob!

      I dabbled in C++ in college and it just wasn’t my favorite subject. Had it not been for an Oracle Database class my senior year, I may not have stuck with development and database administration. I find SQL, at least the Oracle version of it, to be very readable and writable. It doesn’t produce fancy, web based applications like Java and C based languages but I love what it does…

      Regards,
      Jason

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s