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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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 ;)
>>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.
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.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
It could be. I'm not familiar with MGMT views so there might be additional permissions that are necessary.
ASKER
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
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 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.
Deleting the question will delete a useful question/answer about how object privileges are cascaded through view.
ASKER
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
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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.
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.
ASKER
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.
You give access to USR2 through a role.
You asked to give access through a user.
Regards,
Franck.
ASKER
I just wanted a solution. I do not see where I asked to give access through a user but OK.
ASKER
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
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
In the event YZlat does not return, I have already proposed my closing recommendations in http:#a39184805
ASKER
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
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
ASKER
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
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'
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' ASKER
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
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.