Administering User Security in Oracle Database 12c R2

May 25, 2018 at 4:24 pm | Posted in Oracle, study tips, Technical Tips | Leave a comment
Tags: ,

Let’s talk about administering user security. If you’ve been studying for your Oracle Database 12c R2 Administration Certified Associate exam, you’ll know there are several security sub-components to be aware of. You have to understand how to create users, grant privileges, create and grant roles, revoke, and create and assign profiles. You also have to know how to authenticate users and assign quotas to specific users as well.

These topics, and specifically the topic of managing complex passwords, remind me of a scene in one of my favorite movies, Ironman 3. Tony Stark (aka Ironman) is self-implanting an electronic device in his arm to prepare for what he describes as the “big brother” to the current Ironman suit, called the Mark 42. He describes this event as the “Autonomous freehand self-propulsion test,” which allows him to just move his arms and call out the different components of his Ironman suit with biometrics.

Just like the old Ironman suit was replaced by the Mark 42, the 11g VERIFY_FUNCTION and VERIFY_FUNCTION_11G password verify functions are deprecated in the 12c release. Those weaker password restrictions have been replaced with stronger authentication verification in 12c. Oracle 12c R2 provides a SQL file named CATPVF.SQL  under the standard $ORACLE_HOME/rdbms/admin location. This SQL files holds three specific password verification functions. The purpose of these functions is to make sure that all the users within the database are modifying and creating passwords that are considered complex and meet critical requirements set by the Department of Defense Database Security Technical Implementation Guide, Department of Defense Database Security Technical Implementation Guide requirements and the Security Technical Implementation Guides (STIG) requirements.

