Archive for the ‘Basic Necessities’ Category

Good afternoon, Oracle Database Security Professionals!

I was tasked to investigate a Schema that had direct access to some tables it had no business having. I was told to remove this direct access. Should be a quick easy task, right?

  1. I looked at the user’s permissions with SQL Plus. The user had no direct access to any of the tables I was told it had access to.
  2. I created a user with the create like function in SQL Developer DBA View.
  3. I logged into my new user and ran the following query:SQL> select * from all_tables where owner in (‘Schema_1′,’Schema_2’);Wow! My new user had access to all the tables my customer told me it had access to. I thought about public synonyms but that would still require access to the table. I even tried to revoke access to one of the tables. Got something along the lines of “Can’t revoke a privilege that is not granted by Grantee”. I am a SYSDBA. How can I not revoke?
  4. Next, I looked at Grants on the individual table…

grants_on_tables

What’s this? Who is PUBLIC? Well, PUBLIC seemed to be granting access to this table to any Schema or User that can connect to the database. I was quite correct in my assumption. After confirming with the customer, what I did next was to revoke these table privileges as SYSDBA.

SQL> revoke all on “SCHEMA”.”TABLE_NAME” from “PUBLIC”;

The statement below also works but would require 11 steps instead of one.

SQL> revoke SELECT on “SCHEMA”.”TABLE_NAME” from “PUBLIC”;

Finally, I went back to my created user to confirm:

SQL> select * from all_tables where owner in (‘ODS’,’DOC’);

no rows selected

Now that is what I expected. I never knew there was a way to grant public use of objects in Oracle. Now I know. This may be news to some of you as I seem to be constantly learning what I don’t know about Oracle. The bottom line is, be careful of what you grant to PUBLIC.

Thanks for reading!

Jason

Advertisements

Happy Friday, Oracle bloggers and blog followers alike!

Today’s installment comes on the heels of testing the bugfix for BUG:12880299. I will refer to Doc ID 1453883.1 on Oracle Support and maybe a couple other Doc ID’s. I recommend you have these documents open as you read. I ran into a couple issues while following the document. The purpose of this article is to show how I solved the issues I ran into.

One of the first issues I noticed was covered in the Read Me for the patch. Oracle recommends you have the latest version of OPatch installed in your ORACLE HOME. You verify this by running the lsinventory command as oracle in the ORACLE HOME.

$ opatch lsinventory

Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oracle/product/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 11.1.0.6.6
OUI version : 11.2.0.1.0

I looked past this step, stopped the database instances, and attempted to apply the patch

$ opatch apply

ApplySession failed during prerequisite checks: Prerequisite check “CheckActiveFilesAndExecutables” failed.
System intact, OPatch will not attempt to restore the system

OPatch failed with error code 74

After going back to the read me again, I read Oracle’s “recommendation” to have the latest version of OPatch. It even supplies the document to use.  latest version of OPatch In this document there is a link to a video that shows you how to install the latest version of OPatch. Let me clear something up…

You need to download the OPatch version that matches up to your version of Oracle. For example, don’t use OPatch for 12c if you are using Oracle11g.

Once you have transferred Oracle’s download to your patches directory, follow the document’s easy steps.

cd $ORACLE_HOME
mv OPatch OPatch.bkp
unzip <download directory>/p6880880_<version>_<platform>.zip
cd OPatch
./opatch version

–Include OPatch in PATH

$ export PATH=$ORACLE_HOME/OPatch:$PATH

Now, my version of OPatch is up to date with my database version.

$ opatch lsinventory

OPatch version : 11.2.0.3.12
OUI version : 11.2.0.1.0

Now I can run the patch without fail!!

$ opatch apply

OUI-67073:UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.

Ugh. Another stinking issue. The solution will be my next blog topic.

To be continued…

If you are reading this, you have been a DBA for awhile and your company is expanding. Congratulations!

This morning, I had the privilege of interviewing someone in a different department than I work in. They are losing their DBA and my supervisor suggested I work with their director to interview some possible candidates. I am by no means an expert in this category. The last person I helped interview was hired despite my recommendation that we keep looking. I have learned a lot about people and databases since then so here goes.

Prepare the day before

Study the job description(JD) and the candidate’s resume. Is the job description accurate for what the position entails? This sounds funny but this is a necessity. Many times, the job description is a summary of the skill-sets of the outgoing candidate. This person may have been junior when they started but developed their skills over time with the company. If it is not 100% accurate, it is a good idea to let the candidate know beforehand.

Are they a good fit on paper? If their resume does not match up to the JD, should they even be invited to the interview? Chances are, they have already been screened by management or HR but it is still a question that should be asked.

Prepare a list of questions to ask. Use the candidate’s resume and the JD to form a list of ten questions or so that ensure their resume is accurate and they are truly qualified and interested in working for this oraganization.

Arrive early to the location

I was a bit too early to the meeting. There was no one at the receptionist desk to let me into the conference room. That was just fine with me. I went back down to the lobby and waited for the director to show up.

The Interview

Try to make the candidate feel at ease. Nervousness is normal so it is good to take this out of the equation if possible. If you have your list of questions in front of you this should be the easy part. Take notes as they answer the questions so you can compare their answers to the other candidates. Make sure the questions on paper are consistent throughout the interview process.

Ask a couple of tailored questions as the interview progresses. Ask specific questions about their answers that probe deeper than the typical “I like this type of operating system.” Why do you prefer this OS?

Follow Up

