http://data base secuirty

Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
1
THE NEED FOR SYSTEMS SECURITY
The opening of mission-critical systems to partners and customers over the Internet poses new
challenges to traditional notions of enterprise security. Data access must now be controlled at a very
fine level of granularity, often to the level of individual customers or users. Organizations providing
"hosting" environments seek to deploy common applications which nonetheless can incorporate
customer-specific preferences, and operate on customer-specific data. Oracle8i addresses these
requirements by providing highly granular, server-enforced access control and flexible privilege models.
Users can be strongly authenticated, even remotely, and data is protected in transit by network
encryption. Oracle8i enforces the same strong security whether users access data directly, or through
middle tiers, such as application servers or transaction processing (TP) monitors.
Another challenge of system security is ease of management. Organizations spend significant time and
resources managing multiple user accounts and privileges. Additionally, they often must implement
security in multiple applications accessing the same data ¾ which is duplicative and often leads to
security vulnerabilities ¾ instead of building security once, in the data server. Security is often
complex and expensive to implement. Oracle8i addresses these needs by offering integrated security
and directory services, which enables Public Key Infrastructure (PKI)-based single sign-on. Single
Station Administration allows organizations to manage users and their privileges centrally, with greater
ease and lower cost. Flexible, granular security can be built once in the data server, instead of in
multiple applications, and business logic may be divorced from actual privileges and data, which means
that applications can be developed once, then reused and redeployed at significant cost savings.
Within the enterprise, information is stored on physically separate computers in different locations.
Therefore, it is essential that users be able to access all information easily and consistently.
Consequently, a database server must provide the technology to hide the complexity of data access from
users, allowing them to access distributed information as if it were all stored on the same computer.
Oracle8i addresses this requirement by providing a transparent interface to all data in the system,
improving access to information and simplifying application development.
Oracle8i addresses all these security and functionality needs by providing complete and robust facilities
for managing data and implementing a strong, yet flexible, security policy. This paper describes these
security facilities, and how an organization can use them to enforce an overall security policy
throughout Oracle8i.
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
2
SECURITY CONCEPTS
Basic computer security concepts require that an information system be able to identify and control
critical aspects such as:
· Who the authorized users are (identification and authentication).
· What users should have access to (object access controls).
· What types of operations users can perform on those objects (also part of object access control).
· What types of activities have occurred (e.g., the ability to maintain accountability via auditing).
· Extended security concepts further address issues such as data and system integrity, reliability and
availability, further conditional access controls (such as for special business rules), and assurance
that all the above are operating properly and consistently. The following sections discuss these
security concepts as supported by Oracle8i.
Strong User Authentication For Accountability
The basis for system security is strong user identification and authorization; if you cannot establish,
with certainty, who a user is, then it is impossible to hold users accountable for their actions, and to
ensure that users only have access to the data they need to do their jobs, but no more. Oracle8i supports
a number of choices for user authentication: Oracle-based (by password, or by industry-standard X.509
certificates), host-based (by the underlying operating system), or third-party based (network
authentication services, smart cards and biometric devices).
Oracle Password-Based Authentication
In Oracle password-based authentication, each Oracle8™ user must have a username and password.
To connect to the database, a properly-authenticated operating system user must supply his database
username and password. However, password-based schemes, to be secure, must ensure that passwords
can be changed regularly, are of sufficient complexity, and are not easily guessed.
Oracle8 provides built-in, robust password management facilities to enable administrators to:
· Enforce minimal password length.
· Ensure password complexity (i.e., that passwords contain symbols or numbers as well as
alphabetic characters).
· Disallow passwords that are easily guessed words, such as a user’s last name or
company name.
Administrators can prevent password-guessing attempts by locking accounts automatically after a
number of incorrect password entries; an administrator can also lock an account “on the fly” if he
detects a security breach. Passwords can be forced to expire over any period (every ninety days, for
example) to ensure that users change their passwords regularly. Administrators can also prevent
passwords from being reused, either permanently, or for a specified period of time. Password
preferences may be assigned to an entire enterprise, groups of users, or individual users by
means of user profiles, providing complete flexibility for an organization to implement desired security
preferences.
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
3
In distributed systems, a password passing from a client to server may pose a security risk. If the
password is passed in clear text (unencrypted), any eavesdropper snooping for data can also read the
password. The Oracle password protocol provides security for client-server and server-server password
communication by encrypting passwords passed over a network. The Oracle password protocol uses a
session key valid for a single database connection attempt to encrypt the user's password. Each
connection attempt uses a separate key for encryption, making the encryption more difficult to decipher.
After the key-encrypted password is passed to the server, the server decrypts it, then re-encrypts it using
a Data Encryption Standard (DES) based one-way encryption algorithm and compares it with the
password stored in the database. If they match, the user successfully connects to the database. The
Oracle password protocol is used to encrypt all passwords upon an attempted connection — whether
local connection, client to server, or server to server. Oracle8i also supports secure remote
administration protected by password, even when the database is not available. Users connecting as
SYSDBA and SYSOPER connect using user-specific passwords, providing individual accountability
for these privileged users.
Host-Based Authentication
Oracle8i's identification and authentication facility also allows you to specify that users should be
authenticated by operating system mechanisms, consolidating username and password information and
allowing users to enter an application without having to specify a username and password.
Third Party-Based Authentication
Oracle Advanced Security, an option to Oracle8i, supports multiple third-party authentication
technologies, such as Kerberos, DCE, smart cards and biometric authentication (Identix), as well as
integration with Bull’s ISM and ICL’s Access Manager. These hardware and software technologies
verify a user’s identity in a stronger way than passwords. For example, SecurID cards provide twofactor
authentication — something you have (the card) and something you know (a personal
identification number (PIN)). Many of these network authentication services also provide single signon
for users. Users authenticate themselves once to a central service (e.g., Kerberos), and may then
connect to multiple applications or databases without providing additional credentials. In addition, any
device compliant with RADIUS (Remote Authentication Dial-In User Service) is capable of integrating
with Oracle8i to provide strong user authentication. Oracle8i’s integration with third-party security
providers offers customers a choice among a number of strong authentication and single sign-on
services.
Public Key Infrastructure-Based Authentication
Oracle8 introduced single sign-on for Oracle users through X.509 (version 1) digital certificates and a
proprietary authentication protocol. The advantage of X.509 certificates is that they may be used to
uniquely identify an individual within an organization and thus enable strong authentication. Also,
instead of remembering multiple passwords, a user need only remember the password that unlocks his
Oracle wallet. The certificate and private key contained in the wallet are used to authenticate the user
to multiple services, including application servers and data servers, which need no longer store and
manage local passwords for users.
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
4
Oracle Advanced Security offers enhanced PKI-based single sign-on through use of interoperable X.509
(version 3) certificates for authentication over Secure Sockets Layer (SSL), the standard for Internet
authentication. In addition to strong user authentication, SSL also provides network data confidentiality
and data integrity for multiple types of connections: LDAP (Lightweight Directory Access Protocol), IIOP
(Internet Intra-ORB Protocol), and Net8™ .
Oracle Wallet Manager provides secure management of PKI (public key infrastructure)-based user
credentials: a user’s private key, his certificate, and a list of trustpoints, the list of root certificates that
the user trusts. Wallets are protected using password-based, strong encryption.
In most cases, a user need never access a wallet once it has been configured, but can easily access his
wallet using Oracle Enterprise Login Assistant, a very simple-to-use login tool that hides the complexity
of a private key and certificate from users. Once users have securely opened their wallets, they can then
connect to multiple databases over SSL, without providing additional passwords. This provides the
benefit of strong authentication as well as single sign-on.
Remote Authentication
Oracle Advanced Security supports remote authentication of users through RADIUS, a standard
lightweight protocol used for user authentication, authorization, and accounting. RADIUS, a proposed
standard of the Internet Engineering Task Force (IETF), is a popular means of enabling remote
authentication of users. For example, a user accessing his corporate network remotely first authenticates
himself to RADIUS; then, after successful authentication, the user is able to access applications within his
corporate network.
Oracle Advanced Security provides an interface which can be used with any third-party authentication
service that supports the RADIUS protocol. The advantage to customers is that multiple authentication
devices (for example, tokens or smart cards) may be used for authentication to the Oracle8i database, as
long as the mechanism or device supports the RADIUS protocol.
Authentication Through a Middle Tier
In applications which use a heavy middle tier, such as a transaction processing monitor, it is important
to be able to preserve the identity of the client connecting to the middle tier. Yet, one advantage of a
middle tier is connection pooling, to allow multiple users to access a data server without each of them
needing a separate connection. In such environments, you need to be able to set up (and break down)
connections very quickly, without the overhead of establishing a separate, authenticated database
session for each connection. For these environments, Oracle8i offers n-tier authentication, “lightweight
session” creation via the Oracle® Call Interface, so that applications can have multiple user sessions
within a single database session. These “lightweight sessions” allow each user to be authenticated by a
database password, without the overhead of a separate database connection, as well as preserving the
identity of the real user through the middle tier. See “Authentication through a Middle Tier,” described
later in this paper.
Mutual Authentication For Secure Distributed Computing
While user authentication is important, it is equally important in distributed systems to ensure
that a number of network principals ¾ including application servers, web servers, and database servers
¾ are who they say they are. For example, database A, attempting to connect to database B, needs
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
5
assurance that database B really is database B, just as database B needs to be sure of database A’s
identity.
Oracle8i enables secure distributed transactions ¾ without compromising user credentials ¾ by means
of mutual authentication of databases, and by strong user authentication without disclosure of
credentials. Mutual database authentication and strong user authentication are accomplished by
industry-standard X.509 (version 3) certificates, without using passwords or any other “hard-coded,”
potentially vulnerable means of authentication. Furthermore, administrators can configure their systems
so that databases are only trusted to connect as certain users. For example, an AP application might
need to retrieve information about employees from the HR database in order to perform expense
reporting processing. Not only could the AP and HR databases mutually authenticate, but the HR
database could grant access to only those users in AP who need to query the employee information in
order to process expense reports.
Privileges That Protect Data
To insure data security, Oracle8i implements "security by default." A user can only perform an
operation on a database object (such as a table or view) if that user has been authorized to perform
that operation. A privilege is an authorization to perform a particular operation; without privileges, a
user cannot access any information in the database. To ensure data security, a user should only be
granted those privileges that he needs to perform his job functions. This is known as the principle of
“least privilege.”
To allow you to grant users only those specific privileges they need to perform their jobs, and not any
more, Oracle8i provides a large number of very granular privileges. These privileges fall into two
categories: system privileges and object privileges.
System Privileges
A system privilege authorizes a user to perform a specific operation. One example of a system privilege
is the CREATE USER privilege, which allows a user to create a database username; another is
SELECT ANY TABLE, which allows a user to query any table in the database. Oracle8i provides over
100 different system privileges, such as permission to connect to the database and permission to change
a table's attributes. A privilege can be granted to a user “with ADMIN option.” This allows the
grantee authority to further grant and revoke privileges from other users.
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
6
Object Privileges
An object privilege authorizes a user to perform a specific operation on a specific object. For example,
you can grant a user the ability to select from the EMP table by granting him the SELECT privilege on
that table. With this privilege, the user can query the EMP table but cannot query any other tables in
the database nor update the EMP table. You can also grant object privileges “with GRANT option.”
This allows the grantee authority to further grant the object privilege to other users. Oracle8i provides a
varying number of object privileges per object type, such as permission to insert into a table and
permission to select from a sequence.
By providing these two types of very granular privileges, Oracle8i allows you to implement separation
of function and to control access to information at a very fine level, ensuring that database users are
only authorized to perform those specific operations required by their job functions. In addition, other
Oracle8i features (like roles and stored procedures, described later in this paper) not only allow you to
control which privileges a user has, but under what conditions he can use those privileges.
Secure Metadata
Oracle8i also provides protection for the data dictionary, ensuring that only those individuals making a
database administrator-privileged connection can alter the data dictionary. In Oracle8i, users granted
ANY privilege (such as ALTER ANY TABLE, DROP ANY VIEW) can exercise these privileges on
any appropriate object in any schema, except the SYS schema, which includes the data dictionary. This
allows developers and others who need privileges on objects in multiple schemas (e.g., ALTER ANY
TABLE) to continue to have that access via ANY privileges, while ensuring that they do not
inadvertently alter the data dictionary. Users making SYS-privileged connections (that is, connecting as
SYSDBA or SYSOPER) are able to modify the data dictionary, as one would expect a DBA to be able
to do.
Views To Customize Access To Information
While privileges allow you to control which operations a user can perform on database objects, views
allow you to further limit the data that a user can access within these objects. A view is a content- or
context-dependent subset of one or more tables (or views). For example, you can define a view that
allows a manager to view only the information in the EMP table that is relevant to employees in his own
department. The view may contain only certain columns from the base table (or tables), such as the
example below, in which only the employee name and salary information are contained in a view.
Content may also be limited to a subset of the rows in the base table, such as a view of the employee
table which contains records for employees assigned to department 20.
Similarly, you can define a view that allows payroll clerks to update payroll information on certain days
of the month only. This flexibility allows you to restrict the data that a user can see or modify to only
that data that he truly needs to access, at only the times that access is appropriate. This allows you to
enforce your unique business rules within the database. View can be created with additional business
considerations in mind. For example, views may be created “with check option,” which enforces that
inserts and updates performed through the view must be accessible by the view query itself. This helps
ensure data consistency from the user’s viewpoint.
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
7
CREATE VIEW
emp_salaries AS
SELECT ename,
salary FROM emp
WHERE mgrname =
user;
SELECT, UPDATE privileges
EMP Table
Not Necessary
Figure 1: A View Controlling Salary Access By Manager
Stored Procedures To Customize Operations On Data
Oracle8i stored procedures offer another powerful and flexible way for you not only to limit those
privileges a user has and the data that he can access, but to define a limited set of related operations that
he can perform within the database. It is often desirable to encapsulate business rules into stored
procedures for several reasons. One of them is that, if security is written in the front-end application,
the user can bypass all the security of the application if the user has direct privileges in the database.
Another reason is that stored procedures help enforce least privilege as well as business integrity, by
ensuring that users have the minimum privileges they need to perform their job functions, and only
access data according to well-formed business rules.
A package is a group of one or more stored procedures that are stored and managed together.
Stored procedures and functions are sets of PL/SQL™ (Oracle's procedural language) or Java™
statements stored in compiled form within the database. You can define a procedure so that it performs
a specific business function, then grant a user the ability to execute that procedure without granting him
any access to the objects and operations that the stored procedure uses. This prevents users from
exercising privileges to perform operations outside of the context of the pre-defined authorized
procedure.
For example, the INCREASE_PAY stored procedure illustrated in Figure 2 allows managers to
increase their employees' salaries. By executing this stored procedure, managers are allowed to
increase employees' salaries by no more than 15 percent.
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
8
While you could have just granted these managers the ability to update the EMP_SALARIES view, the
stored procedure allows you to enforce your business rules within the database by restricting managers
from giving their employees increases that violate these rules. Note that the managers need not have
access to the EMP_SALARIES view in order to execute this procedure; because a stored procedure
performs an explicitly defined operation, users only need permission to execute this procedure, not
permission to access the underlying objects. This prevents users from accessing the procedure's
underlying objects outside the context of your business rules.
UPDATE privilege
Not Necessary
EXECUTE privilege
Create procedure INCREASE_PAY
(employee_no in number, increase_amount
In number)
as begin
if increase_amount <= .15
then
update emp_salaries
set salary = salary*(1+increase_amount)
where empno= employee_no;
else
null;
end if;
end;
EMP_SALARIES view
Figure 2. Stored Procedure To Update Salary
Flexible Procedures To Lower Cost of Ownership
The type of procedure described above relies on a “definer’s rights” privilege model; for example, users
who have EXECUTE permission on Chuck’s (the definer’s) procedure access Chuck’s data with
Chuck’s privilege set, for the duration of the transaction only. “Definer’s rights” procedures are useful
for encapsulation of privileges within a business context; that is, users need not have direct privilege on
objects, merely the privilege to execute a procedure which accesses objects according to well-defined
business rules.
However, object-oriented technology and the use of new programming languages such as Java require a
more flexible privilege model, in which business logic is separate from data and the privileges required
to access an object. For example, an Enterprise JavaBeans™ that updates a bank account balance
should update Jane’s account balance if Jane accesses the bean, but John’s account balance if John
accesses the bean. Furthermore, the Enterprise JavaBeans may be deployed in a bean store, and the
beans may actually act upon different databases, or different schemas within the same database.
Alternatively, developers of data cartridges wish to deploy application libraries, in which business logic
must remain independent of specific users’ privileges. To support these requirements, Oracle8i extends
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
9
its privilege model by offering “invoker’s rights” procedures, available in both PL/SQL and Java, which
execute with an invoker’s privilege set, on an invoker’s schema.
Invoker’s rights procedures enable organizations to lower their cost of deploying applications, since
business logic — for example, a procedure which updates account balances — is not tied to a particular
user’s privilege set or a particular schema, and thus can be used (and reused) by many applications and
users. For example, an organization may have a common set of applications which multiple divisions
use, but the data upon which the applications act are separated from one another. Division 1 employees
never access Division 2’s data, and vice versa. One approach to this problem would be to physically
separate data on different servers, which is expensive, and makes it difficult to do necessary summaries
at a corporate level. Another approach is to maintain the data of each application in a separate schema,
and have the application reside in an application-owned schema. Invoker’s rights procedures enable
users from each division to access the same application, while acting upon their own data only.
Invoker’s rights procedures thus enhance the ability of organizations to deploy common applications
which nonetheless “act” differently for different sets of users. The result is stronger security at a lower
cost of deployment.
The Virtual Private Database
Giving customers and partners direct access to mission-critical systems over the Internet may yield
reduced cost, better service, and more timely information, but it also offers new challenges.
Organizations must not only keep data safe from prying eyes, but they must segregate data
appropriately, often to the level of individual customers or users. Also, many companies are interested
in providing Internet “hosting” environments, with a well-designed and well-managed computing
infrastructure, but must keep the data of each “hosted” corporation separate and secure from each other,
while allowing customizations and data access methods which best meet their individual needs.
Within the Intranet, organizations continue to struggle with traditional access control problems, such as
the classic “application security problem”: when access control is embedded in an application, users
who have access to ad-hoc queries or reporting tools bypass the security mechanisms of the application.
Oracle8i addresses these diverse security needs by introducing the Virtual Private Database ¾ serverenforced,
flexible, fine-grained access control, together with a secure application context, enabling
multiple customers and partners to have secure direct access to mission-critical data. The Virtual Private
Database enables, within a single database, per-user or per-customer data access with the assurance of
physical data separation. For Internet access, the Virtual Private Database can ensure that online
banking customers see only their own accounts, and that web storefront customers see their own orders
only. Web hosting companies can maintain multiple companies’ data in the same Oracle8i database,
while allowing each company to see only their own data.
The Virtual Private Database enables fine-grained access control by associating one or more security
policies with tables or views. Direct or indirect access to a table with an attached security policy causes
the data server to consult the policy function. The policy function returns an access condition known as
a predicate (a WHERE clause) which the data server appends to the SQL statements, dynamically
modifying the user’s data access. For example, if an organization’s security policy is that customers
can see their own orders, a user issuing the following query:
SELECT * FROM orders;
could have her query transparently and dynamically rewritten by Oracle8i as follows:
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
10
SELECT * FROM orders WHERE cust_num = SYS_CONTEXT (‘userenv’,
‘session_user’);
This limits access to only those orders for which the customer matches the logged-in user.
The Virtual Private Database enables dynamically modified data access, transparently to both users and
applications, based on any criteria; an organization can have different access conditions per user, per
group of users, or per application.
Flexible Implementation
The Virtual Private Database offers flexible policy implementation, to allow customers to fine-tune their
security policies based on their specific needs:
· Attach security policies to tables or views. Many applications already use views for security
reasons, or to enforce business rules. Attaching security policies to either views or tables allows
organizations to add fine-grained access to their existing applications without completely
rewriting them.
· Add security policies to only those tables or views where it is needed. For example, to
implement the policy ‘customers can see only their own orders,’ one need only add security
policies to the ORDERS and ORDER_LINES table.
· Enable different policies for different types of access, (e.g., select, insert, delete, and update).
For example, you could implement a policy on the EMP table that enables users to query name
and address information for any employee, but allows them to update only their own records.
· Add multiple policies per table. For example, a hosting application may allow different
companies’ HR systems to enable different access control conditions. Companies can add
additional security policies on top of the base HR application security policy (e.g., that data access
is limited by company), without affecting the base security enforcement and data separation
policies.
Context-Based Security Enforcement
To make the Virtual Private Database easy to implement, Oracle8i offers application contexts:
secure, application-specific attributes on which you can base your fine-grained access control policies.
Application contexts are completely user-definable, as are their attributes. A human resources
application may base its security policy on ‘organization,’‘employee number,’ and ‘position.’ For
example, a user in the ‘manager’ position can see the employee records of all employees in his
‘organization,’ while a user in the ‘employee’ position can only see and update records matching his
own ‘employee number.’ Alternatively, a general ledger application may base its security policy on ‘set
of books,’ and ‘cost center.’ You can use application contexts within policy functions to determine the
correct access condition (predicate) to return. You can also use application contexts within a predicate.
Oracle8i ensures that application contexts are secure, by enforcing that only trusted packages implement
them and can set context values.
Oracle8i also provides access to session primitives ¾ information the database maintains about a user
session ¾ which can be used for access control. The USERENV application context provides access to
these session primitives, including external name, proxy user and userid, protocol, port number, and full
DN (distinguished name) from an X.509 certificate.
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
11
Predefined attributes can be very useful for access control. For example, if you are using a three-tier
application which creates lightweight user sessions through OCI, you can access the PROXY_USER
attribute in the USERENV application context to determine whether the user's session was created by a
middle tier application. Your policy function could allow a user to access data only for connections
where the user is proxied. If not (that is, in cases where the user is connecting directly to the database),
the user would not be able to access any data. You could use the information in the DN to perform
access control; for example, you could use the OU (Organizational Unit) component of a DN to limit
users to viewing their own organization’s records only.
Scaleable Security
Fine-grained access control is highly scaleable; rewritten statements are fully parsed, optimized and
available to be shared by other users. Use of application context with fine-grained access control offers
even greater performance benefits, because an application context functions as a secure data cache. For
example, if you were to rewrite a query to limit data access based on a user’s position, organizational
unit, and employee number, you could either use a subquery to retrieve all these values from metadata
tables (which may involve several full table scans), or you could retrieve these
values into an application context and reference the context within your security policy whenever
you need to access attributes. As a result, you can have highly granular access control, with
excellent performance.
Strong, Server-Enforced Security
The Virtual Private Database provides the following benefits:
· Lower Cost of Ownership. Organizations can reap huge cost savings by building security once, in
the data server, instead of implementing the same security in each application that accesses data.
· Eliminate the “Application Security Problem.” Users can no longer bypass security policies
embedded in applications because security policies are associated directly with data. The same
security policy is automatically enforced by the data server, no matter how a user accesses data,
whether through a report-writing tool, a query, or through an application.
· Enable Applications You Could Never Build Before. In the past, organizations could not give
customers and partners direct access to their production systems, because there was no way to
secure the data. Internet hosting companies could not have data for multiple companies reside in
the same data server, because they could not separate each company’s data. Now, all these
scenarios are possible, because the Virtual Private Database gives you server-enforced, finegrained
access control with the assurance of physical data separation.
Triggers To Customize Functionality
Like stored procedures, database triggers are user-defined sets of PL/SQL or Java statements, also
stored in compiled form. While users explicitly execute stored procedures, database triggers are
automatically executed (or "fired") within the data server based on pre-specified events. A trigger is
defined to execute either before or after an insert, update, or delete, so that when that operation is
performed on that table, the trigger automatically fires. Four types of triggers are available for
definition on a table: BEFORE statement, BEFORE row, AFTER statement, and AFTER row.
Statement triggers are executed once regardless of the number of rows affected by the triggering
statement. Row triggers are fired once for each row affected by the triggering statement.
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
12
The security benefits of database triggers are similar to those of stored procedures: more granular
access control and consistent rule enforcement. In addition to those benefits, database triggers allow
you to perform behind-the-scenes operations based on user activity. For example, you could define a
BEFORE UPDATE trigger on the EMP table that automatically records the existing values in the table
before a user updates them. That way, you have a record of both the old and new values in any updated
rows. You can also define multiple triggers of each type (statement or row) on a single table, to audit
several different types of operations. Triggers can be used to apply security rules to the database. For
example, if employee salary information should only be updated on a weekday between 8 a.m. and 6
p.m., a trigger can be defined to implement this business rule:
CREATE TRIGGER check_salary_access
before delete or insert or update
ON scott.emp
BEGIN
/* If today is a Saturday or Sunday, then return an error.*/
IF(TO_CHAR(SYSDATE, 'DY') = 'SAT' OR
TO_CHAR(SYSDATE, 'DY') = 'SUN')
THEN raise_application_error( -20501,
'May not change employee table during the weekend');
ENDIF;
/*If the current time is before 8:00AM or after 6:00PM, then
return an error. */
IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR
TO_CHAR(SYSDATE, 'HH24') >=18)
THEN raise_application-error(-20502,'May only change
employee
table during working hours');
ENDIF;
END;
All actions and checks done as a result of the SQL statement in a trigger must succeed. If any step is
not successful, then all transactions are rolled back, ensuring data integrity.
While database triggers allow you to extend security based on actions involving specific database
tables, event triggers allow you to extend security (e.g., access control and auditing) on larger events
occurring within the database. Oracle8i provides event triggers on multiple database events, including
login, logoff, startup, shutdown, as well as create, alter, and drop. Event triggers may be defined at the
database level (e.g., startup), or for individual schemas (e.g., CREATE statements in the Order Entry
schema).
For example, you could enable security policies immediately on login, based on how a user logged in or
where he connected from. Or, you could use a login trigger to immediately set an application context
for a user, to limit his access to data. You could use a login trigger to automatically enable more
stringent auditing if a user connects to the database outside normal working hours, and disable these
auditing options with a logoff trigger. Event triggers can be used to extend the innate security
mechanisms of the Oracle8i data server, giving organizations more control over how and when users
access data.
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
13
Roles To Manage Privileges
While Oracle8i's granular privileges let you closely restrict the types of operations a user can perform in
the database, managing these privileges may be complex. For example, if ten payroll clerks are
responsible for maintaining payroll information, you would be required to grant each of these ten users
the privileges necessary for him to perform his job. If their managers also must have access to
information, you must grant these privileges to the managers, as well. If you decide to reduce the
number of privileges these users require, you must revoke privileges from each of these users
individually, making privilege management time-consuming and complex.
To address the complexity of privilege management, Oracle7™ introduced roles. Roles are user-defined
collections of privileges that can be granted to and revoked from users, and even from other roles. For
example, you can create the PAYROLL_CLERK role, grant it all privileges necessary for payroll
clerks to perform their jobs, then simply grant this single role to all payroll clerks. In addition, you can
create the PAYROLL_MANAGER role, grant it the PAYROLL_CLERK role and any other necessary
privileges, then grant it to all payroll managers. To later grant an additional privilege to all payroll
clerks and their managers, you need only grant an additional privilege to the PAYROLL_CLERK role;
similarly, to revoke a privilege from all payroll clerks and managers, you need only revoke the privilege
from the role. A role can also be defined to prompt the user for a password when that role is invoked,
thus providing another layer of security for the system.
In addition to using roles to simplify privilege management, you can use roles to restrict the set of
privileges accessible to a user at any time. For example, you can specify "default" roles that are
enabled automatically for a user whenever he connects to the database, and specify additional roles that
can only be enabled explicitly (by the user or within an application). You can also explicitly disable a
role for a user to prevent him from using a certain collection of privileges when it is no longer
appropriate (such as when he changes jobs). In addition, a role can be dropped completely from the
database, making it no longer available to any user.
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
14
Clerk
Manager
Users Roles
Human
Resources
Clerk
Human
Resources
Manager
Default
Default
EMP Table
BONUS Table
Privileges
CREATE SESSION
SELECT
SELECT
UPDATE
UPDATE
INSERT
DELETE
Figure 3: Use Roles To Assign Users Groups of Privileges
Roles To Manage Application Security
One beneficial use of the ability to dynamically enable and disable roles is to associate a role with an
application. You can specify that a certain role be enabled for authorized users at the beginning of the
application, then disabled at the end of the application. This restricts users from exercising the
privileges within the role outside of the application. For example, you can enable the
PAYROLL_CLERK role at the beginning of the payroll application, then automatically disable it when
a payroll clerk exits from the application. This ensures that payroll clerks do not use the privileges of
the role in any way other than that allowed by business rules defined within the
payroll application.
Roles can also be useful in managing privileges in an application development environment. For
example, certain privileges can be granted to developers to enable them to create their own objects.
These privileges are not required by users of an application but are needed by developers. You can also
associate a role with a database tool, allowing you to control which operations a user can perform using
that tool. For example, you can associate the database roles PAYROLL_CLERK and
PAYROLL_MANAGER with the menu roles in Oracle® Developer so that the tool only displays those
menu entries that are accessible to clerks or managers, based on the enabled roles.
Enterprise Roles For Centralized Privilege Management
The challenge of managing user accounts and privileges is magnified in large enterprises, which often
have a number of employees dedicated to creating user accounts, assigning privileges to them, and
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
15
reassigning privileges as necessary. To address this need, Oracle8 introduced enterprise roles: a
container of one or more global roles (encompassing one or more data servers), centrally administered,
maintained in a proprietary data schema. Through Oracle Advanced Security, Oracle8i extends the
benefits of enterprise roles by storing and retrieving them from Oracle Internet Directory (or selected
other LDAP v3-compliant directory servers) via LDAP, an Internet standard for directory access.
Enterprise roles enable centralized authorization of users; for example, a user may be granted the
enterprise role “HR Clerk,” which contains the global role “HR User” on the Human Resources
database, and the “Employee” global role on the Corporate Information database. If a user changes
jobs, an administrator can simply change his enterprise role assignment, which alters his privileges in
multiple databases throughout the enterprise. Also, you can add capabilities to enterprise roles (granted
to multiple users) without having to update the authorizations of each user independently.
User/Schema Separation
A benefit of managing users in a directory is that it may reduce the number of user accounts needed. In
most cases, users do not need their own accounts - or their own schemas - in a database. Typically,
users merely need to access an application schema. For example, suppose users John, Firuzeh and Jane
are all users of the Payroll application, and they need access to the Payroll schema on the Finance
database. None of them needs to create their own objects in the database; in fact, they need only access
Payroll objects. For most applications, users should be able to share a schema since they don’t need to
create their own objects in the database.
Oracle Advanced Security supports mapping many enterprise users to the same shared schema on an
individual database. This separation of users from schemas reduces the cost of user administration.
Instead of creating a user account (that is, a user schema) in each database a user needs to access, as
well as creating the user in the directory, you can create a user once, in the directory, and “point” the
user at a shared schema that many other enterprise users can also access. In the previous example, if
John, Firuzeh and Jane all access the Sales database, you need only create a single schema, e.g.
‘SALES_APPLICATION’ which all three users can access, instead of creating an account for each user
on the Sales database.
User/schema separation reaps the benefit of deploying a directory within the enterprise. Centralized user
administration and far fewer user accounts provides thus provides greater security as well as lower cost
of ownership.
Single Station Administration
Managing thousands of user accounts is one of the largest administration challenges facing large
organizations. Creating user accounts and assigning privileges is often a multi-step process, requiring
multiple tools. Significant new functionality has been added in Oracle8i to address this need. Oracle
Enterprise Security Manager (an extension to Oracle’s traditional database security manager) provides
“single station administration.” From a single console, an administrator can perform the following:
· Create a enterprise users in Oracle Internet Directory
· Create a user in multiple Oracle8i databases
· Create a shared schema
· Map enterprise users to shared schemas
· Create enterprise roles that span multiple databases
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
16
· Assign one or more enterprise roles to a user
Oracle Enterprise Security Manager provides one tool to centrally manage enterprise users and their
roles, as well as administer enterprise domains ¾ groups of databases and enterprise roles ¾ resulting
in a lower cost of user administration throughout the enterprise. Another benefit of single station
administration is that if security is easy to administer, organizations are more likely to implement
security well throughout the enterprise.
Auditing To Monitor Database Activity
A critical aspect of any security policy is maintaining a record of system activity to ensure that users are
held accountable for their actions. To address this requirement, Oracle8i provides an extensive audit
facility.
Granular Auditing
The Oracle8i audit facility allows you to audit database activity by statement, by use of system
privilege, by object, or by user. For example, you can audit activity as general as all user connections to
the database, and as specific as a particular user creating a table. You can also audit only successful
operations, or unsuccessful operations. For example, auditing unsuccessful SELECT statements may
catch users on ‘fishing expeditions’ for data they are not privileged to see. You can also set default
object auditing options so that new objects automatically have auditing enabled from object creation (for
example, any new tables are audited automatically for unsuccessful selects). Audit trail records are
stored in an Oracle8i table, making the information available for viewing through ad hoc queries or any
appropriate application or tool.
Efficient Auditing
Oracle8i implements auditing efficiently: statements are parsed once for both execution and
auditing, not separately. Also, auditing is implemented within the server itself, not in a separate,
add-on server which may be remotely situated from the statements which are being executed
(thereby incurring network overhead). The granularity and scope of these audit options allow you
to record and monitor specific database activity without incurring the performance overhead that
more general auditing entails. And, by setting just the options of interest to you, you avoid the
“catch-all, and throw away” audit methods which intercept and log all statements, and then filter
them to retrieve the ones of interest.
Extensible Auditing
To record customized information that is not automatically included in audit records, you can use
triggers (described in "Triggers to Customize Functionality") to further design your own audit auditing
conditions and audit record contents. For example, you could define a trigger on the EMP table to
generate an audit record whenever an employee's salary is increased by more than 10 percent and
include selected information, such as before and after values of SALARY:
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
17
CREATE TRIGGER audit_emp_salaries
AFTER INSERT OR DELETE OR UPDATE ON employee_salaries
for each row
begin
if (:new.salary> :old.salary * 1.10)
then
insert into emp_salary_audit values (
:employee_no,
:old.salary,
:new.salary,
user,
sysdate);
endif;
end;
Furthermore, you can use event triggers to enable auditing options for specific users on login, and
disable them on logoff.
Oracle8i also gives you the option of sending audit records to the database audit trail or your operating
system's audit trail, when the operating system is capable of receiving them. This option, coupled with
the broad selection of audit options and the ability to customize auditing with triggers or stored
procedures, gives you the flexibility of implementing an auditing scheme that suits your specific
business needs.
Auditing For Three-Tier Applications
Many three-tier applications authenticate users to the middle tier, then the TP monitor or application
server connects as super-privileged user, and does all activity on behalf of all users. With Oracle8i, you
are not only able to preserve the identity of the real client over the middle tier and enforce “least
privilege” through a middle tier, but you can audit actions taken on behalf of the user by the middle tier.
Oracle8i’s audit records capture both the logged-in user (e.g., the TP monitor) who initiated the
connection, and the user on whose behalf an action is taken. For example, to capture all SELECTs on
the BONUS table done by a middle tier called ‘appsrv,’ you would enabled the following audit option:
AUDIT SELECT ON bonus BY appsrv ON BEHALF OF ANY;
Audit records capture both the user taking the action and the user on whose behalf the action was taken.
Auditing user activity, whether users are connected through a middle tier or directly to the data server,
enhances user accountability, and thus the overall security of multi-tier systems.
Active Auditing
While an auditing facility can record attempts to breach database security or actual breaches, they do
not alert administrators or security officers that the breach is happening. In fact, it can be hours, days,
or months before analysts detect a security breach by examining the audit trail. Consequently, if one of
the purposes of your auditing policy is to detect potential breaches of security, you need an alarm
facility to alert the appropriate administrator when the database or operating system detects
suspicious behavior.
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
18
When you use database triggers to perform customized auditing, Oracle8i can send an alarm to a
waiting process that a potential breach of security is occurring.
AUTHENTICATION THROUGH A MIDDLE TIER
The growth of three-tier systems (for example, browser to application server to database) has increased
dramatically with the growth of the Internet. Indeed, three-tier applications are often referred to as the
"Internet computing model." Oracle8i n-tier authentication addresses many security difficulties that
arise in three-tier applications. It enables organizations to reap the benefits of Internet computing, while
minimizing the security risks of three-tier systems. This section includes:
· Advantages of n-Tier Authentication
· Security Challenges of Three-tier Computing
· Oracle8i n-Tier Authentication Solutions
Advantages of n-Tier Authentication
Three-tier systems provide many benefits to organizations. Application servers and web servers enable
users to access data stored in legacy applications. Users like using a familiar, easy-to-use browser
interface. Organizations can separate application logic from data storage, partitioning the former in
application servers and the latter in databases. Organizations can also lower their cost of computing by
replacing many "fat clients" with a number of "thin clients" and an application server.
In addition, Oracle n-tier authentication delivers the following security benefits:
· A limited trust model, by controlling the users on whose behalf middle tiers can connect, and the
roles the middle tiers can assume for the user
· Scalability, by supporting lightweight user sessions through OCI, and eliminating the overhead of
reauthenticating clients
· Accountability, by preserving the identity of the real user through to the database, and enabling
auditing of actions taken on behalf of the real user
Security Challenges of Three-tier Computing
While three-tier computing provides many benefits, it raises a number of new security issues:
· Who Is the Real User?
· Does the Middle Tier Have Too Much Privilege?
· How to Audit? Whom to Audit?
· Can the User Be Reauthenticated to the Database?
Who Is the Real User?
Most organizations want to know the identity of the actual user who is accessing the database, for
reasons of access control or auditing. User accountability is diminished if the identity of the users
cannot be traced through all tiers of the application.
Furthermore, if only the application server knows who the user is, then all per-user security enforcement
must be done by the application itself. Application-based security is very expensive. If each application
that accesses the data must enforce security, then security must be reimplemented in each and every
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
19
application. It is often preferable to build security on the data itself, with per-user accountability
enforced within the database.
Does the Middle Tier Have Too Much Privilege?
Some organizations are willing to accept three-tier systems within the enterprise, in which "allprivileged"
middle tiers, such as transaction processing (TP) monitors, can perform all actions for all
users. In this architecture, the middle tier connects to the database as the same user for all application
users. It therefore needs to have all privileges that application users need to do their jobs.
This computing model may be undesirable in the Internet, where the middle tier resides outside, on, or
just inside a firewall. More desirable, in this context, is a limited trust model, in which the identity of
the real client is known to the data server, and the application server (or other middle tier) has a
restricted privilege set. Also useful is the ability to limit the users on whose behalf a middle tier can
connect, and the roles the middle tier can assume for the user. For example, many organizations would
prefer that users have different privileges depending on where they are connecting from. A user
connecting to a web server or application server on the firewall might only be able to use very minimal
privileges to access data, whereas a user connecting to a web server or application server within the
enterprise might be able to exercise all privileges she is otherwise entitled to have.
How to Audit? Whom to Audit?
Accountability through auditing is a basic principle of information security. Most organizations want to
know on whose behalf a transaction was accomplished, not just that a particular application server
performed a transaction. A system must therefore be able to differentiate between a user performing a
transaction, and an application server performing a transaction on behalf of a user.
Auditing in three-tier systems should be tied to the issue of knowing the real user: if you cannot preserve
the user’s identity through the middle tier of a three-tier application, you cannot audit actions on behalf
of the user.
Can the User Be Reauthenticated to the Database?
In client/server systems, authentication tends to be straightforward: the client authenticates to the server.
In three-tier systems authentication is more difficult, because there are several potential authentications.
· Client to Middle Tier Authentication
· Middle Tier to Database Authentication
· Client Reauthentication Through Middle Tier to Database
Client to Middle Tier Authentication Client authentication to the middle tier is clearly required if a
system is to conform with basic security principles. The middle tier is typically the first gateway to
useful information that the user can access. Users must, therefore, authenticate to the middle tier. Note
that such authentication may be mutual; that is, the middle tier authenticates to the client just as the
client authenticates to the middle tier.
Middle Tier to Database Authentication Since the middle tier must typically initiate a connection to
a database to retrieve data (whether on its own behalf or on behalf of the user), this connection clearly
must be authenticated. In fact, Oracle8i does not allow unauthenticated connections. Again, middle tier
to database authentication may also be mutual.
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
20
Client Reauthentication Through Middle Tier to Database Client reauthentication from the middle
tier to the database is problematic in three-tier systems. The username may not be the same on the
middle tier and the database. In this case, users may need to reenter a username and password, which
the middle tier uses to connect on their behalf. Or, more commonly, the middle tier may need to map the
username provided, to a database username. This mapping is often done in an LDAP-compliant
directory service, such as Oracle Internet Directory.
For the client to reauthenticate himself to the database, the middle tier either needs to ask the user for a
password (which it then must be trusted to pass to the database), or the middle tier must retrieve a
password for the user and use that to authenticate the user. Both approaches involve security risks,
because the middle tier is trusted to handle the user’s password properly, and not use it maliciously.
One of the only cases for which reauthentication does not involve trusting the middle tier occurs when a
middle tier downloads an applet to a client, and the client connects directly to the database via the
applet. In this case, the application server is literally just that: it serves the application (applet) to the
user, and has no part in further authentication of the user.
Reauthenticating the client to the back-end database is not always beneficial. First, two sets of
authentication handshakes per user involves considerable network overhead. Second, you must trust the
middle tier to have authenticated the user. (You clearly must trust the middle tier if it retrieves or
otherwise is privy to the user’s password.) It is therefore not unreasonable for the database to simply
accept that the middle tier has performed proper authentication. In other words, the database accepts the
identity of the real client without requiring the real client to authenticate herself.
For some authentication protocols, client reauthentication is just not possible. For example, many
browsers and application servers support the Secure Sockets Layer (SSL) protocol. Both the Oracle8i
database (through Oracle Advanced Security) and Oracle Application Server support the use of SSL for
client authentication. However, SSL is a point-to-point protocol, not an end-to-end protocol. It cannot be
used to reauthenticate a browser client (through the middle tier) to the database.
The reason for this is that a user cannot securely give up his private key to the middle tier in order for
the reauthentication of the client to occur. Once the user’s private key is compromised, the user’s very
identity is compromised. In addition, there is no way to "tunnel" through a middle tier so that the
authentication of the browser client to the database can occur directly.
In short, organizations deploying three-tier systems require flexibility as regards reauthentication of the
client. In some cases, they cannot reauthenticate the client; in other cases, they may choose whether or
not to reauthenticate the client.
Oracle8i n-Tier Authentication Solutions
The following sections explain how Oracle8i addresses each of the challenges listed above.
· Passing Through the Identity of the Real User
· Limiting the Privilege of the Middle Tier
· Reauthenticating the Real User
· Auditing Actions Taken on Behalf of the Real User
Database Security in Oracle8i, An Oracle Technical White Paper
November 1999
21
Passing Through the Identity of the Real User
Many organizations want to know who the real user is through all tiers of an application, without
sacrificing the benefits of a middle tier. Oracle8i provides the ability to preserve client identity through
the Oracle Call Interface (OCI). OCI enables a middle tier to set up, within a single database
connection, a number of "lightweight" user sessions, each of which uniquely identifies a connected user.
These lightweight sessions reduce the network overhead of creating separate network connections from
the middle tier to the database. The application can switch between these sessions as required to process
transactions on behalf of users.
The full authentication sequence from the client to the middle tier to the database occurs as follows:
1. The client authenticates to the middle tier, using whatever form of authentication the middle tier
will accept. For example, the client could authenticate to the middle tier using a
username/password, or an X.509 certificate by means of SSL.
2. The middle tier authenticates itself to Oracle8i, using whatever form of authentication Oracle8i will
accept. This could be a password, or an authentication mechanism supported by Oracle Advanced
Security, such as a Kerberos ticket or an X.509 certificate (SSL).
3. The middle tier then creates one or more sessions for users using the Oracle Call Interface. The
lightweight session information must include username as a minimum. The middle tier may
optionally provide a password for the client, and the roles for the client.
4. Since the database cannot require the middle tier to provide a password for the client, authentication
is performed by OCI. To create the session for the client, the middle-tier server calls the
OCISessionBegin function. Prior to calling OCISessionBegin, the OCIAttrSet
function is called to provide the needed information about the client to the middle tier server. It is
called in turn with the following attributes:
· OCI_ATTR_USERNAME ¾ Sets the database user name of the client. This attribute is
mandatory.
· OCI_ATTR_PASSWORD ¾ If the client has provided a database password to be validated by
the database, then the middle tier server passes it along with the username. If this attribute is not
provided, then it is assumed that the middle tier server has authenticated the client.
· OCI_ATTR_PROXY_CREDENTIALS ¾ This attribute tells the server that the client is
connecting through a middle tier server.
· OCI_ATTR_INITIAL_CLIENT_ROLES ¾ If the middle tier server wants to activate a set of
roles upon connecting as the client, then the list is passed along with this attribute.
5. The database verifies that the middle tier is privileged to create sessions on behalf of the user, using
the roles provided. (See "Limiting the Privilege of the Middle Tier," below).
The OCISessionBegin call will fail if the application server is not allowed to proxy on behalf of
the client by the administrator, or if the application server is not allowed to activate the specified roles.
Limiting the Privilege of the Middle Tier
"Least privilege" is the principle that users should have the fewest privileges necessary to perform their
duties, and no more. As applied to middle tier applications, this means that the middle tier should not
have more privileges than it needs. Oracle8i enables you to limit the middle tier such that it can connect
only on behalf of certain users, using only specific roles.
For example, suppose that user Sarah wants to connect to the database through a middle tier, appsrv
(which is also a database user). Sarah has multiple roles, but it is desirable to restrict the middle tier to
exercise only the clerk role on her behalf. A DBA could effectively grant permission for appsrv to
initiate connections on behalf of Sarah using her clerk role only, using the following syntax:
ALTER USER Sarah GRANT CONNECT THROUGH appsrv WITH ROLE clerk;
By default, the middle tier cannot create connections for any client. The permission must be granted on a
per-user basis.
To allow appsrv to use all of the roles granted to the client Sarah, the following statement would be
used:
ALTER USER sarah GRANT CONNECT THROUGH appsrv WITH ROLE ALL;
Each time a middle tier initiates a lightweight (OCI) session for another database user, the database
verifies that the middle tier is privileged to connect for that user, using the role specified.
Reauthenticating the Real User
As described above, it is not always beneficial to reauthenticate users to the database after they have
been authenticated by the middle tier. However, if you wish to do this for an added measure of security,
you can pass the database the user’s password using the OCI_ATTR_PASSWORD attribute of the
OCIAttrSet call.
Auditing Actions Taken on Behalf of the Real User
The n-tier authentication features of Oracle8i enables you to audit actions a middle tier performs on
behalf of a user. For example, suppose an application server hrappserver creates multiple
lightweight sessions for users Ajit and Jane. A DBA could enable auditing for SELECTs on the bonus
table that hrappserver initiates for Jane as follows:
AUDIT SELECT ON bonuses BY hrappserver ON BEHALF OF Jane;
Alternatively, the DBA could enable auditing on behalf of multiple users (in this case, both Jane and
Ajit) connecting through a middle tier as follows:
AUDIT SELECT ON bonuses BY hrappserver ON BEHALF OF ANY;
This auditing option only audits SELECT statements being initiated by hrappserver on behalf of
other users. A DBA can enable separate auditing options to capture SELECTs against the bonus table
from clients connecting directly to the database:
AUDIT SELECT ON bonuses;



linda ahmed anwar
sec 11
cs

http://learning

What Is PHP?
PHP is the Web development language written by and for Web developers.
PHP stands for PHP: Hypertext Preprocessor. The product was
originally named Personal Home Page Tools, and many people still
think that’s what the acronym stands for. But as it expanded in scope,
a new and more appropriate (albeit GNU-ishly recursive) name was
selected by community vote. PHP is currently in its fifth major
rewrite, called PHP5 or just plain PHP.
PHP is a server-side scripting language, which can be embedded in
HTML or used as a standalone binary (although the former use is
much more common). Proprietary products in this niche are
Microsoft’s Active Server Pages, Macromedia’s ColdFusion, and Sun’s
Java Server Pages. Some tech journalists used to call PHP “the open
source ASP” because its functionality is similar to that of the
Microsoft product—although this formulation was misleading, as
PHP was developed before ASP. Over the past few years, however,
PHP and server-side Java have gained momentum, while ASP has lost
mindshare, so this comparison no longer seems appropriate.
We’ll explore server-side scripting more thoroughly in Chapter 2, but
for the moment you can think of it as a collection of super-HTML tags
or small programs that run inside your Web pages—except on the
server side, before they get sent to the browser. For example, you can
use PHP to add common headers and footers to all the pages on a
site or to store form-submitted data in a database.

Strictly speaking, PHP has little to do with layout, events, on the fly DOM manipulation, or
really anything about what a Web page looks and sounds like. In fact, most of what PHP does
is invisible to the end user. Someone looking at a PHP page will not necessarily be able to tell
that it was not written purely in HTML, because usually the result of PHP is HTML.
PHP is an official module of Apache HTTP Server, the market-leading free Web server that
runs about 67 percent of the World Wide Web (according to the widely quoted Netcraft Web
server survey). This means that the PHP scripting engine can be built into the Web server
itself, leading to faster processing, more efficient memory allocation, and greatly simplified
maintenance. Like Apache Server, PHP is fully cross-platform, meaning it runs native on several
flavors of Unix, as well as on Windows and now on Mac OS X. All projects under the aegis
of the Apache Software Foundation—including PHP—are open source software.
What Is MySQL?
MySQL (pronounced My Ess Q El) is an open source, SQL Relational Database Management
System (RDBMS) that is free for many uses (more detail on that later). Early in its history,
MySQL occasionally faced opposition due to its lack of support for some core SQL constructs
such as subselects and foreign keys. Ultimately, however, MySQL found a broad, enthusiastic
user base for its liberal licensing terms, perky performance, and ease of use. Its acceptance
was aided in part by the wide variety of other technologies such as PHP, Java, Perl, Python,
and the like that have encouraged its use through stable, well-documented modules and
extensions. MySQL has not failed to reward the loyalty of these users with the addition of
both subselects and foreign keys as of the 4.1 series.
Databases in general are useful, arguably the most consistently useful family of software
products—the “killer product” of modern computing. Like many competing products, both
free and commercial, MySQL isn’t a database until you give it some structure and form. You
might think of this as the difference between a database and an RDBMS (that is, RDBMS plus
user requirements equals a database).
There’s lots more to say about MySQL, but then again, there’s lots more space in which to
say it.
The History of PHP
Rasmus Lerdorf—software engineer, Apache team member, and international man of
mystery—is the creator and original driving force behind PHP. The first part of PHP was developed
for his personal use in late 1994. This was a CGI wrapper that helped him keep track of
people who looked at his personal site. The next year, he put together a package called the
Personal Home Page Tools (a.k.a. the PHP Construction Kit) in response to demand from users
who had stumbled into his work by chance or word of mouth. Version 2 was soon released
under the title PHP/FI and included the Form Interpreter, a tool for parsing SQL queries.
By the middle of 1997, PHP was being used on approximately 50,000 sites worldwide. It was
clearly becoming too big for any single person to handle, even someone as focused and energetic
as Rasmus. A small core development team now runs the project on the open source
“benevolent junta” model, with contributions from developers and users around the world.
Zeev Suraski and Andi Gutmans, the two Israeli programmers who developed the PHP3 and
PHP4 parsers, have also generalized and extended their work under the rubric of Zend.com
(Zeev, Andi, Zend, get it?).

The History of MySQL
Depending on how much detail you want, the history of MySQL can be traced as far back as
1979, when MySQL’s creator, Monty Widenius, worked for a Swedish IT and data consulting
firm, TcX. While at TcX, Monty authored UNIREG, a terminal interface builder that connected
to raw ISAM data stores. In the intervening 15 years, UNIREG served its makers rather well
through a series of translations and extensions to accommodate increasingly large data sets.
In 1994, when TcX began working on Web data applications, chinks in the UNIREG armor,
primarily having to do with application overhead, began to appear. This sent Monty and his
colleagues off to look for other tools. One they inspected rather closely was Hughes mSQL,
a light and zippy database application developed by David Hughes. mSQL possessed the distinct
advantages of being inexpensive and somewhat entrenched in the market, as well as
featuring a fairly well-developed client API. The 1.0 series of mSQL release lacked indexing,
however, a feature crucial to performance with large data stores. Although the 2.0 series of
mSQL would see the addition of this feature, the particular implementation used was not
compatible with UNIREG’s B+-based features. At this point, MySQL, at least conceptually,
was born.
6 Part I . PHP: The Basics
Monty and TcX decided to start with the substantial work already done on UNIREG while
developing a new API that was substantially similar to that used by mSQL, with the exception
of the more effective UNIREG indexing scheme. By early 1995, TcX had a 1.0 version of this
new product ready. They gave it the moniker MySQL and later that year released it under a
combination open source and commercial licensing scheme that allowed continued development
of the product while providing a revenue stream for MySQL AB, the company that
evolved from TcX.
Over the past ten years, MySQL has truly developed into a world class product. MySQL now
competes with even the most feature-rich commercial database applications such as Oracle
and Informix. Additions in the 4.x series have included much-requested features such as
transactions and foreign key support. All this has made MySQL the world’s most used open
source database.
Reasons to Love PHP and MySQL
There are ever so many reasons to love PHP and MySQL. Let us count a few.
Cost
PHP costs you nothing. Zip, zilch, nada, not one red cent. Nothing up front, nothing over the
lifetime of the application, nothing when it’s over. Did we mention that the Apache/PHP/MySQL
combo runs great on cheap, low-end hardware that you couldn’t even think about for
IIS/ASP/SQL Server?
MySQL is a slightly different animal in its licensing terms. Before you groan at the concept of
actually using commercial software, consider that although MySQL is open-source licensed
for many uses, it is not and has never been primarily community-developed software. MySQL
AB is a commercial entity with necessarily commercial interests. Unlike typical open source
projects, where developers often have regular full-time (and paying) day jobs in addition to
their freely given open source efforts, the MySQL developers derive their primary income
from the project. There are still many circumstances in which MySQL can be used for free
(basically anything nonredistributive, which covers most PHP-based projects), but if you
make money developing solutions that use MySQL, consider buying a license or a support
contract. It’s still infinitely more reasonable than just about any software license you will
ever pay for.
For purposes of comparison, Table 1-1 shows some current retail figures for similar products
in the United States. All prices quoted are for a single-processor public Web server with the
most common matching database and development tool; $0 means a no-cost alternative is a
common real-world choice.

Open source software: don’t fear the cheaper
But as the bard so pithily observed, we are living in a material world—where we’ve internalized
maxims such as, “You get what you pay for,” “There’s no such thing as a free lunch,” and
“Things that sound too good to be true usually are.” You (or your boss) may, therefore, have
some lingering doubts about the quality and viability of no-cost software. It probably doesn’t
help that until recently software that didn’t cost money—formerly called freeware, shareware,
or free software—was generally thought to fall into one of three categories:
. Programs filling small, uncommercial niches
. Programs performing grungy, low-level jobs
. Programs for people with bizarre socio-political issues
It’s time to update some stereotypes once and for all. We are clearly in the middle of a sea
change in the business of software. Much (if not most) major consumer software is distributed
without cost today; e-mail clients, Web browsers, games, and even full-service office
suites are all being given away as fast as their makers can whip up Web versions or set up
FTP servers. Consumer software is increasingly seen as a loss-leader, the flower that attracts
the pollinating honeybee—in other words, a way to sell more server hardware, operating
systems, connectivity, advertising, optional widgets, or stock shares. The full retail price of a
piece of software, therefore, is no longer a reliable gauge of its quality or the eccentricity-level
of its user.
On the server side, open source products have come on even stronger. Not only do they
compete with the best commercial stuff; in many cases there’s a feeling that they far exceed
the competition. Don’t take our word for it! Ask IBM, any hardware manufacturer, NASA,
Amazon.com, Rockpointe Broadcasting, Ernie Ball Corporation, the Queen of England, or
the Mexican school system. If your boss still needs to be convinced, further ammunition is
available at www.opensource.org and www.fsf.org.
The PHP license
The freeness of open source and Free software is guaranteed by a gaggle of licensing schemes,
most famously the GPL (Gnu General Public License) or copyleft. PHP used to be released
under both the GPL and its own license, with each user free to choose between them. This has
recently changed. The program as a whole is now released under its own extremely laissezfaire
PHP license on the model of the BSD license, whereas Zend as a standalone product is
released under the Q Public License (this clause applies only if you unbundle Zend from PHP
and try to sell it).
You can read the fine print about the relevant licenses at these Web sites:
. www.php.net/license/
. www.mysql.com/doc/en/GPL_license.html
. www.troll.no/qpl/annotated.html
Most people get PHP or MySQL via download, but you may have paid for it as part of a Linux
distribution, a technical book, or some other product. In that case, you may now be silently
disputing our assertion that PHP costs nothing. Here’s the twist: Although you can’t require a
fee for most open source software, you can charge for delivering that software in a more convenient
format—such as by putting it on a disk and shipping the disk to the customer. You
can also charge anything the market will bear for being willing to perform certain services or
accept certain risks that the development team may not wish to undertake. For instance, you

are allowed to charge money for guaranteeing that every copy of the software you distribute
will be virus-free or of reasonable quality, taking on the risk of being sued if a bunch of customers
get bad CD-ROMs that contain hard-drive-erasing viruses.
Usually, open source software users can freely choose the precisely optimal cost-benefit
equation for each particular situation: no cost and no warranties, or expensive but well supported,
or something in between. No organized attempt has been made yet to sell service and
support for PHP (although presumably that will be one of the value-adds of Zend). MySQL AB
does sell support as part some of its licensing packages for the MySQL product. Other open
source products, such as Linux, have companies such as Red Hat standing by to answer your
questions, but the commercialization process is still in the early stages for PHP.
Ease of Use
PHP is easy to learn, compared to the other ways to achieve similar functionality. Unlike Java
Server Pages or C-based CGI, PHP doesn’t require you to gain a deep understanding of a
major programming language before you can make a trivial database or remote-server call.
Unlike Perl, which has been semijokingly called a “write-only language,” PHP has a syntax
that is quite easy to parse and human-friendly. And unlike ASP.NET, PHP is stable and ready
to solve your problems today.
Many of the most useful specific functions (such as those for opening a connection to an
Oracle database or fetching e-mail from an IMAP server) are predefined for you. A lot of
complete scripts are waiting out there for you to look at as you’re learning PHP. In fact, it’s
entirely possible to use PHP just by modifying freely available scripts rather than starting
from scratch—you’ll still need to understand the basic principles, but you can avoid many
frustrating and time-consuming minor mistakes.
We must mention one caveat: Easy means different things to different people, and for some
Web developers it has come to connote a graphical, drag-and-drop, What You See Is What You
Get development environment. To become truly proficient at PHP, you need to be comfortable
editing HTML by hand. You can use WYSIWYG editors to design sites, format pages, and
insert client-side features before you add PHP functionality to the source code. There are
even ways, which we’ll detail in Chapter 3, to add PHP functions to your favorite editing environment.
It’s not realistic, however, to think you can take full advantage of PHP’s capabilities
without ever looking at source code.
Most advanced PHP users (including most of the development team members) are diehard
hand-coders. They tend to share certain gut-level, subcultural assumptions—for instance,
that hand-written code is beautiful and clean and maximally browser-compatible and therefore
the only way to go—that they do not hesitate to express in vigorous terms. The PHP
community offers help and trades tips mostly by e-mail, and if you want to participate, you
have to be able to parse plain-text source code with facility. Some WYSIWYG users occasionally
ask list members to diagnose their problems by looking at their Web pages instead of
their source code, but this rarely ends well.
That said, let us reiterate that PHP really is easy to learn and write, especially for those with
a little bit of experience in a C-syntaxed programming language. It’s just a little more involved
than HTML but probably simpler than JavaScript and definitely less conceptually complex
than JSP or ASP.NET.


If you have no relational database experience or are coming from an environment such as
Microsoft Access, MySQL’s command line interface and lack of implicit structure may at first
seem a little daunting. Again, the word easy is relative. However, MySQL’s increasingly faithful
adherence to the ANSI SQL-92 standard and a comprehensive suite of external client programs,
coupled with graphical administration tools such as PHPMyAdmin and the new
MySQL Control Center, will get even neophyte users up and running quickly compared to
other databases. None of these will substitute for learning a little theory and employing
good design practices, but that subject is for another chapter.
HTML-embeddedness
PHP is embedded within HTML. In other words, PHP pages are ordinary HTML pages that
escape into PHP mode only when necessary. Here is an example:

Hello,

. We know who you are! Your first name is .

You are visiting our site at

Here is a link to your account management page: /”>’s account
management page

When a client requests this page, the Web server preprocesses it. This means it goes through
the page from top to bottom, looking for sections of PHP, which it will try to resolve. For one
thing, the parser will suck up all assigned variables (marked by dollar signs) and try to plug
them into later PHP commands (in this case, the echo function). If everything goes smoothly,
the preprocessor will eventually return a normal HTML page to the client’s browser

Stability
The word stable means two different things in this context:
. The server doesn’t need to be rebooted often.
. The software doesn’t change radically and incompatibly from release to release.
To our advantage, both of these connotations apply to both MySQL and PHP.
Apache Server is generally considered the most stable of major Web servers, with a reputation
for enviable uptime percentages. Although it is not the fastest nor the easiest to administer,
once you get it set up, Apache HTTP Server seemingly never crashes. It also doesn’t require
server reboots every time a setting is changed (at least on the Unix side). PHP inherits this
reliability; plus, its own implementation is solid yet lightweight. In a two-and-a-half-month
head-to-head test conducted by the Network Computing labs in October 1999, Apache Server
with PHP handily beat both IIS/Visual Studio and Netscape Enterprise Server/Java for stability
of environment.
PHP and MySQL are also both stable in the sense of feature stability. Their respective development
teams have thus far enjoyed a clear vision of their project and refused to be distracted
by every new fad and ill-thought-out user demand that comes along. Much of the effort goes
into incremental performance improvements, communicating with more major databases, or
adding better session support. In the case of MySQL, the addition of reasonable and expected
new features has hit a rapid clip. For both PHP and MySQL, such improvements have rarely
come at the expense of compatibility. Applications written in PHP3 will function with little or
no revision for PHP4 and 5. And because of the standards-based SQL support, MySQL 3.x
databases are easily moved to more current versions (and most likely always will be).
Speed
PHP is pleasingly zippy in its execution, especially when compiled as an Apache module on
the Unix side. The MySQL server, once started, executes even very complex queries with
huge result sets in record-setting time.
PHP5 is much faster for almost every use than CGI scripts. There is an unfortunate grain of
truth to the joke that CGI stands for “Can’t Go Instantly.” Although many CGI scripts are written
in C, one of the lowest-level and therefore speediest of the major programming languages,
they are hindered by the fact that each request must spawn an entirely new process after
being handed off from the http daemon. The time and resources necessary for this handoff
and spawning are considerable, and there can be limits to the number of concurrent processes
that can be running at any one time. Other CGI scripting languages such as Perl and
Tcl can be quite slow. Most Web sites have moved away from use of CGI for performance and
security reasons.
Although it takes a slight performance hit by being interpreted rather than compiled, this is
far outweighed by the benefits PHP derives from its status as a Web server module. When
compiled this way, PHP becomes part of the http daemon itself. Because there is no transfer
to and from a separate application server (as there is with ColdFusion, for instance) requests
can be filled with maximum efficiency.
Although no extensive formal benchmarks have compared the two, much anecdotal evidence
and many small benchmarks suggest that PHP is at least as fast as ASP and readily outperforms
ColdFusion or JSP in most applications.


Open source licensing
We’ve already dealt with the cost advantages of open source software in the “Cost” section of
this chapter. The other major consequence of these licenses is that the complete source code
for the software must be included in any distribution.
In fact, the Unix version of PHP is released only as source code; so far, the development team
has staunchly resisted countless pleas to distribute official binaries for any of the Unixes. At
first, new users (particularly those also new to Unix) tend to feel that source code is about as
useful as a third leg, and most vastly prefer a nice convenient rpm. But there are both pragmatic
and idealistic reasons for including folders full of pesky .c and .h files.
The most immediate pragmatic advantage is that you can compile your PHP installation with
only the stuff you really need for any given situation. This approach has performance and
security advantages. For instance, you can put in hooks to the database(s) of your choice.
You can recompile as often as you want: maybe when an Apache security release comes out,
or when you wish to support a new database application. By compiling a custom application
specifically suited to your system, or any given snapshot of your system, performance and
stability are increased over their already respectable baseline.
What sets open source software apart from its competitors is not just price but control.
Plenty of consumer software is now given away under various conditions. Careful scrutiny of
the relevant licenses, however, will generally reveal limits as to how the software can be used.
Maybe you can run it at home but not at the office. Perhaps you can load it on your laptop,
but you’re in violation if you use it for business purposes. Or, most commonly, you can use it
for anything you want but forget about looking at the code—much less changing it. There are
even community licenses that force you to donate your improvements to the codebase but
charge you for use of the product at the end!
Don’t even think about coming back with a riposte that involves violating a software
license—we’re covering our ears; we’re not listening! Especially with the explosion in no-cost
software, there’s just no good reason to break the law. Besides, it’s bad karma for software
developers. What goes around, comes around, don’t ya know?
For all their openness, the licenses for MySQL and PHP are quite different. You should not
assume that you understand the MySQL terms simply because you have read the PHP
license. They have many similarities to be sure but also some radically different provisions,
especially when it comes to when you should pay.
Table 1-3 shows examples of the various source and fee positions in today’s software
marketplace.
Table 1-3: Source/Fee Spectrum
Fee Structure Closed Source Controlled Source Open Source
Fee for all uses Macromedia ColdFusion — —
Fee for some uses Corel WordPerfect Sun Java MySQL
No fee for any use Microsoft IE Sun StarOffice GPLed software
Caution

Genuinely open source software like PHP cannot seek to limit the purposes for which it is
used, the people allowed to use it, or a host of other factors. The most critical of these rights
is the one allowing users to make and distribute any modifications along with the original
software. In the most extreme case, where one or more developers decide to release a separate,
complete version of a piece of software, this practice is referred to as code forking.
If somewhere down the road you develop irreconcilable differences with the PHP development
team, you can take every bit of code they’ve labored over for all these years and use it
as the basis of your own product. You couldn’t call it PHP, and you’d have to include stuff in
your documentation that gave due credit to the authors—the rationale is that source code
distributions make it next to impossible for any single person or group to hijack a program to
the detriment of the community as a whole, because every user always has the power to take
the source and walk.
Users new to the open source model should be aware that this right is also enjoyed by the
developers. At any time, Rasmus, Zend, and company can choose to defect from the community
and put all their future efforts into a commercial or competing product based on PHP. Of
course, the codebase up to this point would still be available to anyone who wanted to pick
up the baton, and for a product as large as PHP that could be a considerable number of volunteer
developers.
This leads to one other oft-forgotten advantage of open source software: You can be pretty
sure the software will be around in a few years, no matter what. In these days of products
with the life spans of morning glories, it’s hard to pick a tool with staying power. Fans of OS/2,
Amiga, NeXT, Newton, Firefly, Netscape, BeOS, Napster, and a host of other once-hot technologies
know the pain of abandonment when a company goes belly-up, decides to stop supporting
a technology, or is sold to a buyer with a new agenda. The open source model reduces the
chances of an ugly emergency port in a couple of years and thus makes long-term planning
more realistic.
Many extensions
PHP makes it easy to communicate with other programs and protocols. The PHP development
team seems committed to providing maximum flexibility to the largest number of users.
Database connectivity is especially strong, with native-driver support for about 15 of the most
popular databases plus ODBC. In addition, PHP supports a large number of major protocols
such as POP3, IMAP, and LDAP. PHP4 added support for Java and distributed object architectures
(COM and CORBA), making n-tier development a possibility for the first time. PHP5
extends this support even further, offering a fully incorporated GD graphics library and
revamped XML support with DOM and simpleXML.
Most things that PHP does not support are ultimately attributable to closed-source shops on
the other end. For instance, Microsoft has not thus far been eager to cooperate with open
source projects like PHP. Potential users who complain about lack of native Mac OS 9 or .NET
support on the PHP mailing list are simply misinformed about where the fault lies.
Fast feature development
Users of proprietary Web development technologies can sometimes be frustrated by the
glacial speed at which new features are added to the official product standard to support
emerging technologies. With PHP, this is not a problem. All it takes is one developer, a C
compiler, and a dream to add important new functionality. This is not to say that the PHP

team will accept every random contribution into the official distribution without community
buy-in, but independent developers can and do distribute their own extensions which may be
later folded into the main PHP package in more or less unitary form. For instance, Dan Libby’s
elegant xmlrpc-epi extension was adopted as part of the PHP distribution in version 4.1, a few
months after it was first released as an independent package.
PHP development is also constant and ongoing. Although there are clearly major inflection
points, such as the transition between PHP4 and PHP5, these tend to be most important deep
in the guts of the parser—people were actually working on major extensions throughout the
transition period without critical problems. Furthermore, the PHP group subscribes to the
open source philosophy of “release early, release often,” which gives developers many opportunities
to follow along with changes and report bugs. Compare this release scheme to the
.NET transition, which has left developers with almost a year in which Microsoft is not really
improving IIS but has not yet released a prime-time version of .NET server.
It hasn’t always been the case that MySQL added new features in a timely fashion. It would
probably be fair to say that a significant chunk of PostgreSQL users are former MySQL users
frustrated by the lack of transaction support, for example. However, the 4.0 and 4.1 versions
have remedied this and other inequities. Transactions are in the software today, while subselects
and foreign keys are experimental but coming along nicely.
Popularity
PHP is fast becoming one of the most popular choices for so-called two-tier development
(Web plus data). Figure 1-2 charts growth since 1999.

Summary
PHP and MySQL, individually or together, aren’t the panacea for every Web development
problem, but they present a lot of advantages. PHP is built by Web developers for Web developers
and supported by a large and enthusiastic community. MySQL is a powerful standardscompliant
RDBMS that comes in at an extremely competitive price point, even more so if you
qualify for free use. Both technologies are clear-cut cases of the community banding together
to address its own needs.


linda ahmed anwar
sec 11
cs

http://learning little abot MYSQL :
What is MySQL?
MySQL is a SQL (Structured Query Language) database server. SQL is the most popular database
language in the world. MySQL is a client server implementation that consists of a server daemon
mysqld and many different client programs/libraries.
The main goals of MySQL are speed and robustness.
The base upon which MySQL is built is a set of routines that have been used in a highly demanding
production environment for many years. While MySQL is currently still in development it already
offers a rich and highly useful function set.

See the ‘CREDITS’ file in the distribution for persons that have been involved in the MySQL
project.
History of MySQL
We once started off with the intension to use mSQL to connect to our own fast low level (ISAM)
tables. However, after some testing we came to the conclusion that mSQL was not fast or flexible
enough for our needs. This resulted in a new SQL interface to our database but with almost the same
API interface as mSQL. This API was chosen to ease porting of third party code.
It is not perfectly clear where the name MySQL derives from. Our base directory and a large amount
of our libraries and tools have had the prefix ’my’ for well over 10 years. However, Monty’s daughter
(some years younger) is also named My. So which of the two gave its name to MySQL is still a
mystery, even for us.
The main features in MySQL
Multi-threaded.
C, C++, JAVA, Perl, Python and TCL API’s. See section MySQL client tools and API’s [p 81]
Lots of column types like: signed/unsigned integers 1,2,3,4,8 bytes long, FLOAT, CHAR,
VARCHAR, TEXT, BLOB, DATE, SET and ENUM types. See section Column types. [p 31]
Join optimiser with one-sweep multi-join (all joins made in one pass).
Full function support in the SELECT and WHERE parts. Example: select
column1+column2 from table where column1/column2 > 0
SQL functions are implemented through a very optimised class library and should be as fast as
they can get! Usually there shouldn’t be any memory allocation at all after the query
initialisation.
Full support for SQL GROUP BY and ORDER BY. Support for group functions (SUM, MAX and
MIN).
A privilege and password system with is very flexible and secure. Allows host based verification.
All password traffic on the net is encrypted.

What is the current MySQL version?
You can always check http://www.tcx.se/ for the latest version of MySQL.
How do I get MySQL?
On the Internet, try using a web browser to http://www.tcx.se/
Or ftp to ftp://ftp.sunet.se/pub/unix/databases/relational/mysql/. That isSunet’s (Swedish University
Network) FTP archive in Sweden.
7
Should I get MySQL in source or binary distribution?
If you want to read (and/or modify) the C and C++ code that makes up MySQL you should always get
a source distribution. The code is always the ultimate manual. The source distribution also contains
more tests and examples than the binary distribution.
For most people who want to run MySQL on a platform that has binary releases, a binary version of
MySQL is more convenient. However, TcX’s binary release is not compiled in the same way as the
source release so there are some differences in where support files are located.
Which operating systems does MySQL support?
We use GNU autoconf so it will be possible to port to all modern systems with working Posix threads
and a C++ compiler. The client code requires C++ but not threads. We use the software ourselves
primarily on Solaris (currently 2.5.1) and some on RedHat Linux 4.2 (kernel 2.0.30).
A working Posix thread library is needed for the server. On Solaris 2.5 we use SUN PThreads (the
native thread support in 2.4 and earlier versions are not good enough) and on Linux we use Linux
Threads by Xavier Leroy @email{Xavier.Leroy@inria.fr}.
A good web page about different thread implementations is http://www.humanfactor.com/pthreads/.
The MySQL distribution includes a patched version of Provenzano’s Pthreads from MIT (see
http://www.mit.edu:8001/people/proven/pthreads.html) in thedistribution. This can be used for some
operating systems that does not have posix threads.
We have also tried to use another user level thread package named FSU Pthreads (see
http://www.informatik.hu-berlin.de/~mueller/pthreads.html). Thisimplementation is being used for the
SCO port.
See the thr_lock and thr_alarm programs in the mysys directory for some tests/examples of these
problems. More information can be found in the ‘PORTING’ file in the distribution.
What languages are supported by MySQL.
mysqld can give error messages in the following languages: Czech, Dutch, English (default), French,
German, Norwegian, New Norwegian, Polish, Portuguese, Spanish and Swedish.
To start mysqld with a language use the --language=lang or -L lang switch:
mysqld --language swedish mysqld --language /usr/local/share/swedish
The language files are located (by default) in
‘ mysql_base_dir/share/ LANGUAGE/’
8
How/when will you release updates?
We are going to use the following policy when updating MySQL:
Each minor patch will increment the last number in the version string. When there are new features or
minor incompatibilities with previous versions, the second number in the version string will be
incremented.
On the rare occasions when a fatal bug is found that can’t be avoided we will make new binary
releases for Solaris & Linux as soon as possible. Other people may make binary releases for other
systems but probably less frequently.
For other fatal bugs we will make patches available as soon as we have located and fixed the bug.
For non crucial but annoying bugs we will make patches available if they are sent to me,
otherwise we will combine many of them into a bigger patch.
When there are more then about 10 patches we will make a new full source release.
When we have made a lot of changes we will make a new source and binary release. (About once
a month?)
What is UNIREG ?
Unireg is our tty interface builder, but it uses a low level connection to our NISAM (with is used by
MySQL) and because of this it is very quick. It has existed since 1979 (on Unix in C since ~1986).
Unireg has the following components:
One table viewer with updates/browsing.
Multi table viewer (with 1 scrolling region)
Table creator. (With lots of column tags you can’t create with MySQL) This is WYSIWYG (for
a tty). You design a screen and Unireg prompts for the column specification.
Report generator
A lot of utilities (Quick export/import of tables to/from text files, analysis of table contents...)
Powerful multi table updates (which we use a lot) with a BASIC like language with LOTS of
functions.
Dynamic languages (at present in Swedish and Finnish). If somebody wants an English version
there are a few files that would have to be translate.
The ability to run updates interactively or in a batch.
Emacs like key definitions with keyboard macros.
All this in a binary of 800k.
The convform utility. Changes .frm and text files between different character sets.
The pack_isam utility. Packs a NISAM table (makes it 50-80% smaller). The table can be read by
MySQL like an ordinary table. Only 1 record has to be decompressed / access. Cannot handle
BLOB:s or updates (yet).

What functionality is missing in MySQL.
The following functionality is missing in the current version of MySQL. For the priority of new
extensions you should consult: http://www.tcx.se/TODO
Sub-SELECT. The following will not work in MySQL:
SELECT * from table WHERE id IN (SELECT id from table2)
MySQL only supports INSERT ... SELECT.... Independent sub-SELECTs will be
availably in 3.22.0. One can use the function IN() in other context thought.
MySQL doesn’t yet support sqlSELECT ... INTO TABLE..... Currentlyl MySQL only
supports SELECT ... INTO OUTFILE....
Outer joins. LEFT OUTER JOIN will be availabe in 3.22.0.
Transactions is not supported. MySQL will shortly support atomic operations which is like
transactions without rollback. With atomic operations you can make a bunch of
insert/select/whatever commands and be guaranteed that no other thread will interfere. In this
context you won’t usually need rollback. Currently you can do this with the help of the LOCK
TABLES/UNLOCK TABLES command. See section Lock tables syntax [p 48]
Triggers is not supported. The planed update language will be able to handle stored procedures,
but without triggers. Triggers usually slow down everything, even for queries when they aren’t
needed.
The FOREIGN KEY syntax in MySQL exists only for compatibility with other SQL vendors
CREATE TABLE commands: It doesn’t do anything. The FOREIGN KEY syntax without ON
DELETE .. is mostly used for documentation purposes. Some ODBC applications may uses
this to produce automatic WHERE clauses thought, but this is usually easy to override. FOREIGN
KEY is sometimes used as a constraint check, but this check is in practice unnecessary if one
insert rows in the tables in the right order. In MySQL one can go around the problem that ON
DELETE ... isn’t implement by adding the approative DELETE statement to the application
when one deletes record from a table that has FOREIGN KEY. In practice this is as quick (in
some case quicker) and much more portable than using FOREING KEY Foreign keys is
something that makes life very complicated, because the foreign key definition must be stored in
some database and then the hole ’nice approach’ by using only files that can be moved, copied
and removed will be destroyed. In the near future we will extend FOREIGN KEYS so that the at
least the information will be saved and may be retrieved by mysqldump and ODBC.
MySQL doesn’t support views, but this is on the TODO.
Some other SQL has -- as start comment. MySQL has # as the start comment character, even if
the MySQL command line tool removes all lines that starts with --. MySQL will not support this
degenerated comment style because we have had many problems with automatic generated SQL
queries that has used something like the following code:
20
UPDATE table_name SET credit=credit-!payment!
Where instead of !payment! we automaticly insert the value of the payment. What do you think
will happen when ’payment’ is negative ? Because 1--1 is legal in SQL, we think is terrible that
’--’ means start comment. If you have a sql program in a textfile that contains -- comments you
should use
replace " --" " #" < text-file-with-funny-comments.sql mysql database.
instead of the normal
mysql database < text-file-with-funny-comments.sql
You can also change the -- to # comments in the command file with:
replace " --" " #" -- text-file-with-funny-comments.sql
and change them back with:
replace " #" " --" -- text-file-with-funny-comments.sql
What standards does MySQL follow?
Entry level SQL92. ODBC level 0-2.
What functions exist only for compatibility?
GRANT. See section GRANT syntax. (Compatibility function). [p 49] This always succeeds. You
should use the MySQL privilege tables. See section How does the privilege system work? [p 26]
CREATE INDEX. See section CREATE INDEX syntax (Compatibility function). [p 49] This
always succeeds. You should create your index with CREATE TABLE. See section CREATE
TABLE syntax. [p 36] You can also use ALTER TABLE. See section ALTER TABLE syntax [p
37] .
DROP INDEX. See section DROP INDEX syntax (Compatibility function). [p 49] This always
succeeds. You can use ALTER TABLE to drop indexes. See section ALTER TABLE syntax [p
37] .
Limitations of BLOB and TEXT types
If you want to GROUP BY or a ORDER BY on a BLOB or TEXT field, you must make the field into a
fixed length object. The standard way to do this is with the SUBSTRING functions. If you don’t do
this only the first max_sort_length (default=1024) will considered when sorting.
SELECT comment from table order by substring(comment,20);
How to go cope without COMMIT-ROLLBACK
MySQL doesn’t support COMMIT-ROLLBACK. The problem with COMMIT-ROLLBACK is that
for it to handle this efficiently it would require a completely different table layout than MySQL uses
today. MySQL would also need extra threads that does automatic cleanups on the tables and the disk
usage space needed would be much higher. This would make MySQL about 2-4 times slower than it
is today. One of the reasons that MySQL is so much faster than almost all other SQL databases
21
(typical times are at least 2-3 times faster) is the lack of COMMIT-ROLLBACK.
For the moment, we are much more for implementing the SQL server language (stored procedures).
With this you very seldom really need COMMIT-ROLLBACK besides being able to do many more
things without losing any speed.
Loops that need transactions can normally be coded with the help of LOCK TABLES and one doesn’t
need cursors when one can update records on the fly.
We have transactions and cursors on the TODO but not quite prioritised. If it is implemented it will be
as a option to CREATE TABLE. That means that COMMIT-ROLLBACK will only work on those
tables and only those tables will be slower.
We at TcX have a greater need for a real fast database than a 100% general database. Whenever we
find a way to implement these without any speed loss we will probably do it, but for the moment there
is many more important things to do. Check the TODO for how we prioritise things at the moment.
Customers with extended mail support can alter this slightly, so things may be reprioritised.
The current problem is actually ROLLBACK. Without ROLLBACK you can do anything with LOCK
TABLES. To support ROLLBACK MySQL would had to be changed to store all old records that was
updated and revert everything back to the starting point if ROLLBACK was issued. For simple cases
this isn’t that hard to do (the current isamlog could be used for this), but if one wants to have
ROLLBACK with ALTER/DROP/CREATE TABLE it would make everything much harder to
implement.
To avoid using ROLLBACK one can do:
LOCK TABLES ...
- Test conditions.
- Update if everything is ok.
UNLOCK TABLES.
This is usually much faster, but not always. The only thing this doesn’t handle if someone does a kill
on the process...
One can also use functions to update things in one operation. By doing all updates relatively and/or
only update those fields that actually have changed one can get a very efficient application.
For example, when we are doing updates on some customer information, we only update the customer
data that has changed and only test that not any of the changed data, or data that depends on the
changed data, has changed in the original row. The test for change is down with the WHERE clause in
the UPDATE statement. If the record wasn’t updated we give the client a message: "Some of the data
you have changed has been changed by another user", and then we show the old row versus the new
row in a window. The user can then decide which version of the customer record he should use.
This gives us something like ’column locking’ but actually even better, because we only update some
of the columns with relative information. This means that a typical update statement looks something