Database Support: March 2013

Sunday, March 31, 2013

Security, Authentication Methods - Part 2

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.

Contact: Franz

Security, Authentication Methods - Part 2

Authentication by the Operating System

On some operating systems, Oracle can use information maintained by the operating system, to authenticate users. There are benefits to this approach:

· Once a user is authenticated by the operating system, that user can connect to oracle without having to specify a username or password

For example the user would connect to the operating system user account, and then connect to the oracle database like this:

# sqlplus /

· Because the control over user authentication is centralized in the operating system, Oracle does not need to manage user passwords. Oracle still needs to maintain the usernames in the database.

· Audit trails in the database, and in the operating system can use the same user names.

When the operating system is used to authenticate database users, managing distributed database environments and database links requires special care.

Authentication by the Network

Authentication over a network is handled either by the SSL protocol, or by a third-party service. To use a network authentication service with Oracle, you will need to install the enterprise edition of the Oracle database server with the advanced security option.

· Authentication using SSL

· Authentication using third-party services

· Authentication using Kerberos

· Authentication that is PKI-based

· Authentication with RADIUS

· Using Directory-Based services

Authentication Using SSL

The Secure Socket Layer (SSL) protocol is an application layer protocol. It can be used for user authentication to a database, and it is independent of global user management in Oracle Internet Directory. Users can use SSL to authenticate to the database even without a directory server in place.

Authentication Using Third-Party Services

Authentication over a network makes use of third-party network authentication services. Prominent examples include Kerberos, Public Key Infrastructure (PKI), the Remote Authentication Dial-In User Service (RADIUS), and directory-based services.

If network authentication services are available to you, then Oracle can accept authentication from the network service. If you use a network authentication service, then some special considerations arise for network roles and database links.

Franz Devantier,

Need a database health check, or a security audit?

devantierf@gmail.com

Classifieds

Year of the Stem Cell

Devantier Hosting, from $2.75

Host then Profit GVO

Global Domains International

Traffic Exchange Profits

Easy Downlines

Fast Cash and Traffic

Simple Safelists

Traffic Wave - Autoresponder

Traffic Hoopla

Business Related Social Media

Saturday, March 30, 2013

Security, Authentication Methods - Part 1

Dear Readers,

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.

Contact: Franz

Security, Authentication Methods - Part 1

By authentication we mean verifying the identity of someone, a user, a device, or an entity who wants to; access data, resources, or applications. Authentication or validating the identity, establishes a relationship of trust for further interactions. Authentication enables accountability, making it possible to link access and actions to specific identities.

After authentication, authorization processes allow or limit the levels of access permitted, and the actions that are allowed for that specific entity. Authorization operates with Privileges, roles, profiles, and resource limitations.

Oracle allows a database instance to use all of the methods, or any of the methods. Oracle requires special authentication procedures for database administrators, because they perform powerful database operations.

Oracle encrypts passwords during transmission to ensure the security of network authentication.

The identity of database users can be validated in a number of different ways, in order to prevent unauthorized use of the database:

· Validate at the Operating System

· Validate with the network, and LDAP directories

· Validate at the database level

· Validate on multitier systems

· Validate using secure socket layers

· Validate database administrators

Franz Devantier,

Need a database health check, or a security audit?

devantierf@gmail.com

Classifieds

Year of the Stem Cell

Devantier Hosting, from $2.75

Host then Profit GVO

Global Domains International

Traffic Exchange Profits

Easy Downlines

Fast Cash and Traffic

Simple Safelists

Traffic Wave - Autoresponder

Traffic Hoopla

Business Related Social Media

Friday, March 29, 2013

Security, Policies and Tips - Part 10

Dear Readers,

Security, Policies and Tips - Part 10

Use Application Context and Fine-Grained Access Control

Application Context

Application context helps you apply fine-grained access control because you can link function-based security policies with applications.

Oracle has a predefined application context namespace, USERENV, which provides access to many predefined attributes. These attributes are automatically captured about a specific user session. The username, proxy user, ip address, machine name, and many more attributes are available.

In addition an application also has its own application-specific application context, such as department, country, salary scale, and etc. Such attributes are available to the functions implementing security functions, but not to application users, or users of an ad-hoc query tool, like SQL*Plus.

To define an application context:

· Create a PL/SQL package with functions that validate and set the context for the application. You can user trigger event to set the initial context for users when they log into the application.

· Use the CREATE CONTEXT keywords to specify a unique context name, and associate it with the PL/SQL package that you created

