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] ''