managing users n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Managing Users PowerPoint Presentation
Download Presentation
Managing Users

Loading in 2 Seconds...

play fullscreen
1 / 15

Managing Users - PowerPoint PPT Presentation


  • 185 Views
  • Uploaded on

Managing Users. Objectives. Creating new database users Altering and dropping existing database users Monitoring information about existing users. Users and Security. Default tablespace. Account locking. DBA defines users who can access db

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Managing Users' - violet-jones


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
objectives
Objectives
  • Creating new database users
  • Altering and dropping existing database users
  • Monitoring information about existing users
slide3

Users and Security

Default

tablespace

Accountlocking

  • DBA defines users who can access db
  • Security domain defines the settings that apply to users

Authentication

mechanism

Temporary

tablespace

Security

domain

Role

privileges

Tablespacequotas

Direct

privileges

Resource

limits

slide4

Database Schema

(Some of the objects a user can own)

Tables

- Triggers

- Constraints

Indexes

Views

Sequences

Stored program units

Synonyms

User-defined data types

Database links

  • Schema: named collection of objects like tables, views, procedures, etc.
  • When a user is created a schema with same name is created
  • Hence username and schema name used interchangeably
slide5

Checklist for Creating Users

(Developers not end users)

  • 1. Choose a username and authentication mechanism.
  • 2. Identify tablespaces in which the user needs to store objects.
  • 3. Decide on quotas for each tablespace.
  • 4. Assign default tablespace and temporary tablespace.
  • 5. Create a user.
  • 6. Grant privileges and roles to the user.
creating a new user server authentication
Creating a New User: Server Authentication

Set the initial password:

CREATE USER peter

IDENTIFIED BY my1stson

DEFAULT TABLESPACE data01

TEMPORARY TABLESPACE temp

QUOTA 15m ON data01

PASSWORD EXPIRE;

Expires at login forcing user to change password

slide7

Creating a New User: Operating System Authentication

(User logs on to machine running Oracle server)

Use OS_AUTHENT_PREFIX (in parameter file)

Example: O/S User = user15;

OS_AUTHENT_PREFIX

OS_empty string““

OPS$(default)

Database User

OS_USER15USER15

OPS$USER15(default)

Remote LoginPossible

Create

Oracle

User:

OS_user15

No

No

Yes

slide8

Creating a New User: Operating System Authentication

(User logs on to machine running Oracle server)

  • E.g., An OS user tikekarr;
  • Use IDENTIFIED EXTERNALLY clause with create user
  • Also exists as a database user
  • Oracle will not validate
  • To use sql*plus say
    • Sqlplus /
creating a new user guidelines
Creating a New User: Guidelines
  • Choose a standard password initially;use O/S authentication sparingly.
  • Use the EXPIRE keyword to force users to reset their passwords.
  • Always assign temporary tablespace.
  • Restrict quotas to few users;use QUOTA UNLIMITED with caution.
  • Educate users:
    • To connect
    • To change password
controlling account lock and password
Controlling Account Lock and Password

ALTER USER peter

IDENTIFIED BY hisgrandpa

PASSWORD EXPIRE;

changing user quota on tablespace
Changing User Quota on Tablespace

ALTER USER peter

QUOTA 0 ON data01;

  • To get a user out of system (fired/resigned):
  • Use password expiration
  • Lock account
  • Alter password
  • Change profile
  • Export/import user schema elsewhere
dropping a user
Dropping a User

DROP USER peter;

  • Use the CASCADE clause if theschema contains objects.

DROP USER peter CASCADE;

User currently connected cannot be dropped

monitoring users
Monitoring Users

DBA_USERS

USERNAME

USER_ID

CREATED

ACCOUNT_STATUS

LOCK_DATE

EXPIRY_DATE

DEFAULT_TABLESPACE

TEMPORARY_TABLESPACE

DBA_TS_QUOTAS

USERNAME

TABLESPACE_NAME

BYTES

MAX_BYTES

BLOCKS

MAX_BLOCKS

monitoring users1
Monitoring Users

Select tablespace_name, blocks, max_blocks, bytes, max_bytes

From dba_ts_quota

Where username = ‘SCOTT’;

-1 in MAX_BLOCKS or MAX_BYTES indicates unlimited quota

Select username, account_status, temporary_tablespace

From dba_users;

-lists all users, their account status and temp. ts

summary
Summary
  • Creating users specifying the appropriate password mechanism
  • Controlling usage of space by users