· You can do perform either of these options

o Reference the application context from the policy function that implements your fine-grained access control

o Create an event trigger on logon to set the initial context for a user. This may involve looking up account details, and setting the values retrieved as context values

· Reference the application context, to limit users to seeing their own records only. You can use fine grained access control to dynamically modify the users query from for example. “Select * from orders;” to “Select * from orders where custno= SYS_CONTEXT(‘order_entry’,’cust_num’);”

Fine-grained access control

This enables you to use functions to implement security policies and to associate those security policies with tables, views, or synonyms.

The database server automatically enforces your security policies, no matter how the data is accessed, including, for example, through an application by ad hoc queries.

Fine-grained access control enables you to make use of the following capabilities:

· Limit access to data at the row level, by using different policies for SELECT, INSERT, UPDATE, and DELETE

· Use the security policies where you need them, for example on account information

· You can invoke a policy on a table, only if a specific column is referenced

· You can restrict access to data, using a combination of row-level and column-level controls, by applying a VPD policy to a view

· Static policies are always applied, whereas dynamic policies can change during execution

· You can use multiple policies on one table

· You can define policies for a specific application, by packaging them together in policy groups. Each policy group is a set of policies that belongs to one specific application

· Control the use of “INDEX” in row level security policies

· Specify a special application context, called a driving context, to indicate the policy group in effect, when tables views or synonyms are accessed. The fine-grained access control engine looks up the driving context to determine which policy group is in effect and enforces all the associated policies that belong to that specific group.

The PL/SQL package DBMS_RLS is for administering your security policies. With DBMS_RLS you can add, drop, enable, disable, and refresh all of the policies and policy groups that you create.

Franz Devantier,

Need a database health check, or a security audit?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report: The report will give you all the information you need to start making a nice long-term residual income stream for yourself.

Thursday, March 28, 2013

Security, Policies and Tips - Part 9

Dear Readers,

Security, Policies and Tips - Part 9

Use Secure Application Roles to Verify IP Address

For example in the previous article, you had validated that the user came from the middle tier “FREDSERVER”, you could go on to validate that the IP address was what you thought it should be.

Typically IP addresses are not a reliable way to validate a user, because IP addresses can be falsified. So the primary check should never be on an IP address, but certainly secondary checks would be advantageous.

So in this case you want to make sure that a certain user session was created by proxy for a middle-tier user, connecting from a specific IP address. The middle tier first authenticates itself to the database before creating a lightweight session. The database first ensures that the middle tier has the privilege to create a session on behalf of the user.

Now the secure application role (fred_admin) is set depending on the outcome of the associated package fred.padmin. Firstly the package will check that it is coming from the correct proxy server. If this check passes, then it will check in addition if it is from the expected IP address. The SYS_CONTEXT(‘userenv’,’ip_address’) can be queried to determine this. If the proxy middle-tier server, and the IP address are correct, then the package will issue the SET ROLE command, otherwise not.

As you can see, this will offer a secondary or additional layer of security for the application. This setup will make it that much more difficult for malicious users to access the application and perform inappropriate operations in the application.

Franz Devantier,

Need a database health check, or a security audit?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report: The report will give you all the information you need to start making a nice long-term residual income stream for yourself.

Wednesday, March 27, 2013

Security, Policies and Tips - Part 8

Dear Readers,

Security, Policies and Tips - Part 8

Use Proxy Authentication and a Secure Application Role

To enable a role in three-tier systems, the user must access the database through a middle-tier application that requires proxy authentication and a secure application role.

Proxy authentication can distinguish between a middle creating a session on behalf of a user, and the user connecting directly. Both the proxy user or the middle tier user and the real user information are captured in the user session.

Secure application roles are implemented by a package, which performs the desired validation before allowing a user to assume the privileges that are granted to the role. When an application uses proxy authentication, the secure application role package can validate that the user session was created by proxy before setting the role. If the user is connecting directly then the role will not be set.

Let’s say for example that you want to restrict the use of the Fred_admin role, to users that are accessing the database by proxy through the middle tier FREDSERVER.

CREATE ROLE fred_admin IDENTIFIED USING fred.padmin;

The package fred.padmin performs the desired validation, permitting the role (fred_admin) to be set only if the user is connected by proxy.

