Managing ASM ACL With Command Line Utilities

Managing ASM ACL With Command Line Utilities

Using SQL Statements to Manage Oracle ASM File Access Control

You can use the ALTER DISKGROUP SQL statement to manage file access control for Oracle ASM disk groups. These SQL statements are available for both database and Oracle ASM instances.

The SQL statements that support disk group access control are:

• ALTER DISKGROUP ADD USERGROUP ... WITH MEMBER

Adds an Oracle ASM user group to a disk group. The user group name is limited to a maximum of 30 characters. The databases identified in the MEMBER clause must be in the disk group, as shown by V$ASM_USER, or the command returns an error. Any users authenticated as SYSASM or SYSDBA can create new user groups. For example:

SQL> SELECT group_number, os_name FROM V$ASM_USER;

GROUP_NUMBER OS_NAME

------------ ----------------------------------------------------------------

           1 oracle1

           1 oracle2

...

SQL> ALTER DISKGROUP data ADD USERGROUP 'test_grp1'

     WITH MEMBER 'oracle1','oracle2';

• ALTER DISKGROUP DROP USERGROUP

Drops an Oracle ASM user group from a disk group. Dropping a group might leave some files without a valid group. For those files to have a valid group, you must manually update the group associated with those files to a valid group.

SQL> ALTER DISKGROUP data DROP USERGROUP 'test_grp1';

• ALTER DISKGROUP MODIFY USERGROUP ADD MEMBER

Adds users to the specified user group. The users must be in the disk group, as shown by V$ASM_USER, or the command returns an error. Only the creator of the group or the Oracle ASM administrator can modify group membership.

SQL> ALTER DISKGROUP data MODIFY USERGROUP 'test_grp2' ADD MEMBER 'oracle2';

• ALTER DISKGROUP MODIFY USERGROUP DROP MEMBER

Removes users from the specified user group. If a member is not in the user group, then an error is returned. Only the creator of the group or the Oracle ASM administrator can modify group membership.

SQL> ALTER DISKGROUP data MODIFY USERGROUP 'test_grp2' DROP MEMBER 'oracle2';

• ALTER DISKGROUP ADD USER

Adds operating system (OS) users to an Oracle ASM disk group, so that these users can have access privileges on the disk group. The users must be existing operating system users, and their user names must have a corresponding operating system user ID or system ID. If a user exists in the disk group, as shown by V$ASM_USER, then the command records an error and continues to add other users, if any.

The operating system user of a running database instance is automatically added to a disk group when the database instance accesses that disk group and creates files. However, for a database instance to read files in a disk group without creating any files, then you must use the ADD USER clause to add that database user to the disk group. Also, you can use this clause to add a database user to an existing disk group immediately after setting the Oracle ASM File Access Control disk group attributes and before creating new files.

SQL>  ALTER DISKGROUP DATA ADD USER 'oracle1';

• ALTER DISKGROUP DROP USER

Drops operating system users from an Oracle ASM disk group. If a user is not in the disk group, then this command records an error and continues to drop other users, if any.

If the user owns any files on the same Oracle ASM disk group, then this command fails with an error, unless the CASCADE keyword is specified. If the latter case, then the user is deleted, along with all the files that the user owns.

If any files owned by the user are currently open, then the DROP USER command fails, and no files are deleted.

SQL>  ALTER DISKGROUP DATA DROP USER 'oracle1';

• ALTER DISKGROUP SET PERMISSION

Modifies permissions of an Oracle ASM file. Setting read only permission to a file that has read write permission revokes the write permission. Only the file owner or the Oracle ASM administrator can change the permissions of a file. You cannot change the permissions on an open file.

SQL> ALTER DISKGROUP data SET PERMISSION OWNER=read write, GROUP=read only,

     OTHER=none FOR FILE '+data/controlfile.f';

• ALTER DISKGROUP SET OWNERSHIP

Changes the owner or group of a file to the specified user or user group name, respectively. If the specified user or user group name does not exist, this command fails with an error. Only the owner of the file or the Oracle ASM administrator can run this command, and only the Oracle ASM administrator can change the owner. Also, the user group name must exist, and the owner of the file must be a member of that group. You cannot change the ownership of an open file.

SQL> ALTER DISKGROUP data SET OWNERSHIP OWNER='oracle1', GROUP='test_grp1'

     FOR FILE '+data/controlfile.f';