oracle database 11g privileges roles and application contexts
TRANSCRIPT
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
1/44
Using Privileges and Roles
Learning Objectives
After completing this topic, you should be able to
recognize how roles and privileges can be used
recognize how to use secure application roles
1. Authorizing a role
Authorization is the process that determines the privileges that the user is allowed to
exercise. In Oracle Database, authorization is determined by the grant of system and
object privileges. A named set of grants is a role, and it may be granted as a unit.
Supplement
Selecting the link title opens the resource in a new browser window.
Learning Aid
Access the learning aid Style onsiderationsfor more information on the style
considerations for the Oracle gDatabase used in this course.
A privilege is a right to execute a particular type of !"# statement or to access anotheruser$s object. Oracle Database allows very fine%grained control over what users can or
cannot do within the database. &rivileges are divided into two categories'
system privileges and
(ach system privilege allows a user to perform a particular database operation or class of
database operations )for example, the privilege to create tablespaces is a system
privilege*. !ystem privileges can be granted by the administrator or by someone who
explicitly gives permission to administer the privilege. +here are over - distinct system
privileges.
object privileges
Object privileges allow a user to perform a particular action on a specific object, such as a
table, view, seuence, procedure, function, or pac/age. 0ithout specific permission, users
can access only their own objects. Object privileges can be granted by the owner of an
object, by the administrator, by someone with GRANTANYPRIVILEGE, or by someone
who has been explicitly given permission to grant privileges on the object.
http://dowindow%28%27../html/laod_odsc_a06_it_enus_t201_frame.html')http://dowindow%28%27../html/laod_odsc_a06_it_enus_t201_frame.html')http://dowindow%28%27../html/laod_odsc_a06_it_enus_t201_frame.html') -
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
2/44
In most systems, it is too time consuming to grant necessary privileges to each user
individually, and there is too great a chance of error. Oracle Database provides for easy
and controlled privilege management through roles. 1oles are named groups of related
privileges that are granted to users or to other roles.
!raphic
In this example, users Girard and Vance are assigned to the HR_C!R" role, and
user #eena is assigned to the HR_C!R" and HR_$GR roles. %he HR_C!R"
role consists o& Select emplo'ees and (pdate emplo'ees pri)ileges and the
HR_$GR role consists o& *elete emplo'ees and Insert emplo'ees pri)ileges.
1oles are designed to ease the administration of privileges in the database and therefore,
they improve security. +here are six characteristics for a role'
&rivileges are granted to and revo/ed from roles as though the role were a user.
1oles can be granted to and revo/ed from users or other roles as though they were system
privileges. An exception is that you cannot grant the IDENTIFIEDGLOBALLYrole to anything.
A role can consist of both system and object privileges.
A role can be enabled or disabled for each user who is granted the role.
A role can reuire a password to be enabled.
1oles are not owned by anyone, and they are not in any schema.
In this example, the HR_CLERKrole is granted the SELECTand UPDATEprivileges on the
EMPLOYEEStable. +he HR_MGRrole is granted the DELETEand INSERTprivileges on the
EMPLOYEEStable and the HR_CLERKrole. +he manager is granted the HR_MGRrole and
can now select, delete, insert, and update the EMPLOYEEStable.
2enefits of roles are
easier privilege management
1ather than granting the same set of privileges to several users, you can grant the
privileges to a role, and then grant that role to each user.
dynamic privilege management
Dynamic privilege management ensures that if the privileges associated with a role are
modified, all the users who are granted the role acuire the modified privileges
automatically and immediately.
selective availability o" privileges# and
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
3/44
1oles can be enabled and disabled to turn privileges on and off temporarily. (nabling a
role can also be used to verify that a user has been granted that role.
granting through the operating system
Operating system commands or utilities can be used to assign roles to users in a database
in some operating systems.
1oles are disabled in !"# subprograms. +he owner of a !"# subprogram
declared with definer$s rights must have the privileges reuired for the subprogram
granted directly and not through a role. 4or a subprogram with invo/er$s rights, roles are
enabled unless the subprogram is invo/ed directly or indirectly from a definer$s%right
subprogram.
+here are several roles that are defined automatically for Oracle Databases when you run
database creation scripts. CONNECTis granted automatically to any user who is created
with Oracle (nterprise 5anager.
SELECT_CATALOG_ROLEis provided for accessing data dictionary views and pac/ages.
)+his role is deprecated in favor of the SELECTANYDICTIONARYsystem privilege.* +he
DBArole includes nearly all privileges and should not be granted to nonadministrators.
!raphic
%he table contains details &or the C+##!C%, R!S+(RC!,
SCH!*(!R_*$I#, *-, and S!!C%_C%+G_R+! roles.
Other roles that authorize you to administer special functions are created when that
functionality is installed. 4or example, XDBADMINcontains the privileges that are reuired
to administer the 65# database if that feature is installed. AQ_ADMINISTRATOR_ROLE
provides privileges to administer advanced ueuing. HS_ADMIN_ROLEincludes the
privileges needed to administer heterogeneous services.
7ou must not alter the privileges granted to these functional roles without the assistance
of Oracle !upport because you may inadvertently disable the needed functionality.
&rior to Oracle Database -g1elease 8, the CONNECTrole had the privileges ALTER
SESSION, CREATESESSION, CREATECLUSTER, CREATESYNONYM, CREATEDATABASE
LINK, CREATETABLE, CREATESEQUENCE, and CREATEVIEW. 9ow the CONNECTrolehas only the CREATESESSIONprivilege, and all other privileges are removed.
Although the CONNECTrole has freuently been used when provisioning new accounts in
Oracle Database, simply connecting to the database does not reuire all those privileges.
5a/ing this change enables new and existing database customers to enforce good
security practices more easily.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
4/44
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
5/44
Option 4:%his option is incorrect. %he CONNECTrole is granted automaticall' to
an' user who is created with +racle !nterprise $anager.
orrect ans&er's(%
. +he rstrconn.sl script located in the :O1A;#(
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
6/44
HRDBhas a MANAGERrole that allows a manager to modify certain fields of the employee
records that are assigned to the managers. BUGDBhas a USERrole that allows a user to
create bugs and update bugs that the user created. +he PRACTICE_MGRenterprise role
is assigned a set of global roles' MANAGERin HRDB, USERin BUGDB, and PARTICIPANT
in PRO'DB. (ach global role is defined in the local database, so the practice manager will
get different privileges in each database.
HRDBhas an EMPLOYEErole that allows every employee to view and modify certain
records, such as a profile. BUGDBhas a DEVELOPERrole that is allowed to update any
bug that is assigned to the developer. +he PROGRAMMERenterprise role is assigned
EMPLOYEEand DEVELOPERglobal roles.
+ote
ll users assigned the same enterprise role recei)e the same pri)ileges.
An enterprise role is an OID directory structure that can contain global roles on multiple
databases. +he enterprise role is granted to enterprise users. 0hen an enterprise user
attempts to connect to the database, the enterprise directory is ueried to obtain any
global roles associated with the user.
+he global role is defined locally in the database and granted privileges and roles. 7ou
cannot grant the global role to a user or a role in the database. An enterprise user can be
authorized to use the role only by an enterprise directory service.
se (5 Database ;ontrol (nterprise ser !ecurity to create an enterprise role. +his
creates a container for global roles. An example is HRACCESS_ROLE.
!raphic
%he Create !nterprise Role 5 HRCC!SS_R+! page consists o& a #ame &ield
with HRCC!SS_R+! entered in it. %his page also contains the *- Global
Roles and Grantees tabbed pages. %he *- Global Roles tabbed page contains a
table with #ame as HR_CC!SS and the t'pe o& *atabase as p2orcl.
+o create mapping to a global role, on the (nterprise ser !ecurity page of the Database
control, perform the following steps'
!raphic
%his page contains the links 6 $anage !nterprise *omains, $anage *atabases,
$anage !nterprise (sers, $anage (ser *e&ined !nterprise Groups, and +I*
Realm dministration.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
7/44
. ;lic/ the ,anage -nterprise omainslin/.
8. On the 5anage (nterprise Domains page, select the domain and clic/ on"igure.
In this example, the domain selected is +racle*e&ault*omain. It is listed in a table with columns 6 Select,
#ame, *atabases, (ser7Schema $appings, !nterprise Roles, 1rox' 1ermissions, and *omain dmins.
?. On the ;onfigure Domain ' OracleDefaultDomain page, clic/ the -nterpriseRolestab.
Currentl' the *atabases tabbed page is opened.
@. On the (nterprise 1oles tabbed page, clic/ reate.
%his page also contains the +k and Cancel buttons in addition to the Create button.
. (nter the name and clic/ Add.
HRCC!SS_R+! is entered in the #ame &ield.
B. On the !earch And !elect ' Database Clobal 1oles page, select the database and enter the credentials
for a user with access to DBA_ROLES. +hen select the roles and clic/ Select.
8ou pro)ide the details in the *atabase drop7down list and (ser #ame and 1assword &ields. In thisexample, the orcl4 database is selected. %he user name entered is ksmith and the corresponding
password is also entered.
nd 'ou select the role b' selecting the checkbox in the Select column against the rele)ant role name in
the #ame column o& the table in the page.
+he (5 Database ;ontrol (nterprise ser !ecurity tool enables you to associate an
enterprise role with a user. 0hen the user connects to the database, the global roles
assigned to the enterprise role are enabled for the user.
!raphic
%he Create !nterprise Role 5 HRCC!SS_R+! page is opened.
+o assign an enterprise user to an enterprise role, perform the following steps'
. on the ;reate (nterprise 1ole ' =1A;;(!!
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
8/44
Assigning the enterprise role to many users by using this method can be tedious.
(nterprise roles can be assigned to groups of users. se the Oracle Identity 5anagement
!elf !ervice console to create a group and assign users to the group. +hen use (!
pages to assign the group to the role by using the (#)G*+psnode instead of the
(#)Us%snode.
!raphic
%he Create Group section under the *irector' tabbed page o& the +racle Identit'
$anagement page is currentl' opened. It contains the &ields 6 #ame, *ispla'
#ame, and *escription. Sales is entered in the #ame and the *ispla' #ame
&ields. %he description is :$embers o& the Sales %eam:. %he Group Visibilit' &ield
has two selectable radio buttons 6 1ublic and 1ri)ate. In this example, 1ublic is
selected.
0. Securing objects &ith procedures
0hen the built%in authorization mechanisms are not sufficient, an object%oriented
approach is useful. &rogram units, procedures, and functions in the database, by default,
run with definer$s rights. +his means that the program unit executes with the privileges of
the owner of the unit.
+he HRapplication owner has a M%,-(al_H-s.*/table with very strict access
controls. +he HRuser creates !"# procedures and functions to implement the allowed
access.
HRcan grant the EXECUTEprivilege on these program units to authorize certain users toperform only the actions that are implemented in the procedures. sers can access the
table only through the procedures because they have not been granted any other object
privileges. +he procedures successfully access the table because they run with the
privileges of HR.
&rogram units can also run with invo/er$s rights. In this case, the procedure executes with
only the privileges of the user that executes the procedure. +his is useful for procedures
that are very general and owned by SYS. An example is the DBMS_LOBpac/age that
provides the LOBaccess routines that can be called by any user. 2ut the invo/er must
have privileges to access the LOB.
Often users are authorized to access certain objects only through an application that can
control and chec/ their actions.
2efore Oraclei, password%enabled roles offered limited control because the password
had to be supplied by the user or embedded in the application. If users have the
password, they can enable the role at any time. If the password is embedded )hidden in
the application code*, there are several utilities that can easily reveal the password.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
9/44
+he secure application role is enabled only through a pac/age. +he server chec/s the
calling stac/ to ensure that the proper pac/age is being used so that it cannot be
spoofed. +o enable a secure application role, the associated procedure must be called,
which controls whether the role is enabled. +he procedure can ma/e additional chec/s
and get information about the user$s environment by using a call to
SYS_CONTEXT)$USERENV
$,###
*, where###
can be, for example,IP_ADDRESS
or
PROXY_USER.
+he EXECUTEprivilege is granted on the pac/age only to the application. !o even if users
/now the details of the pac/age, they cannot enable the role, except through the
application.
. 2mplementing a secure application role
1oles grant authorizations to the authenticated user. 7ou may reuire additional
validation. 4or example, access may be restricted by time of day or client I& address.
2ecause a secure application role is a role implemented by a pac/age, the pac/age can
do the desired validation.
2esides limiting the privileges of the application server, the secure application role
prevents users from accessing data outside an application. +hey are forced to wor/ within
the framewor/ of the application privileges that they have been granted.
+o create a secure application role, perform the following steps'
. ;reate the secure application role.
8. ;reate the pac/age that sets the role. 0hen creating the pac/age, create the pac/age specification andthe pac/age body.
?. Crant the execute privilege on the pac/age so that the application server can set the role for the
application user.
@. 0rite the application server code that sets the role by calling the pac/age just created.
2ecause the pac/age does not need to exist when the CREATEROLEcommand is issued,
step 8 can be performed before step .
$uestion
During which step of implementing a secure application role is the AUTHID
CURRENT_USERclause used to define procedures with invo/er$s rights>
Options%
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
10/44
. ;reating the secure application role
8. ;reating the pac/age that sets the role
?. Cranting the execute privilege on the pac/age
@. 0riting the application server code that sets the role by calling the pac/age
Ans&er
Option 1:%his option is incorrect. %his is the &irst step in implementing a secure
application role. t this stage, 'ou use the CREATEROLEcommand to identi&' the
package that sets the role.
Option 2:%his option is correct. %his is the second step in the process and
consists o& creating the package speci&ication and the package bod'. t this stage,
'ou use the AUTHIDCURRENT_USERclause to de&ine procedures with in)oker;s
rights. It applies onl' to packages and stand7alone procedures and &unctions.
Option 3:%his option is incorrect. %his is the third step in the process. t this
stage, the execute pri)ilege is granted on the package.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
11/44
. !ecure application roles limit the privileges of the application server
8. 9o additional validation is reuired
?. !ecure application roles prevent users from accessing data outside an application
@. +he pac/age needs to exist when the CREATEROLEcommand is issued
Ans&er
Option 1:%his option is correct. -esides limiting the pri)ileges o& the application
ser)er, the secure application role pre)ents users &rom accessing data outside an
application.
Option 2:%his option is incorrect. Roles grant authori=ations to the authenticated
user. 8ou ma' reuire additional )alidation.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
12/44
!ometimes, a secure pac/age may need to access a table to retrieve all the roles that
can be enabled. +his table must be accessed by a definer$s rights procedure called from
the secure procedure that sets the role. ;reate a definer$s rights pac/age to return a list
of roles from the table )via an open cursor* to the invo/er$s rights procedure. +hen fetch
from the cursor the list of roles to be enabled.
ode
CREATE OR REPLACE PACKAGE *%_*l%s
AUTHID CURRENT_USER
IS
PROCEDURE s%._sal%s_%p_*l%&
END&
1
+he pac/age that enforces the secure application role selects from the APP_ROLEStable.
+he APP_ROLEStable validates users in the application by ensuring that the userenabling the role is connecting from a valid I& address. It contains the following columns'
ode
USERNAME ROLE IP_ADDRESS
PHALL SALES_REP 2340430520224
PHALL SALES_REP 2340430520267
USERNAME
USERNAMEcontains the name of the user who is trying to enable the role.
ROLE# and
+he name of the role that the user wants to enable is available in the ROLEcolumn.
IP_ARESS
+he IP_ADDRESScolumn consists of the client$s address that can enable the row.
In this example, if the PHALLuser can enable only the SALES_REProle from the I&
addresses 2340430520224and 2340430520267, the table would contain these details.
ode
USERNAME ROLE IP_ADDRESS
PHALL SALES_REP 2340430520224
PHALL SALES_REP 2340430520267
+he WHEREclause includes the following criteria'
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
13/44
ode
CREATE OR REPLACE PACKAGE BODY *%_*l%s IS
PROCEDURE s%._sal%s_%p_*l%
AS
!_-, *%0app_*l%s0-,8TYPE& BEGIN
SELECT -,
INTO !_-,
FROM *%0app_*l%s
WHERE s/s_(*#.%9.:;+s%%#!;
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
14/44
In this example, the account used by the application server is granted the EXECUTE
privilege on the pac/age. +he application server can execute the pac/age to enable the
role when it creates a session for the end user.
+he application server connects as the apps!user and sets the role after it starts the
user$s session.
ode
GRANT %9%(+.% ON *%_*l%s TO apps!&
0hen starting, the application server connects as the APPSRVuser and creates a
connection pool. 0hen starting a session for a user, the application server gets a
connection from the pool.
+o set the role for a user, the application server calls a procedure in the pac/age
referenced in the CREATEROLEcommand. +he procedure is called after the application
server establishes a session for the user.
ode
s%(0*%_*l%s0s%._sal%s_%p_*l%&
Do not grant the secure role to the user as a default role. If the role is granted to the user,
it is enabled at login if the default role is set to all. If there are roles that should be
enabled for users at login, this command disables the secure role.
ode
ALTER USER phall DEFAULT ROLE ALL EXCEPT *%_sal%s_%p&
Data dictionary views include the following roles'
ode
SQL? SELECT @
6 FROM ,>a_appl-(a.-*#_*l%s 3 WHERE ROLE ) ;OE_SALES_REP;&
ROLE SCHEMA PACKAGE
OE_SALES_REP SEC OE_ROLES
SQL?
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
15/44
SESSION_ROLESand
After the application server sets the role, the role appears in the SESSION_ROLESdata
dictionary view.
!A_APPLICATION_ROLES
+he data dictionary view called DBA_APPLICATION_ROLESlists all the secure applicationroles in the database. +he example displayed selects the role previously created. +he
SCHEMAcolumn contains the owner of the pac/age. 1oles are not owned by a schema.
Summary
In this topic, you$ve learned how roles and privileges can be used, and how to use secure
application roles.
reating an Application onte*t
Learning Objective
After completing this topic, you should be able to
recognize how an application context is used
1. escription o" application conte*t
An application context is a memory container with read%only attributes. It can be
populated only by a named pac/age. 7ou can use these attributes explicitly or implicitly inyour application. sing an application context is li/e writing down an often%used phone
number and /eeping it next to your phone, where you can find it easily, rather than
loo/ing it up every time you need it.
A context has an allocated area of memory called a namespace. +he context or
namespace has named attributes. (ach attribute can hold a text string value. In some
programming languages, this is /nown as an associative array.
!raphic
%he application context reads a 10S> program a&ter being set b' a 10S>
package.
+he application context namespace identifies the application context. 4or example, you
may have the HRAPPand OEAPPnamespaces that are used with the =uman 1esources
and Order (ntry applications respectively. 5ultiple namespaces enable you to use the
same attribute$s name in a different namespace without interfering with other
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
16/44
namespaces. 4or example, the HRAPPand OEAPPapplication contexts can each have an
attribute named ID that contains a different value in each context.
;ontext memory accessed locally is allocated from the f ixed portion of program global
area, also /nown as &CA, which belongs to each server process. +his memory allocation
is not subject to the effects of the PGA_AGGREGATE_TARGETparameter.
A namespace is a set of names in a naming system where all names are uniue. (ach
context is created in its own namespace. In the example, each attribute of the HRAPP
context is uniue within the HRAPPcontext.
ode
CREATE CONTEXT happ
USING h_(*#.%9.&
,>"s_s%ss-*#0s%._(*#.%9. :
;happ;< ;%"p_-,;< !_%"p_-, =&
CREATE CONTEXT hrapp
USING hr_"#n$e%$;
?hen a context is created, a memor' area is allocated, and the context is gi)en a name.
package is associated with the context at creation. %his package is the onl' package that
is allowed to set the attributes in a context.
In the example, the HRAPPcontext is created, and a package is named that has exclusi)e
rights to set the attributes o& the context. In the example, the package is HR_CONTEXT.%he CREATECONTEXTcommand is used to create a context in the namespace and
associate a package with the context.
&'ms_sess(#n)se$_"#n$e%$ *
+hrapp+, +emp_(&+, v_emp_(& -;
SET_CONTEXTis used to create attributes or set the )alues o& them. I& the attribute does
not exist, it is created. In the example, the %"p_-,attribute is created in the HRAPP
context, and the )alue o& !_%"p_-,is assigned.
Although the main benefit of using application contexts is improved performance,
contexts are used with Oracle Database security features, such as Eirtual &rivateDatabase, also referred to as E&D, or fine%grained access control, referred to as 4CA;.
0ith application context, you can write applications that use the attributes of the
application context to perform the following'
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
17/44
Authorize users when they log on. 4or example, you can verify that the user is connecting
through a specific computer by verifying the user$s I& address. 7ou would use the USERENV
context to access the user$s current I& address.
!et context attributes that are used by 4CA; policies. !ession properties can be used to limit the
rows that the user can access. In an order%entry application, customers can access only their ownorders by setting a context attribute with the current user$s customer number. 2ecause of the way
the context is set and used, you can implement 4CA; without changing the application.
!et the attributes that are used in the application. In this situation, set the attributes as part of the
context, rather than accessing the attributes from a table. 4or example, if the user$s employee
number is used freuently in the application, you can create a context attribute that contains the
employee number, rather than selecting the employee number from a table.
+he application developer or security developer writes a pac/age that assigns values to
the context attributes. +he attributes of the context can be set only with this pac/age. +his
is the pac/age that is associated with the context when the context is created. +hispac/age can be called by the application or a logon trigger. +he pac/age sets the
attributes that are used for that session. +hese must be attributes that are freuently used
by the application.
+he benefit of the context comes from caching these values in memory and not having to
perform !"# or system callouts to retrieve them.
(ach application can have its own application%specific context. 4or example, the context
attributes for a human resources application can include position, organizational unit, and
country, whereas context attributes for the order%entry system may be customer ID and
name.
Applications can use multiple contexts, and a context can be used by multiple
applications.
A special context is the built%in USERENVcontext. +he USERENVcontext is populated with
values that are commonly found in the VSESSIONand VPROCESSviews and is
available to all applications.
$uestion
0hich statements accurately describe application context>
Options%
. It is a memory container with read%only attributes
8. It can be populated by only the USERENVcontext
?. Its namespace identifies the application context
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
18/44
@. A context can$t be used by multiple applications
Ans&er
Option 1:%his option is correct. n application context is a memor' container with
read7onl' attributes. 8ou can use these attributes explicitl' or implicitl' in 'ourapplication.
Option 2:%his option is incorrect. n application context can be populated onl' b'
a named package.
Option 3:%his option is correct. context has an allocated area o& memor' called
a namespace. %he context or namespace has named attributes. !ach attribute
can hold a text string )alue.
Option 4:%his option is incorrect. pplications can use multiple contexts, and a
context can be used b' multiple applications.
orrect ans&er's(%
. It is a memory container with read%only attributes
?. Its namespace identifies the application context
A context can be classified by the source of the data values that it contains. +he value
that is assigned to the context attribute can have different sources.
+he built%in context called USERENVincludes the following types of attributes'
client properties, such as the I& address of the client computer
user properties, such as the proxy, session, and operating system user
authentication methods, such as the type )which indicates whether the user is authenticated
through the database*, the operating system, the networ/, or a proxy, and
national language support, also called 9#!, settings, such as NLS_TERRITORY
7ou can set attributes that are stored in a context from any database object. 4or example,
an EMPLOYEEStable can include cost center, title, signing authority, and other information
that is useful for row%level security. Also values returned from procedures and functions
can be used. 4or example, use a function that returns the employee ID to assign a value
to the context attribute.
An externally initialized application context is characterized by attributes and values that
are initialized through external resources, such as an O;I call, a job ueue process, or a
database lin/.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
19/44
It provides the following'
for remote sessions, automatic propagation of context values that are in an external initialized
context namespace
for job ueues, restoration of context values that are in an externally initialized context
namespace, and
for O;I, a mechanism to initialize context values that are in an externally initialized context
namespace
Any client program can initialize this context by ma/ing O;I calls, and there are logon
event triggers that can verify the values. It is up to the application to interpret and trust the
values of the attributes. +his type of context is created with this command.
9ote that with an externally initialized application context, the middle%tier server canactually initialize context values on behalf of database users. ;ontext attributes are
propagated for the remote session at initiation time, and the remote database accepts the
values if the namespace is externally initialized.
ode
CREATE CONTEXT %9.%#al USING %9._pa(a$% INITIALIED
EXTERNALLY&
5any organizations centralize user information and user management in a #ightweight
Directory Access &rotocol or #DA& based directory, such as Oracle Internet Directory,referred to as OID. Application context attribute values can be stored in OID. +his type of
context is created in the database with this command.
ode
CREATE CONTEXT h$app USING h_$_(*#.%9. INITIALIED
GLOBALLY&
0hen an enterprise user connects to the database, the attributes defined in the global
context of that user$s OID entry are placed in the named application context. +he globalcontext named in the preceding example is HRGAPP. +he attributes that are available are
dependent on the attributes defined in the #DA& directory. +he SYS_CONTEXTfunction
can be used to access the attributes of the context.
+he HRGAPPcontext and the TITLEattribute must be added to the user$s OID entry in
the Oa(l%DBAppC*#.%9.object.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
20/44
ode
SYS_CONTEXT:;HRGAPP;
Options%
. 2uilt%in context
8. (xternalized application context
?. #ocal application context
@. Clobal application context
Ans&er
Option 1:%his option is incorrect. %he built7in context called USERENVincludes
attributes such as client properties, user properties, authentication methods, and
national language support settings.
Option 2:%his option is incorrect. n externall' initiali=ed application context is
characteri=ed b' attributes and )alues that are initiali=ed through external
resources, such as an +CI call, a @ob ueue process, or a database link.
Option 3:%his option is correct. 8ou can set attributes that are stored in a context
&rom an' database ob@ect. lso, )alues returned &rom procedures and &unctions
can be used.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
21/44
SYS_CONTEXT:;SYS_LDAP_USER_DEFAULT;
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
22/44
happis the context namespace and
HR_CONTEXTis the trusted pac/age that can set attributes in the context namespace
In the example code, the pac/age and security objects are created by a user designated
for this purpose.
ode
CREATE CONTEXT happ USING h_(*#.%9.&
After you have created the context, you can set or reset context attributes by using the
DBMS_SESSION0SET_CONTEXTpac/age. +he values of the attributes that you set
remain either until you reset them or until the user ends the session.
7ou can set the context attributes only in two locations'
ode
CREATE CONTEXT happ USING h_(*#.%9.&
inside a procedure of the trusted pac/age that you have named in the CREATECONTEXT
statement and
inside the function named in the policy
+his prevents a malicious user from changing context attributes without proper attributevalidation.
ode
CREATE CONTEXT happ USING h_(*#.%9.&
Administration of application contexts is integrated in (nterprise 5anager, also referred to
as (5, with a graphical user interface for managing application contexts, 4CA; policies,
and Oracle #abel !ecurity policies. 7ou can use (5 to create a context and associate it
with a !"# pac/age.
;reate a !"# pac/age with functions that sets the context for your application. +his
example creates the HR_CONTEXTpac/age. +he pac/age sets the EMP_IDattribute of
the HRAPPcontext. +he session username is used to get the employee ID, and
SET_CONTEXTis used to set a context attribute.
ode
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
23/44
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
24/44
BEGIN
h_(*#.%9.0s%._%"p_-,:=&
END&
1
Access application context attributes and use them where they are needed in your
application. +he name of the context and the attribute are the two arguments used to
return an attribute value.
ode
SELECT @ FROM ,%pa."%#.s
WHERE "a#a$%_-, )
s/s_(*#.%9.:;happ;
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
25/44
ode
s/s_(*#.%9. :;+s%%#!;< ;-p_a,,%ss;=
s/s_(*#.%9. :;happ;< ;%"p_-,;=
I& address of the client computer by using the IP_ADDRESSattribute of the built%in USERENV
context and
%he I1 address o& the client computer is returned b' using this code5
s's_context A;useren);, ;ip_address;B
ode
sys
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
26/44
+he memory for the application context is allocated from the session$s program global
area, also referred to as &CA, by default. In many application architectures, the middle%
tier application is responsible for managing connection pooling for application users.
sers authenticate themselves to the application. +he application then uses a single
identity to log in to the database and maintains all connections. In a connection%pooling
environment, usually any user can use any connection.
+he application decides the connection that a user reuest can use. In this environment,
it is not possible to maintain application attributes by using session%dependent secure
application context because the context is private to each session, and because of the
sessionless model of the application, any user can use any session.
!raphic
%he pplication context in SG is !$1 I* 9 232. It is accessed in two programs in
two di&&erent database sessions. %he' are 10S> program in (ser database
session 2 and 10S> program - in (ser database session 4.
+he application context accessed globally is a type of secure application context that can
be shared among trusted sessions. It can be shared because the memory for the context
is allocated from the system global area, referred to as !CA.
5iddle%tier applications use globally accessed application contexts to manage application
attributes securely and globally. Clobal application contexts with connection pooling allow
multiple connections to access one or more contexts, instead of setting up an application
context for each user session.
Clobally accessed application contexts provide additional flexibility for web%basedapplications. +hey also provide enhanced performance through the reuse of common
application contexts among multiple sessions, instead of setting up application contexts
for each session.
Application contexts accessed globally provide performance improvements through
connection reuse. +hese application contexts are initialized once, instead of being
initialized for each session individually.
+ote
%he application must initiali=e the context.
+he middle tier sets the application context for each session. +he context accessed
globally allows the middle tier to store various application context definitions in a central
place in the !CA and apply the context to a user session at session%creation time. +his
then becomes that session$s driving context. +his also reduces the setup time of the user
session when the application is using connection pooling.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
27/44
A context accessed globally cannot be initialized from OID or an external source. !o any
context accessed globally must be a local context accessed globally.
+o support connection pooling managed by middle%tier applications, the DBMS_SESSION
interface gives the application the ability to add a client identifier for each application
context. +he application can manage the context globally, whereas each client notes onlyits assigned application context.
+he application must test input to prevent a malicious user from injecting a call to
DBMS_SESSIONor any !"# injection attempt. 2ecause DBMS_SESSIONis granted to
PUBLIC, such an injection can allow the user unauthorized privileges. 2y default, a
context is not accessed globally.
+ote
context accessed globall' is not a)ailable in the Real pplication Clusters, also
called RC, en)ironment &or connections that span instances. %he context
accessed globall' is stored in the shared pool o& one instance and is not a)ailable
in the other instances o& the cluster.
;onsider an example of actions being performed. ;ertain actions are ta/en by the user,
and others are ta/en by a program running on the application.
+he application server starts up and builds its connection pool by establishing multiple
connections to the database as the APPSMGRuser. ser PHALLlogs in to and is
authenticated by the application server.
!raphic
%he boxes in the &low chart list actions per&ormed. %he &irst box lists actions 6
ogs in, $akes another reuest, and ogs out 6 per&ormed b' the user 1H as
the second, sixth, and eighth step. %he second box lists steps 2, D, E, F, , and ,
which are per&ormed b' the application ser)er, which connects to the user 1H
and the database. %he' are -uilds connection pool, !stablishes session,
1rocesses reuest, Completes reuest, 1rocesses second reuest, and Clears
context respecti)el'.
+he application server establishes a session for the reuest from PHALLby performingthe following functions'
using a connection from the connection pool
calling SET_IDENTIFIERto assign a session client identifier for the PHALLusername
setting the application context, and
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
28/44
saving the client identifier in the browser of PHALLas part of a coo/ie or maintaining the client
identifier within the application server
+he application server processes the reuest of PHALL. 0hen the application reads
contexts, it does not include the client identifier because the SET_IDENTIFIERcall has
already identified the session.
0hen the application server finishes the reuest of PHALL, it issues a
CLEAR_IDENTIFIERcall and returns the connection to the pool.
+he PHALLuser ma/es another reuest. +he application starts differently because there
is a coo/ie identifying the current context'
the session client identifier is retrieved from the coo/ie or the application server storage
the client identifier is used in the SET_IDENTIFIERcall after the connection is obtained from the
pool, and
the application runs as before, accessing the context from the previous call
PHALLlogs out from the application. +he application server issues CLEAR_CONTEXTto
clear out the application context.
All SYS_CONTEXTcalls within this database session return only application context
values belonging to the client session F for example, SYS_CONTEXT)$HRAPP$,$ID$* returns
PHALL.
). PL3S$L pac/age and procedures
se the programs in the DBMS_SESSIONpac/age to perform these functions'
ode
s%._(*#.%9.:(*#.%9.< a..->+.%< !al+% =&
,>"s_s%ss-*#0s%._(*#.%9.
:;happ;< ;%"p_-,;< !_%"p_-, =&
,>"s_s%ss-*#0s%._-,%#.--%:(l-%#._-,=&
,>"s_s%ss-*#0s%._-,%#.--%:26345=&
maintain application contexts
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
29/44
list application contexts, and
maintain client identifiers that are used with global application contexts
+he procedures and functions in DBMS_SESSIONinclude only those that are related to
application contexts.
+he first SET_CONTEXTexample sets the EMP_IDattribute in the HRAPPcontext to the
value in the V_EMP_ID!"# variable. 2ecause the client identifier is not included as
an argument, this context is not being shared.
ode
s%._(*#.%9.:(*#.%9.< a..->+.%< !al+% =&
,>"s_s%ss-*#0s%._(*#.%9.
:;happ;< ;%"p_-,;< !_%"p_-, =&
+he second example sets a session client identifier for PHALL, enabling the session to
access a context accessed globally.
ode
,>"s_s%ss-*#0s%._-,%#.--%:(l-%#._-,=&
,>"s_s%ss-*#0s%._-,%#.--%:26345=&
+he SET_IDENTIFIERprocedure sets an identifier that can be used to share a global
context. It has this specification.
In this specification, the (l-%#._-,is the identifier being set for this session. +his
identifier is arbitrary and it has no relationship with the s%ss-*#_-,username. +he
application chooses the identifier. 2ecause this identifier is often placed in the browser
coo/ie, it should not be the information that can violate the privacy of the user.
+he identifier should be a randomly chosen string or number. If the application code sets
the client identifier with a call to DBMS_SESSION0SET_IDENTIFIER, (l-%#._-,is
recorded in audit trails and can provide a way to lin/ a user to an action, if the applicationmaintains a (l-%#._-,%to%user mapping.
ode
PROCEDURE s%._-,%#.--% :
(l-%#._-, VARCHAR6 =
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
30/44
+he CLEAR_IDENTIFIERprocedure clears the current session identifier that has been
set with DBMS_SESSION0SET_IDENTIFIER. It has no arguments.
+he SET_CONTEXTprocedure sets a context attribute. It has this syntax.
In this syntax, #a"%spa(%is the name of the application context, a..->+.%is thename of the attribute to be set, !al+%is the value to be assigned to the attribute,
+s%#a"%is the username attribute for the application context, and (l-%#._-,is the
client identifier that identifies a user session to set a context.
ode
PROCEDURE s%._(*#.%9. :
#a"%spa(% VARCHAR6+.% VARCHAR6+.%is the name of the attribute to be cleared.
If a..->+.%is not included, all contexts for the client are cleared.
ode
PROCEDURE (l%a_(*#.%9. :
#a"%spa(% VARCHAR6T/pT/ptype has this specification.
ode
TYPE AppC.9R%(T/p IS RECORD :
#a"%spa(% VARCHAR6:37=+.% VARCHAR6:37=T/p IS TABLE OF AppC.9R%(T/p
INDEX BY BINARY_INTEGER&
+he UNIQUE_SESSION_IDfunction returns an identifier, which is uniue for all sessions
that are currently connected to this database. 5ultiple calls to this function during thesame session always return the same result. It has this specification.
+his identifier is different from the identifier that you can set by using SET_IDENTIFIER.
+his identifier is a hexadecimal representation of s%ss-*#_-,, also called !ID,
s%-al, and a seuence number. It can be up to 8@ bytes and can consist of
alphanumeric characters.
ode
FUNCTION +#-+%_s%ss-*#_-, RETURN VARCHAR6
$uestion
0hich DBMS_SESSIONprocedure would you use to find all current context
namespaces>
Options%
. UNIQUE_SESSION_ID
8.LIST_CONTEXT
?. SET_IDENTIFIER
@. SET_CONTEXT
Ans&er
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
32/44
Option 1:%his option is incorrect. %his &unction returns an identi&ier, which is
uniue &or all sessions that are currentl' connected to this database.
Option 2:%his option is correct. %his procedure lists the current context
namespaces. It uses the s'ntax PROCEDUREl-s._(*#.%9.Al-s.OUT
AppC.9Ta>T/p, ls-%OUTNUMBERB.
Option 3:%his option is incorrect. %his procedure sets an identi&ier that can be
used to share a global context.
Option 4:%his option is incorrect. %his procedure sets a context attribute.
orrect ans&er's(%
8. LIST_CONTEXT
0. 2mplementing the application conte*t
+o implement an application context accessed globally, perform these steps.
2ecause the application server sets the session client identifier when a session is
established, the application programs that access the application context are coded the
same, whether or not the context is accessed globally. In either case, you do not include
the session client identifier when reading an application context.
+hese instructions apply only to application contexts that are preserved between
database connections. 4or a stateless session, you can use an application context
without accessing it globally.
!raphic
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
33/44
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
34/44
dbms
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
35/44
ode
dbms"s_s%ss-*#0(l%a_-,%#.--%:=&
get the client identi"ier
If there is a session client identifier, this user has already established a session. +herefore,
use the client identifier to ensure that the users access the correct application context. It is
common practice to place the client identifier in the session coo/ie, but the client identifier
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
36/44
can be stored in the application as well. Do not put the client identifier in the session
coo/ie.
set the client identi"ier "or this session# and
!et the client identifier for this session because the application context can be shared
among database connections. It does not matter which database connection processesthis reuest from the user.
clear the identi"ier
At the end of the reuest, before the connection is put bac/ into the connection pool, clear
the current client identifier. +his ensures that the next session cannot inadvertently use a
left over client identifier to access an inappropriate context.
0hen the user has completed all reuests and wants to end the session, clear the
context that applies to the user$s session.
In this example, the attributes for the HRAPPapplication context for the client identifier
26345are cleared because the attribute name is not included as the third argument.
ode
EXEC ,>"s_s%ss-*#0(l%a_(*#.%9.
:;HRAPP;< ;26345;=&
$uestion
0hen implementing application context accessed globally, during which stepwould you include the ACCESSEDGLOBALLYclause to indicate that the context
can be accessed from multiple sessions>
Options%
. ;reating the application context accessed globally
8. 5odifying the program that establishes a session
?. 5odifying the application program that handles subseuent reuests in the same
session
@. ;reating or modifying the application program that ends a session to clear the
context
Ans&er
Option 1:%his option is correct. 8ou create an application context using the
s'ntax CREATECONTEXTappl-(a.-*#_(*#.%9.USINGpackage ACCESSED
GLOBALLY. 8ou must indicate that the application context is accessed globall'
using the ACCESSEDGLOBALLYclause.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
37/44
Option 2:%his option is incorrect. %his is the second step in implementing
application context accessed globall'. t this stage, 'ou get a uniue )alue to use
as a client identi&ier, set the application context, set the session client identi&iers,
and sa)e the client identi&ier in a cookie.
Option 3:%his option is incorrect. %his is the third step in implementing
application context accessed globall'. t this stage, 'ou retrie)e the client
identi&iers, set the client identi&ier &or the session, and clear the client identi&ier
when the reuest ends.
Option 4:%his option is incorrect. %his is the &inal step in implementing application
context accessed globall'. t this stage, 'ou retrie)e the client identi&ier and clear
the context.
orrect ans&er's(%
. ;reating the application context accessed globally
+he data dictionary includes the following views that are related to application contexts'
ode
SQL? CREATE CONTEXT happ USING h_(*#.%9.&
C*#.%9. (%a.%,0
SQL? SELECT @
6 FROM ,>a_(*#.%9. 3 WHERE #a"%spa(% ) ;HRAPP;&
NAMESPACE SCHEMA PACKAGE TYPE
HRAPP SYS HR_CONTEXT ACCESSED LOCALLY
SQL?
ALL_CONTEXT
ALL_CONTEXTdescribes all context namespaces in the current session for which
attributes and values have been specified by using the DBMS_SESSION0SET_CONTEXT
procedure. It has the same columns as DBA_CONTEXT.
!A_CONTEXT
DBA_CONTEXTdescribes all context namespaces that are defined in the database,
regardless of whether any attributes have been specified for them by using the
DBMS_SESSION0SET_CONTEXTprocedure. +his example uses this view.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
38/44
!A_GLO!AL_CONTEXT
DBA_GLOBAL_CONTEXTcontains a description of all context information that is accessible
globally. +his view is a subset of the information in DBA_CONTEXT.
SESSION_CONTEXT#./CONTEXT# and G./CONTEXT# and
SESSION_CONTEXT, VCONTEXT, and GVCONTEXTlist the attributes and the attributevalues for the current session. +he views include NAMESPACE, ATTRIBUTE, and VALUEas
each column.
NAMESPACEis the namespace that the active attribute is in, ATTRIBUTEis the name of the
active attribute, and VALUEis the value of the active attribute. SESSION_CONTEXTand
VCONTEXTlist the same information. GVCONTEXTis used with 1eal Application
;lusters, which is also /nown as 1A;.
./GLO!ALCONTEXTand G./GLO!ALCONTEXT
VGLOBALCONTEXTand GVGLOBALCONTEXTlist the attributes and the attribute values
for application contexts that are accessed globally. +he views include NAMESPACE,ATTRIBUTE, VALUE, USERNAME, and CLIENTIDENTIFIERas columns.
NAMESPACEis the namespace that the active attribute is in, ATTRIBUTEis the name of the
active attribute, VALUEis the value of the active attribute, USERNAMEis the username for
the session, and CLIENTIDENTIFIERis the uniue session identifier.
GVGLOBALCONTEXTis used with 1eal Application ;lusters or 1A;.
$uestion
0hich data dictionary view describes all context namespaces that are defined inthe database, regardless of whether any attributes have been specified for them
by using the DBMS_SESSION0SET_CONTEXTprocedure>
Options%
. DBA_GLOBAL_CONTEXT
8. ALL_CONTEXT
?. SESSION_CONTEXT
@. DBA_CONTEXT
Ans&er
Option 1:%his option is incorrect. %his )iew contains a description o& all context
in&ormation that is accessible globall'.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
39/44
Option 2:%his option is incorrect. %his )iew describes all context namespaces in
the current session &or which attributes and )alues ha)e been speci&ied b' using
the DBMS_SESSION0SET_CONTEXTprocedure.
Option 3:%his option is incorrect. %his )iew lists the attributes and the attribute
)alues &or the current session.
Option 4:%his option is correct. %his )iew describes all context namespaces that
are de&ined in the database, regardless o& whether an' attributes ha)e been
speci&ied &or them b' using the DBMS_SESSION0SET_CONTEXTprocedure. It
contains NAMESPACE, SCHEMA, PACKAGE, and TYPEcolumns.
orrect ans&er's(%
@. DBA_CONTEXT
If you try to change the context outside of the pac/age specified in the CREATECONTEXTcommand, you receive an error message stating that you do not have the privilege
reuired to change the context.
If the SYS_CONTEXTarguments are constants, the SYS_CONTEXTfunction wor/s much
li/e a bind variable, enabling cursor sharing.
ode
SQL? %9%( DBMS_SESSION0SET_CONTEXT :;happ;< ;%"p_-,;< 7=&
BEGIN DBMS_SESSION0SET_CONTEXT :;happ;< ;%"p_-,;< 7=& END&
@
ERROR a. l-#% 2
ORA72732 -#s+-(-%#. p-!-l%$%s
ORA7J526 a. SYS0DBMS_SESSION< l-#%
ORA7J526 a. l-#% 2
0hen you execute a statement, Oracle Database gta/es a snapshot of the entire
application context. 0ithin the duration of a uery, the context remains the same for all
fetches of the uery. If you attempt to change the context within a uery, the change does
not ta/e effect in the current uery. 2ecause a simple application context is allocated per
session, the application context can be versioned.
Eersioning is not available for the application context accessed globally. Eersioning
returns the SYS_CONTEXTvalues at a point in time. 2ecause multiple client sessions may
be accessing the same global application context values at any time, versioning is not
possible.
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
40/44
If you try to execute SYS_CONTEXTin a parallel uery environment, you receive a uery
error.
If SYS_CONTEXTis used inside a !"# function that is embedded in a parallel uery, the
function cannot pic/ up the application context. +his is true because the application
context exists only in the user session. +o use these features in combination, you mustcall SYS_CONTEXTdirectly from the uery.
Application contexts accessed globally are not available in a 1A;.
0hen using an application context for security, the source of the values for the context
attributes must be thoroughly validated. If the source of the context is user input, there the
attribute may be altered to allow unintended access.
Summary
In this topic, you$ve learned how to implement an application context accessed globally.
Using Roles and Application onte*ts
Learning Objectives
After completing this topic, you should be able to
use roles in Oracle Database 11g
use application context in Oracle Database 11g
-*ercise overvie&
7ou want to implement the secure application role and configure and test an enterprise
role. 7ou$ve also implemented a local application context named (5&
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
41/44
testing application context
viewing context information
4as/ 1% Using secure application roles
7ou have created a secure application role called =1
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
42/44
Steps list
2nstructions
?. ;lic/ reate
@. +ype HRACCESSin the 9ame text box and clic/ the scroll bar do&n arro&
. ;lic/ Add
B. +ype s%(in the ser 9ame text box, type *a(l%_2in the &assword text box and clic/ !o
J. !elect the 5R6A-SSchec/box and clic/ Select
K. ;lic/ the !ranteestab
. ;lic/ Add
-. +ype Bin the 9ame text box and clic/ !o
. !elect the cn78S-ARS#cn7users# dc7easynomadtravel#dc7comchec/box and clic/ Select
8. ;lic/ ontinue
?. ;lic/ O/
4as/ 0% 4esting an enterprise role
;onnected as the 2!(A1! user, who has access to the =1A;;(!! role, you viewed
the identity information, the roles granted to the session, and ueried the
=1.(5O7((! table. 7ou now want to do the same connected as another user who
does not have access to the =1A;;(!! role. ;onnect as user AHI9C with a password
of oracle
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
43/44
Steps list
2nstructions
. +ype WHERE %"pl*/%%_-, ) 27J&and press -nter
4as/ % 4esting application conte*t
As the !(; user, you have implemented a local application context named (5&
-
8/10/2019 Oracle Database 11g Privileges Roles and Application Contexts
44/44