OraFAQ Forum: Security » Insufficient Privileges

Home » RDBMS Server » Security » Insufficient Privileges

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Insufficient Privileges [message #205313]

Fri, 24 November 2006 04:26

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

Report message to a moderator

Re: Insufficient Privileges [message #205323 is a reply to message #205313]

Fri, 24 November 2006 04:46

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: Insufficient Privileges [message #205791 is a reply to message #205313]

Mon, 27 November 2006 10:12

bwetkstr
Messages: 114
Registered: August 2005

Senior Member

for views you need direct priv, not via roles, same for procedures,...

Report message to a moderator

Previous Topic:	Database encryption
Next Topic:	System Setup and Security

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 11 20:05:26 CST 2024