Dear Readers,
My name is Franz Devantier, creator of this blog. I am an Oracle Certified
Professional (OCP DBA 11g) Security DBA.
I will be sharing with you the basic duties of an Oracle DBA, and also
some of the undocumented, and not so well known tasks.
I will make a deal with you: If you refer me to a company that needs
database support, from a few hours per week to full time, and I am able to sign
a contract with them.
Then I will give you 10% of the monthly
contract or deal price every month. When
the contract ends, and we re-sign the contract, I will again give you 10% of
the monthly contract price. This will go
on until the company no longer employs or contracts me or my agents to look
after their databases.
I can do this, because that 10% is my
marketing budget. When we re-sign the
contract, in the future, it may depend on you giving the thumbs up again, and
that is worth 10% of the monthly contract price, to be given to you as
commission.
Security,
Privileges, Roles, Profiles and Resource Limitations - Part
12
Roles in a Distributed Environment
There is one important point when you use roles in a
distributed database environment. You
need to ensure that all the needed roles are set as the default roles for a
distributed session. When you connect to
a remote database from within a local database session, then you cannot enable
roles. Even if you executed a remote
procedure to enable a role at the remote site it would not enable the role.
Secure Application Roles
Secure application roles are special roles that can only be
enabled by an authorized PL/SQL package.
This arrangement limits the enabling of these roles to within the
application.
Security can be compromised when passwords are embedded in
application source code or stored in a database table, even in encrypted
form. A secure application role
overcomes this security weak-point. When
the secure application role is created, the PL/SQL package that is authorized
to enable the role is specified.
In the specified PL/SQL package, the first step is to see
if the user invoking the package has the rights to execute the package. Then a check to see if the combined
privileges are sufficient to enable the role is conducted. Any amount of additional custom checks can be
build in, for example, the package may check to see the user has connected
through a proxy, to check if the IP address is in the correct range etc. If all the tests pass, then the PL/SQL
package will enable the role.
Oracle Database provides secure application roles, which
are roles that can only be enabled by authorized PL/SQL packages. This
mechanism restricts the enabling of such roles to the invoking application.
Security is strengthened when passwords are not embedded in
application source code or stored in a table. Instead, a secure application
role can be created, specifying which PL/SQL package is authorized to enable
the role. Package identity is used to determine whether privileges are sufficient
to enable the roles. Before enabling the role, the application can perform
authentication and customized authorization, such as checking whether the user
has connected through a proxy.
Users can’t change the security domain inside a definer’s
right procedure, so a secure application role can only be enabled inside an
invoker’s rights procedure. This adds
another level of security, because the user does not have the rights to execute
the processes in the application, however after the secure application role has
authorized the user and the role is enabled, then the user can execute the
application code with all the needed access.
Creation of Secure Application Roles
When a secure application role is created, the syntax
“IDENTIFIED USING <…>” is added.
So the syntax to create a secure application role could look something
like this:
CREATE ROLE fred_app_role IDENTIFIED BY fred.app_role;
·
The syntax tells us
that “fred_app_role” is a secure application role.
·
The role can only be
enabled by procedures defined inside the PL/SQL package fred.app_role.
In order to create a secure application role, you must have
the CREATE ROLE system privilege.
If a secure application role is assigned to a user as the
default role (it tends to automatically become the default role), then the role
is automatically enabled at logon, without reference to the package. This is because a user with a default role
does not have to be authenticated in any way, whether by a package or by a
password for a password protected role.
So this is a potential problem which can be avoided by;
·
If a secure
application role is granted to a user then issue the statement ALTER USER …
DEFAULT ROLE ALL EXCEPT <fred_app_role>;
After this the role will only be
able to be enabled by an application issuing the authorized PL/SQL package.
·
When assigning roles
use the GRANT ALL EXECEPT <role>; syntax.
Roles that are enabled inside an invoker’s rights procedure, remain in
effect even after the procedure exits.
To deal with this you can have a
dedicated procedure that cleans up the granted roles, for the rest of the
session. Otherwise, when the user comes
out of the application, but is still connected to the session, the user will
potentially have abnormally powerful rights.
So the cleanup procedure will do something like “GRANT ALL EXCEPT
fred_app_role”. This way the user can
continue their session with the same rights that they had before running the
application.
User Resource Limits
As part of the security domain of each user, you can define
or set limits on the amount of system resources, that the user will be allowed
to use. You can prevent a runaway
process from consuming all the resources, and bringing the system to a
standstill.
You manage the resource limit feature with the Database
Resource Manager.
Profiles provide a way of setting various resource
limits. You can invoke the password
complexity package from the profile. You
can create different profiles for groups of users, or you can let the users all
use the default profile. You can define
an unlimited number of profiles.
Setting resource limits, does have a slight performance
impact, because when a user creates a session (logs on), then Oracle will load
the resource limit data for each user.
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment