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

>>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.

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

>>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

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 ;)

>>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.

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

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

>>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.

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

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.

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.

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

>>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.

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.

>>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.

Oh, I did try granting USR2 direct access to MGMT$HA_BACKUP but it didn't help

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.

I just wanted a solution. I do not see where I asked to give access through a user but OK.

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

>> 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

Future Moderators,

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

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

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

>>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' 

Select all Open in new window

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