|
July, 2005
In
This Issue
|
|
All articles have been reprinted with the written consent of their respective authors. | ||||||||||||||||
|
||||||||||||||||
|
Use of Grouping_ID in Summary Queries This is a way to analyze grouped data when grouping more than one column with GROUP BY ROLLUP(col1,col2,..) using GROUPING_ID(col1,col2,..).
Click here
for the script. |
||||||||||||||||
|
||||||||||||||||
|
Protect from Prying Eyes: Encryption
in Oracle 10g, Part 2 In Part 1 of this article, we discussed some of the basics of data encryption, including what is encryption, encryption algorithms, what type of encryption scheme to use when, and more. Now, let’s take a look at the other side of encryption: decryption and its elements. The DECRYPT function in the Crypto package provides the reverse of the encryption. Let’s decrypt the value we encrypted in part 1 of this article. Since the encryption value is in RAW, we can write the encryption and decryption in one session and pass the encrypted value. For this, we have defined a SQL*Plus variable named enc_val, as shown in line 3 in the following code. Lines 4 through 21 show the piece of code used to perform encryption of an input value called “ConfidentialData” and the rest of lines show the decryption of the encrypted value in the variable enc_val.
Click here
for the paper. | ||||||||||||||||
|
||||||||||||||||
|
How to Lock Down Enterprise Data with
a Scalable Infrastructure Service How do you protect privacy at the level of individual records in
applications, databases, and file systems? As data resources become
networked in more complex three tier e-business applications, their
vulnerability to external attack grows. Although access control has
been deployed as a security mechanism almost since the birth of
server systems, many still look at data security as a problem to be
addressed as the need arises – this is often after threats to the
secrecy and integrity of data have occurred. While laws and
regulations interpret "protecting privacy" in a number of ways, any
enterprise solution for protecting data — especially data at rest —
must involve two things: secure encryption technology to protect
confidential data and careful management of access to the
cryptography keys that unlock the encrypted data. This paper will
show how to design information systems that protect the privacy and
ownership of individual information while not impeding the flow of
information. We address scalability as a particularly vital problem
and propose alternative solutions for data encryption as an
enterprise IT infrastructure component. In this paper, we explore a
new approach for data privacy and security in which a security
administrator protecting privacy at the level of individual fields
and records, and providing seamless mechanisms to create, store, and
securely access databases. Such a model alleviates the need for
organizations to purchase expensive hardware, deal with software
modifications, and hire professionals for encryption key management
development tasks. Instead of building walls around servers or hard
drives, a protective layer of encryption is provided around specific
sensitive data items or objects. This prevents outside attacks as
well as infiltration from within the server itself. This also allows
the security administrator to define which data stored in databases
are sensitive and thereby focusing the protection only on the
sensitive data, which in turn minimizes the delays or burdens on the
system that may occur from other bulk encryption methods. In this
paper we will outline the different strategies for encrypting stored
data so you can make the decision that is best to use in each
different situation, for each individual field in your data store to
be able to practically handle different security and operating
requirements. Click here
for the paper. |
||||||||||||||||
|
||||||||||||||||
|
Unloading Data Using External Tables in Oracle 10g
This is an introduction to a new feature of
external tables in 10g, namely the ability to unload data from the
database using SQL. Introduced in 9i, external tables provided a new
means of reading flat file data without first loading it into a
staging table. They offered additional benefits over traditional
SQL*Loader methods in that loading data became as simple as an
INSERT..SELECT, with all the SQL processing and manipulation options
that such a statement implies. It also meant that more of the ETL
application could be self-contained in PL/SQL packages, as call-outs
to SQL*Loader or batch scripts were no longer necessary to load
data. Oracle 10g has taken external tables a stage
further by enabling an external table to be created as a CTAS (
Create Table As Select ) operation, which enables a one-time
unloading of data. When I first saw the headlines about "writing" to
an external table, I must admit to being excited by the
possibilities of a native data unloader ( no more sqlplus spools,
UTL_FILE writes, Pro*C unloaders etc ). Alas, this is not the case.
Oracle has in fact supplied a new access driver known as "ORACLE_DATAPUMP"
which means that Oracle will "unload" an external table to a
proprietary format usable by the Data Pump set of utilities (
significant new 10g feature to replace imp and exp ). So, generating
an ASCII, CSV file from external tables is not possible, but this
new feature is exciting nonetheless.
Click here for the paper. |
||||||||||||||||
|
||||||||||||||||
|
Understanding Microsoft Internal Storage Management Microsoft SQL Server is built in such a way that it can be managed by Database Administrators (DBA) with a wide range of skills and experience. SQL Server masks the complexity of managing a Relational Database Management System (RDBMS) to make it more appealing to administrators with various skill sets. Some database administrators can manage their SQL Servers without having to know the internals of SQL Server, However, as the size of the database grows, tuning and optimization becomes critical to the health and performance of SQL Server. The focus of this paper is to help understand the way SQL Server manages and allocates storage internally. Understanding this process will lead to more efficient space management, application development, and data retrieval.
Click here for the paper. | ||||||||||||||||
|
||||||||||||||||
Once your project is approved and ready to execute, a project team must be put together. Some of the resources might be full-time, some part-time. You may have a mix of contractors and employees. In many cases, the employee team members are assigned based on availability and best fit. However, in many cases, you need to hire for the positions. These could be employee hires or contractors. The interview process is important – even more so if the person
will be a full-time employee. Here are a few simple rules to
remember before your interview.
Click
here
for more information | ||||||||||||||||
|
||||||||||||||||
|
Webcasts
| ||||||||||||||||
|
||||||||||||||||
|
||||||||||||||||
|
||||||||||||||||
|
Interactive Crossword
Puzzle: "Name That Car"
|
||||||||||||||||
|
||||||||||||||||
|
We love getting white papers, tips, articles, and code examples/archives from our readers around the world. Send your submission to newsletter@quest-pipelines.com. If your article is published, it will be mailed to over 28,000 subscribers. Our mission at Quest Software - RevealNet Labs is to anticipate the daily responsibilities and challenges faced by database professionals. Our products help thousands of people solve problems and implement solutions every day. This newsletter is designed to help facilitate the sharing of information among database professionals. About the Newsletter This newsletter is distributed to Quest Software - RevealNet Labs customers, prospects and friends who have subscribed to it from our website. If you would like to unsubscribe, please visit http://qlist01.quest.com/UnsubMailingList/ Subscribe a Friend! Do you know someone who would like to receive the Pipeline Newsletter? If so, please enter their email address in the box below and click SUBMIT.
Past Issues of the Pipeline Newsletter |
||||||||||||||||
