The Systems People Inc.

The Systems People Inc.

Customer Focused Service since 1993

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.

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus hr/hr@odmdb

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 20:11:16 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;
select * from v$instance
              *
ERROR at line 1:
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.

SQL> connect / as sysdba;
Connected.
SQL> grant select_catalog_role to hr;

Grant succeeded.

SQL> connect hr/hr;
Connected.
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 odmdb
domU-12-31-39-03-BD-92
11.2.0.1.0        30-APR-12 OPEN         NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO


SQL>

The user must have SELECT_CATALOG_ROLE to select from V$ views.

More in our next blog.

 Blog Stats

  • Total posts(18)
  • Total comments(0)

Forgot your password?