Oracle Database 11 g is a comprehensive platform database, there are many features of the Oracle product that support different kinds of companies worldwide. This writing assignment will present an overview of some Oracle features. More specifically this paper will focus on the main features of oracle database administration, like security, indexing constraints and views. Basic user security Security refers to activities and measures to ensure the confidentiality, integrity, and availability of an information system and its main asset, data. (Coronel, 2010).
Database security consists of the mechanisms that protect the database against intentional or accidental threats. Security considerations apply not only to the data held in a database but security breaches can affect other parts of the system. Database security takes into consideration hardware, software, users and the data. To effectively implement security requires appropriate controls, which are defined in specific mission objective. This need for security, while often having been neglected or overlooked in the past , is now increasingly recognized by organizations.
The reason for this turnaround is the increasing amounts of crucial corporate data being stored on computer and the acceptance that any loss or unavailability of this data could prove to be disastrous. . ( Connolly, 2005). Oracle, like the rest of the computer industry, has provided a large sampling of security settings for the database. The basic levels of security break down to account, access, and system level security. To access a database, a user must run a database application and connect to the database instance using a valid user name defined in the database.
For Oracle, the creation of a new user with password authentication must be set. Whenever the user attempts to connect to the database, this user will be presented with a Connect or Log On dialog box, prompting for a user name and password to access the specified database. (Connolly, 2005) Apart for authentication and authorization there are three other areas of database security that this paper will discuss which are privileges and roles. Database security strives to insure that only authenticated users perform authorized activities at authorized times and there are many concepts as it relates to the security of a database.
The main methods use in ensuring security of database include authorization, access control, views and integrity constraints. Authorization and authentication deals with the granting of access to the database and helps determine whether a user is who he or she claims to be. These methods control the access by defining each user to the database, assigning passwords to each user, defining user groups, assigning access privileges and controlling the physical access. (Coronel, 2010). Views also act as a form of security by restricting certain parts of the database from different users.
Integrity constraints also add to ensuring database security by preventing data from being invalid, and hence giving misleading or incorrect results. Although access control and the methods briefly described above are the centrepiece of database security, with the widespread use of the internet, a database administrator must be concerned with securing the perimeter and be involved directly and/or indirectly interacting with system administrator, web-administrator in other areas of security. (Murray, 2010).
Because roles allow for easier and better management of privileges, privileges should normally be granted to roles and not to specific users. Finally the overall Oracle system has its own security to control new table creation, or deletion. These combine to present a very complex and configurable security model. (Connolly, 2005). Describe the purpose of an index Connolly describes an index as a structure that provides faster access to the rows of a table based on the values of one or more columns. The presence of an index can significantly improve the performance of a query.
However, since indexes may be updated by the system every time the underlying tables are updated, additional overheads may be incurred. Indexes are usually created to satisfy particular search criteria after the table has been in use for some time and has grown in size. The creation of indexes is not standard SQL. (Connolly, 2005). Using primary key and unique key constraints to manage indexes on primary key and unique key columns. Oracle Database enforces a unique key or primary key integrity constraint on a table by creating a unique index on the unique key or primary key.
This index is automatically created by the database when the constraint is enabled. No action is required by you when you issue the create table or alter table statement to create the index, but you can optionally specify a using index clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled. To enable a unique or primary key constraint, thus creating an associated index, the owner of the table must have a quota for the tablespace intended to contain the index, or the unlimited tablesapce system privilege.
The index associated with a constraint always takes the name of the constraint, unless you optionally specify otherwise. (Oracle, 2008) Describe why or why not foreign key columns should be indexed Index prefixes on foreign key columns are not normally supported in Oracle. Updating a primary key (you shouldn’t do that anyway) or deleting a row in the parent table needs to make sure that no entry referencing the old row is created in the child table until the transaction commits.
Stellato recommended to create an index which leads on the foreign key column(s), to support not only joins between the primary and foreign keys, but also updates and deletes. (Stellato, 2012). Use of views and synonyms to provide application transparency Creating a synonym that points to another object eliminates the need to specify the schema owner or name of the object. This lets you create a layer of abstraction between an object and the user, often referred to as object transparency. Synonyms allow you to transparently manage objects separately from the users who access the objects.
You can also seamlessly relocate objects to different schemas or even different databases. The application code that references the sequences doesn’t need to change—only the definition of the synonym. (Kuhn, 2012). Uses of materialized view A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. It enables you to store data retrieved by the view query and reuse this data without executing the view query again.
These views are often referred to as “snapshots,” as they take a picture or capture a set of data at a specific point in time. Below Casteel describes a list of uses of materialized views. 1. Complex queries or queries on large databases often require a lot of processing, which can affect system users. Most businesses want to maintain optimal performance for transactional processing. Replicating data for reporting and analysis allows dedicating system resources to transactional processes. 2.
Remote users could improve query performance by replicating data to a local database. Instead of transferring data across long distances, a local copy could be used for satellite offices. 3. Data analysis needs might require freezing data for a specific time for comparison purposes. (Casteel, 2009) Metadata Metadata simply put is “data about data. ” When talking about databases metadata can also be called the data dictionary. It is described as data that provides information like data type, length, textual description, and other characteristics of the data. Oracle, 2005). Along with managing data, database administrator must to be able to manage and control the definition of the data elements used in databases. Data easily misinterpreted or misused by users, developers and administrators, if they do not the understand the structure, the limitations, the definition, and the description of data. Which will eventually lead to database integrity problems. Developers and administrators make regular use of the metadata to help them better understand about database objects and the data contained in the database.
And users of the database, must know what the data is before it becomes useful as information. Information about data is referred to as metadata. Metadata characterizes data. It is used to provide documentation such that data can be understood and more readily consumed by a company. (Taylor, 2003). The data dictionary is used to store information about database objects and is a vital store of metadata, just about any type of descriptive information about the composition of the data is stored. (Oracle, 2005). Tuning SQL statements
Tuning is an ongoing effort to make your databases process information efficiently. The process of SQL tuning is of making sure that the SQL statements that the database management system will issue runs in the fastest possible time. It provides an easy and optimal way to discover efficient paths for queries that may not be performing as quickly or as efficiently as they could be. Tuning analyzes a SQL statement and provides alternative paths to the database management system. (Casteel, 2009) Steps one might take to tune an SQL statement involves three basic steps : 1.
Identifying high load or top SQL statements that are responsible for a large share of the application workload and system resources, by reviewing past SQL execution history available in the system. 2. Verifying that the execution plans produced by the query optimizer for these statements perform reasonably. 3. Implementing corrective actions to generate better execution plans for poorly performing SQL statements. These three steps are repeated until the system performance reaches a satisfactory level or no more statements can be tuned. (Oracle, 2008).