Grant SELECT_CATALOG_ROLE to user

In last blog we connected locally and remotely to our AWS Oracle instance.
We used user HR to login to DB instance. However we were unable to select from some basic V$ views.
Let us explore that in blog.

01 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
02 $ sqlplus hr/hr@odmdb
03
04 SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 20:11:16 2012
05
06 Copyright (c) 1982, 2009, Oracle. All rights reserved.
07
08
09 Connected to:
10 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
11 With the Partitioning, OLAP, Data Mining and Real Application Testing options
12
13 SQL> select * from v$instance;
14 select * from v$instance
15 *
16 ERROR at line 1:
17 ORA-00942: table or view does not exist

We get the error ORA-00942 that V$INSTANCE view does not exist. Tha is not TRUE. It does exist; We know it exists.
However the HR user does not have permission (privilege) to see these views (V$ views). Let us grant the required permission and try again.

01 SQL> connect / as sysdba;
02 Connected.
03 SQL> grant select_catalog_role to hr;
04
05 Grant succeeded.
06
07 SQL> connect hr/hr;
08 Connected.
09 SQL> select * from v$instance;
10
11 INSTANCE_NUMBER INSTANCE_NAME
12 --------------- ----------------
13 HOST_NAME
14 ----------------------------------------------------------------
15 VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
16 ----------------- --------- ------------ --- ---------- ------- ---------------
17 LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
18 ---------- --- ----------------- ------------------ --------- ---
19 1 odmdb
20 domU-12-31-39-03-BD-92
21 11.2.0.1.0 30-APR-12 OPEN NO 1 STOPPED
22 ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
23
24
25 SQL>

The user must have SELECT_CATALOG_ROLE to select from V$ views.
More in our next blog.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s