Oracle Database – ORA-01017 when running “sqlplus / as sysdba”

 I had a new oracle database server that was working great, and after some security, changes to tighten up the server, the operating system user was receiving an ORA-01017 error every time it tried to connect using the “sqlplus / as sysdba” command. The user could connect with no problem using the command “sqlplus sys as sysdba” and entering the password.

As it turned out the operating system user in this case “oracle” was not in the Oracle group “dba”.

After adding the user back into the dba group the user was able to log back in without an issue:

usermod -a -G dba oracle

Linux X11 Forwarding with SU Switch User

Over the years access is often granted to one of the administrative users and then once we are on the server we have to switch user to another account to do the technical services work. This creates a problem with X11 Forwarding and so this is one work around to the problem. X authentication is based on cookies. So the secondary account needs to have access to the same cookies as the original login user.

Here is a nice easy way to do this.  This was done on an AIX 7.1 server:

Before you issue the su or sudo, request the xauth cookies and look for the current DISPLAY that’s connecting to your X server:

$ xauth list
You’ll get something like

somehost.somedomain:10 mit-magic-cookie-1 4d22408a71a55b41ccd1657d377923ae

Then, execute a switch user (su) and add the cookie to that user:

$ xauth add somehost.somedomain:10 MIT-MAGIC-COOKIE-1 4d22408a71a55b41ccd1657d377923ae

(just copy’n-paste the output of the above ‘xauth list’ onto ‘xauth add’) That’s it. Now, you _should_ be able to start any X application.

 Oracle – Security Setup

I had a client ask me last week to change up all the oracle – security setup in their production Oracle Database. The cleanup was relatively straightforward because we only use the basic accounts for the PeopleSoft environment and all the passwords had been changed since going live so there was very little risk to the environment, but they wanted to be safe so:

PeopleSoft Default Accounts: Access-Id, this account is typically by default sysadm, however you can define this account to be pretty much any name, it just needs to ensure that it is an 8-digit id and even though Oracle says the password can be greater, I highly recommend the password be 8 digits and make sure not to include any funky characters (we have seen all sorts of issues with this password being anything special). The next account we need to ensure it is working correctly is the Connect-Id, this account is used for authentication purposes with PeopleSoft.  This account has a base of 3 tables it can select data from, PSOPRDEFN, PSACCESSPRFL, PSSTATUS. As of PeopleTools 8.55, an additional grant for select is on table PSACCESSPROFILE which basically replaces PSACCESSPRFL and supposedly supports passwords up to 30 characters. The next account you will see is the PS account, this account is created when the environment is built and contains an table PS.PSDBOWNER that shows the database name with the schema owner for that database. The PS account is not allowed to be connected to.  The last two accounts you should have be default are SYS & SYSTEM, neither of which should be accessed by anybody expect your DBA.

The first major item is that they wanted the dba’s to have dedicated accounts instead of using the sysadm account.  So to do this:

In SQL*Plus:

create user dbauser1 identified by dbapassword1 ;

grant create session, grant any privilege to dbauser1;

grant connect, resource, dba to dbauser1;

grant unlimited tablespace to dbauser1;

All the work the dbauser1 needs to do is in the sysadm schema, but when it logs in it will default to the schema dbauser1.  So to fix this, we build a trigger to execute a call to switch the current schema on login:

In SQL*Plus:

CREATE OR REPLACE TRIGGER LOGIN_SCHEMA
AFTER LOGON ON DATABASE WHEN (USER in (‘DBAUSER1′,’DBAUSER2’))
DECLARE
exsql VARCHAR2(100);
BEGIN
exsql := ‘ALTER SESSION SET CURRENT_SCHEMA = SYSADM’;
EXECUTE IMMEDIATE exsql;
END;
/

In order to troubleshoot the environment we had created a sysadm_read account which had the role sysadm_read_only, which has select access to all PS tables in the SYSADM schema. This account was shared by multiple users for data validation.  It was determined that they wanted to have dedicated accounts for each user and they wanted to have a higher level of password security so this is where things go creative.

The first element that I want to enable is a better level of password security. Oracle has delivered password verify functions which can be applied to the environment quickly and easily.  You will want to modify the script to ensure that it affects the correct profile for your environment.  The script is:  utlpwdmg.sql which for Oracle 12c introduced two functions ora12c_verify_function and ora12c_strong_verify_function.  For my example I went with the ora12c_verify_function that more than meets the minimum requirements for the client. So now we create a new profile for the database users:

In SQL*Plus:

create profile sysadm_read_only limit
failed_login_attempts 3
password_lock_time 1
password_life_time 90
password_reuse_max 3
PASSWORD_VERIFY_FUNCTION    ora12c_verify_function    ;

This creates a new profile that allows for only 3 failed login attempts, if the user fails all 3 times the account is locked out for 1 day. Password is valid for 90 days, and can only be used every 4th time it is changed, and the password verification as at least 8 characters, at least 1 letter, at least 1 digit, must not contain database name, must not contain user name or reverse user name, must not contain oracle, must not be too simple like welcome1, and must differ by at least 3 characters from the old password (ora12c_verify_function).

Now we just need to create the new users with the sysadm_read_role:

In SQL*Plus:

create user readuser1 identified by readpassword password expire profile sysadm_read_only;

grant sysadm_read_role to readuser1;

grant connect to readuser1;

Now when the user logs into the environment they will be required to change their password and they will have read only access to the sysadm schema.  We have stored procedure that is scheduled to run nightly that grants select access to all PS tables in the sysadm schema and creates public synonyms for each one so that the read only users can select from the PS tables.  The grants are done to the sysadm_read_role Role.

Oracle Sequences

Sometimes in the middle of the night you will be working on a conversion effort and you will need to populate a field with a sequence of values.  This is easily done using Excel but if you are dynamically loading data you may want to have a more flexible feature.  Oracle sequences have that ability.

Create A Sequence:

CREATE SEQUENCE adjustment_fix START WITH 1001 INCREMENT BY 1 NOCACHE NOCYCLE;

So this sequence will start are 1001 and each additional next value will by greater by 1 — so 1002, 1003, 1004….etc.  I had an oddball situation where I needed an 18 digit number that increased by 10000000. So in my case it was:

CREATE SEQUENCE adjustment_fix START WITH 300000000010000000 INCREMENT BY 10000000 NOCACHE NOCYCLE;

Now that you have the sequence, you need to populate the rows with the sequence.  So I created a temporary table and loaded all the values in the table.  Then I had another field for the sequence number.  I tried to insert the sequence while doing the insert into the staging table of the values I needed staged for the adjustment but it whined and complained.  So I did the insert and then did an update of the rows:

SQL:    UPDATE STAGING_TABLE_4_ADJ SET ADJ_SEQ = adjustment_fix.nextval;

BAM!  Now I have 2200 rows of adjustment data with a unique sequence number on each row so that I can load the staging data tables.

Audit Create Session Logins

I had an interesting security breach the other day where a developer who had sql access to the production environment gave out his access to basically “everybody”. There was a huge panic and at the end of the day, I found a tip from Burleson at dba-oracle.com that showed that you can easily turn on auditing for session creation and it gives a wonder account of the people that are failing to login because of the changed credentials.
First you need to set the audit parameters on and bounce the system if it isn’t.

audit_trail=true
audit_file_dest=’/dest/to/my/audit/logs/’

from SQL*Plus: audit create session whenever not successful;

Now when you query: Select * from dba_audit_trail; you will get a listing of the folks that have failed to create a session because of incorrect credentials.