SYSDBA and Now SYSASM

 There are two main administrative privileges in Oracle: SYSOPER and SYSDBA

(In version 11g this has been augmented by the SYSASM privilege, this basically works in the same manner technically but will not be addressed here see note 429098.1 “11g ASM New Feature” for more information)

SYSDBA and SYSOPER are special privileges as they allow access to a database instance even when it is not running and so control of these privileges is totally outside ofthe database itself.

SYSOPER privilege allows operations such as:
Instance startup, mount & database open ;
Instance shutdown, dismount & database close ;
Alter database BACKUP, ARCHIVE LOG, and RECOVER.

This privilege allows the user to perform basic operational tasks without the ability to look at user data.

SYSDBA privilege includes all SYSOPER privileges plus full system privileges
(with the ADMIN option), plus ‘CREATE DATABASE’ etc..
This is effectively the same set of privileges available when previously
connected INTERNAL.

The SYSASM privilege enables the separation of the database operating system credentials from the ASM credentials.

Use the SYSASM privilege instead of the SYSDBA privilege to connect to and administer an ASM instance. If you use the SYSDBA privilege to connect to an ASM instance, then Oracle writes warnings to the alert log file because commands that you run using the SYSDBA privilege on an ASM instance will eventually be deprecated.

Oracle writes alerts to the alert log files if you issue CREATE, ALTER, or DROP DISKGOUP statements that should be performed by SYSASM.

Alert entry

WARNING: Deprecated privilege SYSDBA for command 'STARTUP'
WARNING: Deprecated privilege SYSDBA for command 'SHUTDOWN'

SQL> create diskgroup dgext external redundancy disk ‘/dev/raw/raw7′
WARNING: Deprecated privilege SYSDBA for command ‘CREATE DISKGROUP’

As of 11G R2 if we try to alter a disk group:

alter diskgroup data mount;

We get the following error:

Error at line 1:

ORA-15032: not all alterations performed

ORA-15260: permission denied on ASM Disk group

As oracle moves forward sysdba will be fully deprecated on an ASM instance

Advertisements
Leave a comment

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

%d bloggers like this: