Skip to main content

What is Oracle ASM File Access Control and Why You Need It


<<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

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.

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.

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

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
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 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;
Tablespace created.
SQL> select name from v$datafile where name like '%_TST2%';
NAME
--------------------------------------------------------------------------------
+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)

But What if the Database is Down

SQL> select name from v$database;
NAME
---------
TST1T
SQL> shut immediate;
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;
Tablespace created.
SQL> select name from v$datafile where name like '%_TST2%';
NAME
--------------------------------------------------------------------------------
+DG_TST2_DATA/TST1T/DATAFILE/afac_tst.269.987348441


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> 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..

Comments

  1. 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.
    SQL Azure Online Training
    Azure SQL Training
    SQL Azure Training

    ReplyDelete

Post a Comment

Popular posts from this blog

ORA-28374: typed master key not found in wallet

<<Back to Oracle DB Security Main Page ORA-46665: master keys not activated for all PDBs during REKEY SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY xxxx WITH BACKUP CONTAINER = ALL ; ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY xxxx WITH BACKUP CONTAINER = ALL * ERROR at line 1: ORA-46665: master keys not activated for all PDBs during REKEY I found following in the trace file REKEY: Create Key in PDB 3 resulted in error 46658 *** 2019-02-06T15:27:04.667485+01:00 (CDB$ROOT(1)) REKEY: Activation of Key AdnU5OzNP08Qv1mIyXhP/64AAAAAAAAAAAAAAAAAAAAAAAAAAAAA in PDB 3 resulted in error 28374 REKEY: Keystore needs to be restored from the REKEY backup.Aborting REKEY! Cause: All this hassle started because I accidently deleted the wallet and all wallet backup files too and also forgot the keystore password. There was no way to restore the wallet back. Fortunately in my case the PDB which had encrypted data was supposed to be deco...

How to Find VIP of an Oracle RAC Cluster

<<Back to Oracle RAC Main Page How to Find Out VIP of an Oracle RAC Cluster Login clusterware owner (oracle) and execute the below command to find out the VIP hostname used in Oracle RAC $ olsnodes -i node1     node1-vip node2     node2-vip OR $ srvctl config nodeapps -viponly Network 1 exists Subnet IPv4: 10.0.0.0/255.255.0.0/bondeth0, static Subnet IPv6: Ping Targets: Network is enabled Network is individually enabled on nodes: Network is individually disabled on nodes: VIP exists: network number 1, hosting node node1 VIP Name: node1-vip VIP IPv4 Address: 10.0.0.1 VIP IPv6 Address: VIP is enabled. VIP is individually enabled on nodes: VIP is individually disabled on nodes: VIP exists: network number 1, hosting node node2 VIP Name: node2-vip VIP IPv4 Address: 10.0.0.2 VIP IPv6 Address: VIP is enabled. VIP is individually enabled on nodes: VIP is individually disabled on nodes:

ORA-65104: operation not allowed on an inactive pluggable database alter pluggable database open

<<Back to DB Administration Main Page ORA-65104: operation not allowed on an inactive pluggable database SQL> alter pluggable database TEST_CLON open; alter pluggable database TEST_CLON open * ERROR at line 1: ORA-65104: operation not allowed on an inactive pluggable database Cause The pluggable database status was UNUSABLE. It was still being created or there was an error during the create operation. A PDB can only be opened if it is successfully created and its status is marked as NEW in cdb_pdbs.status column SQL> select PDB_NAME,STATUS from cdb_pdbs; PDB_NAME             STATUS -------------------- --------------------------- PDB$SEED             NORMAL TEST_CLON            UNUSABLE Solution:  Drop the PDB and create it again. Related Posts How to Clone Oracle PDB (Pluggable Database) with in the Same Container

ORA-46630: keystore cannot be created at the specified location

<<Back to DB Administration Main Page ORA-46630: keystore cannot be created at the specified location CDB011> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATAC4/CDB01/wallet/' IDENTIFIED BY "xxxxxxx"; ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATAC4/CDB01/wallet/' IDENTIFIED BY "EncTest123" * ERROR at line 1: ORA-46630: keystore cannot be created at the specified location Cause  Creating a keystore at a location where there is already a keystore exists Solution To solve the problem, use a different location to create a keystore (use ENCRYPTION_WALLET_LOCATION in sqlnet.ora file to specify the keystore location), or move this ewallet.p12 file to some other location. Note: Oracle does not recommend deleting keystore file (ewallet.p12) that belongs to a database. If you have multiple keystores, you can choose to merge them rather than deleting either of them.

ORA-16905: The member was not enabled yet

