Transcript Users and Basic Security

CHAPTER 6
Users and Basic Security
Progression of Steps for Creating a
Database Environment
1. Install Oracle database binaries (Chapter 1)
2. Create database (Chapter 2)
3. Establish a way to manage your environment (Chapter 3)
4. Create and manage tablespaces and datafiles (Chapter 4)
5. Understand and manage control files, online redo logs,
and archiving (Chapter 5)
6. Create users and establish basic security (Chapter 6)
User Management and Basic Security
Concepts
• Creating users
• Establishing password security
• Modifying users
• Dropping users
• Database profiles
• System privileges
• Object privileges
• Database roles
Difference between User and Schema
• User is the account through which you logon to the
•
•
•
•
•
database
Schema is a collection of database objects owned by a
user
When you logon to a database, by default your logon user
is associated with a schema of the same name
You can alter the schema that your logon user is
associated with via the ALTER SESSION SET
CURRENT_SCHEMA statement
Understanding the difference between user and schema
is subtle, and usually is not that important
People use the term user and schema synonymously, but
there is a slight difference
User vs. Schema example
SQL> conn / as sysdba
select user, schemaname
from sys.gv_$session
where sid =(select sid from sys.gv$mystat where rownum = 1);
USER
SCHEMANAME
------------------------------ -----------------------------SYS
SYS
SQL> alter session set current_schema = system;
select user, schemaname
from sys.gv_$session
where sid =(select sid from sys.gv$mystat where rownum = 1);
USER
SCHEMANAME
------------------------------ -----------------------------SYS
SYSTEM
Managing Default Users
• Lock default accounts not being used
• Unlock them as required
• SQL to generate SQL to lock and expire passwords:
select
'alter user ' || username || ' password expire account lock;'
from dba_users;
Checking Default Accounts
select
distinct u.username
,case when d.user_name is null then 'DBA created account'
else 'Oracle created account'
end
from dba_users u
,default_pwd$ d
where u.username=d.user_name(+);
Note: The DEFAULT_PWD$ view is available starting with
Oracle Database 11g. For more details about guidelines
regarding checking for default passwords, see My Oracle
Support note 227010.1.
Password ever been changed?
select
name
,to_char(ctime,'dd-mon-yy hh24:mi:ss')
,to_char(ptime,'dd-mon-yy hh24:mi:ss')
,length(password)
from user$
where password is not null
and password not in ('GLOBAL','EXTERNAL')
and ctime=ptime;
Considerations when Creating Users
• User name
• Authentication method
• Basic privileges
• Default permanent tablespace
• Default temporary tablespace
Choosing a User Name and
Authentication Method
• Usually you should choose a username that provides
some idea as to the use of the account
• Authentication methods
• Password
• External service, such as the OS
• Global user via enterprise directory service (Oracle Internet
Directory)
Assigning Default Permanent and
Temporary Tablespaces
select
username
,password
,default_tablespace
,temporary_tablespace
from dba_users;
• None of your users, other than the SYS user, should have
a default permanent tablespace of SYSTEM
• All of your users should have a temporary tablespace of
something like TEMP
Modifying Passwords
• Use the ALTER USER ... IDENTIFIED BY statement
• Oracle Database 11g and higher, passwords are case
sensitive
Enforcing Password Security
• Use easily remembered passwords so you don’t have
them written down or recorded in a file somewhere.
Because the passwords aren’t sophisticated, they aren’t
very secure.
• Enforce a level of sophistication for passwords. Such
passwords aren’t easily remembered and thus must be
recorded somewhere, which isn’t secure.
Enforcing Password Security
SQL> @?/rdbms/admin/utlpwdmg
• For Oracle Database 12c, set the
PASSWORD_VERIFY_FUNCTION of the DEFAULT profile to
ORA12C_VERIFY_FUNCTION :
SQL> alter profile default limit
PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
• If for any reason you need to back out of the new security
modifications, run this statement to disable the password
function:
SQL> alter profile default limit
PASSWORD_VERIFY_FUNCTION null;
Reasons for Logging On as a Different
User
• You’re copying a user from one environment (such as
production) to a different environment (such as test), and
you want to retain the original password.
• You’re working in a production environment, and you need
to be able to connect as the user who owns objects to
execute CREATE TABLE statements, issue grants, and so
on. In a production environment, you may not know the
user’s password because of poor maintenance
procedures.
Logging On as a Different User
1. As a DBA, temporarily store a user’s encrypted
password.
2. Change the user’s password.
3. Connect to the user with the new password, and run
Data Definition Language (DDL) statements.
4. Connect as a DBA, and change the password back to
the original.
Reasons to Modify a User Account
• To change a user’s password
• To lock or unlock a user
• To change the default permanent and/or temporary
tablespace
• To change a profile or role
• To change system or object privileges
• To modify quotas on tablespaces
Dropping Users
• First lock the account, this locks access to the account,
but not the account’s objects
• Make sure the account isn’t being used, you may want to
export the account before you drop it
• Drop the account
SQL> drop user mv_maint cascade;
Database Profiles
• Enforcing password security settings
• Limiting system resources that a user consumes
Implementing Password Security
• Use a database profile
• Attributes you can control:
• FAILED_LOGIN_ATTEMPTS
• PASSWORD_GRACE_TIME
• PASSWORD_LIFE_TIME
• PASSWORD_LOCK_TIME
• PASSWORD_REUSE_MAX
• PASSWORD_REUSE_TIME
• PASSWORD_VERIFY_FUNCTION
Limiting Database Resource Usage
• Set RESOURCE_LIMIT to TRUE
• Use a database profile
• Resource limits you can control:
• COMPOSITE_LIMIT
• CONNECT_TIME
• CPU_PER_CALL
• CPU_PER_SESSION
• IDLE_TIME
• LOGICAL_READS_PER_CALL
• LOGICAL_READS_PER_SESSION
• PRIVATE_SGA
• SESSIONS_PER_USER
Types of Privileges
• System privileges
• Object privileges
Assigning Database System Privileges
• Database system privileges allow you to do tasks such as
connecting to the database and creating and modifying objects
• There are hundreds of different system privileges
• You can view system privileges by querying the
DBA_SYS_PRIVS view:
SQL> select distinct privilege from dba_sys_privs;
PRIVILEGE
---------------------------------------ALTER ANY DIMENSION
ALTER ANY RULE
UPDATE ANY CUBE BUILD PROCESS
ALTER SYSTEM
...................
Assigning Database System Privileges
• SQL> grant create table, create database link to
inv_mgmt;
• SQL> grant create trigger to inv_user;
• To revoke a system privilege:
SQL> REVOKE <priv> FROM <user>;
Assigning Database Object Privileges
• Database object privileges allow you to access and
manipulate other users’ objects
• The types of database objects on which you can grant
privileges include tables, views, materialized views,
sequences, packages, functions, procedures, userdefined types, and directories
• To be able to grant object privileges, one of the following
must be true:
• You own the object
• You’ve been granted the object privilege with GRANT OPTION
• You have the GRANT ANY OBJECT PRIVILEGE system privilege
Assigning Database Object Privileges
Table level grants:
SQL> grant insert, update, delete, select on registrations to
inv_mgmt_app;
Column level grants:
SQL> grant insert (inv_id, inv_name, inv_desc) on
inventory to inv_mgmt_app;
Grouping and Assigning Privileges
• A role is a database object that allows you to logically
group system and/or object privileges together so you can
assign those privileges in one operation to a user
• Roles help you manage aspects of database security in
that they provide a central object that has privileges
assigned to it
Grouping and Assigning Privileges
SQL> grant select any table to jr_dba;
SQL> grant create any table to jr_dba;
SQL> grant create any view to jr_dba;
SQL> grant create synonym to jr_dba;
SQL> grant create database link to jr_dba;
SQL> grant jr_dba to lellison;
PL/SQL and Roles
• In PL/SQL, cannot access an object through a role
• Access must be granted explicitly
Summary
• After you create a database, one of your first tasks is to
secure any default user accounts.
• You should consider using a secure profile for any users
you create.
• Additionally, think about password security when creating
users.