Sunday, May 11, 2014
Oracle Database Strong Authentication (certificate)
1) Create and configure the server wallet
Create an empty server wallet:
$> mkdir /u01/app/oracle/server_wallet
$> cd /u01/app/oracle/server_wallet
$> orapki wallet create -wallet /u01/app/oracle/server_wallet -auto_login -pwd welcome1
After running the above command the following two files will be created in the directory:
$> ls -la /u01/app/oracle/server_wallet
-rw------- 1 oracle oinstall 3589 May 4 11:53 cwallet.sso
-rw------- 1 oracle oinstall 3512 May 4 11:53 ewallet.p12
Create server's self signed certificate:
$> orapki wallet add -wallet /u01/app/oracle/server_wallet -dn "CN=server" -keysize 512 -self_signed -validity 365 -pwd welcome1
2) Create and configure the client wallet
Create an empty wallet for the client(into a new directory) :
$> mkdir /u01/app/oracle/client_wallet
$> cd /u01/app/oracle/client_wallet
$> orapki wallet create -wallet /u01/app/oracle/client_wallet -auto_login -pwd welcome2
Note: We used the -auto_login option to make sure that we are able to move the cwallet.sso file to other machines. If the client is running on the same machine as the server then you can use the -auto_login_local
The next step is to create a certificate request :
orapki wallet add -wallet . -dn "CN=client,O=oracle,C=ro" -keysize 1024 -pwd "Welcome2"
orapki wallet export -wallet . -dn "CN=client,O=oracle,C=ro" -request ./certificate_request.txt -pwd "welcome1"
Copy the certificate request from the "client" directory to the "server" directory:
scp certificate_request.txt oracle@192.168.1.101:/u01/app/oracle/server_wallet
Sign the certificate of the client and also export server's CA certificate:
$> cd /u01/app/oracle/server_wallet
$> orapki cert create -wallet . -request ./certificate_request.txt -cert ./signed_certificate.cert -validity 365 -pwd "welcome1"
$> orapki wallet export -wallet /u01/app/oracle/server_wallet -dn "CN=server" -cert server_ca.cert
Note: The server's auto login wallet is used as a certificate authority. By using this method you will avoid using a real CA to sign the certificates of the clients.
Copy the signed certificate(signed_certificate.cert) and the server's root certificate ( server_ca.cert) to the client's wallet directory and import them into client's wallet:
$> cd /u01/app/oracle/server_wallet
$> cp signed_certificate.cert /u01/app/oracle/client_wallet/
$> cp server_ca.cert /u01/app/oracle/client_wallet/
$> orapki wallet add -wallet . -trusted_cert -cert ./server_ca.cert -pwd "welcome2"
$> orapki wallet add -wallet . -user_cert -cert ./signed_certificate.cert -pwd "welcome2"
3) Create the user within the database
SQL> create user client identified externally as 'CN=client,O=oracle,C=ro';
SQL> grant create session to client;
Note: The user within the database has to be created specifying the distiguished name (DN) on their certificate. The user certificate created earlier had the DN 'CN=client,O=oracle,C=ro' so we have to use the same string while creating the user in the database
Note: Steps 2) & 3) can be repeated for all the users that will be authenticated using SSL. In the example we configured the database user and the client wallet and the certificates for a generic user called "client" but the same steps apply to any user.
After performing all the above steps we have all the needed certificates for both the server and the client in the server_wallet and client_wallet directories on the database server machine. Furthermore we do have an user in the database that is associated with the DN of the certificate stored in the client's wallet. We executed all these steps on the same machine(on the DB Server) just to emphasize the idea that we can centralize the configuration of the SSL authentication. For a large user population we can repeat steps 2) and 3) until we get the wallets with the right certificates for all the users and have all the users created in the database.
4) Configuring server side listener
Configure a TCPS listener address
Use Net Manager to create an new TCPS listener or add new TCPS address to an existing listener. You will also need to add the wallet location. For example,
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = seclin4.ro.oracle.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = seclin4.ro.oracle.com)(PORT = 1522))
)
)
WALLET_LOCATION =
(SOURCE=
(METHOD=File)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/server_wallet)))
5) Configuring server side sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/server_wallet)
)
)
6) Configuring client side sqlnet.ora
In the client sqlnet.ora set TCPS as an authentication method and set the wallet location,
SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/client_wallet)
)
)
7) Configuring client side tnsnames.ora
Use Net Manager or Net Configuration Assistant to create a service name using TCPS, e.g.
ORCLSSL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = seclin4.ro.oracle.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
8) Configuring the database
The database parameter OS_AUTHENT_PREFIX must be null and REMOTE_OS_AUTHENT must be FALSE:
SQL> alter system set remote_os_authent=FALSE scope=spfile;
SQL> alter system set os_authent_prefix='' scope=spfile;
The instance will need to be restarted for these changes to take effect.
9) Testing the configuration
The first test is to confirm the client is able to tnsping the alias for the TCPS listener.This will confirm that the client wallet is properly accessible by the client:
$> tnsping ORCLSSL
TNS Ping Utility for Linux: Version 10.2.0.2.0 - Production on 29-NOV-2006 10:16:32
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/u01/10.2/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.171.112.16)(PORT = 1522))) (CONNECT_DATA = (SID = v10g)))
OK (100 msec)
The second test is to connect as the user that is associated with the certificate stored in the wallet:
$> sqlplus /@ORCLSSL
SQL*Plus: Release 10.2.0.2.0 - Production on Wed Nov 29 10:17:27 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>SQL> select user from dual;
USER
------------------------------
CLIENT
Move SYS.AUD$ table out of SYSTEM tablespace
How to move the database audit trail tables( SYS.AUD$ & SYS.FGA_LOG$) to another tablespace using DBMS_AUDIT_MGMT?
-------------------------------------------------------
Note : This move operation can be performed even when the database is up and an audit trail is being written
1) Check the current tablespace of the audit trail tables:
CONN / AS SYSDBA
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name; 2
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSAUX
FGA_LOG$ SYSAUX
2) Check the current size of two tables:
select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');
SEGMENT_NAME SIZE_IN_MEGABYTES
------------------- -----------------------------
AUD$ 12
FGA_LOG$ .0625
3) Create a new tablespace and make sure that it's size is large enough for the tables that will be moved:
SQL> create tablespace audit_tbs datafile '/u01/app/oracle/oradata/d1v11202/audit_tbs1.dbf' size 100M autoextend on;
Tablespace created.
4) Move the audit trail tables using procedure DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.
SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
PL/SQL procedure successfully completed.
4) Check whether the tables were moved successfully:
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
----------------------- ------------------------------
AUD$ AUDIT_TBS
FGA_LOG$ AUDIT_TBS
NOTE :
=======
The requirement for the introduction of this package came from Audit Vault. Currently it is not supported to use the DBMS_AUDIT_MGMT package on a pre 11gR2(11.2.0.1)
instance which is not an Audit Vault source database. Its features include the purge of audit records and can be used to move the audit tables AUD$ and FGA_LOG$ to another tablespace.
Audit Trails with dbms_audit_mgmt, which has been officially introduced/supported in Oracle 11g R2.
The DBMS_AUDIT_MGMT package is installed by default in Oracle 11.2, and in patchsets 10.2.0.5 and 11.1.0.7 but has also been made available as a separately installable patch for other versions.
----------------
Script to move SYS.AUD$ table out of SYSTEM tablespace
-------------------------------------------------------
- Restart the database with audit_trail=NONE before running the script ---
create tablespace "AUDIT"
datafile '$HOME/data/aud01.dbf' size 500k
default storage (initial 100k next 100k pctincrease 0) ;
create table audx tablespace "AUDIT"
storage (initial 50k next 50k pctincrease 0)
as select * from aud$ where 1 = 2 ;
rename AUD$ to AUD$$ ;
rename audx to aud$ ;
create index i_aud2
on aud$(sessionid, ses$tid)
tablespace "AUDIT" storage(initial 50k next 50k pctincrease 0);
-------------------------------------------------------
Note : This move operation can be performed even when the database is up and an audit trail is being written
1) Check the current tablespace of the audit trail tables:
CONN / AS SYSDBA
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name; 2
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSAUX
FGA_LOG$ SYSAUX
2) Check the current size of two tables:
select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');
SEGMENT_NAME SIZE_IN_MEGABYTES
------------------- -----------------------------
AUD$ 12
FGA_LOG$ .0625
3) Create a new tablespace and make sure that it's size is large enough for the tables that will be moved:
SQL> create tablespace audit_tbs datafile '/u01/app/oracle/oradata/d1v11202/audit_tbs1.dbf' size 100M autoextend on;
Tablespace created.
4) Move the audit trail tables using procedure DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.
SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
PL/SQL procedure successfully completed.
4) Check whether the tables were moved successfully:
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
----------------------- ------------------------------
AUD$ AUDIT_TBS
FGA_LOG$ AUDIT_TBS
NOTE :
=======
The requirement for the introduction of this package came from Audit Vault. Currently it is not supported to use the DBMS_AUDIT_MGMT package on a pre 11gR2(11.2.0.1)
instance which is not an Audit Vault source database. Its features include the purge of audit records and can be used to move the audit tables AUD$ and FGA_LOG$ to another tablespace.
Audit Trails with dbms_audit_mgmt, which has been officially introduced/supported in Oracle 11g R2.
The DBMS_AUDIT_MGMT package is installed by default in Oracle 11.2, and in patchsets 10.2.0.5 and 11.1.0.7 but has also been made available as a separately installable patch for other versions.
----------------
Script to move SYS.AUD$ table out of SYSTEM tablespace
-------------------------------------------------------
- Restart the database with audit_trail=NONE before running the script ---
create tablespace "AUDIT"
datafile '$HOME/data/aud01.dbf' size 500k
default storage (initial 100k next 100k pctincrease 0) ;
create table audx tablespace "AUDIT"
storage (initial 50k next 50k pctincrease 0)
as select * from aud$ where 1 = 2 ;
rename AUD$ to AUD$$ ;
rename audx to aud$ ;
create index i_aud2
on aud$(sessionid, ses$tid)
tablespace "AUDIT" storage(initial 50k next 50k pctincrease 0);
Enable Standard Database Auditing
Enable Standard Auditing
SQL> show parameters audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=db scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Startup
ORACLE instance started.
Total System Global Area 440401920 bytes
Fixed Size 1249440 bytes
Variable Size 109055840 bytes
Database Buffers 322961408 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> show parameters audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
SQL> audit all on scott.emp by access;
Audit succeeded.
SQL> update emp set sal=sal*0.95 where job='MANAGER';
3 rows updated.
You will get the following record in the audit trail:
SQL> select username, owner, obj_name, action_name, sql_text from dba_audit_trail;
USERNAME OWNER OBJ_NAME ACTION_NAME SQL_TEXT
---------- ---------- -------------------- --------------- --------------------
SCOTT SCOTT EMP UPDATE
If however you change the audit trail to DB_EXTENDED and restart the database:
SQL> alter system set audit_trail='db_extended' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Startup
ORACLE instance started.
Total System Global Area 440401920 bytes
Fixed Size 1249440 bytes
Variable Size 113250144 bytes
Database Buffers 318767104 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> show parameters audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB_EXTENDED
The same activity will produce a record with the text:
SQL> select username, owner, obj_name, action_name, sql_text from dba_audit_trail;
USERNAME OWNER OBJ_NAME ACTION_NAME SQL_TEXT
---------- ---------- -------------------- --------------- --------------------
SCOTT SCOTT EMP UPDATE
SCOTT SCOTT EMP UPDATE update emp set sal=s
al*0.95 where job='M
ANAGER'
Note
====
You can also use the following syntax:
SQL> alter system set audit_trail='db','extended' scope=spfile;
System altered.
Or:
SQL> alter system set audit_trail=db,extended scope=spfile;
System altered.
But you can’t use ‘DB,EXTENDED’—which is a little confusing at first:
SQL> alter system set audit_trail='db,extended' scope=spfile;
alter system set audit_trail='db,extended' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value xml,extended for parameter audit_trail, must be from among extended, xml, db_extended, false, true, none, os, db
SQL> show parameters audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=db scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Startup
ORACLE instance started.
Total System Global Area 440401920 bytes
Fixed Size 1249440 bytes
Variable Size 109055840 bytes
Database Buffers 322961408 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> show parameters audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
SQL> audit all on scott.emp by access;
Audit succeeded.
SQL> update emp set sal=sal*0.95 where job='MANAGER';
3 rows updated.
You will get the following record in the audit trail:
SQL> select username, owner, obj_name, action_name, sql_text from dba_audit_trail;
USERNAME OWNER OBJ_NAME ACTION_NAME SQL_TEXT
---------- ---------- -------------------- --------------- --------------------
SCOTT SCOTT EMP UPDATE
If however you change the audit trail to DB_EXTENDED and restart the database:
SQL> alter system set audit_trail='db_extended' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Startup
ORACLE instance started.
Total System Global Area 440401920 bytes
Fixed Size 1249440 bytes
Variable Size 113250144 bytes
Database Buffers 318767104 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> show parameters audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB_EXTENDED
The same activity will produce a record with the text:
SQL> select username, owner, obj_name, action_name, sql_text from dba_audit_trail;
USERNAME OWNER OBJ_NAME ACTION_NAME SQL_TEXT
---------- ---------- -------------------- --------------- --------------------
SCOTT SCOTT EMP UPDATE
SCOTT SCOTT EMP UPDATE update emp set sal=s
al*0.95 where job='M
ANAGER'
Note
====
You can also use the following syntax:
SQL> alter system set audit_trail='db','extended' scope=spfile;
System altered.
Or:
SQL> alter system set audit_trail=db,extended scope=spfile;
System altered.
But you can’t use ‘DB,EXTENDED’—which is a little confusing at first:
SQL> alter system set audit_trail='db,extended' scope=spfile;
alter system set audit_trail='db,extended' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value xml,extended for parameter audit_trail, must be from among extended, xml, db_extended, false, true, none, os, db
Oracle Case-Sensitive Passwords
In Oracle Database prior to release 11g, user passwords were case insensitive. For example:
SQL> conn scott/tiger
Connected.
SQL> conn scott/TIGER
Connected.
This arrangement presents a problem for standards such as the Payment Card Industry (PCI) Data Security Standard, which require passwords to be case sensitive.
Problem solved; in Oracle Database 11g passwords can be case sensitive as well. While creating the database via DBCA, you will be prompted whether you want to upgrade to the "new security standards," one of which is the case-sensitive password. If you accept, passwords will be recorded in the same case as they were created. Here is the resulting behavior, assuming you have accepted the new standard:
SQL> conn scott/tigger
Connected.
SQL> conn scott/TIGGER
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Note how "tigger" and "TIGGER" are treated differently.
Now, some of your apps may not be passing the password in proper case right now. A typical example is a user input form: Many forms accept passwords with no case conversion being performed. However, with Oracle Database 11g, that login may fail unless the user enters the password in case-sensitive format or the developer changes the app to convert to upper or lower case (which may not be possible quickly).
If you wish, however, it is still possible to revert to case insensitivity by altering a system parameter, SEC_CASE_SENSITIVE_LOGON, as shown in the example below.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon = false;
System altered.
SQL> conn scott/TIGER
Connected.
When you upgrade an existing Oracle 10g database to 11g, you can migrate your passwords to the new standard. You can check the status of the password by querying the DBA_USERS view, especially the new column PASSWORD_VERSIONS.
select username, password, password_versions
from dba_users;
USERNAME PASSWORD PASSWORD
------------------------- ------------------------------ --------
SYSTEM 10G 11G
SYS 10G 11G
MGMT_VIEW 10G 11G
The first thing you notice is that the password column is NULL, not populated with the hashed value as it is in Oracle Database 10g and prior versions. So what happened to the password? It's still stored in the database (in the table USER$) but it is not visible in the DBA_USERS view. When the user is created as either global or externally authenticated, the status is indicated—GLOBAL or EXTERNAL—but the hash value of the password is not displayed.
Next, note the column PASSWORD_VERSIONS, which is new in Oracle Database 11g. This column signifies the case sensitivity of the password. The value "10G 11G" signifies that the user was either created in 10g and migrated to 11g or created in 11g directly.
SQL> conn scott/tiger
Connected.
SQL> conn scott/TIGER
Connected.
This arrangement presents a problem for standards such as the Payment Card Industry (PCI) Data Security Standard, which require passwords to be case sensitive.
Problem solved; in Oracle Database 11g passwords can be case sensitive as well. While creating the database via DBCA, you will be prompted whether you want to upgrade to the "new security standards," one of which is the case-sensitive password. If you accept, passwords will be recorded in the same case as they were created. Here is the resulting behavior, assuming you have accepted the new standard:
SQL> conn scott/tigger
Connected.
SQL> conn scott/TIGGER
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Note how "tigger" and "TIGGER" are treated differently.
Now, some of your apps may not be passing the password in proper case right now. A typical example is a user input form: Many forms accept passwords with no case conversion being performed. However, with Oracle Database 11g, that login may fail unless the user enters the password in case-sensitive format or the developer changes the app to convert to upper or lower case (which may not be possible quickly).
If you wish, however, it is still possible to revert to case insensitivity by altering a system parameter, SEC_CASE_SENSITIVE_LOGON, as shown in the example below.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon = false;
System altered.
SQL> conn scott/TIGER
Connected.
When you upgrade an existing Oracle 10g database to 11g, you can migrate your passwords to the new standard. You can check the status of the password by querying the DBA_USERS view, especially the new column PASSWORD_VERSIONS.
select username, password, password_versions
from dba_users;
USERNAME PASSWORD PASSWORD
------------------------- ------------------------------ --------
SYSTEM 10G 11G
SYS 10G 11G
MGMT_VIEW 10G 11G
The first thing you notice is that the password column is NULL, not populated with the hashed value as it is in Oracle Database 10g and prior versions. So what happened to the password? It's still stored in the database (in the table USER$) but it is not visible in the DBA_USERS view. When the user is created as either global or externally authenticated, the status is indicated—GLOBAL or EXTERNAL—but the hash value of the password is not displayed.
Next, note the column PASSWORD_VERSIONS, which is new in Oracle Database 11g. This column signifies the case sensitivity of the password. The value "10G 11G" signifies that the user was either created in 10g and migrated to 11g or created in 11g directly.
Oracle Default Passwords
Oracle Database 11g now offers a way to quickly identify users with default passwords, implemented in the rather ludicrously simple way of checking a single data dictionary view: DBA_USERS_WITH_DEFPWD. (Note that DBA_ is a standard prefix; it does not contain only DBA users with default passwords.) You can identify these users by issuing:
select *
from dba_users_with_defpwd
And here is the output:
USERNAME
------------------------------
DIP
MDSYS
WK_TEST
CTXSYS
OLAPSYS
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA
WMSYS
You can see SCOTT listed above, because his password is TIGER, the default one. Change it with:
SQL> alter user scott identified by tiger1;
User altered.
Now if you check the view:
SQL> select * from dba_users_with_defpwd;
You won't see SCOTT on the list anymore. It's that simple!
- Script to check for Default Passwords being used for some common usernames. (8.1.7 - 11.1.0.7)
select username "User(s) with Default Password!", account_status "Status"
from dba_users
where password in
('E066D214D5421CCC', -- dbsnmp
'24ABAB8B06281B4C', -- ctxsys
'72979A94BAD2AF80', -- mdsys
'9AAEB2214DCC9A31', -- mdsys
'C252E8FA117AF049', -- odm
'A7A32CD03D3CE8D5', -- odm_mtr
'88A2B2C183431F00', -- ordplugins
'7EFA02EC7EA6B86F', -- ordsys
'9B616F5489F90AD7', -- ordcommon
'4A3BA55E08595C81', -- outln
'F894844C34402B67', -- scott
'3F9FBD883D787341', -- wk_proxy
'79DF7A1BD138CF11', -- wk_sys
'7C9BA362F8314299', -- wmsys
'88D8364765FCE6AF', -- xdb
'F9DA8977092B7B81', -- tracesvr
'9300C0977D7DC75E', -- oas_public
'A97282CE3D94E29E', -- websys
'AC9700FD3F1410EB', -- lbacsys
'E7B5D92911C831E1', -- rman
'AC98877DE1297365', -- perfstat
'66F4EF5650C20355', -- exfsys
'84B8CBCA4D477FA3', -- si_informtn_schema
'D4C5016086B2DC6A', -- sys
'5638228DAF52805F', -- sys
'D4DF7931AB130E37') -- system
/
select *
from dba_users_with_defpwd
And here is the output:
USERNAME
------------------------------
DIP
MDSYS
WK_TEST
CTXSYS
OLAPSYS
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA
WMSYS
You can see SCOTT listed above, because his password is TIGER, the default one. Change it with:
SQL> alter user scott identified by tiger1;
User altered.
Now if you check the view:
SQL> select * from dba_users_with_defpwd;
You won't see SCOTT on the list anymore. It's that simple!
- Script to check for Default Passwords being used for some common usernames. (8.1.7 - 11.1.0.7)
select username "User(s) with Default Password!", account_status "Status"
from dba_users
where password in
('E066D214D5421CCC', -- dbsnmp
'24ABAB8B06281B4C', -- ctxsys
'72979A94BAD2AF80', -- mdsys
'9AAEB2214DCC9A31', -- mdsys
'C252E8FA117AF049', -- odm
'A7A32CD03D3CE8D5', -- odm_mtr
'88A2B2C183431F00', -- ordplugins
'7EFA02EC7EA6B86F', -- ordsys
'9B616F5489F90AD7', -- ordcommon
'4A3BA55E08595C81', -- outln
'F894844C34402B67', -- scott
'3F9FBD883D787341', -- wk_proxy
'79DF7A1BD138CF11', -- wk_sys
'7C9BA362F8314299', -- wmsys
'88D8364765FCE6AF', -- xdb
'F9DA8977092B7B81', -- tracesvr
'9300C0977D7DC75E', -- oas_public
'A97282CE3D94E29E', -- websys
'AC9700FD3F1410EB', -- lbacsys
'E7B5D92911C831E1', -- rman
'AC98877DE1297365', -- perfstat
'66F4EF5650C20355', -- exfsys
'84B8CBCA4D477FA3', -- si_informtn_schema
'D4C5016086B2DC6A', -- sys
'5638228DAF52805F', -- sys
'D4DF7931AB130E37') -- system
/
Subscribe to:
Posts (Atom)