Today, most Web application programs require at least some basic security policy. For example, a website that provides password-protected content, a website that only has a administrator, a website, a website, a magazine, an e-commerce website, an enterprise intranet, etc.
Today, most Web application programs require at least some basic security policy. For example, a website that provides password-protected content, a website that only has a administrator, a website, a website, a personal magazine, an e-commerce website, an enterprise intranet, and so on.
The most common design method for building these types of Web application programs is to integrate security policies into the business logic of Web application programs, that is, the application determines whether a user has the right to visit a data in the database. In this case, the role of the database is only to store data and to supply data as required. In other words, if the Web uses the program Command database to provide specific information, the database will directly execute the command without checking the user's permissions.
In this article, you will learn how to use the security features built in Oracle to fulfill application security rules at the database level to improve the overall security of the application. As an incidental benefit, implementing data access security directly in the database not only improves the security of the utilization process, but also reduces complexity.
Database security requirements
What if I control data access from a Web application? In most cases, there is no title; this is a good solution, especially when the involved data is not critical or confidential. This method is used in many books and online resources. In fact, there is a popular PHP/MySQL book against each exploitation program to create more than one database user account, this is because "additional users or complex permissions will degrade MySQL performance by checking more information before a certain hold 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 governance system (CMS) is created ). The application database stores the content announced on the website. Most of the data is open-minded, and anonymous Web users are allowed to read the data, but only the data is allowed to be edited and changed. Use a single database account to visit and modify records in the database, and use a password to protect the access permissions of pages that only administrators can visit with PHP code for security control.
If the Web application's public end suffers an SQL injection attack, such as a public search form (that is, a form with insufficient coding, the attacker may be able to execute any SQL statement on the database objects that can be visited by the public account. Of course, in this case, the execution of the SELECT statement will not cause any big title, because the data is public. However, because the public and governance permissions apply the same database account, attackers can execute UPDATE and DELETE statements, or even DELETE tables from the database.
How can this problem be prevented? The simplest way is to restrict the permission of the public database account to correct data. Let's take a look at how Oracle solves this title.
Overview of Oracle Security
Oracle Database provides many methods for Web developers to control data access, from governance to specific database objects (such as tables, views, and processes) visit to control the 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 only focus on the basic aspects of Oracle data access security:
· Verification and user account
· Permission
· Role
Authentication and user account. Like other databases, each user (database account) who requests to visit Oracle must pass verification. Verification can be performed by databases, control 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. Oracle Role is a famous set of permissions. Although user account permissions can be granted directly, application roles can greatly simplify user governance, 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 to modify permissions-you only need to modify the roles associated with the role, without having to modify each user account.
Oracle has three predefined roles to simplify the initial creation of new users:
· CONNECT role-this role allows users to CONNECT to the database and perform basic control, such as creating their own tables. By default, this role cannot access tables of other users.
· The RESOURCE role-the RESOURCE role is similar to the CONNECT role, but the user has more system permissions, such as trigger creation or stored procedure.
· DBA role-the answer user has all system permissions.
Application authorization and permissions
In this section, we will discuss how to apply Oracle authorization and permissions to improve the security of the simple CMS example discussed at the beginning of this article. Assuming that the supplied content should be stored in the WEB_CONTENT table using the user's content.
First, create the table. Start Oracle database Special Edition and log on as a system administrator. If you have not released an example HR user, release it. Follow the instructions in the special edition installation guide. Note that by default, the HR user is assigned the RESOURCE role. Here, assign the user the DBA role so that the account can be applied to govern the database of the CMS exploitation program. Of course, the HR user account will not be used for online access, and it is only used to manage the database.
Now, you can create a new table by applying the object browser or by performing 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 was created using the HR user account, the table is returned to the HR account and is in HR mode. Before you explicitly grant other users the permission to visit the table, other users cannot visit this table. If you do not believe it, you can create a new user and use the user to visit the WEB_CONTENT table.
Now, we create two new users, CMS_USER and CMS_EDITOR. Ultimately, CMS_USER will be granted the read-only permission on the WEB_CONTENT table, and the user will be used as the database account that the anonymous Web user provides the content. The CMS_EDITOR account will have more permissions on the table and will be used as the account compiled by CMS (the account needs to change and protect the data in the table ).
You can use the XE Gui or execute 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 line:
Insert into hr. web_content (page_id, page_content) VALUES (1, 'Hello World ');
Will generate an error message
ORA-01031: insufficient privileges
To promise 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 a more effective method. If the Oracle database of the application is not XE, you can perform the following control:
Create 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 apply the CMS_USER account to all database connections begged by anonymous Web users, apply the CMS_EDITOR account to the connection caused by the password-protected Governance page. 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
In this article, we just briefly introduce some of the most basic features of Oracle data access security. In addition, Oracle has many other features that increase the security of your Web application to a new level-including virtual private database (VPD) and tag security.
