Features of using authentication roles in Oracle starting from 10.2.0.5
This article is unlikely to be interesting to the Oracle guru, since they are most likely already aware of this feature, although it is not as well documented as we would like. This note is written on the personal experience of migrating one system to an older version and is addressed to those who work with Oracle not so long ago, uses authentication roles in their project and plans to migrate their system to versions 10.2.0.5, 11.1.0.7 or 11.2. 0.x.
A small introduction - 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 special pitfalls in this migration, since I did this more than once. I got bogged down, ran all the necessary scripts (before the migration, actually the migration and after it), checked for the disabled and gave the base to the developers for testing. But literally right away they turned to me with the problem that they could not work with the database, because not a single object in the database was available to them.
I checked, indeed, when accessing objects that are allowed to access through roles with authentication - and this access is preferable - I get an error message that this object was not found -
I checked the roles and users - the roles really have privileges on the objects and these roles are really granted to users.
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.
I started to understand further and finally found out that this problem arises due to the fact that developers do not use the design in the code
because everything worked before that, and because 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 ones will tell me where it is described in the dock?
Having started digging around this problem on the Internet, after a short search, I came across a post on stackoverflow.com - stackoverflow.com/questions/2282061/grants-by-role-changed-in-oracle-11g , which clarified the situation. From there, I took a solution to the problem - to disable authentication for roles, since it was actually not used in the project.
Thus, this post serves as a kind of recommendation for those planning a migration in order to save their time on solving this problem.
A small introduction - 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 special pitfalls in this migration, since I did this more than once. I got bogged down, ran all the necessary scripts (before the migration, actually the migration and after it), checked for the disabled and gave the base to the developers for testing. But literally right away they turned to me with the problem that they could not work with the database, because not a single object in the database was available to them.
I checked, indeed, when accessing objects that are allowed to access through roles with authentication - and this access is preferable - I get an error message that this object was not found -
ORA-00942: table or view does not exist.I checked the roles and users - the roles really have privileges on the objects and these roles are really granted to users.
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.
I started to understand further and finally found out that this problem arises due to the fact that developers do not use the design in the code
SET ROLE my_role IDENTIFIED BY password;because everything worked before that, and because 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 ones will tell me where it is described in the dock?
Having started digging around this problem on the Internet, after a short search, I came across a post on stackoverflow.com - stackoverflow.com/questions/2282061/grants-by-role-changed-in-oracle-11g , which clarified the situation. From there, I took a solution to the problem - to disable authentication for roles, since it was actually not used in the project.
Thus, this post serves as a kind of recommendation for those planning a migration in order to save their time on solving this problem.