Solved

Insufficient privileges issue on a view when user has privileges on the view and underlying table

Posted on 2013-05-16
32
476 Views
Last Modified: 2013-05-27
User USR1 has been granted select on MGMT$HA_BACKUPS, and unser USR1 schema a view has been created on MGMT$HA_BACKUPS called USR1.USR1_BACKUPS.

There is a USR2 that has SELECT privileges on MGMT$HA_BACKUP table and USR1.USR1_BACKUPS view, but when I attempt select * from USR1.USR1_BACKUPS while logged in as USR2, I get an error below:



SQL> select * from USR1.USR1_BACKUPS;
select * from USR1.USR1_BACKUPS;
                     *
ERROR at line 1:
ORA-01031: insufficient privileges
0
Comment
Question by:YZlat
  • 12
  • 11
  • 5
  • +1
32 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 39172832
For usr1 to 'grant' permission to usr2 to see the view it was granted permission on, you need to grant with the admin option.

Think about this:
I grant you select on a table.  Should any user you then decide be able to access it?  No.  I granted you access and only you.

Creating a view then granting access to someone else access to that view is doing just that.
0
 
LVL 23

Assisted Solution

by:David
David earned 63 total points
ID: 39173123
Think about the flip side:  I may grant a user select on a view, but that does not (and never should) mean the user may query the underlying table(s).
0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 62 total points
ID: 39174078
Hi,
You need replace that:
>> There is a USR2 that has SELECT privileges on MGMT$HA_BACKUP table
with a WITH GRANT OPTION on:
>> unser USR1 schema a view has been created on MGMT$HA_BACKUPS called USR1.USR1_BACKUPS

Example of minimal privileges to acheive that:
SQL> connect / as sysdba
Connected.
SQL> grant create view to USR1 identified by USR1;

Grant succeeded.

SQL> grant select on SYSMAN.MGMT$HA_BACKUP to USR1 with grant option;

Grant succeeded.

SQL> create view  USR1.USR1_BACKUPS as select * from MGMT$HA_BACKUP;

View created.

SQL> grant create session to USR2 identified by USR2;

Grant succeeded.

SQL> grant select on USR1.USR1_BACKUPS to USR2;

Grant succeeded.

SQL> connect USR2/USR2
Connected.
SQL> select * from USR1.USR1_BACKUPS;

no rows selected

SQL> connect / as sysdba
Connected.
SQL> drop user USR1 cascade;

User dropped.

SQL> drop user USR2 cascade;

User dropped.

Open in new window


Regards,
Franck.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39174474
>>with a WITH GRANT OPTION on:

I suggest you be careful when granting a privilege with the grant option.  This allows this user to grant that same permission to any other user.  It can be a security issue.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39174759
franckpachot, that's exactly what I did. USR1 was already created, so all I needed was to create USR2:

SQL> connect / as sysdba
Connected.
SQL> create user USR2
identified by USR2
deafult tablespace MGMT2
temporary tablespace TEMP
account unlock;

User created.

SQL> grant create session to USR2;

Grant succeeded.

SQL> grant select on USR1.USR1_BACKUPS to USR2;

Grant succeeded.

SQL> connect USR2/USR2
Connected.

SQL> select * from USR1.USR1_BACKUPS;
select * from USR1.USR1_BACKUPS
                   *
ERROR at line 1:
ORA-01031: insufficient privileges
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39174777
>>USR1 was already created

But was USR1 granted select on MGMT$HA_BACKUP with the ADMIN OPTION?

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9013.htm#SQLRF01603
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 39175000
Hi,
>>USR1 was already created
you can run my example as-is even if users are already created it just adds required privileges (except the drop of course)

@slightwv
with admin option is used for system privileges, not for object privileges.

>>I suggest you be careful when granting a privilege with the grant option.  This allows this user to grant that same permission to any other user.  It can be a security issue.
But it's exaclty the goal here, no ? It's not an issue but a requirement ;)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39175045
>>with admin option is used for system privileges, not for object privileges.

Correct.  I was mixing options.  Thanks for correcting.

>>But it's exaclty the goal here, no ? It's not an issue but a requirement ;)

Just pointing out that this should not be done on a whim.  You need to understand what it is doing.

I don't understand the complete setup the asker is running but personally I cannot see where I would ever do this.  It would make maintenance a nightmare.

