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.