Transcript Oracle Database 10g: Administration Workshop I

Administering User Security
Copyright © 2005, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
• Create and manage database user accounts
• Create and manage roles
• Grant and revoke privileges
• Control resource usage by users
9-2
Copyright © 2005, Oracle. All rights reserved.
User Management: Overview
•
•
Users
Jenny
•
Roles
HR_MGR
•
•
Privileges
9-3
Insert
employees
•
Create a user with an assigned
storage area (tablespace).
Assign quota to limit storage
usage.
Limit resource usage with
profile.
Authenticate a user with a
password.
Manage password rules with
profiles (expire passwords and
lock account).
Assign privileges to roles and
roles to users.
Copyright © 2005, Oracle. All rights reserved.
Database Users and Schemas
•
•
•
A schema is the collection of
objects owned by a user.
Username and schema are
often used interchangeably.
A user can be associated
with only one schema, but
he or she can use objects
from many schemas with the
appropriate permissions.
Schema Objects
Tables
Triggers
Indexes
Views
Sequences
Stored program units
Synonyms
User-defined data types
Database links
9-4
Copyright © 2005, Oracle. All rights reserved.
Database User Accounts
Each database user account has:
• A unique username
• An authentication method
• A default tablespace
• A temporary tablespace
• A user profile
• A consumer group
• A lock status
9-5
Copyright © 2005, Oracle. All rights reserved.
> User
Privilege
Role
Profile
Predefined Accounts: SYS and SYSTEM
•
The SYS account:
– Is granted the DBA role
– Has all privileges with ADMIN OPTION
– Is required for startup, shutdown, and some
maintenance commands
– Owns the data dictionary
– Owns the Automatic Workload Repository (AWR)
9-7
•
The SYSTEM account is granted the DBA role.
•
These accounts are not used for routine
operations.
Copyright © 2005, Oracle. All rights reserved.
Creating a User
Select Administration > Schema > Users & Privileges >
Users, and then click the Create button.
9-8
Copyright © 2005, Oracle. All rights reserved.
Privileges
User
> Privilege
Role
Profile
There are two types of user privileges:
• System: Enables users to perform particular
actions in the database
• Object: Enables users to access and manipulate a
specific object
HR_DBA
Object privilege:
Update employees
9-9
System privilege:
Create session
Copyright © 2005, Oracle. All rights reserved.
System Privileges
9-10
Copyright © 2005, Oracle. All rights reserved.
Object Privileges
To grant object privileges:
1. Choose the object type.
2. Select objects.
3. Select privileges.
9-12
Copyright © 2005, Oracle. All rights reserved.
Revoking System Privileges
with ADMIN OPTION
DBA
Jeff
Emi
GRANT
User
Privilege
Object
DBA
REVOKE
Jeff
REVOKE CREATE
TABLE FROM jeff;
9-13
Copyright © 2005, Oracle. All rights reserved.
Emi
Revoking Object Privileges
with GRANT OPTION
Bob
Jeff
Emi
Bob
Jeff
Emi
GRANT
REVOKE
9-14
Copyright © 2005, Oracle. All rights reserved.
Creating a Role
User
Privilege
> Role
Profile
Select Administration > Schema > Users & Privileges > Roles.
9-15
Copyright © 2005, Oracle. All rights reserved.
Benefits of Roles
•
•
•
9-16
Easier privilege management
Dynamic privilege management
Selective availability of privileges
Copyright © 2005, Oracle. All rights reserved.
Predefined Roles
9-17
CONNECT
CREATE SESSION
RESOURCE
CREATE CLUSTER, CREATE INDEXTYPE,
CREATE OPERATOR, CREATE PROCEDURE,
CREATE SEQUENCE, CREATE TABLE, CREATE
TRIGGER, CREATE TYPE
SCHEDULER_
ADMIN
CREATE ANY JOB, CREATE EXTERNAL JOB,
CREATE JOB, EXECUTE ANY CLASS,
EXECUTE ANY PROGRAM, MANAGE SCHEDULER
DBA
Most system privileges, several other roles. Do
not grant to nonadministrators.
SELECT_
CATALOG_
ROLE
No system privileges, but HS_ADMIN_ROLE and
over 1,700 object privileges on the data
dictionary
Copyright © 2005, Oracle. All rights reserved.
Secure Roles
• Roles may be nondefault.
SET ROLE vacationdba;
• Roles may be protected through authentication.
• Roles may also be secured programmatically.
CREATE ROLE secure_application_role
IDENTIFIED USING <security_procedure_name>;
9-18
Copyright © 2005, Oracle. All rights reserved.
Profiles and Users
Users are assigned
only one profile at
any given time.
Profiles:
• Control
resource
consumption
• Manage
account status
and password
expiration
9-19
Copyright © 2005, Oracle. All rights reserved.
User
Privilege
Role
> Profile
Where We Are
Done:
Creating a user
Creating a role
Assigning system and
object privileges to a role
Creating a profile
Limiting resource usage
with a profile
9-21
To Do—Modifying user
accounts:
•
Assigning storage area
(tablespace)
•
Assigning quota to limit
storage usage
•
Authenticating users with
passwords
•
Managing password rules
with profiles (expiring
passwords and locking
accounts)
•
Assigning privileges to
roles and roles to users
Copyright © 2005, Oracle. All rights reserved.
Modifying Users: Default and Temporary
Tablespaces and Locking
•
•
Default: Default location of database objects
Temporary: Used for sorting
Best practice: Be specific in your tablespace
assignments.
9-22
Copyright © 2005, Oracle. All rights reserved.
Assigning Quota to Users
Users who do not have the UNLIMITED TABLESPACE
system privilege must be given a quota before they
can create objects in a tablespace. Quotas can be:
• Unlimited
• A specific value in megabytes or kilobytes
9-23
Copyright © 2005, Oracle. All rights reserved.
Authenticating Users
•
•
•
9-25
Password
External
Global
Copyright © 2005, Oracle. All rights reserved.
Administrator Authentication
Operating System Security:
• DBAs must have the OS privileges to create and
delete files.
• Typical database users should not have the OS
privileges to create or delete database files.
Administrator Security:
• SYSBA and SYSOPER connections are authorized
via password file or OS.
– Password file authentication records the DBA user
by name.
– OS authentication does not record the specific user.
– OS authentication takes precedence over password
file authentication for SYSDBA and SYSOPER.
9-27
Copyright © 2005, Oracle. All rights reserved.
Locking and Unlocking Accounts
Failed login attempts
User
Account locked
Manual locking
DBA
Manual unlocking
Account unlocked
9-28
Copyright © 2005, Oracle. All rights reserved.
Setting Password Expiration
Password management includes the following:
• Specifying the maximum lifetime for a password
• Specifying a grace period for changing a
password
Note: Do not use profiles that cause the SYS, SYSMAN,
and DBSNMP passwords to expire and the accounts to
get locked.
9-29
Copyright © 2005, Oracle. All rights reserved.
Unlocking a User Account and
Resetting the Password
Select the user, and click Unlock User.
9-30
Copyright © 2005, Oracle. All rights reserved.
Assigning Privileges to Roles and
Roles to Users
Users
Jenny
Roles
Privileges
HR_MGR
Delete
employees
David
Rachel
HR_CLERK
Select
employees
Insert
employees
9-31
Copyright © 2005, Oracle. All rights reserved.
Update
employees
Assigning Roles to Users
9-32
Copyright © 2005, Oracle. All rights reserved.
Checklist for Creating Users
•
•
•
•
•
9-33
Select a profile.
Select an authentication technique.
Assign a default tablespace and temporary
tablespace.
Decide on quotas for each tablespace.
Grant privileges and roles to the user.
Copyright © 2005, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Create and manage user accounts
• Create and manage roles
• Grant and revoke privileges
• Control resource usage by users
9-34
Copyright © 2005, Oracle. All rights reserved.
Practice Overview:
Administering Users
This practice covers the following topics:
• Creating a profile to limit resource consumption
• Creating two roles:
a. HRCLERK
b. HRMANAGER
•
9-35
Creating three new users: One manager and two
clerks
Copyright © 2005, Oracle. All rights reserved.