<<Back to Oracle DataGuard Main Page ORA-16905 Physical Standby Database is disabled DGMGRL> show configuration; Configuration - DG_ORCL1P   Protection Mode: MaxPerformance   Members:   ORCL1PP - Primary database     ORCL1PS - Physical standby database (disabled)       ORA-16905: The member was not enabled yet. Fast-Start Failover:  Disabled Configuration Status: SUCCESS   (status updated 58 seconds ago) DGMGRL> DGMGRL> enable database 'ORCL1PS'; Enabled. DGMGRL>  show configuration; Configuration - DG_ORCL1P   Protection Mode: MaxPerformance   Members:   ORCL1PP - Primary database     ORCL1PS - Physical standby database Fast-Start Failover:  Disabled Configuration Status: SUCCESS   (status updated 38 seconds ago)

How to Switch Log File from All Instances in RAC

<<Back to Oracle RAC Main Page Switch The Log File of All Instances in Oracle RAC. In many cases you need to switch the logfile of the database. You can switch logfile using alter system switch logfile command but if you want to switch the logfile from all the instances you need to execute the command on all the instances individually and therefore you must login on all the instances. You can avoid this and switch logfile of all instances by just running the below command from any of the instance in RAC database SQL> ALTER SYSTEM SWITCH ALL LOGFILE;   System altered.

ORA-46655: no valid keys in the file from which keys are to be imported

<<Back to DB Administration Main Page SQL> administer key management import encryption keys with secret "xxxx" from '/tmp/pdb02_tde_key.exp' force keystore identified by "xxxx" with backup; administer key management import encryption keys with secret "xxxxxx" from '/tmp/pdb02_tde_key.exp' force keystore identified by "xxxxxx" with backup * ERROR at line 1: ORA-46655: no valid keys in the file from which keys are to be imported Cause: Either the keys to be imported already present in the target database or correct container (PDB) is not set. Solution: In my case I got the error because I attempted to import the keys for newly plugged database PDB02 from CDB$ROOT container. To Solve the issue just switched to the correct container and re run the import. SQL> show con_name CON_NAME ------------------------------ CDB$ROOT <===Wrong Container selected  SQL> alter session set container=PDB02; Session alt...

Starting RMAN and connecting to Database

  <<Back to Oracle Backup & Recovery Main Page Starting RMAN and connecting to Database Starting RMAN and connecting to Database To start RMAN you need to set the environment and type rman and press enter. You can connect to database either using connect command or using command line option. using command line option localhost:$ export ORACLE_HOME=/ora_app/product/18c/dbd2 localhost:$ export PATH=$ORACLE_HOME/bin:$PATH localhost:$ export ORACLE_SID=ORCL1P localhost:$ rman target / Recovery Manager: Release 18.0.0.0.0 - Production on Sun Apr 4 08:11:01 2021 Version 18.11.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved. connected to target database: ORCL1P (DBID=4215484517) RMAN> using connect option localhost:$ rman RMAN> connect target sys@ORCL1P  target database Password:******** connected to target database: ORCL1P (DBID=4215484517) NOTE: To use connect command you need to ensure that  you have proper TNS sentry...

Error: ORA-16883: unable to translate DGConnectIdentifier property

<<Back to Oracle DataGuard Main Page Error: ORA-16883: unable to translate DGConnectIdentifier property DGMGRL> show configuration; Configuration - dg_tst1t   Protection Mode: MaxPerformance   Members:   tst1tp - Primary database     Error: ORA-16778: redo transport error for one or more members     tst1ts - Physical standby database       Error: ORA-16883: unable to translate DGConnectIdentifier property DGMGRL> show database tst1ts Database - tst1ts   Role:               PHYSICAL STANDBY   Intended State:     APPLY-ON   Transport Lag:      (unknown)   Apply Lag:          (unknown)   Average Apply Rate: (unknown)   Real Time Query:    OFF   Instance(s):     TST1T Database S...

How to Modify Database Startup Mode (Startoption) in Clusterware Registry

<<Back to Oracle RAC Main Page How to Modify Database Startup Option Using srvctl In this post I will show you, how you can modify the database startup option / Database startup mode in clusterware registry using srvctl. There are some time requirement to change the startup option (from default  OPEN ) to  MOUNT, or "READ ONLY" eg if in case of Physical Standby Configuration Let us Check the Current Configuration $ srvctl config database -d ORCL Database unique name: ORCL Database name: Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_2 Oracle user: oracle Spfile: +DATA/ORCL/PARAMETERFILE/spfileORCL.ora Password file: +DATA/ORCL/PASSWORD/pwORCL Domain: Start options: open Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Server pools: Disk Groups: DATA,RECO Mount point paths: Services: Type: RAC Start concurrency: Stop concurrency: OSDBA group: oinstall OSOPER group: oinstall Database instances: ORCL1,...