DEFAULT_ROLE is an optional toggle field. If this toggle is checked, the associated role will become default role.

The use of DEFAULT ROLE only relates to accessing the database from outside the application, using a third party tool, like Discoverer or SQL.

The P2K user is the owner of all the schema objects (tables, stored procedures, views, etc.) in the database and can see them all. Other users cannot see them unless they have been granted explicit permission to do so, or a role they have that is enabled has explicit permission to do so. Because all professional interface users are also database users, they must be granted the P2K_USER role. This role has explicit permission to access all the P2K owned schema objects. If you make this role a DEFAULT ROLE, then when that user connects to the database using an external tool, they will have access to all the objects that the P2K_USER role has.

For example, user JJONES is defined in the application. If they connect to the database using SQL*Plus, they may get the following result

select * from P2K_HR_IDENTITIES;
              *
ERROR at line 1:
ORA-00942: table or view does not exist

The user will have P2K_USER role granted because they need it to run the application, but if it is a DEFAULT ROLE, then the select statement above will return results, because the user has that role enabled upon login, by default.

This is particularly important for end-user reporting tools like Discoverer, as the users will need access to the tables, views and functions required to produce the reports desired. In this case, it is recommended that a different role (not P2K_USER) as a default role be created and rights granted to that role for just the objects, and capabilities needed to produce the reports. End user reporting needs SELECT capabilities on views (and maybe tables) rather than SELECT, INSERT, DELETE etc.

---

Notes #

Click to create a new notes page