Home » RDBMS Server » Security » OS Authentication vi SQLNET on localhost only? (RDBMS, 10.0.2.4, RHEL5 redhat)
OS Authentication vi SQLNET on localhost only? [message #326868] Thu, 12 June 2008 22:20 Go to next message
beornharris
Messages: 5
Registered: June 2008
Location: Australia
Junior Member
Background:
I need os authentication (ie no password entry) for the apache user to access a non-privileged oracle account ONLY from the local machine.

Problem:
I thought this would be simple to set up. Enable OS Authentication and create my ops$apache oracle user, et voila! but this is NOT as SIMPLE as it sounds.

Option 1) LOCAL OS Authentication
If I use local OS Authentication ( export ORACLE_SID=SID; sqlplus / ) then I get permission denied (ORA-12546: TNS:permission denied). After much playing, I finally figured that this is because the apache USER does not have privileges on the Oracle installation directories (Note that I have the Oracle Instant Client + SQL+ installed for NORMAL users), so to use Local OS Authentication I need to either make apache a member of the oinstall group, or open up permissions on the oracle installation tree so that the apache user will have permissions on the oracle Binary. Both bad options.

Option 2) OS Authentication via SQLNet
Enable REMOTE_OS_AUTHENT in the database and set listener/db security to block access from anywhere but the local host. This works, as requests go through the listener which has the appropriate privileges to start a session, BUT I could not find a way to disallow access from other machines. I could not find anything in SQLNet config to block specific access types (I need the DB externally available for the appropriate user/pass authenticated accounts). I finally decided to implement a workaround where a logon trigger would check the local and client hosts and only allow access from local

if my_util_pkg.db_hostname != SYS_CONTEXT('USERENV', 'HOST') then
raise_application_error(...)
end if;

This works for my non-privileged apache user...but for my ADMINISTRATIVE ops$oracle account, the trigger doesn't fire allowing free access to this administrative account ("as sysdba" doesn't work, but the ops$oracle user still has a lot of DB Privs for the application administration). In any case, this solution is not perfect, as its easy to fake a host name (and I haven't figured out how to resolve hosts to IP addresses yet)

Any ideas on a resolution to this?

Cheers

Beorn
Re: OS Authentication vi SQLNET on localhost only? [message #326881 is a reply to message #326868] Thu, 12 June 2008 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I solved a similar challenge 2 jobs ago & a number of years in the past. The actual details are lost in fog of old age.
In my case I was running on RH AS apache user was OS user "nobody"; which had no shell defined for security reason in /etc/passwd.

IIRC, between invoking sudo & the use of a "HERE" script, I was able to run a script as a regular OS user.

Simply put, I know it can be done & it took me only 2 - 3 days of trail & error debugging to arrive with a working solution.

Good Luck & please post results back here if you succceed.
Re: OS Authentication vi SQLNET on localhost only? [message #326890 is a reply to message #326868] Fri, 13 June 2008 00:03 Go to previous messageGo to next message
beornharris
Messages: 5
Registered: June 2008
Location: Australia
Junior Member
Hi, thanks for the reply.

Im interested in anything else you can remember for your solution as I just cant see a solution that works.

Without going via listener/SQLNet apache simply doesnt (and shouldnt) have permissions on the Oracle binaries and libraries required to spark up an internal connection. If it was some time ago you did this, it may not be valid anymore if oracle has changed/tightened permissions on the default directory install.
Because of this, the only solution I can see (since I dont want to change Oracle file/directory permissions) is to go via SQLNet/listener...but then I need some way to be able to selectively deny access to certain accounts (os authenticated) from connections originating anywhere but on the local host.

Ill keep on it Surprised)
As always, any advice appreciated!

Cheers

Beorn
Re: OS Authentication vi SQLNET on localhost only? [message #326906 is a reply to message #326868] Fri, 13 June 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Option 1) If you have the following permission, you should have no problem using SQL*Plus with any user:
$ORACLE_HOME/bin, $ORACLE_HOME/lib: drwxrwxr-x
sqlplus: -rwxr-x--x

Option 2) You can allow connections from only one host with the following sqlnet.ora settings:
TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(<your server IP>)

Regards
Michel
Re: OS Authentication vi SQLNET on localhost only? [message #326910 is a reply to message #326906] Fri, 13 June 2008 01:13 Go to previous messageGo to next message
beornharris
Messages: 5
Registered: June 2008
Location: Australia
Junior Member
Thanks Michel, but that wont do any good!

1) user/group info
uid=48(apache) gid=48(apache) groups=48(apache)
uid=501(oracle) gid=502(oinstall)

Oracle File/Directory Permissions
drwxrwx--- oracle oinstall product/
drwxrwx--- oracle oinstall product/10.2.0/
drwxr-x--- oracle oinstall product/10.2.0/db_1/
drwxr-xr-x oracle oinstall product/10.2.0/db_1/bin/
-rwsr-s--x oracle oinstall product/10.2.0/db_1/bin/oracle

These are DEFAULT install perms which, as I mentioned, I can change...but dont want to. As you can see, the apache user can not access the oracle binary (or linked libraries) unless I either a) change the permissionss, or b) join apache to the oinstall unix group