In my case, I am not the final decision maker. If you are the one that gets to let the candidate know which direction the organization is going, let them know as soon as a decision is made. If the candidate really wants the job, they probably won’t be able to breathe until they know where they stand. Even if you interview 100 candidates, take the time to write a quick email to each of the candidates. This says a lot about your character.

Good Luck!!

Thanks for reading,

Jason

The DBA Interview

Posted: December 24, 2014 in Basic Necessities

It’s Christmas eve so I thought it appropriate to deviate a bit from Oracle stuff.

It’s wonderful to log in and manage the vast world of technology that is known as the Oracle Database. There’s only one catch. It’s not near as fun if you are not getting paid. You need a job. If you are like me, it it much less hassle to work for someone else. I have known some great consultants and DBA entrepreneurs but if this does not describe you, you are going to have to pass a job interview in order to get paid to manage our favorite database system.

The Phone Screen or Recruiter Interview

This can be performed by the staffing agency or recruiter. This function is sometimes done by the hiring manager so it is prudent to be prepared. The recruiter may meet you for coffee but don’t be fooled into thinking this is a social visit. There is a good chance this is an elimination round. Be prepared to talk about your resume and work history. If it is done in person, dress appropriately. Even if you don’t get a good first impression, there is no harm moving on to the technical interview. I have met recruiters that were less than stellar but ended up interviewing for a great company.

If this is your first time meeting the hiring manager, it will likely be a phone screen. I have been hired over the phone several times so always be ready to close the deal!  In my experience, the phone interview is mostly a social interview. The manager is trying to see if you will be a good fit on his or her team. This is where your strategy comes into play. Try to gauge their personality and leadership style. If the manager is extremely excited about this opportunity, get excited! If they have a sense of humor, laugh and smile a lot. If they are serious and business oriented, follow their cues and match their level of seriousness.  Don’t be afraid to ask professional questions about the job description. It will show you have done your homework and are interested in the position.  I am by no means an Oracle expert but I can say that I have never landed a job based on my technical skills. It has been my social skills every single time. You wouldn’t be there if they didn’t believe you already possessed the technical skills to do the job.

Again, be prepared to defend your resume. Don’t oversell your work experience. It won’t work unless you can state an example for every line in your resume. Your social skills are paramount to passing the first round of cuts and moving on to the technical interview.

The Technical Interview

Count on the technical expert to pore over your resume and look to prove you are who you say you are. This person will likely be a technical lead or a Senior DBA of some flavor or another. Fight fire with fire. It is your responsibility to pore over every detail of the job description well before you walk on stage.  Make sure you not only have an answer for every line of your resume but do the same with the job description. You may need to tailor your resume a bit to match the job description.

If there is a line in the job description that does not describe you, don’t add it to your resume. Instead, pull up the Oracle documentation about the subject. If you don’t know the answer, Oracle documentation will. There are also some great blogs out there that can help you. Be prepared. I always walk into every interview with the same attitude. This is my job to win or lose. They really want to hire you. Don’t give them a reason not to.

I have failed the technical interview before and still received an offer. Some technical interviews are designed to trip you up so don’t let them see you sweat. Just answer the best you can and move on to the next question. The interviewer may be trying to assess the level of expertise you have to see where you will fit in. Are you a senior, mid-level, or junior DBA? This will be especially true in a large shop with a team of DBA’s. It’s okay to say, “I have never worked with that technology.” In my current job, I am working with an Exadata RAC system. I have never worked with Exadata before. The interviewer asked me if I had ever worked with Exadata. After I said no, he answered with, “That’s okay. I haven’t interviewed anyone who has.” Had I lied, he may have come back with a round of questions to prove I had the experience.

Follow up

I still believe in thank you notes. You may not have time to use the postal system. They will likely make a hiring decision in the next couple of days. I believe it is acceptable to send a thank you note through email. If you don’t have the hiring manager’s email address, send it to the recruiter, thanking him or her for the opportunity to interview for the position. Politely ask if they would forward the email to the hiring manager. This should be done within an hour or two of the interview.

I am an Oracle DBA, not because of my ingrained talent or vast experience. I am an Oracle DBA because of the people that have helped me get here and because I take the time to work on my people skills. My mother worked herself into senior leadership in an investment firm without a college degree or advanced certifications because she learned people skills and worked extremely hard. The certifications came later and at the company’s expense. Don’t be one of those people who has mad skills and education but can’t land the job because of your reclusive personality. Leave that persona to the software engineers (just kidding).

As always, thanks for reading. Good luck with your interview! Please post your personal experience in interviews. Your words of advice can help all of us…

Merry Christmas!

Take care,

Jason

Welcome back, Oracle fans!

 

When you get a new laptop, one of the first tools an Oracle DBA needs is the Oracle Client. Here are a few steps that will make your life easier before and after downloading the client.

 

1) Map network drive to your tnsnames.ora file.  http://windows.microsoft.com/en-us/windows/create-shortcut-map-network-drive#1TC=windows-7

2) Go online and install 11gr2 client (as administrator).  http://www.oracle.com/technetwork/database/enterprise-edition/downloads/ 

3) Set environment variable

START-->System and Security-->System-->Advanced System Settings-->Environment Variables-->System Variables

Select new.

Enter TNS_ADMIN. In the value column, set it to the tnsnames.ora file location you set in step 1.

These steps seem simple and they are. If you skip steps one or three, you may be wondering why your Oracle development tool of choice can’t find your databases.

 

I hope this helps,

Jason