Oracle Database Admin (DBA) Tutorial: User Management (Users, Roles and Privileges)

User Management

• Creating Users
• Altering Users

·       Dropping Users

1.    Creating Users:

·       System privilege required to CREATE USER.

·       DBA or security administrator has the CREATE USER system privilege.

 Ex: CREATE USER testuser IDENTIFIED BY password

            DEFAULT TABLESPACE users

           TEMPORARY TABLESPACE temp

           QUOTA UNLIMITED ON users;

Description:

}  CREATE USER command to create a user called testuser.

}  IDENTIFIED BY clause to define the password.

}  DEFAULT TABLESPACE keyword to define the location of the default tablespace, which is USERS.

}  TEMPORARY TABLESPACE keyword defines the temporary tablespace that will be assigned to the user, which in our case is the TEMP tablespace.

}  Unlimited quota to testuser on users tablespace.

}  IDENTIFIED Clause - how Oracle Database authenticates the user.

}  BY password - User must specify password to log on to the database.

}  QUOTAS - How much space a user can take up in tablespace.

}  PROFILE Clause - Specify the profile you want to assign to the user.
 It limits the amount of database resources the user can use.

}  DEFAULT TABLESPACE Clause – Specifies for objects that the user creates.
 If omit - then the user's objects are stored in the database default tablespace. If no default tablespace has been specified for the database, then the user's objects are stored in the SYSTEM tablespace.

}  TEMPORARY TABLESPACE Clause Specify the tablespace or tablespace group for the user's temporary segments.

2.    Altering Users

·       System Privileges Required  - ALTER USER

·       Security administrators have this system privilege.

·       Includes the ability to set tablespace quotas for a user on any tablespace in the database, even if the user performing the modification does not have a quota for a specified tablespace.

·       The following statement alters the security settings for the user, jiten:

Ex:-

ALTER USER testuser IDENTIFIED BY <password>

       DEFAULT TABLESPACE data_ts

       TEMPORARY TABLESPACE temp_ts

       QUOTA 100M ON data_ts ;

3.    Dropping Users

·       When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user schema.

·       Note: Do not attempt to drop the SYS or SYSTEM user. Doing so will corrupt your database.

·       A user that is currently connected to a database cannot be dropped.

·       To drop a connected user, you must first terminate the user sessions using the SQL statement ALTER SYSTEM with the KILL SESSION clause.

·       To drop a user and all the user schema objects (if any), you must have the DROP USER system privilege.

·       For dependent schema objects, use the CASCADE option to drop the user and all associated objects and foreign keys.

·       For example, if you intend to drop a user who owns a table, then check whether any views or procedures depend on that particular table.

·       The following statement drops the user, testuser and all associated objects and foreign keys that depend on the tables owned by testuser.

Ex:-

DROP USER testuser CASCADE;

4.    Listing All users and Associated Information

The following query lists all users and their associated information as defined in the database:

SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;

5.    Predefined Accounts: SYS and SYSTEM

·       The SYS and SYSTEM accounts have the database administrator(DBA) role granted to them by default.

·       The SYS account in addition has all privileges with ADMIN OPTION and owns the data dictionary.  To connect to the SYS account, you must use the AS SYSDBA clause.

·       Any user that is granted the SYSDBA  privilege can connect to the SYS account by using AS SYSDBA clause.

·       Only privileged users, who are granted the SYSDBA OR SYSOPER privilege, are allowed to start up and shut down the database instance.

·       The SYSTEM account is granted the DBA  role by default, but not the SYSDBA privilege.

·       The SYS and SYSTEM accounts are required accounts in the database. They can’t be dropped.

Privileges

·       A right to run a particular type of SQL statement, or the right to access an object belonging to another user, run a PL/SQL package, and so on. The types of privileges are defined by Oracle Database.

A.      System Privileges

B.      Object Privileges

             

1.       System Privileges:

Defines what actions a user is allowed to take within a database. 

Ex: The privilege to create tablespaces is a system privilege.

It is granted by the administrator or by someone who explicitly gives permission to administer the privilege. There are more than a hundred system privileges.

The most important system privileges are:

                             create session.

                             create table

                             create view

                             create procedure

                             sysdba

                             sysoper

a.       SYSOPER privilege:

Allows operations such as: 

              Startup a database,

                             Shutdown a database,

                             Backup a database,

                             Recover a database and

                             Create  a database

              This privilege allows the user to perform basic operational tasks without the ability to look at user data.

SYSDBA privilege: Includes all SYSOPER privileges plus full system privileges
        (with the ADMIN option), plus 'CREATE DATABASE' etc..

2.       Object Privileges: Object privileges allow a user to perform a particular action on a specific object, such as

                             Table

                             View

                             Sequence

                             Package

                             Directory

                             Procedure

                             Function

                             Package

                             Materialized View

·       Without specific permission, users can access only their own objects.

·       Object privileges can be granted by the owner of an object, by the administrator, or by someone who has been explicitly given permission to grant privileges on the object.

User Roles

·       A role is a set or group of privileges that can be granted to users or another role.

·       Oracle Database provides some predefined roles to help in database administration. 

·       These roles, are automatically defined for Oracle databases when you run the standard scripts that are part of database creation.

·       You can grant privileges and roles to, and revoke privileges and roles from, these predefined roles in the same way as you do with any role you define.

·       There are several roles that are defined automatically for Oracle databases when you can run database creation scripts. CONNECT is granted automatically to any user created with Enterprise manager. In earlier versions of the database(before 10g) the CONNECT role included more privileges, such as CREATE TABLE and CREATE DATABASE LINK, which have been removed for security reasons.