| OS Authentication vi SQLNET on localhost only? [message #326868] |
Thu, 12 June 2008 22:20  |
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 #327265 is a reply to message #326933] |
Sun, 15 June 2008 21:53  |
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 |
 |
BlackSwan
Messages: 26766
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
)
there HAS to be a solution somewhere!!