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.

When administering users with SQL*Plus, the users must be existing operating system users and their user names must have corresponding operating system user IDs. However, only users in the same cluster as the Oracle ASM instance can be validated.

Note:

When modifying ownership of files, the previous owner of a file cannot be dropped while the file is still open even after the ownership of the file has changed.

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 operating system users of 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. 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 the operating system user associated with that database to the disk group. Also, you can use this clause to add the operating system user associated with a database 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 REPLACE USER

    Replaces one operating system user with another in an Oracle ASM disk group. The replacement user must not be a user currently in the disk group user list.

    If the command succeeds, all files that were previously owned by current user are now owned by the replacement user. The current user is automatically removed from the user list of the disk group. Adding the current user back to the disk group is possible, but this user does not own any files.

    When changing the owner of an open file, the new owner cannot be dropped with the owner's files until all instances within a cluster are restarted.

    Only an Oracle ASM administrator can run this command.

    SQL> ALTER DISKGROUP disk_group REPLACE USER oracle1 WITH oracle2;

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

    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.

    SQL> ALTER DISKGROUP data SET OWNERSHIP OWNER='oracle1', GROUP='test_grp1'
     FOR FILE '+data/controlfile.f';

See Also:

The ALTER DISKGROUP SQL statement in the Oracle Database SQL Language Reference