Check Oracle ASM File Access Control

Check the Access Control from SQL command in Oracle ASM

Check Oracle ASM File Access Control information with V$ASM_USER

SELECT dg.name AS diskgroup, u.group_number, u.user_number, u.os_id, u.os_name
FROM V$ASM_DISKGROUP dg, V$ASM_USER u
WHERE dg.group_number = u.group_number AND dg.name = 'DATA';

DISKGROUP GROUP_NUMBER USER_NUMBER OS_ID OS_NAME
--------- ------------ ----------- ----- -------
DATA      1            1           1001  oracle1
DATA      1            2           1002  oracle2
DATA      1            3           1003  grid

Check File Access Control information with V$ASM_USERGROUP

SELECT dg.name AS diskgroup, ug.group_number, ug.owner_number, u.os_name,
ug.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug
WHERE dg.group_number = ug.group_number AND dg.name = 'DATA'
AND ug.owner_number = u.user_number;

DISKGROUP GROUP_NUMBER OWNER_NUMBER OS_NAME USERGROUP_NUMBER NAME
--------- ------------ ------------ ------- ---------------- --------
DATA      1            3            grid    1                asm_data

Check File Access Control information with V$ASM_USERGROUP_MEMBER

SELECT dg.name AS diskgroup, um.group_number, um.member_number, u.os_name,
um.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP_MEMBER um,
V$ASM_USERGROUP ug WHERE dg.group_number = um.group_number AND
dg.group_number = ug.group_number AND dg.group_number = u.group_number AND dg.name = 'DATA'
AND um.member_number = u.user_number AND um.usergroup_number = ug.usergroup_number;

DISKGROUP GROUP_NUMBER MEMBER_NUMBER OS_NAME USERGROUP_NUMBER NAME
--------- ------------ ------------- ------- ---------------- --------
DATA      1            1             oracle1 1                asm_data
DATA      1            2             oracle2 1                asm_data

Check ASM File Access Control information with V$ASM_FILE

SELECT dg.name AS diskgroup, a.name, f.permissions, f.user_number, u.os_name,
f.usergroup_number, ug.name
FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug, V$ASM_FILE f, V$ASM_ALIAS a
WHERE dg.name = 'FRA' AND dg.group_number = u.group_number AND
u.group_number = ug.group_number AND ug.group_number = f.group_number AND
f.group_number = a.group_number AND
f.usergroup_number = ug.usergroup_number AND f.user_number = u.user_number AND
f.file_number = a.file_number;

DISKGROUP NAME                PERMISSIONS USER_NUMBER OS_NAME USERGROUP_NUMBER NAME
--------- ------------------- ----------- ----------- ------- ---------------- -------
DATA      USERS.259.685366091 rw-r-----   3           grid    1                asm_fra
DATA      TEMP.264.685366227  rw-r-----   3           grid    1                asm_fra

For more detail refer to the following link:
https://docs.oracle.com/en/database/oracle/oracle-database/18/ostmg/automatic-storage-management-administrators-guide.pdf

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.