Inside the fred.padmin package, you can make use of the SYS_CONTEXT(‘userenv’,’proxy_userid’), and/or SYS_CONTEXT(‘userenv’,’proxy_user’). You will retrieve the name of the proxy server “FREDSERVER”. If the proxy server “system context” has the value of “FREDSERVER”, then you perform the SET ROLE command for the fred_admin role, otherwise you don’t.

The above scenario effectively ensures that if you do not connect through the designated middle tier server, then you will not be granted any of the privileges in the fred_admin role, and will effectively have no access to the data through the application. You would have effectively prevented a potential security breach.

Franz Devantier,

Need a database health check, or a security audit?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report: The report will give you all the information you need to start making a nice long-term residual income stream for yourself.

Tuesday, March 26, 2013

Security, Policies and Tips - Part 7

Dear Readers,

Security, Policies and Tips - Part 7

Use Role Passwords Unknown to the User

You should grant privileges through roles that require a password. The role password should not be known to the user. The privileges granted to this role, should be those privileges that are needed within an application.

You enable these roles within the application by issuing a SET ROLE statement. The password for this role is known only by the creator of the role. The password must be embedded in the application, or retrievable from a database table, that is accessed by the stored procedure. Preferably the password should be stored in encrypted form in the database table.

When the password for the role is hidden in such a way, it tends to discourage the average user from trying to use the privileges without using the application. This setup will improve your overall security, but it is not foolproof, because it is essentially Security by obscurity.

Security by obscurity is not a good security practice. This type of security will protect against lazy users who merely want to bypass the application. Some of these users will potentially have access to the application code, and could find the passwords for the roles, if they wanted to.

Security by obscurity will not protect against malicious users. It is possible to decompile compiled application code, into source code. Such users could then search through the source code to find the embedded passwords, or the algorithms, that were used to encrypt the stored passwords. Decompiled code is not as easily readable as conventional source code, but it can be used to rebuild applications if required.

Of course even with the algorithm for the password, stored in a database table, the user will still not have execute permission on the procedure which access the table where the encrypted passwords are stored. Also they would not have any permissions on the table where the password is stored.

However a malicious user may eventually be able to obtain the execute privileges on the stored procedure and retrieve the role password. In such a case you security would have been compromised, but it will take some work to compromise. However embedding the passwords, and protecting them from the average user, will go a long way to good security.

Franz Devantier,

Need a database health check, or a security audit?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report: The report will give you all the information you need to start making a nice long-term residual income stream for yourself.

Monday, March 25, 2013

Security, Policies and Tips - Part 6

Dear Readers,

Security, Policies and Tips - Part 6

Encapsulate Privileges in Stored Procedures

Restrict or prevent ad hoc query tools from using or exercising application privileges. You can do this by encapsulating these privileges into stored procedures.

You can grant users execute privileges on these procedures. This way you avoid having to grant the privileges directly to the users. In this way the privileges can’t be used outside of the appropriate procedure.

This will force users to exercise privileges only in the context of well-formed business applications, even if they are using an ad-hoc query tool. You could for example only allow a user to update a table by executing a stored procedure, instead of updating the table directly. In this way the update is controlled, and inappropriate updates are not allowed by the procedure. In this way you will effectively prevent the user from selecting and updating certain tables outside of the application.

Franz Devantier,

Need a database health check, or a security audit?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report: The report will give you all the information you need to start making a nice long-term residual income stream for yourself.

Sunday, March 24, 2013

Security, Policies and Tips - Part 5

Dear Readers,

Security, Policies and Tips - Part 5

Enable and Disable Roles Promptly

Only enable a role when the application starts, and then disable the role again as soon as the application terminates. Here are some pointers to manage this

· Create distinct application roles for each application

· Create a role for a specific application, that contains all of the privileges necessary to run the application successfully

· To provide tighter security for different users of the application, you can create roles, that only have a subset of the privileges of the main role

· Protect each database role by a password. To prevent unauthorized use of the roles

Create a role designed for ad hoc query building. This role will only have select privileges on the application objects, and not have update, insert, or delete privileges. You may protect this role with a password as well.

Role granting guidelines:

· Use the SET ROLE statement at application startup to enable one of the database roles associated with that application. If the role is authorized by a password, then the SET ROLE statement within the application must include the password, which should be encrypted by the application. If a role is authorized by the operating system, then the system administrator must set up accounts in advance for application users with appropriate operating system privileges

· When the application terminates, the database roles that were enabled whend the application started up, must be disabled

· Grant application users, database roles only when needed

