How to Create Personalized ASM User

<<Back to Oracle ASM Main Page

ASM User Management

You can create a personalized user in ASM and grant privileges to administer ASM Instance. This works similar to database instance except one thing
When you revoke the last privilege of a user in an Oracle ASM password file, the user is not automatically deleted as is done in the Oracle Database password file. You must run DROP USER to delete a user with no privileges in an Oracle ASM password file

Creating a New Personalized ASM User

SQL> set line 200
SQL>col USERNAME for a15
SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users;
USERNAME        SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM
--------------- ----- ----- ----- ----- ----- -----
SYS             TRUE  TRUE  TRUE  FALSE FALSE FALSE
ASMSNMP         TRUE  FALSE FALSE FALSE FALSE FALSE
SQL> CREATE USER ASM_PER1 IDENTIFIED by XXXXX;
User created.

SQL>select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users where USERNAME='ASM_PER1';
USERNAME                                 SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM
---------------------------------------- ----- ----- ----- ----- ----- -----
ASM_PER1                                 FALSE FALSE FALSE FALSE FALSE FALSE
SQL> GRANT SYSASM , sysdba TO ASM_PER1;
Grant succeeded.
SQL> grant sysdba, sysoper to ASM_PER1;
Grant succeeded.

Connecting to ASM Using Personalized ASM User

$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 18 14:49:35 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
SQL> conn ASM_PER1 as sysasm
Enter password:
Connected.
SQL> alter diskgroup DG_TEST mount;
Diskgroup altered.

Revoking Privileges from a Personalized ASM User

SQL>revoke SYSASM,sysdba,sysoper from ASM_PER1;
Revoke succeeded.
SQL>select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users where USERNAME='ASM_PER1';
USERNAME                                 SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM
---------------------------------------- ----- ----- ----- ----- ----- -----
ASM_PER1                                 FALSE FALSE FALSE FALSE FALSE FALSE
Note: As you can see the user is still present in password file although it does not have any privileges

Changing ASM User Password

SQL>ALTER USER ASM_PER1 IDENTIFIED BY xxxxxx;

Drop User from ASM

SQL> drop user  ASM_PER1;
User dropped.
SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users where USERNAME='ASM_PER1';
no rows selected