Systems Engineering and RDBMS

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.

SET ORACLE_SID=+ASM
SET ORACLE_HOME=C:\oracle\product\11.1.0

C:\>sqlplus / as sysdba

Now create user and grant him sysasm privilege.

SQL> create user decipher identified by decipher
2  default tablespace users;

User created.

SQL> grant sysasm to decipher;

Grant succeeded.

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.

Resources:

  • Book: Oracle 11g New Features for Developers and DBAs by Sam Alapati and Charles kim
  • Oracle 11g storage administrator guide:  here.
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: