Oracle | SA_USER

Oracle SA_USER_ADMIN Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Use this package to add, alter and drop compartments, groups, labels, levels, and user access and privileges.

AUTHID CURRENT_USER

Dependencies

LBAC_STANDARD

SA_USER_ADMIN_INT

SA_UTL

Documented No

First Available 10.1

Security Model Owned by LBACSYS with no privileges granted.

Security Privileges

Privileges	Description
COMPACCESS	Allows session access to data authorized by the row's compartments, independent of the row's groups
FULL	Allows full read and write access to all data protected by the policy
PROFILE_ACCESS	Allows a session to change its labels and privileges to those of a different user
READ	Allows read access to all data protected by the policy
WRITE_ACROSS	Allows a user to set or change groups and compartments of a row label, but does not allow changes to the level. (Active only if LABEL_UPDATE is active.)
WRITE_DOWN	Allows users to set or lower the level, within a row label, to any level equal to or greater than the minimum level authorized for the user. (Active only if LABEL_UPDATE is active.)
WRITE_UP	Allows users to set or raise only the level, within a row label, up to the maximum level authorized for the user. (Active only if LABEL_UPDATE is active.)

Source {ORACLE_HOME}/rdbms/admin/prvtolsdd.plb

Subprograms

ADD_COMPARTMENTS	DROP_COMPARTMENTS	SET_LEVELS
ADD_GROUPS	DROP_GROUPS	SET_PROG_PRIVS
ALTER_COMPARTMENTS	DROP_USER_ACCESS	SET_ROW_LABEL
ALTER_GROUPS	SET_COMPARTMENTS	SET_USER_LABELS
DROP_ALL_COMPARTMENTS	SET_DEFAULT_LABEL	SET_USER_PRIVS
DROP_ALL_GROUPS	SET_GROUPS

ADD_COMPARTMENTS

Adds (assigns) compartments to a user's authorizations, indicating if the compartments are authorized for write and read privileges sa_user_admin.add_compartments( policy_name IN VARCHAR2, user_name IN VARCHAR2, comps IN VARCHAR2, access_mode IN VARCHAR2, in_def IN VARCHAR2, in_row IN VARCHAR2);

BEGIN 

        sa_user_admin.add_compartments(

          policy_name => 'DATA_ACCESS',

          user_name   => 'UWCLASS',

          comps       => 'MA',

          access_mode => SA_UTL.READ_ONLY,

          in_def      => 'Y',

          in_row      => 'Y');

      END;

      /

ADD_GROUPS

Adds (assigns) groups to a user, indicating if the groups are authorized for write and read privileges sa_user_admin.add_groups( policy_name IN VARCHAR2, user_name IN VARCHAR2, groups IN VARCHAR2, access_mode IN VARCHAR2, in_def IN VARCHAR2, in_row IN VARCHAR2);

BEGIN

        sa_user_admin.add_groups(

          policy_name => 'DATA_ACCESS',

          user_name   => 'UWCLASS',

          groups      => 'BD, EM, SM, IT',

          access_mode => SA_UTL.READ_ONLY,

          in_def      => 'Y',

          in_row      => 'Y');

      END;

      /

ALTER_COMPARTMENTS

Changes the write access, default label indicator, and row label indicator for the specified compartments sa_user_admin.alter_compartments( policy_name IN VARCHAR2, user_name IN VARCHAR2, comps IN VARCHAR2, access_mode IN VARCHAR2, in_def IN VARCHAR2, in_row IN VARCHAR2);

BEGIN

        sa_user_admin.alter_compartments(

          policy_name => 'DATA_ACCESS',

          user_name   => 'UWCLASS',

          comps       => 'FA',

          access_mode => SA_UTL.READ_ONLY,

          in_def      => 'Y',

          in_row      => 'Y');

      END;

      /

ALTER_GROUPS

Changes the write access, default label indicator, and row label indicator for the specified groups sa_user_admin.alter_groups( policy_name IN VARCHAR2, user_name IN VARCHAR2, groups IN VARCHAR2, access_mode IN VARCHAR2, in_def IN VARCHAR2, in_row IN VARCHAR2);

BEGIN

        sa_user_admin.alter_groups(

          policy_name => 'DATA_ACCESS',

          user_name   => 'UWCLASS',

          groups      => 'IT',

          access_mode => SA_UTL.READ_ONLY);

      END;

      /

DROP_ALL_COMPARTMENTS

Drop all assigned compartments from the named user for the named policy sa_user_admin.drop_all_compartments( policy_name IN VARCHAR2, user_name IN VARCHAR2);

exec sa_user_admin.drop_all_compartments('DATA_ACCESS', 'UWCLASS');

DROP_ALL_GROUPS

Drop all assigned groups from the named user for the named policy sa_user_admin.drop_all_groups( policy_name IN VARCHAR2, user_name IN VARCHAR2);

exec sa_user_admin.drop_all_groups('DATA_ACCESS', 'UWCLASS');

DROP_COMPARTMENTS

Drop the named compartment from the user's authorizations under the named policy sa_user_admin.drop_compartments( policy_name IN VARCHAR2, user_name IN VARCHAR2, comps IN VARCHAR2);

exec sa_user_admin.drop_compartments('DATA_ACCESS', 'UWCLASS', 'FA');

DROP_GROUPS

Drop the named groups from the user's authorizations under the name policy sa_user_admin.drop_groups( policy_name IN VARCHAR2, user_name IN VARCHAR2, groups IN VARCHAR2);

exec sa_user_admin.drop_groups('DATA_ACCESS', 'UWCLASS', 'OP');

DROP_USER_ACCESS

Removes all Oracle Label Security authorizations and privileges from the specified user based on the named policy sa_user_admin.drop_user_access( policy_name IN VARCHAR2, user_name IN VARCHAR2);

exec sa_user_admin.drop_user_access('DATA_ACCESS', 'UWCLASS');

SET_COMPARTMENTS

Assigns compartments to a user and identifies default values for the user's session label and row label sa_user_admin.set_compartments( policy_name IN VARCHAR2, user_name IN VARCHAR2, read_comps IN VARCHAR2, write_comps IN VARCHAR2, def_comps IN VARCHAR2, row_comps IN VARCHAR2);

exec sa_user_admin.set_compartments('DATA_ACCESS', 'UWCLASS', 'IS', 'IS', 'IS', 'IS');

SET_DEFAULT_LABEL

Sets a user's initial session label to the one specified sa_user_admin.set_default_label( policy_name IN VARCHAR2, user_name IN VARCHAR2, def_label IN VARCHAR2);

exec sa_user_admin.default_label('DATA_ACCESS', 'UWCLASS', 
      'P::OP::AO');

SET_GROUPS

Assigns groups to a user and identifies default values for the user's session label and row label sa_user_admin.set_groups( policy_name IN VARCHAR2, user_name IN VARCHAR2, read_groups IN VARCHAR2, write_groups IN VARCHAR2, def_groups IN VARCHAR2, row_groups IN VARCHAR2);

BEGIN 

        sa_user_admin.set_groups(

          policy_name  => 'DATA_ACCESS',

          user_name    => 'UWCLASS',

          read_groups  => 'SM',

          write_groups => 'IT',

          def_groups   => 'SM',

          row_groups   => 'SM');

      END;

      /

SET_LEVELS

Assigns a user minimum and maximum levels and identifies default values for the user's session label and row label sa_user_admin.set_levels( policy_name IN VARCHAR2, user_name IN VARCHAR2, max_level IN VARCHAR2, min_level IN VARCHAR2, def_level IN VARCHAR2 DEFAULT NULL, row_level IN VARCHAR2 DEFAULT NULL);

BEGIN 

        sa_user_admin.set_levels(

          policy_name  => 'DATA_ACCESS',

          user_name    => 'UWCLASS',

          max_level    => 'C',

          min_level    => 'P',

          def_level    => 'P');

      END;

      /

SET_PROG_PRIVS

Sets policy-specific privileges for program units. If NULL, then program privs are revoked.

To grant privileges to a stored program unit, you must have the POLICY_DBA role and EXECUTE on the SA_USER_ADMIN package. sa_user_admin.set_prog_privs( policy_name IN VARCHAR2, schema_name IN VARCHAR2, program_unit_name IN VARCHAR2, privileges IN VARCHAR2);

CREATE TABLE emp_work_sched(

      app_name VARCHAR2(30),

      dow_string  VARCHAR2(7),

      time_string VARCHAR2(24));

      

      INSERT INTO emp_work_sched

      (app_name, dow_string, time_string)

      VALUES

      ('UWCLASS', '_MTWTF_', 'GHIJKLMNOP');

      COMMIT;

      

      CREATE OR REPLACE FUNCTION val_work_sched RETURN SIGNTYPE AUTHID DEFINER IS

       dvalu emp_work_sched.dow_string%TYPE;

       retVal SIGNTYPE;

      BEGIN

        SELECT dow_string

        INTO dvalu

        FROM emp_work_sched

        WHERE app_name = 'UWCLASS';

      

        IF dvalu LIKE 'S%S' THEN

          retVal := 1;

        ELSIF dvalu LIKE '_M%F_' THEN

          retVal := 0;

        ELSE

          retVal := -1;

        END IF;

        RETURN retVal;

      END val_work_sched;

      /

      

      exec sa_user_admin.prog_privs('DATA_ACCESS', 'UWCLASS', 'VAL_WORK_SCHED', 'READ');

SET_ROW_LABEL

sets a user's initial row label to the one specified.

A user can reset the row label, but only to: A level that is less than or equal to the level of the session label, and greater than or equal to the user's minimum level

Include a subset of the compartments and groups from the session label, for which the user is authorized to have write access

Setting a row label to an invalid value is disallowed, and the row label value is unchanged. sa_user_admin.set_row_label( policy_name IN VARCHAR2, user_name IN VARCHAR2, row_label IN VARCHAR2);

exec sa_user_admin.set_row_label('DATA_ACCESS', 'UWCLASS', 'S::IT::IS');

SET_USER_LABELS

Sets the user's levels, compartments, and groups using a set of labels, instead of the individual components sa_user_admin.set_user_labels( policy_name IN VARCHAR2, user_name IN VARCHAR2, max_read_label IN VARCHAR2, max_write_label IN VARCHAR2, min_write_label IN VARCHAR2, def_label IN VARCHAR2 DEFAULT NULL, row_label IN VARCHAR2 DEFAULT NULL);

BEGIN 

        sa_user_admin.set_user_labels(

          policy_name     => 'hr_ols_pol',

          user_name       => 'jjones',

          max_read_label  => 'S::IT',

          max_write_label => 'S',

          def_label       => 'S',

          row_label       => 'S');

      END;

      /

SET_USER_PRIVS

Sets policy-specific privileges for a user.

Privs become effective at the next login replacing previously granted privs.NULL revokes all policy.

To assign policy privileges to users, you must have the EXECUTE privilege for the SA_USER_ADMIN package, and must have been granted the POLICY_DBA role. sa_user_admin.set_user_privs( policy_name IN VARCHAR2, user_name IN VARCHAR2, privileges IN VARCHAR2);

exec sa_user_admin.set_user_labels('DATA_ACCESS', 'UWCLASS', 'FULL');

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated:

DBSecWorx