DBA Basic

Users Management

- by OracleWorld - Leave a Comment 170
Post Views: 170

There is no EDW (Enterprise Data Warehouse) system allowing only one user to access and operate with its data 🙂 There as in any other system Oracle will have to deal with different types of users and restrict their access. A multi-user environment requires strict and thought-out management or you will run into trouble sooner rather than later. In this article, I will introduce you to users, privileges, roles and profiles management.

Users

In Oracle, you can create, alter and delete users. Every user is associated with a password and that allows him (not only this – read further 🙂 ) to connect to the database.

The CREATE USER is a very powerful command and therefore is limited to DBAs and SYSADMINs only.

Here is an example of creating a user:

CREATE USER oracle_world 
    IDENTIFIED BY mySecretPassword 
    DEFAULT TABLESPACE common_tablespace 
    QUOTA 10M ON common_tablespace 
    TEMPORARY TABLESPACE temp
    QUOTA 5M ON system 
    PROFILE dba_user
    PASSWORD EXPIRE;

Let me elaborate on every single line:

CREATE USER

  • followed by your desired username
  • username cannot be the same as the role name
  • you can use such user as a proxy user as well

IDENTIFIED BY

  • followed by your password (password policies are set within profiles – see below)

DEFAULT TABLESPACE

  • followed by your defined tablespace (list from dba_tablespaces)
  • this is a space where users will have their objects stored by default (default is SYSTEM but it’s highly recommended to properly structure tablespaces)

QUOTA

  • this defines how much data can user store in a particular tablespace (defined in M as megabytes or G as gigabytes)
  • you cannot set a quota on the temporary tablespace
  • by default, there is no quota applied
  • you can revoke the user rights to store anything by setting the quota to 0
  • you can set unlimited tablespace on the user by executing this command:
    GRANT UNLIMITED TABLESPACE TO oracle_world

TEMPORARY TABLESPACE

  • this defines where temporary objects will be stored
  • whenever user issues any SQL statement – its data has to be stored in TEMP tablespace (check out this article about physical storage structures)

PROFILE

  • followed by a profile name (dba_profiles)
  • this is a set of restrictions and limits on database resources (find more below)

You can change user settings by issuing ALTER USER (all parameters allowed by creation are allowed here as well):

ALTER USER oracle_world 
  DEFAULT TABLESPACE another_tablespace 
  PROFILE dev_user;

With the same command, you can change the password

ALTER USER oracle_world IDENTIFIED BY myNewPassword; -- no single quotes !!

-- or eventually
ALTER USER oracle_world IDENTIFIED BY myNewPassword REPLACE mySecretPassword;

Or eventually, (un)lock the account

ALTER USER oracle_world ACCOUNT LOCK;

-- to unlock
ALTER USER oracle_world ACCOUNT UNLOCK;

Lastly, you can drop the user easily with DROP USER command

-- simple user drop
DROP USER oracle_world;

-- more complex (will check all associated user's object and foreign keys dependent on user's schema .. and drop them all as well)
DROP USER oracle_world CASCADE;ontrol c

Before you issue this command, check if you can safely drop a user.

Privileges

There are two types of privileges:

  • SYSTEM – allow users to perform database operations; they can be very powerful so you (as a DBA) should be very mindful when granting those
  • OBJECT – allow users to access and manipulate objects in the database

For more details about privileges check this article about Permissions.

Roles

Due to the complexity and a huge number of objects inside any database, it’s advisable to group object privileges into roles and grant those to users. Not only you can assign object privileges to a role but also you can nest other roles as well.

I strongly suggest not to go too deep with nested roles – don’t forget the KISS (Keep It Simple, Stupid) principle. It works 🙂

The following commands can be used to maintain roles: CREATE or DROP roles and GRANT them to users or roles. Column-level access can be controlled as well, as you can see in an example below.

--create roles
CREATE ROLE access_data;
CREATE ROLE read_data;

-- grant role to a role
GRANT read_data TO access_data;

-- grant role to a user 
GRANT access_data TO oracle_world; -- yes this user has both roles now

-- revoke role
REVOKE access_data FROM oracle_world;
 
-- drop
DROP ROLE read_data;

-- column level control 
GRANT INSERT (name, salary) ON employees TO oracle_world;

You can also control row-level access with VPD (Virtual Private Database). However, since this might be another article easily 🙂 please google it and check to documentation for more details.

There is more thing you can do with granting roles. You can grant them with extra permissions 🙂 Sounds catchy? Read on!

There are two types of extra privileges

  • GRANT role TO user WITH GRANT OPTION
  • GRANT role TO user WITH ADMIN OPTION

With Grant Option:

  • applicable on object privileges only (not system privileges)
  • only the user who granted it can revoke it
  • you can use CASCADE again to revoke many subsequent grants
  • can be granted to a role

With Admin Option:

  • applicable on system privileges only (not object privileges)
  • cannot be granted to a role

Check this script out to see how to List all privileges for a particular user.

Profiles

Same as users, you can CREATE, ALTER and DROP profiles.

CREATE PROFILE dev_user LIMIT 
   SESSIONS_PER_USER          UNLIMITED 
   CPU_PER_SESSION            UNLIMITED 
   CPU_PER_CALL               3000 
   CONNECT_TIME               45 
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL     1000 
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000
   FAILED_LOGIN_ATTEMPTS      5
   PASSWORD_LIFE_TIME         60
   PASSWORD_REUSE_TIME        60
   PASSWORD_REUSE_MAX         5
   PASSWORD_VERIFY_FUNCTION   verify_function
   PASSWORD_LOCK_TIME         1/24
   PASSWORD_GRACE_TIME        10;

Because all of them are self-explanatory I am not gonna describe every single entry there 🙂

You can also assign a profile to a user, which will limit him according to profile settings.

ALTER USER oracle_world PROFILE dev_user;

As you can with cascade the related objects when dropping a user, you can CASCADE the action too. When you run the

DROP PROFILE dev_user CASCADE;

all users connected to the database with this profile will be automatically assigned to a default profile.

Here are some most useful dba catalogs regarding user management.

  • DBA_USERS – list of users and their details
  • DBA_TS_QUOTAS – quota for users and their usage
  • DBA_PROFILES – list of profiles and their settings
  • PROXY_USERS – list of proxy users and users allowed to use them

There is one last catch I would like to point out. It is a “feature” (which is rather a bug IMO) that restricts you from using PL/SQL objects (procedures, packages, … ) when you grant an object to a role that is assigned to you. Somehow, you have to grant it directly to a user as well to work properly. Don’t ask me why 🙂

Hope this article helped you to understand the basics of what does it take to manage users and setup your environment accordingly.

Leave a Reply