Users

DB User Accounts

DB Administrator accounts:
SYS account

• owns DataDictionary
• OS user logged with SYSDBA privilege becomes SYS account

SYSTEM account

• can’t upgrade database to the newer version
• can’t do backup&recovery

SYSMAN account

• can manage users
• manage resource
• select catalog

Creating user

User consists of unique username (<= 30 characters, alphanumeric $ and _) and authentication method (most comon password).

When using password we would create user as

CREATE USER username IDENTIFIED BY password;

Deleting user and all the objects he owns will be done with the following command

DROP USER username CASCADE;

When user is created he gets automatically default and temporary tablespace which is not optimal and we want to give users specific tablespace and certain amount on memory on it.

ALTER USER username QUOTA 10M ON tablespace_name;

Status

• OPEN – open for business
• LOCKED or EXPIRED – unable to be used

Privileges

List of all System and Object privileges HERE

We are granting privileges with GRANT, retrieving them with REVOKE and letting users grant those privileges on their own to other users with WITH GRANT command.

Special Admin privileges:
SYSDBA

• can do anything in DB
• user becomes SYS
• OS admin groups get that privilege automatically (Win ORA_DBA users, *NIX DBA user group)

SYSOPER

• no acces to data itself
• does backup, startup and shutdown of the DB

Roles

simplify administration. Role is a collection of privileges that can be given to as many users as we want.

GRANT rolename TO username;

Predefined roles are :

• DBA (SYS/SYSTEM)
• RESOURCE – can create tables and many pl/sql objects
• CONNECT – just establishing connection with the DB

Profiles

More on Profiles could be read HERE.

Assigning a profile to the user

ALTER USER username PROFILE profile_name;

 

*** Good link on this topic LINK