Database roles that are used in applications, can be enabled by users outside of the application as well. Protecting the role by a password, will help to control this issue. You can also use a virtual private database, to control the effect of roles being granted outside of the application. In a three-tier system, you can prevent users from acquiring the role outside of the application, by using secure application roles.

You can use the PRODUCT_USER_PROFILE table to control what the user can do:

· You can specify which roles to enable when a user starts a SQL*Plus session. This functionality will work in a similar way to a SET ROLE statement in an application. For example the Oracle Call Interface (OCI)

· You can disable the use of the SET ROLE statement for SQL*Plus users. This will restrict SQL*Plus users to those privileges that they already have, and to the roles enabled for them when QSL*Plus started up.

· You can also enable other reporting tools, and ad hoc query tools that the user may use, to restrict the roles and commands that each user can use while running the specific product

Franz Devantier,

Need a database health check, or a security audit?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report: The report will give you all the information you need to start making a nice long-term residual income stream for yourself.

Saturday, March 23, 2013

Security, Policies and Tips - Part 4

Dear Readers,

Security, Policies and Tips - Part 4

Recommended Application Design Practices to Reduce Security Risks

To avoid or minimize potential security issues, use the following recommended practices for database roles and privileges.

· Enable and Disable Roles promptly, when they are needed, and when they are no longer needed

· Encapsulate Privileges in Stored Procedures

· Make use of Role passwords, that are not know to the application user

· Use Proxy Authentication, and Secure Application Roles

· Use Secure Application Roles to Verify IP addresses, or IP address ranges

· Use Application Context, and Fine-Grained Access Control

Franz Devantier,

Need a database health check, or a security audit?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report: The report will give you all the information you need to start making a nice long-term residual income stream for yourself.

Friday, March 22, 2013

Security, Policies and Tips - Part 3

Dear Readers,

Security, Policies and Tips - Part 3

Reference Terms:

Term	Description
Application Context	Application context is useful for: · Enforcing fine-grained access control · Preserving user identity across multitier environments · Serving as a secure data cache for attributes needed by an application There are three different types of application context: · Secure session-based application contexts, where data is stored in the database user session (UGA). In a namespace specified by “CREATE CONTEXT” · Client session-based application context, using only the CLIENT CONTEXT namespace. No privilege or package security is done · Nonsession-based application contexts. Data is stored in the SGA
Data Encryption	You can also encrypt your data to reduce security risks. Data encryption is not an infallible solution, but it does have its place. Compressing data is also a form of data encryption.
Fine-Grained Access Control	Fine-grained access control is based on dynamically modified statements. · Create a function to add a predicate to a DML statement · User enters a statement, and the database server calls the function that you used to implement the security policy · The defined predicate is added to the statement, which typically includes sys-context values · Oracle runs the dynamically modified statement
Fine-Grained Auditing	Fine-Grained Auditing(FGA), enables you to monitor data access based on the content. A built-in mechanism in the database prevents users from bypassing the audit. Oracle database triggers can monitor DML actions, although SELECT statements are costly to monitor. A trigger will simply insert a record into an audit trail. FGA provides an extensible interface for creating policies to audit SELECT’s and other DML statements on tables and views. Typically you would use the DBMS_FGA.ADD_POLICY package.
Oracle Label Security	· Enables a comprehensive set of access authorizations, contained in the row itself · Provides for flexible policy enforcements, to one type of DML, to label creation, or enabling default labels · Policies can protect individual application tables · Special labelling functions can be added to a policy · Multiple policies protecting different areas, can be created and exist and function together · A single policy can be applied to multiple application tables
Proxy Authentication	Oracle database server supports the following ways of preserving user identity through the middle tier of an application. · Proxy Authentication: in OCI or thick JDBX for database or enterprise users. Enterprise users are managed in Oracle Internet Directory · Client Identifiers: CLIENT_IDENTIFIER attribute in USERENV application context namespace for application users. These users are known to the application, but not the database.
End-User Identity Propagation	Oracle STS can be configured to support scenarios that include both identity propagation and token translation between web services deployed in the same security domain. SOAP messages are used to transfer the security tokens and communicating between web services clients and providers. STS => is a WS-Trust- based token service that allows for a policy-driven trust brokering and secure identity propagation and token exchange between web services. SOAP => Simple Object Access Protocol is a lightweight, XML-based protocol for exchanging information in a decentralized, distributed environment.
Secure Application Roles	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.

Franz Devantier,

Need a database health check, or a security audit?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report: The report will give you all the information you need to start making a nice long-term residual income stream for yourself.