Apply the built-in security features of Oracle to PHP

Welcome to the Oracle community forum, and interact with 2 million technical staff to enter the majority of today's Web applications require at least some basic security policies. For example, websites that provide password-protected content, websites that only have the Administrator's backend, blogs and personal magazines, e-commerce websites, and enterprise intranets. Construct these types of Web

Welcome to the Oracle community forum and interact with 2 million technical staff> most Web applications today require at least some basic security policies. For example, websites that provide password-protected content, websites that only have the Administrator's backend, blogs and personal magazines, e-commerce websites, and enterprise intranets. Construct these types of Web

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Most Web applications today require at least some basic security policy. For example, websites that provide password-protected content, websites that only have the Administrator's backend, blogs and personal magazines, e-commerce websites, and enterprise intranets.

The most common design method for building these types of Web applications is to integrate security policies into the business logic of Web applications, that is, the application determines whether a user has the right to access a data in the database. In this case, the database role only stores data and provides data as requested. In other words, if the Web Application Command database provides specific information, the database will directly execute this command without checking the user's permissions.

In this article, you will learn how to use the built-in security features of Oracle to execute application security rules at the database level to improve the overall security of applications. As an ancillary benefit, implementing data access security directly in the database not only improves application security, but also reduces complexity.

Database Security Requirements

How does one control data access from a Web application? In most cases, there is no problem; this is a good solution, especially when the involved data is non-task critical or top secret. This method is used in many books and online resources. In fact, a popular PHP/MySQL book specifically opposes creating more than one database user account for each application, this is because "additional users or complex permissions will reduce the MySQL execution speed by checking more information before an operation continues ". This is true; however, you may have to consider a few things before giving up the idea of integrating security into the database logic. Let's take a look at the following example.

Assume that a Content Management System (CMS) is created ). The database is used to store the content published on the website. Most of the data is public, allowing anonymous Web users to read, but only allows editing and changing data. Use a single database account to access and modify records in the database, and use a password to protect access permissions for pages accessible only by administrators. Use PHP code to control security.