The functions are:

  • No fewer than 8 characters
  • Must include one alphabetic character and one number
  • Excludes both the username and the reverse of the username
  • Excludes the name of database
  • Excludes oracle and oracle123
  • Must differ from the previous password by a minimum of 8 characters
  • Holds one special character
  • Must have two special characters, two numeric characters, and two upper-case characters
    • Special Character List (‘# ~ ! @ $ % ^ & * ( ) _ – + \ = { } [ ]\ / < > , . ; ? ‘ : | (space))
  • Must be different from the last password by four characters
  • Must be 15 characters
  • Must include one upper-case and one lower-case character
  • Must have one digit
  • Requires one special character
  • Must differ from your last password by eight characters

Can you imagine using authentication that involves embedding a device under your skin? Maybe that’s just science fiction and maybe that’s going to be the norm at some point. Now, just maybe, Tony Stark’s “Autonomous freehand self propulsion test” is using a three-tier backend Oracle 12c R2 database architecture that is already using these advanced complexity functions. Only time will tell, but just know that security is a living, breathing entity that continues to advance into the future.


John Brooks


Customer asks: now that 1Z0-051 is retired, what Oracle exam should I take?

April 30, 2018 at 6:25 pm | Posted in Kaplan IT Training news, Oracle, Vendor news | Leave a comment
Tags: ,

Today marked the retirement of a core OCA level exam: the 1Z0-051 exam, Oracle Database 11g: SQL Fundamentals I.

As is often the case when a still-active certification path loses a core exam, customers wanted to know what to take instead. In this case, three active certification paths were affected by the retirements:

  • Oracle Database 11g Administrator Certified Associate
  • Oracle PL/SQL Developer Certified Associate
  • Oracle Database 12c Administrator Certified Associate
Fast answer: what’s the immediate substitute for 1Z0-061 and 1Z0-051?

For all three certification tracks, you can substitute exam 1Z0-071. This exam was previously referred to as Oracle Database 12c SQL , leading to some confusion among those still pursuing an 11g certification path. However, with the retirement of 051, this exam is now simply called the Oracle Database SQL exam. This is the general associate-level Oracle Database SQL exam, and the training is the same.

What’s retiring next?

On October 31, 2018, Oracle will retire the 1Z0-061, Oracle Database 12c: SQL Fundamentals. Once again, this exam will be replaced in the relevant certification tracks with exam 1Z0-071.

What’s the best way to prepare?

As the authorized practice test provider for Oracle, Kaplan IT Training offers practice tests for Oracle database and Java developer exams. Just choose Oracle as the vendor to browse our complete lineup of exam prep materials.

Happy certifying!


Last Chance to Save (NO EXCLUSIONS)

December 15, 2017 at 11:06 am | Posted in Careers, Certification Paths, LPI, Microsoft, Oracle, PMI | Leave a comment
Tags: , , , , , , , , , , , , , , , , ,

After a long 2017, it’s now that time of year to think about what you should give up or do differently for 2018. Are you planning on losing or gaining more weight? Exercising more, watching more streaming shows,  or curling up with more good books? Perhaps picking up a new hobby or going back to a project you once abandoned?

We have a better idea. One that could translate into a higher salary or better career. Why not make getting an IT or project management certification as one of your New Year’s Resolutions? From the highly sought-after certifications like CISSP, CEH, and Security+ (just released the new version last month) to the latest Microsoft Azure, Windows, and security certifications, Transcender has got you covered!


And this time, it’s no exclusions – opening up the discount to our up-to-date eLearning (now including Juniper to Amazon Web Services) to get started and cloud-based labs to help you hone your new skills . Hurry now. This deal is too good to last into 2018, so you better act now!

Oracle Redaction in the 12c Database: Advanced security for regulatory compliance

May 8, 2015 at 3:56 pm | Posted in Oracle, Technical Tips | Comments Off on Oracle Redaction in the 12c Database: Advanced security for regulatory compliance
Tags: ,

Oracle Redaction in the 12c database is part of the Oracle Advanced Security package. It can be used as a standalone feature, or together with other components of Advanced Security. Oracle redaction allows you to set up policies so that when column data is retrieved, it can be masked or hidden from the person performing the query. It can affect all or part of the data in a column and the data can be any of the more common datatypes that Oracle supports. Redaction allows you to comply with industry regulations such as the Payment Card Industry Data Security Standard as well as the Sarbanes-Oxley Act.

You’ve probably already seen this feature at work when you receive documents from the government or your bank with key data partially hidden, such as social security numbers and credit card numbers. Social security numbers are commonly masked to allow only the last 4 digits to be read, appearing as ***-**-1234. Salary is another column that is commonly redacted in reports.

The key to using redaction is the Oracle supplied package DBMS_REDACT and the procedures and functions contained within it. The procedure DBMS_REDACT.ADD_POLICY allows you to set up a policy on a column of a table or view. For example, view the following statement:

object_schema   =>  ‘bob’,
object_name     =>  ‘employee’,
column_name     =>  ‘salary’,
policy_name     =>  ‘protect salary in bobs employee table’,
enable          =>  TRUE

This statement will create a new policy with the given name on the salary column of the employee table in the schema called bob. Note that all the arguments of this procedure are IN, and that the arguments shown are all VARCHAR2 with the exception of enable, which is BOOLEAN. This policy will be enabled as soon as it’s created, and it will apply to all users except SYS and any other user who happens to have the system privilege called EXEMPT_REDACTION_POLICY. Further, it will redact the entire salary column and use a 0 as the default masking character, since the datatype of the salary column is number.

All of these defaults can be changed to create complex rules with the logic encapsulated in the policy. For example, you may want the policy to apply only to users who have a particular role or who are logged in at a particular time. You also might want the salary to be displayed as a series of $ signs instead of a zero, and if you were performing redaction on social security number, you may only want to redact part of the column so that the last 4 digits are still visible. This procedure has additional arguments that allow you to do all of these things and more. You can even redact a number column such as salary so that the column appears as a random number rather than the true value or a 0. I’m hard pressed to think of a business example where you might want to use this feature, so if you have a  good example, PLEASE reply to this blog and let me know in the comments!

Redaction should apply to read-only situations, since you don’t want a user to update a column in a row without being able to see the old value in the column, or even the new value for that matter.

As far as SQL statement processing is concerned, the redaction is the very last thing that occurs.  In the past you may have said to yourself that the last thing done in a SELECT statement is to sort the returned rows if the ORDER BY clause is part of the query.  Now we have one more potential step that might be performed on the returned rows after the sort: namely, the redaction.

This post just scrapes the surface of everything you can do with this tool.  My hope is that I’ve been able to stir your interest in this topic if you have applications where redaction  is appropriate.  More information about redaction can be found in Chapter 9 of the Oracle 12c Advanced Security Guide.  Click this link if you’d like to become more familiar with this tool:


Bob, the OrclTestGuy

Oracle One-Test Catch-Up Certification Opportunities for OCA and OCP in 12c

October 14, 2014 at 9:01 am | Posted in Certification Paths, Oracle | Leave a comment
Tags: , , , ,

Are you one of those individuals who has allowed your Oracle Database Administrator or Oracle Developer Certification to expire because you’ve been too busy to keep up with the required exams – not to mention any potential classroom training? Is your most recent Oracle certification back at release 9i (or even Oracle7), and are you looking for a way to pole-vault to a newer release, such as 11g or 12c?

There’s good news for you folks. Oracle has provided some new certification paths to help you upgrade to 11g or 12c via a single exam (plus any required training).  Here are the specifics on the new upgrade paths.

Oracle Certified Professionals (OCP): 7, 8, 8i, 9i, 10g, 11g to OCP 12c

If you are an existing Oracle Database Administrator Certified Professional (OCP) from Oracle7 or above, you now have a one-exam option to upgrade to the new  Oracle Database 12c Administrator Certified Professional certification with no required training. Exam 1Z0-060, Upgrade to Oracle Database 12c, is available from Oracle.

upgrade all to12c 060

Note that while coursework is not mandatory, Oracle does recommend you take the optional training course, Oracle Database 12c: New Features for Administrators.

Oracle DBA Certified Associates (OCA): 9i, 10g, 11g to OCP 12c

If you are an existing Oracle Database Administrator Certified Associate (OCA)  from Oracle9i or above, you now have a one-exam option to upgrade to the new  Oracle Database 12c Administrator Certified Professional certification. Exam 1Z0-067, Upgrade Oracle9i/10g/11g OCA to Oracle Database 12c OCP, is available from Oracle.

Upgrade to 12c OCP with exam 1Z0-067.

Oracle has traditionally required that you take classroom instruction when upgrading from an OCA to an OCP, and it is required that you complete one course before sitting the exam. You can view the list of qualifying courses here.

Oracle Certified Associates (OCA) 12c to OCP 12c

If you’ve already earned your Oracle Database Administrator Certified Associate (OCA) in Database 12c, you can take one required course (listed on the Professional Level Certification tab) plus exam 1Z0-063, Oracle Database 12c: Advanced Administration.

upgrade 12c OCA to OCP

Oracle Certified Associates (OCA): Upgrade 9i or 10g to 11g OCP

If you currently have your 9i or 10g OCA for Database Administration, and you’re not ready to go all the way to Database 12c, then successfully completing exam 1z0-034, Upgrade Oracle 9i/10g OCA to Oracle Database 11g OCP plus one required Oracle class will upgrade your current OCA certification to the 11g Oracle Database 11g Administrator Certified Professional certification.  You’ll want to review the exam topics for 1z0-034 on the Oracle certification website (choose the tab Exam Topics next to the Exam Preparation tab).

upgrade to 11g 034

Again, Oracle requires classroom instruction when upgrading from an OCA to an OCP. You can view the list of qualifying courses here.

Oracle Certified Professionals (upgrade to 11g or 10g)

Oracle also retained the earlier one-exam upgrades for the older generations of Oracle.

Is the one-exam approach right for you?

A single-exam upgrade to jump from Oracle7 or Oracle9i to Oracle Database 12 can be an amazing bargain for the right certification candidate. Some potential drawbacks to this approach, however, would be that the candidate must take a more difficult exam with a much larger question base than the tests in the conventional two-exam upgrade path. Where a conventional two-exam certification might have an 80-count or 90-count question pool, there can be up to three times as many in the all-in-one test. You must be sure to allocate enough study time to cover the volume of material that you’ll be tested on.

Another consideration is whether the candidate with an older generation of Oracle OCA needs certification in 11g. Earning a 12c certification does not automatically add the corresponding 11g credentials to your transcript. You must be separately certified in 11g to add that credential, which may be the one a potential or current employer is looking for.

Transcender has you covered with Oracle test prep products. We have already released Cert-1Z0-034, Cert-1Z0-060, and Cert-1Z0-067. And, for those who wish to move forward with new Database 12c certifications, we have 1Z0-063 in production, with a projected publication date late in the fourth quarter.

Best of luck!

Bob Bungenstock, aka OrclTestGuy

Oracle rolling out 12c certifications; grab your exam in beta for a substantial savings

December 26, 2013 at 8:48 am | Posted in Certification Paths, Oracle, Vendor news | Leave a comment

Oracle has recently released or announced the incipient release of several Oracle Database 12c exams. While still in the beta stage, exams are only $50; once released to production they are available for full price, typically $245. If you think you have the chops to ace the exam in beta, it’s an amazingly good value (however, there’s nothing wrong with taking the time to bring yourself up to speed on a new generation of tech).

The following exams have been released to market, and are available from Pearson Vue:

The following will be released in beta soon:

To learn how to register for a beta exam, visit Oracle’s beta exam information page.

To find out about upcoming beta exams, visit Oracle’s Certification Blog.

As a final reminder, Oracle will be retiring several MySQL 5 Database exams in March 2014. For more information, visit Oracle’s exam and certification retirement page.

Oracle University is seeking YOUR Java EE 7 input for a Job Task Analysis Survey

September 26, 2013 at 9:17 am | Posted in Oracle | Leave a comment
Tags: , , ,

Have you ever wondered how vendors actually build their certification exams, or choose the learning objectives for their courseware? Although they employ instructional designers and content writers, vendors also rely heavily on subject matter experts: the people “in the real world” who use the technology day in and day out.

Oracle Certification and Server Technologies Curriculum Development are in the process of building two new Java EE7 Certification exams. They write:

An Oracle certification is typically associated with a particular role related to a single technology. In the case of Java EE7, the technology is very broad and comprehensive. We are looking to you and the industry to validate our definition of these job roles. Our assumption and preliminary investigation shows that programmers and developers working with Java EE technology design and create applications using front-end technologies and/or server-side enterprise technologies.

If you have two to four years experience using the previous Java EE technology versions, or if you are involved in hiring Java programmers for front-end and server-side development, then Oracle wants your input to help shape the next release of certification exams.

Click the link to go to the survey (closes on 30 November 2013):

New to Oracle 11g: The Server Result Cache

November 5, 2012 at 12:30 pm | Posted in Oracle, Technical Tips | Leave a comment
Tags: ,

If you’ve been using the Oracle database for as many years as I have, I’m sure you’ve noticed how Oracle tends to tweak the contents of the SGA from one release to the next. Oracle release 11g is no exception to that trend, and starting with this release we now have two more caches in the Shared Pool: one called the SQL Query Result Cache and another called the PL/SQL Function Result Cache. These two caches differ from virtually all of the remaining memory areas of the SGA in that the data stored in these two buffers are NOT blocks of data. Rather, the data stored is stored in terms of returned rows from either a SQL or PL/SQL command.

Why might Oracle choose to store the results of a user’s SQL command or the return results of a user’s PL/SQL function? Simply put, if a request for a query comes along that is identical to a previous query request that Oracle had stored in one of these buffers, then it’s possible for Oracle to re-use that prior result set. This technique is even better than checking to see if the commands are identical, so you can potentially save reparsing. If the prior result set matches the new request, and the prior result set is stored in the buffer, then we can skip the effort of parsing as well as any logical or physical reads to bring blocks into the database buffer cache. Obviously, Oracle has to be sure that the result set is still accurate before deciding to use it based on a subsequent SQL statement.

Oracle has the overall responsibility for managing these two new memory areas, but you can influence the optimizer using hints such as RESULT_CACHE, the DBMS_RESULT_CACHE package, and the RESULT_CACHE_MODE init.ora parameter. The following link in the Oracle 11gR2 documentation set,,
will take you to an overview explanation of these two buffer caches. It will also provide links to the details of the SQL Result Cache and another to the PL/SQL Function Result Cache.

Until next time,
— Bob the OrclTestGuy

“C” Is For Cloud

July 31, 2012 at 8:34 am | Posted in Oracle | Leave a comment

It’s possible that you may have seen Larry Ellison, the CEO of Oracle Corporation, discussing Oracle’s philosophy, offerings, and advantages as it relates to cloud computing. It’s one of Larry’s favorite topics, and to hear him speak he’d say that cloud computing is something that Oracle has been working on for almost 7 years. And what Larry is referencing is Oracle’s efforts to rewrite all of Oracle’s ERP applications into something called Fusion. Fusion is Oracle’s Open Source project to replace the entire stack of applications from Oracle’s e-business suite, PeopleSoft, and JD Edwards. The fusion applications (of which there are just over 150) and its associated 11g R2 database, along with the Enterprise Manager 12c Cloud Control, provide you with the power to monitor and manage all of these features, regardless of whether your application is sitting on a server in your own datacenter (private cloud) or a server in Oracle’s datacenter (public cloud). And because of the Open Source standards, it’s easy to move your application from your datacenter to Oracle’s, or from Oracle’s datacenter back to your own.

Oracle’s initial Application Services offerings appear to be in the areas of Fusion CRM and Fusion HCM. Customer Relationship and Human Capital comprise a big chunk of the ERP, but it’s certainly not all-inclusive. I think we can safely assume that more components should be arriving shortly. Each application also provides “built-in” social networking to identify teams and provide tools for collaboration among the team members. This appears to be something Oracle picked up in its recent acquisition of the social media company Involver. And the data that Oracle collects from its social media links will be thoroughly analyzed to detect trends, buying habits with the help of the purchase of another company specializing in social analytics, All Things Digital.

Oracle hasn’t ignored those who may want to build their own custom cloud solutions using open standards. Oracle’s public cloud currently provides platform services for a Java Application Server as well as the 11gR2 database. And Oracle emphasizes the ease of moving applications from your private cloud to a public cloud, and then to another public cloud, and so on back to your own private cloud. According to Oracle, this is all made possible because open standards (such as SQL and Java) are used to build the apps. And the tool which allows Oracle to deliver the database as a service is called vFabric Data Director 2.0 from VMware.

I believe the jury is still out on whether this technology is here to stay. For organizations who want to simplify their investment in IT or provide opportunities for their developers to pursue more challenging opportunities this may be a wise decision. If you have any experience with any vendor’s public cloud, please share them with us on this blog.

Thanks for reading and sharing!

MERGE your application to improve performance

April 23, 2012 at 2:09 pm | Posted in Oracle, Technical Tips | Leave a comment
Tags: ,

Hello, Oraclites. Have you taken a look at the new (well, relatively new) MERGE command that is now part of ANSI SQL?  In many cases, incorporating the MERGE command into your application can replace writing a 10 to 20 line program in PL/SQL. And that will lead to significantly improved performance.  This is definitely one of those cases where “less is more.”

How does MERGE work?

The MERGE command functions as either an INSERT command or an UPDATE command all in one,  depending upon a condition that you specify.  And just like INSERT and UPDATE, the new MERGE command is a DML command.  If you totally hose your data trying out the new MERGE command, you can do a ROLLBACK.  No harm, no foul.

Here’s an example that everyone can probably relate to.  WIDGETS Incorporated has 10,000 active and inactive employees, and thus 10,000 rows in the emp table.  The column status contains an I for inactive employees, and an A for active employees.

Here is the description of the emp table:

id          NUMBER(5) PRIMARY KEY
name        VARCHAR2(20) NOT NULL
dept        VARCHAR2(10) NOT NULL
salary      NUMBER(10,2)
hire_date   DATE
status      CHAR(1)

There’s also a new_emp table created by HR and used nightly to update the emp table. HR assigns a new unique ID to new employees who have never worked for WIDGET before, but those who have are reassigned their previous ID. The number of rows in new_emp varies night by night, but averages around 20 rows. Usually one or two out of the 20 represents a rehire. The structure of the new_emp table is exactly the same as the emp table; only the data is different.

What HR wants to do, and the MERGE command allows them to accomplish, is to add the personnel data for a new hire who has never before worked for the company, and assign that employee a status of A (for active). In the same pass through the data, the command should update the emp table by locating inactive employees who have been rehired.  When those records are found, the status should be changed from Inactive to Active, and the starting salary when this person is rehired should be updated in his or her row in the emp table. We’ll take care of updating the other columns later.

USING (SELECT id, name, dept, salary FROM new_emp) N
ON ( =
WHEN MATCHED THEN UPDATE SET E.dept = N.dept, E.salary = N.salary, E.hire_date = sysdate, E.status = ‘A’

This should solve their problem!  The merge (either Inserts or Updates) are going to happen to the emp table.  We are going to use the data returned by the SELECT to compare each returned row with each row in emp to determine if the values of id are equal.  If they are, we’ll do the update in the “WHEN MATCHED” clause.  If they aren’t equal, we’ll do the INSERT in the “WHEN NOT MATCHED” clause.

That’s pretty neat, and what’s really great is we don’t need to write a program to accomplish it. Furthermore, we can deal with all the rows in a single pass of the data.

If you like this, take a look at multitable INSERTS, another great feature for developers and DBAs.

–Bob the OrclTestGuy

Next Page »

Entries and comments feeds.

%d bloggers like this: