Standard authorization in Oracle Database is based on packages, and security on procedure or function level is not supported. This feature is therefore added in the Foundation1 framework. Grants and revokes should be administrated through the use of roles. Even though it is possible (from command tools) to grant access directly to a user, as may be noted from the descriptions below, it is not recommended.
The method-level authorization model is implemented through the system service Security_SYS and the private security table SECURITY_SYS_TAB. This table stores the method restrictions for a specific role and consists of the following columns:
|
Column |
Description |
|---|---|
|
PACKAGE_NAME |
Package name |
|
METHOD_NAME |
Method name to be restricted |
|
ROLE |
Role this applies to |
Note that when a user is connected to several roles, a method restriction is not valid until all roles connected to the user are restricted from executing the specific method.
The Foundation1 security mechanism includes several tables to improve performance and functionality:
FND_USER_ROLE_TAB
|
Administration |
Runtime |
|---|---|
| This table contain the relation between FndUsers and Oracle roles. It may be changed by either running window "User - Details" using tab "Granted Roles" or by using window "Grant Administration". The content of the table is always corresponding to table SYS.DBA_ROLE_PRIVS except when the FndUser is not associated with an Oracle user. When such user is associated with an Oracle user (existing or not), the role privileges in table FND_USER_ROLE_TAB and SYS.DBA_ROLE_PRIVS will be synchronized with each other. | This table will never be used in runtime from either windows or web clients. |
FND_USER_ROLE_RUNTIME_TAB
|
Administration |
Runtime |
|---|---|
| This table contain the expanded role list for each user. It cannot be explicitly changed, but can only be updated by running the operation to refresh the security cache. The content of this table is always corresponding to the content of table SECURITY_SYS_ROLE_PRIVS_TAB except for the User identity itself included in the second table. | This table is always used from web clients when checking the security. Web clients require the security setup to be set to CACHE. This table is not used for windows client. |
SECURITY_SYS_ROLE_TREE_TAB
| Administration | Runtime |
|---|---|
| This table contain the expanded role list for each role. It cannot be explicitly changed, but can only be updated by running the operation to refresh the security cache. The content of this table is based on DBA_ROLE_PRIVS. |
SECURITY_SYS_TAB
| Administration | Runtime |
|---|---|
| This table contain the method restrictions for roles. It can only be changed by running window "Security Permissions". This table will never include method entries connected to a non-granted package. If a role is deleted the method restrictions connected to this role will also be removed. | This table will always be used at session initiation for window clients or from runtime security checking for window as well as web clients. |
SECURITY_SYS_PRIVS_TAB
| Administration | Runtime |
|---|---|
| This table contains the grant relation between database packages/views and roles (or users). The contents of the table is always the same as a subset of view USER_TAB_PRIVS_MADE and can only be updated by running the operation of refreshing the security cache. | This table will always be used at session initiation for window clients if security setup is set to CACHE (otherwise USER_TAB_PRIVS_MADE is used). It is also used from runtime security checking for web clients (where CACHE is necessary). |
PRES_OBJECT_GRANT_TAB
| Administration | Runtime |
|---|---|
| This table contain the enabled presentation objects for
roles. It can only be changed by running window "Security Permissions".
|
This table will be used at session initiation for window clients or from runtime security checking for window as well as web clients if the user is set up to use presentation object security. |
The Presentation Object Security is an extension of the Database Object Security framework. It still relies upon the strong framework of security settings for the low-level Database Objects (views, packages, methods). Grants and revokes upon such objects will always affect what objects a user can access. The Presentation Object Security is mainly a different way of administering these settings. The intention is to lift the administration one level so that the grants and revokes of low level database objects are performed automatically when the administrator is granting or revoking the higher level Presentation Objects (forms, table windows, portlets, web pages, etc.). Settings done using the Presentation Objects tab are at all times reflected in the Database Objects tab. For further clarification see the Granting and revoking and Enabling and disabling sections.
The two different perspectives are both affecting the access to Database Objects for roles. The Presentation Objects Security will in addition also control the access to the Presentation Objects in the application. The two concepts can be seen as two different filters that control what are available in the application. The first “filter” (Database Objects) is always in use. The second “filter” (Presentation Object Security) is possible to enable or disable with a system configuration parameter. It can also be controlled for each user separately. See chapters Configuration and Users.
Example: Two forms frmA and frmB are both using the same view CUSTOMER_ORDER to get data from the database. Methods in package Customer_Order_API are used by the two forms to perform actions in the database. frmA and frmB exist as presentation objects.
Example 1: Presentation Object Security is disabled. The Database Objects tab is used.
Granting the view CUSTOMER_ORDER will result in access to both frmA and frmB
(with the possibility to view the data). What forms are visible in the application
is only controlled by the first “filter”.
Example 2: Presentation Object Security is enabled.
No change is made concerning access to any objects. The enabling of Presentation
Object Security will however result in neither frmA or frmB being visible in the
application, since they are hidden/disabled by the second “filter”.
Example 3: The Presentation Object tab is used.
Enabling presentation object frmA will result in access to frmA in the application,
since both of the “filters” will let this form pass. frmB is still hidden/disabled
by the second “filter”
Example 4: The Presentation Object tab is used.
Granting frmB will result in access to frmB. Since all database objects that
are used by frmB will be granted by this operation, all functionality in frmB will
now be available. Since it is very likely that frmA is also using some of these
granted database objects the functionality in frmA will be extended to some degree.
Example 5: The Presentation Object tab is used.
Disabling (or revoking) presentation object frmA will remove frmA from the application,
the second “filter” will hide it. The access to the database objects is not affected
and thus the functionality in frmB is not limited by this operation.
Example 6:
The Presentation Object tab OR Database Objects tab is used.
Revoking the database object (method) Customer_Order_API.New__ will disable
the standard functionality of creating a new record in the visible frmB. Apart from
this, the functionality in frmB will still be the same.
Basically the Presentation Object Security is recommended for most security administration, while the Database Object tab makes it possible to control the actual access to database objects in detail, database object by database object. Using only Database Object tab for administrating will be less efficient and accurate. The two concepts will however function in parallel, however there shouldn’t be a need for this approach except during a transition period.
Refreshing the security cache is done automatically when granting a permission set to a user or to another permission set. The operation of refreshing the security cache consists of the following steps:
Removing old rows from input table SECURITY_SYS_REFRESH_USER_TAB not owned by current request
Copying permission sets from DBA_ROLE_PRIVS to SECURITY_SYS_ROLE_TREE_TAB
Expanding permission set structure to SECURITY_SYS_EXPANDED_ROLE_TAB (temporary table)
Check input to this procedure (a temporary table with user identities SECURITY_SYS_REFRESH_USER_TAB)
a) Refreshing cache table FND_USER_ROLE_RUNTIME_TAB by
synchronizing with target view
or
b) Refreshing cache table FND_USER_ROLE_RUNTIME_TAB for X users