Creating Operating System Authenticated User | |
Changes to make for external authentication |
1. Set the initSID.ora parameters:remote_os_authent=TRUE os_authent_prefix = “OPS$” 2. Generate a new spfile CREATE spfile FROM pfile=’initorabase.ora’; 3. Add the following to the sqlnet.ora (ONLY ON WINDOWS) sqlnet.authentication_services = (NTS) |
The syntax for CREATE USER where authentication is performed by the operating system on the server |
CREATE USER <user_name> IDENTIFIED EXTERNALLY; |
Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:CREATE USER ops$oracle IDENTIFIED EXTERNALLY;
SELECT username, password, external_name GRANT create session TO ops$oracle; Step 2: Create a user in the operating system named oracle if one does not already exist.Step 3: Go to command line (terminal window in UNIX, cmd in Windows. Type ‘sqlplus /’ You should be connected to the database without having to enter username/password. |
April 13, 2011
Creating Operating System Authenticated User
February 1, 2011
Connect through proxy user
This feature is only available in release 10g and upper.
create user su identified by iamproxyuser; grant create session to su; alter user scott grant connect through su; sqlplus su[scott]/iamproxyuser show user SCOTT
January 28, 2011
Monitoring on DROP USER
conn / as sysdba create user abc identified by abc; CREATE TABLE ddl_log ( operation VARCHAR2(30), obj_owner VARCHAR2(30), object_name VARCHAR2(30), sql_text VARCHAR2(64), attempt_by VARCHAR2(30), attempt_dt DATE); CREATE OR REPLACE TRIGGER bds_trigger BEFORE DROP ON DATABASE DECLARE oper system.ddl_log.operation%TYPE; BEGIN INSERT INTO system.ddl_log SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, NULL, USER, SYSDATE FROM dual; END bds_trigger; / -- now let's test the logging mechanism select * from system.ddl_log; drop user abc; select * from system.ddl_log; select * from DBA_USERS where lower(username) like lower('te%');