Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

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

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
YZlat
Asked:
YZlat
  • 12
  • 11
  • 5
  • +1
5 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
DavidSenior Oracle Database AdministratorCommented:
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
 
Franck PachotCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
slightwv (䄆 Netminder) Commented:
>>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
 
YZlatAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
Franck PachotCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
YZlatAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
YZlatAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
YZlatAuthor Commented:
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
 
Franck PachotCommented:
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
 
Franck PachotCommented:
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
 
YZlatAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
DavidSenior Oracle Database AdministratorCommented:
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
 
YZlatAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
YZlatAuthor Commented:
Oh, I did try granting USR2 direct access to MGMT$HA_BACKUP but it didn't help
0
 
Franck PachotCommented:
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
 
YZlatAuthor Commented:
I just wanted a solution. I do not see where I asked to give access through a user but OK.
0
 
YZlatAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>> 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
 
slightwv (䄆 Netminder) Commented:
Future Moderators,

In the event YZlat does not return, I have already proposed my closing recommendations in http:#a39184805
0
 
YZlatAuthor Commented:
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
 
YZlatAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
YZlatAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 12
  • 11
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now