Most web applications today require at least some basic security strategy. For example, sites that offer password-protected content, sites with only an administrator backend, blogs and personal magazines, e-commerce sites, corporate intranets, and so on.
The most common design approach to building these types of Web applications is to integrate security policy into the Web application's business logic, where the application determines whether a user has permission to access certain data in the database. In this scenario, the database's role is simply to store data and serve it upon request. In other words, if a web application commands the database to provide specific information, the database executes the command directly without checking the user's permissions.
In this article, you will learn how to leverage Oracle's built-in security features to enforce application security rules at the database level to improve the overall security of your application. As a side benefit, securing data access directly in the database not only improves application security but also helps reduce complexity.
What aboutthe need for database-side security
to control data access from web applications? In most cases there is no problem; this is a good solution, especially if the data involved is not mission critical or top secret. This method is used in many books and online resources. In fact, one popular PHP/MySQL book explicitly discourages the creation of more than one database user account per application because "additional users or complex permissions may require more checks before proceeding." information and slow down the execution speed of MySQL." This is true; however, there are a few things you might want to consider before giving up on the idea of integrating security into your database logic. Let's look at the following example.
Let's say you create a content management system (CMS). A database is used to store the content published on the website. Most of the data is public, allowing anonymous web users to read it; only editors are allowed to change the data. Use a single database account to access and modify records in the database, and control security with PHP code by password-protecting access to administrator-only pages.
If the public side of a web application suffers from an attack such as SQL injection on a public search form (that is, a form that is not tightly coded enough), the intruder may be able to execute arbitrary SQL statements on database objects that the public account has access to. Of course, in this case, executing the SELECT statement doesn't pose a big problem because the data is public. But because public and administrative rights use the same database account, the intruder can also execute UPDATE and DELETE statements, or even delete tables from the database.
How can we prevent this from happening? The simplest method is to completely restrict the public database account's permissions to modify data. Let's take a look at how Oracle solves this problem.
Basic Overview of Oracle Security
Oracle Database provides Web developers with many ways to control access to data, from managing access to specific database objects such as tables, views, and procedures to controlling access to data in individual rows or columns. Obviously, a discussion of every security feature or option available with Oracle is beyond the scope of this article. Here, we will not go into too much detail, but only the most basic aspects of Oracle data access security:
· Authentication and user accounts · Permissions · Role
authentication and user accounts. As with other databases, each user (database account) requesting access to Oracle must be authenticated. Validation can be done by a database, operating system, or network service. In addition to basic authentication (password authentication), Oracle also supports strong authentication mechanisms such as Kerberos, CyberSafe, RADIUS, and so on.
Role. An Oracle role is a named set of permissions. Although you can grant user account permissions directly, using roles can greatly simplify user management, especially when you need to manage a large number of users. It's very efficient to create small, manageable roles and then grant users one or more roles based on their security level. Not to mention how easy it is to modify permissions—just modify the role to which the role is associated, rather than modifying each user account.
To simplify the initial work of creating new users, Oracle comes with 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 other users' tables.
·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 the user to have all system privileges.
Authorizations and Permissions in Use
In this section, we 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. It is assumed that the content provided to the application's users is stored in the WEB_CONTENT table.
First, create the table. Start Oracle Database Special Edition and log in as the system administrator. Release the sample HR user if it has not been released yet. Follow the instructions in the Getting Started Guide included with the Special Edition installation. Note that by default, HR users are assigned the RESOURCE role. Here, give the user the DBA role so that the account can be used to manage the database aspects of the CMS application. Of course, the HR user account will not be used for online access, only for database administration.
You can now create a new table using the Object Browser or by executing the SQL Commands window. Here is the code to create 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 owned by the HR account and is in the HR schema, and other users cannot access the table until they are explicitly granted permission to access the table. If you don't believe it, you can create a new user and try using this user to access the WEB_CONTENT table.
Now, create two new users, CMS_USER and CMS_EDITOR. Finally, CMS_USER will be granted read-only permissions on the WEB_CONTENT table, and this user will be used as the database account that serves content as an anonymous Web user. The CMS_EDITOR account will have more permissions on the table and will be used as the CMS editor account (the account needed to change and maintain the data in the table).
New users can be created using XE's graphical interface or by executing the following command:
CREATE USER cms_user IDENTIFIED BY cms_user;
CREATE USER cms_editor IDENTIFIED BY cms_editor;
(For simplicity, the password here corresponds to the username.)
In order for both accounts to log into the database, we need to give them the CONNECT role. To do this, select the CONNECT checkbox under User Information in the Administration/Database Users section of the XE graphical interface, or execute the following command:
GRANT CONNECT to cms_user;
GRANT CONNECT to cms_editor;
Now, if you try to log in as user CMS_USER or CMS_EDITOR and try to read data from WEB_CONTENT table (select * from hr.web_content;), you will encounter the following error:
ORA-00942: table or view does not
exist To access the data or just see the table, you need to grant the CMS_USER and CMS_EDITOR accounts read-only permissions on the WEB_CONTENT table:
GRANT SELECT on hr.web_content to cms_user;
GRANT SELECT on hr.web_content to cms_editor;
The above code enables these two accounts to perform SELECT statements on the WEB_CONTENT table. If you try to execute other statements, you will encounter an error. For example, inserting a row:
INSERT INTO hr.web_content (page_id,page_content) VALUES (1,'hello world');
will produce the error message
ORA-01031: insufficient privileges.
To allow CMS_EDITOR to change the contents of this table, the following permissions need to be granted:
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.
See how easy it is! It can be seen that managing permissions through roles is a more effective method. If the Oracle database used is not XE, you can 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;
Give 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 affect all user accounts with that role. If permissions are granted directly to users, each user account must be updated individually.
After completing the above steps, you can configure your PHP application to use the CMS_USER account for all database connections requested by anonymous Web users and the CMS_EDITOR account for connections initiated by password-protected administrative pages. Now, even if a public web form is compromised, the impact on the database will be minimal because the CMS_USER account only has read-only permissions.
Conclusion
In this article, we have only briefly introduced some of the most basic features of Oracle data access security. In addition, Oracle has many other features that take the security of your Web applications to the next level—including Virtual Private Database (VPD) and tag security.