INTRODUCTION TO ORACLE DATABASE ADMINISTRATION

Internet applications that are currently popular expose the database to various stakeholders. In this environment there is an increased need to understand database security. One of the main functions of a database administrator is to enforce these security standards for the database. A database administrator can control use of the database by allocating usernames and passwords and allocating disk space for users at the system level, as well as operations allowed on individual database objects at the database level.

There are four basic ways in which security on a database can be administered.

  1. Control who has access to the database
  2. Assign access to only selected objects of a database
  3. Record granted and received privileges in the Data dictionary
  4. Create Synonyms for database objects

A privilege is an authorization to perform specified operations within the database. Privileges are simply rights to execute certain SQL statements on specified database objects. All new users of the database must be given specific privileges before they can meaningfully use a database. To ensure, database security users must be given only the minimum set of privileges to perform their functions, and not any more. This is known as the principle of “least privilege.” The database administrator has the highest privilege and has the ability to grant users specific accesses to the database and objects within the database. High-level system privileges of a DBA include:

Action / Explanation / COMMANDS
Create new users / Users with this privilege can create other users / CREATE USER
Remove users / Can drop other users / DROP USER
Dropping tables / Drop any table in any schema / DROP ANY TABLE
Backing Up tables / Using the export utility to back up any table in any schema / BACKUP ANY TABLE

SQL> CREATE USER user

IDENTIFIED BY password;

SQL> CREATE USER scott

IDENTIFIED BY tiger;

Typically a new is allocated a tablespace for creating tables and index segments and also a sort space.

SQL> CREATE USER scott

IDENTIFIED BY tiger

DEFAULT TABLESPACE inventapp – used for tables and index segments

TEMPORARY TABLESPACE inventtemp; -- used for sorting

Once a user is created, DBA can then grant specific system privileges to that user. An application developer is typically the person who may need certain system privileges such as connection to databases, creating tables, sequences, triggers and procedures.

EXAMPLE:

SQL> GRANT CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE TO scott;

Managing these privileges to hundreds of individual users of an organization can become quite a daunting task. Oracle allows DBAs to create ROLES that simplifies the task of maintaining privileges for individual users. A ROLE can refer to a set of related privileges that are commonly managed together. As an example, inventory control managers of different products may need a collection of privileges. There is a many to many relationship between the users and roles. A single user can be assigned many roles and also a single role can be assigned to many users. There are certain predefined roles such as CONNECT, RESOURCE, DBA, SYSDBA AND SYSOPER. A database administrator (DBA) must first create a role with requisite privileges before assigning and managing it. The three step process in creating and managing roles include:

Step1: Create the role.

Step 2: Assign privileges to role.

Step 3: Assign users to role.

SQL> CREATE ROLE INVENTORYMANAGER;

SQL> GRANT CREATE TABLE, CREATE TRIGGER TO INVENTORYMANAGER;

SQL> GRANT MANAGER TO HINKEL, MANNING;

ALTERING PASSWORDS: While only an user with DBA privileges can create users and initialize the password for the first time, any user can later change the password (just for the logged in user) using ALTER USER statement.

SQL> ALTER USER manning IDENTIFIED BY thinline;

Alternatively a user can simply type password from the SQL PLUS session, which will take the user through the following steps:

SQL> password

Changing password for MANNING

Old password:

New password:

Retype new password:

Finally the DBA must actually give space quota on tablespaces:

SQL> ALTER USER manning QUOTA UNLIMITED ON inventapp;

OBJECT PRIVILEGES: A database object is an entity such as a table, view, trigger, procedure or a sequence that users create and store as part of a database. User privileges can be assigned privileges to perform specific actions on various database objects. These actions may range from simply being allowed to EXECUTE to actually DELETE the object. If the DBA wants to give access to specific columns of tables or only to rows that meet specific conditions, it can be easily achieved by creating a view of the table that creates the required subset of the table.

An owner of an object automatically has all the possible privileges on the object. While granting privileges on an object, users with DBA privileges can include a GRANT option – thus giving the grantee to grant object privileges to other users or roles.

EXAMPLES

SQL> GRANT SELECT ON parts TO manning, hinkel;

Allows SELECT access on parts tables to two users – manning and hinkel

SQL> GRANT SELECT, INSERT ON parts TO clayton;

Allows both select and insert access on the parts table to the userid clayton.

SQL> GRANT ALL ON parts TO superioruser;

Allows all possible privileges on tables parts to the username superioruser.

SQL> GRANT ALL ON parts TO firstuser WITH GRANT OPTION;

Allows all object privileges on parts to firstuser; in addition firstuser can now grant the privileges to other users.

SQL> GRANT UPDATE (PARTNAME, SELLINGPRICE) ON PARTS TO manning, hinkel;

Allows update of specific columns partname and sellingprice by usernames manning and hinkel.

SQL> GRANT SELECT parts TO PUBLIC;

The keyword PUBLIC signifies that the SELECT privilege is now being granted to all users of the system.

There are data dictionary tables that maintain a record of all the privileges granted. The DBA can look at these tables to verify the assigned privilege in case there is a question.

SQL> REVOKE SELECT, INSERT ON Parts FROM clayton;

Removes the listed privilege for user clayton.