2) configuring with
TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(<your server IP>)
will prevent access to ALL oracle accounts (ie no connections from outside the box) and NOT just the ones that I want to use OS Authentication for (ops$oracle and ops$apache)

Keep em comin'g Razz there HAS to be a solution somewhere!!

I am starting to investigate the Oracle Connection Manager, which I have had zero exposure to...is anyone familiar with that?

Cheers

Beorn
Re: OS Authentication vi SQLNET on localhost only? [message #326933 is a reply to message #326910] Fri, 13 June 2008 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
These are DEFAULT install perms which, as I mentioned, I can change...but dont want to.

This a solution and a far better one than REMOTE_OS_AUTHENT set to true.
By the way, it is YOUR default install, it depends on mask you gave on the account that installed Oracle.
It does not hurt if you open in read mode path to bin and lib.

Quote:
will prevent access to ALL oracle accounts

I thought Apache connection was the only ne you wanted.

Regards
Michel


Re: OS Authentication vi SQLNET on localhost only? [message #327265 is a reply to message #326933] Sun, 15 June 2008 21:53 Go to previous messageGo to next message
beornharris
Messages: 5
Registered: June 2008
Location: Australia
Junior Member
Quote:
By the way, it is YOUR default install, it depends on mask you gave on the account that installed Oracle.

Actually, my umask was set to 0022 for the install, so the user default permissions have been overridden by the Oracle install...so it is not MY chosen default, but on reflection, you are right that there is no real harm in allowing read for the appropriate libraries/binaries/directories. What I am trying to avoid is having to decide which ones myself and perform the installation in a standardised way. Your comment triggered me to do a quick google on opening up the perms, and I ran across this http://www.dbspecialists.com/presentations/oracle10glinux.html (Point 7). Havent had a chance to investigate further, but looks like it could be my answer....so thanks.

Quote:
I thought Apache connection was the only ne you wanted.

Not exactly...I still need access to the DB via SQLNet...but I dont want OS Authentication for that. ONLY for accounts (where apache and the administrative Oracle user are the only ones...so far) connecting from the local machine.

Anyways, thanks for all the responses so far. I'll let you know how I go with the docco above.

Cheers

Beorn
Re: OS Authentication vi SQLNET on localhost only? [message #327269 is a reply to message #326868] Sun, 15 June 2008 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Quote:

TCP.EXCLUDED_NODES
Purpose

Use the parameter TCP.EXCLUDED_NODES to specify which clients are denied access to the database.

Syntax

TCP.EXCLUDED_NODES=(hostname | ip_address, hostname | ip_address, ...)
Example

TCP.EXCLUDED_NODES=(finance.us.acme.com, mktg.us.acme.com, 144.25.5.25)
TCP.INVITED_NODES
Purpose

Use the parameter TCP.INVITED_NODES to specify which clients are allowed access to the database. This list takes precedence over the TCP.EXCLUDED_NODES parameter if both lists are present.

Syntax

TCP.INVITED_NODES=(hostname | ip_address, hostname | ip_address, ...)
Example

TCP.INVITED_NODES=(sales.us.acme.com, hr.us.acme.com, 144.185.5.73)


[Updated on: Sun, 15 June 2008 23:27] by Moderator

Report message to a moderator

Re: OS Authentication vi SQLNET on localhost only? [message #327290 is a reply to message #327269] Sun, 15 June 2008 23:38 Go to previous message
beornharris
Messages: 5
Registered: June 2008
Location: Australia
Junior Member
Thanks again ana...but that is not suitable for my situation.
I did do quite a bit of RTFMing and googling before posting here, so the most obvious solutions have been considered (as per the rest of this thread)

I guess I am going to go with the relaxed permissions option (as above). The application of patch 4516865 seems to have worked a treat.

Thanks both Michel and Ana for your suggestions and help in getting me to here. Much appreciated.

Cheers

Beorn
Previous Topic: Error in startup the database.
Next Topic: log in details
Goto Forum:
  


Current Time: Wed Dec 07 22:34:26 CST 2016

Total time taken to generate the page: 0.08366 seconds