SYSASM privilege for ASM maintenance: (Oracle 11g)
Posted by decipherinfosys on March 27, 2009
In 11g, Oracle introduced new privilege sysasm specifically for maintaining the ASM instance. Corresponding operating system group is osasm group. The basic idea behind this is to distinguish between storage administrator and database administrator responsibilities. Ideally we can connect to ASM instance using one of the three privileges (sysasm or traditional sysdba and sysoper). Even though Oracle allows connecting to asm instance using sysdba privilege, it does write a warning message in alert+asm.log file. Eventually sysdba privilege will be removed from ASM maintenance. Let us create a user and assign him sysasm privilege.
C:\>sqlplus / as sysdba
Now create user and grant him sysasm privilege.
SQL> create user decipher identified by decipher
2 default tablespace users;
SQL> grant sysasm to decipher;
Since we are connected as sysdba, following warnings are recorded in the alert+asm.log file.
Wed Mar 25 23:11:13 2009
WARNING: Deprecated privilege SYSDBA for command ‘CREATE USER’
Thu Mar 25 23:13:19 2009
WARNING: Deprecated privilege SYSDBA for command ‘GRANT’
Using v$pwfile_users view, we can check what privilege has been granted to user ‘decipher’.
SQL> SELECT * FROM v$pwfile_users WHERE username=’DECIPHER’;
USERNAME SYSDB SYSOP SYSAS
—————————— —– —– —–
DECIPHER FALSE FALSE TRUE
When we are creating database using DBCA option and we choose ASM storage option, Oracle automatically creates password file during disk group configuration. The only user in the password file is SYS. In order to add new user to password file, we have to create user and grant sysasm privilege to the user.
Starting Oracle 11g, this is recommended approach to connect to ASM instance using sysasm privilege. Oracle plans to deprecate sysdba privilege for ASM instance in newer releases.
- Book: Oracle 11g New Features for Developers and DBAs by Sam Alapati and Charles kim
- Oracle 11g storage administrator guide: here.