Features of using roles with authentication in Oracle since 10.2.0.5
This note is unlikely to be interesting to the guru in Orakle, since they are likely already aware of this feature, although it is not as well documented as we would like. This article was written on my own experience of migrating one system to a higher version and it is addressed to those who work with Orakl not so long ago, uses authentication roles in their project and plans to migrate their system to version 10.2.0.5, 11.1.0.7 or 11.2. 0.x.
A little introductory - not so long ago, I needed to do a test migration of one system from version 10.2.0.4 to 11.2.0.1. I did not see any particular pitfalls in this migration, as I did this more than once. I zabekapilsya, drove all the necessary scripts (before migration, the actual migration and after it), checked for people with disabilities and gave the base to the developers for testing. But literally immediately they addressed me with the problem that they could not work with the database, because no object in the database was available to them.
I checked, indeed, when accessing objects that are allowed to access through roles with authentication - and such access is preferential - an error message is
I checked the roles and users - the privileges on the objects really exist for the roles and the users are really grants these roles.
At first, I thought that there were some errors during the migration, something was broken in the dictionary and this is the reason. But after checking the migration logs again, I did not find any errors.
He began to understand further and found out that this problem arises from the fact that the developers in the code do not use the construction
since everything worked before, and the fact that all roles are granted to users as default roles.
After checking the standard documentation for 11.2, for some reason I did not find any mention of this feature in it - maybe someone from the more knowledgeable will tell you where it is described in the dock?
Having started to dig into this problem on the Internet, I, after a brief search, came across a post at stackoverflow.com - stackoverflow.com/questions/2282061/grants-by-role-changed-in-oracle-11g , which clarified the situation. From there, I also took the solution to the problem of disabling authentication from roles, since it was in fact not used in any way in the project.
Thus, this post serves as a kind of recommendation for those who are planning to migrate in order to save their time on solving this problem.
A little introductory - not so long ago, I needed to do a test migration of one system from version 10.2.0.4 to 11.2.0.1. I did not see any particular pitfalls in this migration, as I did this more than once. I zabekapilsya, drove all the necessary scripts (before migration, the actual migration and after it), checked for people with disabilities and gave the base to the developers for testing. But literally immediately they addressed me with the problem that they could not work with the database, because no object in the database was available to them.
I checked, indeed, when accessing objects that are allowed to access through roles with authentication - and such access is preferential - an error message is
ORA-00942: table or view does not exist. that the object was not found - ORA-00942: table or view does not exist.I checked the roles and users - the privileges on the objects really exist for the roles and the users are really grants these roles.
At first, I thought that there were some errors during the migration, something was broken in the dictionary and this is the reason. But after checking the migration logs again, I did not find any errors.
He began to understand further and found out that this problem arises from the fact that the developers in the code do not use the construction
SET ROLE my_role IDENTIFIED BY password;since everything worked before, and the fact that all roles are granted to users as default roles.
After checking the standard documentation for 11.2, for some reason I did not find any mention of this feature in it - maybe someone from the more knowledgeable will tell you where it is described in the dock?
Having started to dig into this problem on the Internet, I, after a brief search, came across a post at stackoverflow.com - stackoverflow.com/questions/2282061/grants-by-role-changed-in-oracle-11g , which clarified the situation. From there, I also took the solution to the problem of disabling authentication from roles, since it was in fact not used in any way in the project.
Thus, this post serves as a kind of recommendation for those who are planning to migrate in order to save their time on solving this problem.
')
Source: https://habr.com/ru/post/166367/
All Articles