1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle database Users, Passwords, their Privileges and Roles

Discussion in 'Server Administration and Options' started by Julian, Sep 7, 2008.

  1. Julian

    Julian Forum Advisor

    Likes Received:
    Trophy Points:
    Oracle database Users, Passwords, their Privileges and Roles

    User privileges may be defined as the permission by which users can perform certain tasks or run some commands on the Oracle database. A user privilege may be to run a SQL statement or access object of another user. Roles may be defined as the group of privileges or other roles that are granted to or evoked from users by administrators. An administrator may grant or evoke one or more roles from a user.

    There are three types of Oracle user privileges, namely,
    1. system privileges,
    2. object privileges and
    3. user roles.
    System privileges are those privileges that grant permission to or evoke permission to perform a specific database operation from a user. System privileges unlike other privileges are very powerful and complex in nature. Such privileges should be granted only to some special users. Regular database users are not granted such system privileges to ensure security and integrity of Oracle database. Data dictionary is protected by use of dictionary protection mechanism so that normal users cannot modify the data dictionary. Only users with administrative or explicit object privileges can access SYS schema objects. However, there are few other ways by which users are granted privileges to access SYS schema objects such as SELECT, EXECUTE, DELETE and SELECT ANY DICTIONARY.

    Every user may be allocated different privileges using GRANT and REVOKE statements.

    Roles need to be defined and enabled before any user can use it. Oracle database contains some predefined set of roles for your assistance. You can manage and manipulate the predefined roles similar to the user-defined roles. Some of the predefined roles in an Oracle database are:

    • DBA
    You may add up more predefined roles with every Oracle product you install.

    An administrator has to manage the user roles. This task involves creating a role; specify the authorization type for that role and dropping or eliminating roles. Any user with system privilege of CREATE ROLE can create roles. Privileges are not granted to any role during its creation and need to be specified afterwards. Roles can also be authorized by use of an application, enterprise directory service, by database or externally by network client or operating system. It is not so that the authorization method of a role cannot be changed. You can easily do so with the ALTER ROLE statement.

    A role may be dropped by any user who has DROP ROLE privilege to do so. To drop a role you need to use the ‘DROP ROLE’ SQL statement. Dropping a role means removing it from the list of roles of all the users. Users are granted privilege by use of GRANT statement and REVOKE statement to revoke the privileges.

    To maintain security and user authentication users are provided with passwords that are required to log on to system. A user may be authenticated by the operating system, network service or associated Oracle database. Passwords in Oracle database have features such as account locking, expiration period, password history, complexity verification etc. to ensure security of database.