Home » RDBMS Server » Security » Insufficient Privileges
Insufficient Privileges [message #205313] Fri, 24 November 2006 04:26 Go to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi,
Below is a case which is really surprising to me.

1)I have a user which has got the following privileges and roles.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW

SQL> select * from session_roles;

ROLE
------------------------------
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE

SQL>


2) After this I selected v$mystat (a portion of output is shown here)

SQL> select * from v$mystat;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       102          0          1
       102          1          1
       102          2         15
       102          3          1
       102          4          1
       102          5          0
       102          6         66


3)Now I try to create view on v$mystat, it shows insufficient privilege error

SQL> create view v1 as select * from v$mystat;
create view v1 as select * from v$mystat
                                *
ERROR at line 1:
ORA-01031: insufficient privileges


What privilege is required to create a view from a synonym?
Even after granting DBA role to this user, creation of view is not allowed.
I am able to create this view if I am connected as SYSDBA

Thanks in advance
Anand
Re: Insufficient Privileges [message #205323 is a reply to message #205313] Fri, 24 November 2006 04:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Grant the priv directly to the base table.
SQL> create view a as select * from v$mystat;
create view a as select * from v$mystat
                               *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> connect sys/sys as sysdba
Connected.

SQL> grant select on v_$mystat to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> create view a as select * from v$mystat;

View created.
Re: Insufficient Privileges [message #205791 is a reply to message #205313] Mon, 27 November 2006 10:12 Go to previous message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
for views you need direct priv, not via roles, same for procedures,...
Previous Topic: Database encryption
Next Topic: System Setup and Security
Goto Forum:
  


Current Time: Thu Mar 28 21:22:07 CDT 2024