<<Back to Oracle ASM Main Page
What is Oracle ASM File Access Control
Oracle ASM File Access Control restricts the access of files to specific Oracle ASM clients (mainly database)that connect as SYSDBA.
Enabling ASM File Access Control requires following
• Linux or UNIX operating system.
• Job role separation at the OS level
• Disk group attributes must be set:
– COMPATIBLE.ASM to 11.2 or higher
– COMPATIBLE.RDBMS to 11.2 or higher
– ACCESS_CONTROL.ENABLED to TRUE
– ACCESS_CONTROL.UMASK to a mask value
Enabling ASM File Access Control requires following
• Linux or UNIX operating system.
• Job role separation at the OS level
• Disk group attributes must be set:
– COMPATIBLE.ASM to 11.2 or higher
– COMPATIBLE.RDBMS to 11.2 or higher
– ACCESS_CONTROL.ENABLED to TRUE
– ACCESS_CONTROL.UMASK to a mask value
What ACLs allows:
• Set permissions at the ASM file level.
• Permissions are none (0), read (4), or read-write (6).
• Permissions are available only on Linux and UNIX operating systems.
• Permissions are none (0), read (4), or read-write (6).
• Permissions are available only on Linux and UNIX operating systems.
When ASM File Access Control is enabled and an ASM file is created the file permissions is set as per the current access_control.umask setting of the DG
default value for which is 066
The permission of an ASM file can be granted to
owner, group, other
{0|2|6} {0|2|6} {0|2|6}
0 masks out nothing
2 masks out write
6 masks out both read and write permissions.
default value for which is 066
The permission of an ASM file can be granted to
owner, group, other
{0|2|6} {0|2|6} {0|2|6}
0 masks out nothing
2 masks out write
6 masks out both read and write permissions.
Use Case:
I have 2 databases configured as follows
DB_NAME OS_USER DG_GROUP
TST1T dbatst1 DG_TST_DATA
TST2T dbatst2 DG_TST2_DATA
I have 2 databases configured as follows
DB_NAME OS_USER DG_GROUP
TST1T dbatst1 DG_TST_DATA
TST2T dbatst2 DG_TST2_DATA
Check the Current Status of Access Control for DG
SQL> select GROUP_NUMBER,name from v$asm_diskgroup where name='DG_TST2_DATA';
GROUP_NUMBER NAME
------------ ------------------------------
1 DG_TST2_DATA
------------ ------------------------------
1 DG_TST2_DATA
SQL> select NAME,VALUE,GROUP_NUMBER from v$asm_attribute where GROUP_NUMBER=1 and NAME like '%access_control%';
NAME VALUE GROUP_NUMBER
------------------------------ ---------------------------------------- ------------
access_control.enabled false 1
access_control.umask 066 1
------------------------------ ---------------------------------------- ------------
access_control.enabled false 1
access_control.umask 066 1
Access Control for DG DG_TST2_DATA is false
Create a Dummy Tablespace
Lets us create a tablespace in dbatst1 database in DG_TST2_DATA DG
SQL> create tablespace AFAC_TST datafile '+DG_TST2_DATA' size 10M;
SQL> create tablespace AFAC_TST datafile '+DG_TST2_DATA' size 10M;
Tablespace created.
SQL> select name from v$datafile where name like '%_TST2%';
SQL> select name from v$datafile where name like '%_TST2%';
NAME
--------------------------------------------------------------------------------
+DG_TST2_DATA/TST1T/DATAFILE/afac_tst.269.987345611
--------------------------------------------------------------------------------
+DG_TST2_DATA/TST1T/DATAFILE/afac_tst.269.987345611
Lets login to ASM and try to delete this data file
login to OS as dbatst2 (which is not the owner of TST1T database) and login to ASM instance as sysdba
[dbatst2@TSTBOX]$asmcmd --privilege sysdba
ASMCMD>
ASMCMD> ls -l +DG_TST2_DATA/TST1T/DATAFILE/afac_tst.269.987345611
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE SEP 20 14:00:00 Y afac_tst.269.987345611
ASMCMD> rm AFAC_TST.269.987345611
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DG_TST2_DATA/TST1T/DATAFILE/AFAC_TST.269.987345611' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DG_TST2_DATA/TST1T/DATAFILE/AFAC_TST.269.987345611' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
But What if the Database is Down
SQL> select name from v$database;
NAME
---------
TST1T
---------
TST1T
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Database closed.
Database dismounted.
ORACLE instance shut down.
File Got Deleted.............
So if you are using separation of duties at OS Level you will definitely don't wont any third OS user (non DB Owner OS User) to delete your files from ASM.
And yes you can achieve this using oracle ASM file access control.
Now let us enable the ASM file access control and repeat the same again
How to Enable ASM File Access Control
Login to ASM and enable file access control
SQL>ALTER DISKGROUP DG_TST2_DATA SET ATTRIBUTE 'access_control.enabled' = 'true';
Create a Dummy Tablespace
Lets us create a tablespace in dbatst1 database in DG_TST2_DATA DG
SQL> create tablespace AFAC_TST datafile '+DG_TST2_DATA' size 10M;
SQL> create tablespace AFAC_TST datafile '+DG_TST2_DATA' size 10M;
Tablespace created.
SQL> select name from v$datafile where name like '%_TST2%';
SQL> select name from v$datafile where name like '%_TST2%';
NAME
--------------------------------------------------------------------------------
+DG_TST2_DATA/TST1T/DATAFILE/afac_tst.269.987348441
--------------------------------------------------------------------------------
+DG_TST2_DATA/TST1T/DATAFILE/afac_tst.269.987348441
login to OS as dbatst2 (which is not the owner of TST1T database) and login to ASM instance as sysdba
[dbatst2@TSTBOX]$asmcmd --privilege sysdba
ASMCMD> ls -l +DG_TST2_DATA/TST1T/DATAFILE/afac_tst.269.987348441
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE SEP 20 15:00:00 Y afac_tst.269.987348441
ASMCMD> cd +DG_TST2_DATA/TST1T/DATAFILE
ASMCMD> ls
AFAC_TST.269.987348441
ASMCMD> rm AFAC_TST.269.987348441
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group (DBD ERROR: OCIStmtExecute)
Conclusion:- Oracle ASM File access control feature is really very useful when you are running shared infrastructure and you want to avoid any accidental damage/deletion of the file alias etc..
This concept is a good way to enhance the knowledge.thanks for sharing.. Great article ...Thanks for your great information, the contents are quiet interesting.
ReplyDeleteSQL Azure Online Training
Azure SQL Training
SQL Azure Training