Category: Security

ASM User Management Q&A

Q: Can you create users in ASM instance?

A: Yes, absolutely.

Q: Why?

A: For better security.

Q: I was told there is no database associated with ASM, it is just an instance, so where is this user really created?

A: You must have a password file to create users. The users are added to password file.

Q: Ha! are there default users?

A: Yes, SYS and ASMSNMP are default users.

Q: What privileges can I grant to a user?

A: You can grant SYSDBA, SYSASM or SYSOPER privileges.

Q: How do you create a new user?

A: Use SQL*Plus, connect to the instance as SYSASM for all user management. You may also use orapwusr command in asmcmd.

Q: Can you show an example of creating a new user using SQL*Plus?

A: Sure.

$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 21:29:59 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  FALSE TRUE  FALSE FALSE FALSE          0

SQL> create user bt1 identified by bt1;

User created.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  FALSE TRUE  FALSE FALSE FALSE          0
BT1                            FALSE FALSE FALSE FALSE FALSE FALSE          0

SQL> grant sysdba, sysasm to bt1;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  FALSE TRUE  FALSE FALSE FALSE          0
BT1                            TRUE  FALSE TRUE  FALSE FALSE FALSE          0

SQL>

Q: I notice that you used 12.1 release database. Is this feature available in lower release as well?

A: Absolutely. the syntax, privileges and functionality is exactly same in 11gR2. This feature is not available in 10g ASM.

$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 5 23:07:34 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Automatic Storage Management option

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  TRUE
ASMSNMP                        TRUE  FALSE FALSE

SQL>

Q: How do you add user using ASMCMD?

A: Use the orapwusr command to add, modify or delete a user. Use the lspwusr command to list the user and privileges.

Q: Can you show how to?

A: Here, I create an user, modify its privilege, list the user, and finally drop the user. Notice that only one privilege can be granted using the –privilege option, and all other privileges gets revoked. Using SQL*Plus, I was able to grant more than one privilege.

$ asmcmd -p
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm 
     SYS   TRUE   FALSE   TRUE 
     BT1   TRUE   FALSE   TRUE 
ASMCMD [+] > orapwusr --add --privilege sysasm bt2
Enter password: ***
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm 
     SYS   TRUE   FALSE   TRUE 
     BT1   TRUE   FALSE   TRUE 
     BT2  FALSE   FALSE   TRUE 
ASMCMD [+] > orapwusr --modify --privilege sysdba bt2
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm 
     SYS   TRUE   FALSE   TRUE 
     BT1   TRUE   FALSE   TRUE 
     BT2   TRUE   FALSE  FALSE 
ASMCMD [+] > orapwusr --modify --password bt1
Enter password: ***
ASMCMD [+] > orapwusr --delete bt2
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm 
     SYS   TRUE   FALSE   TRUE 
     BT1   TRUE   FALSE   TRUE 
ASMCMD [+] >

Q: How do I connect as the new user when logging in?

A: In SQL*Plus, you use the id and password. For “asmcmd”, there is no option – connect as sysasm or sysdba privilege based on the OS group membership.

$ sqlplus bt1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 21:43:41 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> 
SQL> show user
USER is "SYS"
SQL>

Q: Does ASM write anywhere which user logged in?

A: I would guess so, but in reality, it does not write which user. Just says [/], instead of user name. Looking the audit file generated in AUDIT_FILE_DEST location.

$ sqlplus bt1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 21:55:34 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> exit;

$ 
$ cat +ASM_ora_8024_20140305215536821385143795.aud
Audit file /u03/app/oracle/grid/12.1.0/rdbms/audit/+ASM_ora_8024_20140305215536821385143795.aud
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option
ORACLE_HOME = /u03/app/oracle/grid/12.1.0
System name:    Linux
Node name:      btsj.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Instance name: +ASM
Redo thread mounted by this instance: 0 
Oracle process number: 21
Unix process pid: 8024, image: oracle@btsj.com (TNS V1-V3)

Wed Mar  5 21:55:36 2014 -08:00
LENGTH : '149'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[0] ''

Q: You are connected as SYSDBA, how about if you connect as SYSASM?

A: Good question, here the audit file shows the actual user.

$ sqlplus bt1 as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 21:59:43 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> show user
USER is "SYS"
SQL> 
SQL> exit

$ cat +ASM_ora_8106_20140305215946201566143795.aud
Audit file /u03/app/oracle/grid/12.1.0/rdbms/audit/+ASM_ora_8106_20140305215946201566143795.aud
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option
ORACLE_HOME = /u03/app/oracle/grid/12.1.0
System name:    Linux
Node name:      btsj.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Instance name: +ASM
Redo thread mounted by this instance: 0 
Oracle process number: 21
Unix process pid: 8106, image: oracle@btsj.com (TNS V1-V3)

Wed Mar  5 21:59:46 2014 -08:00
LENGTH : '151'
ACTION :[7] 'CONNECT'
DATABASE USER:[3] 'bt1'
PRIVILEGE :[6] 'SYSASM'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[0] ''

March 6, 2014

Validate Database Links – Script

Here is a PL/SQL script to validate the database links accessible to an user. I wrote this to identify the working links after a database refresh or to verify links that connect from non-prod to prod…

Hope it helps…

REM Validate Database Links
REM Private links under connected user and Public links
REM
REM Biju Thomas - 29-Oct-2013
REM
set serveroutput on size 99999
set pages 0 lines 300 trims on
col spoolfile new_value spoolfname
select '/tmp/checklinks_'||user ||'_'||
       substr(global_name, 1, instr(global_name,'.')-1)||
       '.txt' spoolfile from global_name;
spool '&spoolfname'
declare
 --
 -- Get list of links the user has access to
 cursor mylinks is select db_link, owner, created, host, username
                   from all_db_links;
 --
 -- Identify other links in the DB for information
 cursor otherlinks is select db_link, owner
                      from dba_db_links
                      minus
                      select db_link, owner
                      from all_db_links;
 dbname varchar2 (200);
 currentuser varchar2 (30);
 linkno number := 0;
begin

 -- Current database and connected user
 select name, user into dbname, currentuser from v$database;
 dbms_output.put_line('Verifying Database Links '||currentuser||'@'||dbname);
 dbms_output.put_line('========================================================');
 --
 for linkcur in mylinks loop
  linkno := linkno + 1;
  dbms_output.put_line('Checking Link: ' || linkno) ;
  dbms_output.put_line('Link Name    : ' || linkcur.db_link) ;
  dbms_output.put_line('Link Owner   : ' || linkcur.owner) ;
  dbms_output.put_line('Connect User : ' || linkcur.username) ;
  dbms_output.put_line('Connect To   : ' || linkcur.host) ;
  begin
    --
    -- Connect to the link to validate, get global name of destination database
    execute immediate 'select global_name from global_name@"'||linkcur.db_link||'"' into dbname;
    dbms_output.put_line('$$$$ DB LINK SUCCESSFULLY connected to '||dbname);
    --
    -- end the transaction and explicitly close the db link
    commit;
    execute immediate 'alter session close database link "'||linkcur.db_link||'"';
  exception
    --
    -- DB Link connection failed, show error message
    when others then
    dbms_output.put_line('@@@@ DB LINK FAILED  @@@@');
    dbms_output.put_line('Error: '||sqlerrm);
  end;
  dbms_output.put_line('---------------------------------------');
  dbms_output.put_line(' ');
 end loop;
 dbms_output.put_line('Tests Completed.');
 --
 -- List other Links in the DB
 dbms_output.put_line('Other Private Links in the Database');
 dbms_output.put_line('Connect as respective owner to validate these.');
 dbms_output.put_line('----------------------------------------------');
 for olinks in otherlinks loop
   dbms_output.put_line(olinks.owner ||' :: '||olinks.db_link);
 end loop;
end;
/

spool
spool off
set pages 99 lines 80 trims off

Sample Output:

Verifying Database Links APPS@TST
========================================================
Checking Link: 1
Link Name    : CL.FE.NET
Link Owner   : PUBLIC
Connect User : M_S_DBLINK
Connect To   : prod
@@@@ DB LINK FAILED  @@@@
Error: ORA-28000: the account is locked
ORA-02063: preceding line from CL
---------------------------------------
Checking Link: 2

...
...
...

---------------------------------------
Checking Link: 28
Link Name    : EC1.E.NET
Link Owner   : APPS
Connect User : QUERY
Connect To   : (DESCRIPTION=(FAILOVER=yes)(LOAD_BALANCE=yes)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=B902A-VIP.e.net)(PORT=1532))(ADDRESS=(PROTOCOL=TCP)(HOST=B902B-VIP.e.net)(PORT=1532)))(CONNECT_DATA=(SERVICE_NAME=PROD)))
$$$$ DB LINK SUCCESSFULLY connected to PROD
---------------------------------------
Tests Completed.
Other Private Links in the Database
Connect as respective owner to validate these.
----------------------------------------------
SYSTEM :: CPROD.FE.NET
FQUERY :: GPROD2.FE.NET
FQUERY :: GASPRD.FE.NET
FQUERY :: PPROD.FE.NET
XXG :: PPROD.FE.NET
FQUERY :: SPROD.FE.NET

PL/SQL procedure successfully completed.

currently spooling to /tmp/checklinks_APPS_TST.txt

October 29, 2013