I just tried today to limit power of rman :
REVOKE ALTER SESSION, CREATE DATABASE LINK FROM RECOVERY_CATALOG_OWNER;
It seems I can still do a backup… probably those privilege are not needed by rman, maybe just inherited from Connect in an older released !?
There a few major changes in the database administration and the database security between 9i and 10g.
In 9i, I used to grep in the listener.ora to find out the password.
LISTENER_LSC61 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200))
))
PASSWORDS_LISTENER_LSC61 = 1234567890ABCDEF
this 64bit encrypted string can be used in 9i to stop the listener
$ lsnrctl
LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.6.0 – Production on 05-DEC-2005 14:33:51
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Welcome to LSNRCTL, type “help” for information.
LSNRCTL> set current_listener listener_lsc61
Current Listener is listener_lsc61
LSNRCTL> set password 1234567890ABCDEF
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200)))
The command completed successfully
As a dba, it is quite handy, because you can use grep (or awk) to find out the password out of the listener.ora. As a security admin, you should make sure the listener.ora is not readable. Note that the default, when created by netmgr, is to be world-readable 🙁
However, this does no longer work in 10g
LISTENER_LSC62 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10062)(QUEUESIZE=200))
))
PASSWORDS_listener_LSC62 = 1234567890ABCDEF
the encrypted string can no longer be used
$ lsnrctl
LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.4.0 – Production on 05-DEC-2005 14:37:24
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Welcome to LSNRCTL, type “help” for information.
LSNRCTL> set current_listener listener_lsc62
Current Listener is listener_lsc62
LSNRCTL> set password 1234567890ABCDEF
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10062)(QUEUESIZE=200)))
TNS-01169: The listener has not recognized the password
TNS-01189: The listener could not authenticate the user
As a security admin, you would think it is better so. But, how are you going to stop the listener in your script? Well, in 10g, we can use local authentification (default). So if the script is started as oracle, we would not need to use password
LISTENER_LSC63 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10016)(QUEUESIZE=200))
))
PASSWORDS_listener_LSC63 = 1234567890ABCDEF
$ whoami
oracle
$ hostname
dbsrv85a.ex.zkb.ch
$ lsnrctl
LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 – Production on 05-DEC-2005 14:43:33
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type “help” for information.
LSNRCTL> set current_listener LISTENER_LSC63
Current Listener is LISTENER_LSC63
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10016)(QUEUESIZE=200)))
The command completed successfully
I read in an Alex Kornbrust post on Pete Finnigan forum, that a LOCAL_OS_AUTHENTICATION “undocumented” parameter could be used to “avoid” local authentication, but in that case, it is going to be a nightmare to “stop” the listener in an automated script, well, we can still use “kill”, but it is not very beautifoul.
I wrote a mini script to protect my customer from being attacked by an “oracle voyage worm” variant :
revoke CREATE DATABASE LINK from CONNECT; revoke ALL on SYS.UTL_FILE from PUBLIC; revoke ALL on SYS.UTL_HTTP from PUBLIC; revoke ALL on SYS.UTL_SMTP from PUBLIC; revoke ALL on SYS.UTL_TCP from PUBLIC; grant EXECUTE on SYS.UTL_FILE to XDB; grant EXECUTE on SYS.UTL_HTTP to MDSYS; grant EXECUTE on SYS.UTL_HTTP to ORDPLUGINS; @?/rdbms/admin/utlrp
Than, in OEM 10g, check for policy violations.
I added a few grants to special oracle internal users, to avoid invalid objects, which is also a policy violation in OEM… OEM will report a violation if those accounts are not locked and expired
Ref: part 1
I reported this lack of documentation on http://forums.oracle.com/forums/thread.jspa?threadID=330359
Here is my test case (take care, it will create a new db!) :
SQL> startup force quiet nomount; ORACLE instance started. SQL> create database controlfile reuse extent management local default tablespace users default temporary tablespace temp undo tablespace undotbs1; Database created. SQL> @?/rdbms/admin/catalog SQL> @?/rdbms/admin/catproc SQL> col username for a10 SQL> col PROFILE for a7 SQL> col LIMIT for a12 SQL> select username, profile, limit from dba_users join dba_profiles using (profile) where resource_name='FAILED_LOGIN_ATTEMPTS'; USERNAME PROFILE LIMIT ---------- ------- ------------ SYSTEM DEFAULT 10 SYS DEFAULT 10 TSMSYS DEFAULT 10 DIP DEFAULT 10 DBSNMP DEFAULT 10 OUTLN DEFAULT 10
I just noticed FAILED_LOGIN_ATTEMPTS now defaults to 10 in 10gR2. Not found in the doc. I will report it tomorrow in the documentation feedback on otn