Next USR3 will need access to USR2's objects which need access to USR1's objects, then USR4 comes along, etc...

I like a 'master' schema to hold all the objects and grant what is needed to individual roles.  Then grant those roles to specific  users.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39175277
about WITH GRANT option. I am not sure you understood what I need here I do not need USR1 to be able to grant permissions to USSR2. I can do that as sysdba. Basically USR1 has select permisisons on a management view MGMT$HA_BACKUP and created a view in his own schema called USR1_BACKUPS that displays data from MGMT$HA_BACKUP.

Now I need USR2 to get to the same data (from MGMT$HA_BACKUP). I figured since this data is in USR1 schema, USR2 could access it there, since USR2 only needs select privileges, rather then granting USR2 access to MGMT$HA_BACKUP directly
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 39175291
>>what I need here I do not need USR1 to be able to grant permissions to USSR2

Views don't get around object permissions.  A view is basically just a select statement that is executed when the view is called.

USR1 has permission to see MGMT$HA_BACKUP.
USR2 doesn't.
USR2 will be accessing MGMT$HA_BACKUP through the view.
USR2 needs the same permissions to see objects used in the view.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39175426
when I gave USR2 select permissions on MGMT$HA_BACKUP I got the same error.

I am starting to think the insufficient permissions issue might have something to do with MGMT$HA_BACKUP management view.

Because when I created a test user in a different database and granted the test user select privileges on a view in another schema, there was not any issue grabbing data, even when test user did not have pemissions on underlying tables. But those were regular tables and views, not MGMT
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39175436
>>I am starting to think the insufficient permissions issue might have something to do with MGMT$HA_BACKUP management view.

It could be.  I'm not familiar with MGMT views so there might be additional permissions that are necessary.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39184538
I've requested that this question be deleted for the following reason:

solved it myself. The issue was that I was dealing with MGMT views and not regular tables or view
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 39184528
Hi,

>> The issue was that I was dealing with MGMT views and not regular tables or view

The MGMT views are totally regular. Nothing special with them.
See: I've provided an exemple exactly on those views to show the required grants.
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 39184539
The answer to the question is the 'with grant option' clause, as provided above.

Deleting the question will delete a useful question/answer about how object privileges are cascaded through view.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 35

Author Comment

by:YZlat
ID: 39184550
I do not want to use WITH GRANT option since I do not want the user to be able to grant those permissions to someone else. This is not a plausible solution for me and I found another solution without any security risks

All I wanted was that USR2 has the same privileges as USR1 and in order to use MGMT views USR2 needed to be in the same role as USR1, I added USR2 to the same role and that solved my problem. I do not want to use your solution which other experts also find to be not the safest option. Thank you for your suggestion but no thank you
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39184567
>>The issue was that I was dealing with MGMT views and not regular tables or view

Since the objection was posted, Moderators will be along shortly.

Please post what the resolution was so they have the information they require.
0
 
LVL 23

Expert Comment

by:David
ID: 39184596
I concur with the author.  While adding the additional privilege would authorize access, the root cause is his/her misunderstanding about referencing the Mgmt view.  

However, to the author, I see the value in leaving the thread in the knowledge base.  I may be mistaken, but that could be accomplished by stating your solution, and awarding the points to yourself.
0
 
LVL 35

Accepted Solution

by:
YZlat earned 0 total points
ID: 39184635
I also do not want to delete the question entirely, but it needs to be closed and I just do not see a reason for accepting the answer that was not the solution used


My resolution was to add USR2 to MGMT_USER role that USR1 was a member of.

Thanks to all for trying to help
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39184658
>>My resolution was to add USR2 to MGMT_USER role that USR1 was a member of.

That basically grants the permissions you weren't wanting to grant:  "rather then granting USR2 access to MGMT$HA_BACKUP directly"

>>I just do not see a reason for accepting the answer that was not the solution used

You can accept your own post as the solution.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39184673
Oh, I did try granting USR2 direct access to MGMT$HA_BACKUP but it didn't help
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 39184684
Ok. But I hope you realized that your resolution is very different from what you asked in the question.

You give access to USR2 through a role.
You asked to give access through a user.

