Creating Operating System Authenticated User

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 FROM dba_users ORDER BY 1; 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.

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

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%');