If the public end of a Web application suffers an SQL injection attack on a public search form (that is, a form with insufficient coding, the attacker may be able to execute arbitrary SQL statements on database objects accessible to the public account. Of course, in this case, executing the SELECT statement will not cause any major problems, because the data is public. However, since the public and Management permissions use the same database account, intruders can execute UPDATE and DELETE statements, or even DELETE tables from the database.

How can this problem be prevented? The simplest way is to completely restrict the permission of the public database account to modify data. Let's take a look at how Oracle solves this problem.

Oracle Security Overview

Oracle Database provides many methods for Web developers to control data access, from managing specific database objects (such as tables, views, and processes) to control the access to data of individual rows or columns. Obviously, the discussion of each security feature or available option in Oracle is beyond the scope of this article. Here, we will not cover too many details, but will only introduce the most basic aspects of Oracle Data Access Security:

• Verification and User Account
• Permission
• Role

Authentication and user account. Like other databases, each user (database account) requesting access to Oracle must pass verification. Verification can be performed by databases, operating systems, or network services. In addition to basic authentication (password verification), Oracle also supports strong authentication mechanisms, such as Kerberos, CyberSafe, RADIUS, and so on.

Role. An Oracle role is a famous set of permissions. Although user account permissions can be granted directly, using roles can greatly simplify user management, especially when a large number of users need to be managed. Create a small role that is easy to manage, and then assign one or more roles to the user based on the user's security level. This is very efficient. Not to mention how easy it is to modify permissions-you only need to modify the roles associated with the role, without modifying each user account.

To simplify the initial creation of new users, Oracle has three predefined roles:

• CONNECT role-this role allows users to CONNECT to the database and perform basic operations, such as creating their own tables. By default, this role cannot access tables of other users.
• RESOURCE role-the RESOURCE role is similar to the CONNECT role, but it allows users to have more system permissions, such as creating triggers or stored procedures.
• DBA role-allows users to have all system permissions.

Authorization and permissions in use

In this section, we will discuss how to use Oracle's authorization and permissions to improve the security of the Simple CMS example discussed at the beginning of this article. Assume that the content provided to application users is stored in the WEB_CONTENT table.

First, create the table. Start Oracle Database special edition and Log On As a system administrator. If you have not released the example HR user, release it. Follow the instructions in the Getting Started Guide for special edition installation. Note that by default, the HR user is assigned the RESOURCE role. Here, assign the user DBA role so that the account can be used to manage the database of the CMS application. Of course, the HR user account is not used for online access, and it is only used to manage databases.

Now, you can create a new table using the Object Browser or by executing the SQL Commands window. The following code creates the table:

CREATE TABLE WEB_CONTENT (page_id NUMBER PRIMARY KEY,page_content VARCHAR2(255));

Because the table is created using the HR user account, the table belongs to the HR account and is in HR mode. Before explicitly granting other users the permission to access the table, other users cannot access the table. If you do not believe this, create a new user and use this user to access the WEB_CONTENT table.

Now, create two new users, CMS_USER and CMS_EDITOR. Finally, CMS_USER will be granted the read-only permission on the WEB_CONTENT table, and the user will be used as the database account for the content provided by anonymous Web users. The CMS_EDITOR account has more permissions on the table and will be used as the account edited by CMS (the account needs to change and maintain the data in the table ).

You can use the xe gui or run the following command to create a new user:

CREATE USER cms_user IDENTIFIED BY cms_user;CREATE USER cms_editor IDENTIFIED BY cms_editor;

(For simplified purposes, the password here corresponds to the user name .)

To allow both accounts to log on to the database, we need to assign them the CONNECT role. To this end, select the CONNECT check box under user information in the Administration/Database Users section of the XE graphic interface, or execute the following command:

GRANT CONNECT to cms_user;GRANT CONNECT to cms_editor;

Now, if you try to Log On As A CMS_USER or CMS_EDITOR user and try to read data from the WEB_CONTENT table (select * from hr. web_content;), you will encounter the following error:

ORA-00942:table or view does not exist

To access data or only View tables, you must grant the CMS_USER and CMS_EDITOR accounts the read-only permission on the WEB_CONTENT table:

GRANT SELECT on hr.web_content to cms_user;GRANT SELECT on hr.web_content to cms_editor;

The preceding Code enables these two accounts to execute the SELECT statement on the WEB_CONTENT table. If you try to execute other statements, you will encounter an error. For example, insert a row:

INSERT INTO hr.web_content (page_id,page_content) VALUES (1,'hello world');

Will generate an error message

ORA-01031:insufficient privileges

To allow CMS_EDITOR to change the table content, you must grant the following permissions:

GRANT INSERT,UPDATE,DELETE on hr.web_content to cms_editor;

From now on, the CMS_EDITOR account can execute INSERT, UPDATE, and DELETE statements on the WEB_CONTENT table.

How simple is this! It can be seen that role-based permission management is more effective. If the Oracle database is not XE, perform the following operations:

Create a role:

CREATE ROLE reader;CREATE ROLE writer;

Grant role permissions:

GRANT SELECT ON web_content TO reader;GRANT INSERT,UPDATE,DELETE ON web_content TO writer;

Assign a user role:

GRANT reader TO cms_user;GRANT reader TO cms_editor; (they need to read too)GRANT writer TO cms_editor;

Note that if you change the definition of the READER role, these changes will affect all user accounts with this role. If you grant permissions to users directly, you must update each user account one by one.

After completing the preceding steps, you can configure the PHP application to use the CMS_USER account for all database connections requested by anonymous Web users, use the CMS_EDITOR account for connections caused by password-protected management pages. Now, even if the public Web form is under attack, this attack will have little impact on the database, because the CMS_USER account only has read-only permissions.

Conclusion