Regards,
Franck.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39184698
I just wanted a solution. I do not see where I asked to give access through a user but OK.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39184703
I am tired of arguing and if moderator feels you should get the points, please, be my guest and take the points, it really makes no difference to me. I just assumed the soltion that was used should be the one to get accepted not any other one. But that's my personal belief
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39184805
>> I just assumed the soltion that was used should be the one to get accepted not any other one

That is what should be accepted.  However in this case the solution used was not for the question asked.  The question asked was why you were getting 'insufficient privileges.

That was responded to in several posts.

The solution to the question was USR2 needs access granted to the base table used in the view created by USR1.  Just grating access to USR1 then creating a view did not automatically allow USR2 to access the object.

I agree that the 'grant option' wasn't the best solution to the problem but it did address the question.  Other posts also explain the reasons behind what you were getting.

Might I suggest the following solution (If you don't wish to do this, it will be my suggestion for the Moderators):

Accept your post: http:#a39184635

and give assists to:

http:#a39172832
http:#a39173123
http:#a39174078
http:#a39175291
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39187243
Future Moderators,

In the event YZlat does not return, I have already proposed my closing recommendations in http:#a39184805
0
 
LVL 35

Author Comment

by:YZlat
ID: 39189042
I am sorry to say that none of the comments in this post have helped me find the solution. In most cases the experts here are able to provide excellent solutions but in this particular case I am sorry to say none of the comments were helpful to me.

I started out with this error:

SQL> select * from USR1.USR1_BACKUPS;
select * from USR1.USR1_BACKUPS;
                     *
ERROR at line 1:
ORA-01031: insufficient privileges


and it persisted until I added USR2 to the MGMT_USERS group. Normally granting the user select permission on a view and underlying view or table works, but I guess it is not the case when dealing with management views.

I live it up to the moderators to select whatever comment they see fit, but, unfortunatelly none of the comments helped me in this case.

thank you and have a great evening
0
 
LVL 35

Author Comment

by:YZlat
ID: 39189058
please take a look at my comments http:#39175426 and http:#39184538 for any further clarification.
thanks again.

if you need any further clarification please let me know, otherwise feel free to close the question and award points to the expert of your choice.

I just wanted to retian this question with my comments of how I solved it in case anyone else is struggling with the same issue
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39189310
>>Normally granting the user select permission on a view and underlying view or table works

This is what I'm challenging.  This is not the normal behavior of Oracle.  Thus the issue here.  Permissions don't cascade across views.  The MGMT views have nothing to do with it.

Check out the following test case below.

Based on what you have posted, I believe I have the test case set up to what you are saying and it doesn't work.

Feel free to correct the test case to show what you are saying.

Notice the very last error:
SQL> Grant select on usr1_vw to usr2;
Grant select on usr1_vw to usr2
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'MASTER.TAB1'

SQL> Conn system/system
Connected.
SQL> 
SQL> Drop user master cascade;

User dropped.

SQL> Drop user usr1 cascade;

User dropped.

SQL> Drop user usr2 cascade;

User dropped.

SQL> 
SQL> Create user master identified by master;

User created.

SQL> Grant create session, resource to master;

Grant succeeded.

SQL> 
SQL> Create user usr1 identified by usr1;

User created.

SQL> Grant create session, create view to usr1;

Grant succeeded.

SQL> 
SQL> Create user usr2 identified by usr2;

User created.

SQL> Grant create session to usr2;

Grant succeeded.

SQL> 
SQL> --create the base table
SQL> Conn master/master
Connected.
SQL> Drop table tab1 purge;
Drop table tab1 purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> Create table tab1(col1 char(1));

Table created.

SQL> Insert into tab1 values('a');

1 row created.

SQL> Commit;

Commit complete.

SQL> Grant select on tab1 to usr1;

Grant succeeded.

SQL> 
SQL> --create the view from usr1 and grant permission
SQL> Conn usr1/usr1
Connected.
SQL> Create view usr1_vw as select * from master.tab1;

View created.

SQL> Grant select on usr1_vw to usr2;
Grant select on usr1_vw to usr2
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'MASTER.TAB1' 

Open in new window

0
 
LVL 35

Author Comment

by:YZlat
ID: 39191830
slightwv, I really do not see the need to argue with anyone, my problem is solved and that's all i care about, as I have said before, moderators will amke their decision anyway, not me. So I am out
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now