Transcript Managing Users and their Roles

IT 21003 Database Administration
Section 06
Managing Users and Their Roles
Database Security
 Oracle’s database security provides the ability to
 Prevent unauthorized access to the database
 Prevent unauthorized access to schema objects
 Prevent unauthorized activity and audit user actions
 Control disk storage and system resource usage
(profiles)
Managing Users and Their Roles
Database Security
 System Security
Checks for usernames and passwords
Connect authorization
Controls availability of disk space
Controls resource limits, enables and controls auditing
Specifies allowed system operations
 Database Security
 Access to specific structures
 Types of access
Managing Users and Their Roles
Authenticating Users
 All users on Oracle must have an Oracle USERID
with an associated password
 Authentication is possible using various methods
 Operating System
 Database
 There are additional features for database administrator
authentication
 All of these methods can be used within the same
database
 Users cannot change their own authentication
method
 Only users with ALTER USER privilege can change it
Managing Users and Their Roles
Operating-System Authentication
 Authentication checks are made at the OS level
No userid and password necessary when logging on to
Oracle
An Oracle ID must match the incoming OS-ID
 A prefix is normally added to the OS-ID to form the
matching Oracle userid
 Prefix determined by the value of the parameter OS
AUTHENT PREFIX
 Default is OPS$ but can be set to other values (including null)
 A null string allows Oracle accounts to have the same userid as
the corresponding OS-ID
Managing Users and Their Roles
Operating-System Authentication
 Benefits
No need to hardcode passwords when accessing
Oracle applications
Users do not have to submit passwords twice to gain
access
 Oracle allows OS authentication in secure OS
environments
Managing Users and Their Roles
Operating-System Authentication
 Example:
 OS Account ID
 Database userid
fred
ops$fred
Managing Users and Their Roles
Creating and Setting up Users
 The CREATE USER statement
 Create User userid
Identified Externally | By password
{Default Tablespace tablespace_name}
{Temporary Tablespace tablespace_name}
[Quota integer K|M | Unlimited On tablespace_name]
[Profile profile_name]
Managing Users and Their Roles
Creating and Setting up Users
 A user with database authentication
Create User fred Identified By fredpw;
 A user with OS authentication
Create User ops$smithmg Identified Externally;
 Note: These statements do not give users access
to the database
 They will require the CREATE SESSION system
privilege
Managing Users and Their Roles
Creating a User: Example
 Create User ops$fred
Identified Externally
Default Tablespace user1
Temporary Tablespace temp1
Profile cashier;
Managing Users and Their Roles
Creating and Setting up Users
 Each user has a default tablespace in which to
store his or her objects
Can be overridden when the object is created
specifying an alternative tablespace
Default may be changed in an ALTER USER statement
 Remember, if no default or temporary tablespace
is specified, the SYSTEM is used as both
02-03-06
Managing Users and Their Roles
Manipulating User Accounts
 The ALTER USER statement can be used to
Change passwords
Change default and temporary tablespaces
Grant the use of space in a tablespace (Quota)
Set a profile for the user
Set default roles for the user (not covered in this class)
Change the authentication method
 Ordinary users may change only their passwords
 Alter User jonesja Identified By newpass;
Managing Users and Their Roles
Quotas on Tablespaces
 Users are not able to create database objects
until they have a quota on a tablespace (this
includes the default tablespace)
 Quota may be in Bytes, Kbytes, Mbytes
 Default is 0
Managing Users and Their Roles
Quotas on Tablespaces
 QUOTA UNLIMITED ON allows unlimited space
within a tablespace
 Create User john Identified By johnpw
Default Tablespace ts_one
Quota Unlimited On ts_one;
Managing Users and Their Roles
Quotas on Tablespaces
 Quotas may be reduced
A user cannot consume any more space until total
space consumed is less than the new quota
 Do Not allow user quotas on the SYSTEM
tablepsace
Managing Users and Their Roles
Tablespace Quota Information
 Information on tablespace quotas can be found in
dba_ts_quotas
 A value of -1 shows UNLIMITED ON tablespace
quota on the tablespace
 The quota limits extent allocation, not insert of
rows into existing extents
 Quotas are not necessary for temporary
tablespaces
Managing Users and Their Roles
Controlling Tablespace Usage
 Example Statement
 Alter User fred Quota 20M On user_1;
 Fred is not allowed to consume any more space until
his consumption falls below 20MB
 Alter User fred Quota 0 On user_1;
 Effectively removes the ability to create objects and
extend existing objects in the tablespace
 Existing extents are unaffected
Managing Users and Their Roles
Controlling Tablespace Usage
 Sum of tablespace quotas can be greater than the
tablespace size
This is not accounting it doesn’t have to be equal
 Quotas apply to owners of tables, not to users
who access them
 It is usual for applications to own tables, rather than the
users who access them
Managing Users and Their Roles
Dropping Users
 Use the DROP USER statement
 Drop User fred;
 Works only if the user does not own any database objects
 Drop User fred Cascade;
 Never Use This
 Drops all objects owned by the user
 Dependencies such as FK’s are also removed
 Care is necessary because this is a DDL
statement so no rollback is possible
Managing Users and Their Roles
Profiles
 Used to specify resource limits for users
 Profiles Control
Simultaneous sessions allowed per userid
Total CPU time allowed per session
Total CPU time allowed per call
Total connection time (minutes)
Idle time (minutes)
Logical reads per session
Logical reads per call
Size of private SQL area within the shared pool
 Will see profiles on the OCP certification exams
Managing Users and Their Roles
Profiles
 Since Oracle 8i we have a resource manager
 Has similar features to OS resource schedulers
 Allows DBA’s better control over database resources
that are not handled well by the OS
Managing Users and Their Roles
Profiles
 CREATE PROFILE statement
Create Profile clerk Limit
Sessions_Per_User 3
Cpu_Per_Session unlimited (hundredths of secs)
Cpu_Per_Call 7000 (hundredths of secs)
Connect_Time 1440 (in minutes)
Idle_Time 30 (minutes)
Logical_Reads_Per_Session unlimited
Logical_Reads_Per_Call 1500
Composite_Limit 20000
Private_Sga Default;
Managing Users and Their Roles
Profile Specifications
 The example on the previous slide creates a profile with
the following controls:
 The name of the profile is clerk
 Three sessions per user may be active (concurrent)
 Amount of Cpu per call is limited to 70 seconds
 A session may stay connected for 24 hours (24 x 60 = 1440)
 Thirty minutes of idle time is allowed
 Elapsed time of long-running queries does not contribute to idle time
 Logical data block reads have no limit for a session but are limited
to 1500 per call
 The composite limit of 20000 is a weighted sum of
cpu_per_session, logical_reads_per_session, connect_time, and
private_sga
 The number of bytes of private space in the SGA is set to default
 The view dba_profiles shows information about all profiles
Managing Users and Their Roles
DEFAULT Profile
 Every database has a default profile called
DEFAULT
 When the database is created, all of the Default
profile’s limits are set to unlimited
 Any user not explicitly assigned a profile are given
the Default profile
 Any parameter not defined in a profile takes on
the value assigned for that parameter in the Default
profile
 The parameter specifications for the Default
profile can be altered using the ALTER PROFILE
statement
Managing Users and Their Roles
Altering Profiles
 Any parameter in a profile may be changed by a
user with the ALTER PROFILE system privilege
Change does not affect currently connected sessions
 Example
 Alter Profile clerk Limit
Idle_Time 120
Logical_Reads_Per_Session 50000;
Managing Users and Their Roles
Assigning Users to Profiles
 Use the CREATE USER or ALTER USER
statement
 If a user is set to a new profile, the limits do not take
effect until the next user session
 A user can be in only one Profile
 Look at dba_users to find details of user’s profiles
Managing Users and Their Roles
Assigning Users to Profiles
 Examples
Create User user2 Identified By user2pw
Profile cashier;
Alter User user3 Profile cashier;
Create User user4 Identified By user4pw;
Alter User user5 Profile default;
Managing Users and Their Roles
Dropping Profiles
 Use the DROP PROFILE statement
 If users are currently assigned, the CASCADE option
must be used
 If a profile is dropped, all active sessions under the
profile observe the resource limits until disconnected
 Drop Profile clerk Cascasde;
 Never use Cascade option
 All users in clerk are not set to the default profile
 You cannot drop the Default Profile
Managing Users and Their Roles
Activating Resource Limits
 Although profile resource limits can be specified,
they are not activated until resource limitation is
enabled
 Resource limits can be enabled while the
database is open
 Use the ALTER SYSTEM statement as follows:
 Alter System Set Resource Limit = True;
 False is the default for this statement
 This enables resource limits but does not survive a
database shutdown
 Requires the ALTER SYSTEM system privilege
 Limits can be permanently set to True in the parameter
file with:
 Resource_Limit = True
Managing Users and Their Roles
Profiles and Password Management
 Profiles can also be used to manage passwords
by:
Controlling the number of failed login attempts
Appling account locking between attempted logins
Governing lifetime of passwords
Controlling warning periods for account expiration
Limiting password reuse
Using complex password management functions
 This feature does not require Resource_Limit to
be set to True
Managing Users and Their Roles
Password Management
 As part of a user’s profile, you can specify:
 Failed_Login_Attempts
 Limits number of failures allowed between successful logins
 Account_Lock_Time
 Number of days account remains locked after the value of
Failed_Login_Attempts is exceeded
 Password_Life_Time
 Number of days that the same password can be used
 Password_Grace_Time
 After password expires, this is the number of days after the first
successful login that the account remains accessible with the
same password (user receives a warning message)
Managing Users and Their Roles
Password Management
 Password_Reuse_Time
 Time interval during which a password may not be reused in
days
 Password_Reuse_Max
 Number of password changes before current password can be
reused
 Password management is always in effect in
Oracle8i and above
Managing Users and Their Roles
Forcing Account Expiration
 To expire an account in an interactive fashion
Need the ALTER USER system privilege
Alter User fred Password Expire;
 To unlock an account
 Alter User fred Account Unlock;
Managing Users and Their Roles
Password Complexity Verification Function
 There is a supplied script in
OracleHome/rdbms/admin (80% of usable scripts
are located in this folder) called utlpwdmg.sql
 This creates a function which performs the following
checks on a new password:
 Length >= 4
 Not Equal to userid
 Has at least one alpha, one numeric, and one punctuation
mark
 Does not match simple words
 Distinct from previous password by at least three characters
 DBA’s can generate and user their own checking
function script
Managing Users and Their Roles
Information on Password Management
 Use the user_password_limits table
 Select *
From user_password_limits;
Managing Users and Their Roles
Monitoring Users and Sessions
 Users can be monitored by using the Enterprise
Manager or
 Looking in the v$session table
 Select sid, serial#, username
From v$session;
Managing Users and Their Roles
Killing Sessions
 User sessions can sometimes be left running after
becoming inactive, or need killing because of longrunning queries in a hung situation
 Killing a user session involves identifying the
details in v$session
 Issue the Alter System Kill Session statement:
 Alter System Kill Session ‘9,17’;
 Can be disconnected with Enterprise Manager
also
Managing Users and Their Roles
Maintaining Privileges and Roles
 Oracle has two main types of privileges
 System
 Statement Level Privileges – Big Time
 Object
 Single Object Privileges - Small Time
 System-Level privileges control the use of DDL statements
Creation, Alteration, and Removal of objects
Connecting to the database
Execution of DBA functions
 Object-Level privileges provide access to database objects
 Selection from a table
 Update of view information
 Execution of stored procedure code
Managing Users and Their Roles
System Privileges
 Oracle has over 100 system privileges
Allows precise specification of what users can and
cannot do
However, security strategy can become complex to
manage
 System privileges convey much power to the
recipients
 Careful planning is required before granting such
privileges
 Syntax is simple
 Grant system_privilege_name To username;
Managing Users and Their Roles
System Privileges
 If a user needs to create tables, issue the
following:
Grant Create Table To fred;
Fred can now create tables in the fred schema
 System privileges are granted only by DBAs or
users with the GRANT ANY PRIVILEGE system
privilege
 To audit the system privileges granted to users,
query the dba_sys_privs table
Managing Users and Their Roles
Managing System Privileges
 System privileges may be granted with the ADMIN
OPTION
 Grant Alter Any Table To smith With Admin Option;
 This allows smith to alter the structure of any table in
any user schema and to pass this privilege on to any
other user
 When smith logs in, he or she can issue the following:
 Grant Alter Any Table To brown [With Admin Option];
Managing Users and Their Roles
Managing System Privileges
 System privileges may be removed from users
 Revoke system_privilege_name From username;
 Revoke Alter Any Table From smith;
 Brown will still be able to change any user’s tables as this only
affects smith
Managing Users and Their Roles
Object Privileges
 Object privileges are a mechanism to allow users
access to specific database objects
 By default, only the user who owns a database
object can perform any kind of activity or change on
it
 The owner has the full set of access rights on the
object
 Other users must be granted object privileges
 Grant Update On student To fred;
Managing Users and Their Roles
Security of Tables
 There are eight object privileges on tables:
ALTER
May perform DDL on the table
DELETE
Remove whole records (rows)
INDEX
Create indexes based on the table
INSERT [Columns]
Insert new rows into the table. If columns are
specified, only those columns may receive data.
REFERENCES
Reference the table for integrity purposes
SELECT
Query any data (all columns) in the table
UPDATE [Columns]
Update any data in the table. If columns is
specified, only those columns may be updated
ALL
All of the above may be performed
Managing Users and Their Roles
Granting Access to Tables
 Issue the GRANT statement
 Grant privilege(s) On object
To user(s) | role(s) | Public
[With Grant Option]
 Object privileges cannot be granted along with system
privileges and roles in the same GRANT statement
 Oracle knows the nature of the object specified in
the GRANT statement because all objects owned
by a user must have unique names
Managing Users and Their Roles
Useful Dictionary Views
 dba_tab_privs
Shows details of all table privileges in the database
 dba_col_privs
Shows details of all column privileges
 user_col_privs_recd
What column privileges I received
 user_col_privs_made
 What column privileges I created
Managing Users and Their Roles
Revoking Privileges
 Revoke all privileges on an object from a user:
Revoke All On student From fred;
Can revoke privileges individually also
 If a table is being referenced by foreign keys built
on it using the References privilege, the CASCADE
CONSTRAINTS option is needed
Revoke All On company From fred Cascade
Constraints;
Removes any foreign key constraints created by fred
on other tables
 Beware of PUBLIC grants
 Revoking privileges does not always guarantee a
denial if a user has Publicly granted privileges
Managing Users and Their Roles
Revoking Privileges Granted with the Grant
Option
 Example:
 John granted Mary the GRANT OPTION privilege
 Mary granted Steve and Marc a privilege
 John wishes to revoke Mary’s privileges
 Revoke Select On instructor From mary;
 This effect is immediate
 This revokes Steve and Marc’s privileges also
 This is different from System Privileges that use the
ADMIN OPTION
Managing Users and Their Roles
Managing Privileges with Roles
 The complexity of privilege management can be
reduced by using roles to group system and object
privileges for easier control
 Benefits of using roles
 Reduce privilege administration by allowing a cohesive
set of privileges to be granted to users through on role
grant
 Ease of maintenance
 Changing privileges for a role affects all users in the role
 Privileges lost when an object is dropped need to be re-
granted only to the role when the object is re-created
 Can allow ad-hoc or preprogrammed access to tables
through selective control of role availability
Managing Users and Their Roles
User and Application Roles
 Recommendation is to define:
 Application Roles
 Functional Areas
 User Roles
 This allows you to
 Grant application roles to users’ roles rather than
granting individual privileges
 Grant both user roles and application roles to users
 Roles do not belong to a schema (or user)
 Creation of a role puts the creator in the role with the
ADMIN OPTION
Managing Users and Their Roles
Creating and Granting Roles
 Use the CREATE ROLE statement
 Roles can be created by users with the CREATE ROLE
system privilege
 Create Role cashiers;
 Grant roles to users or to other roles
 Grant cashier To fred;
 Grant cashier To managers;
 The cashier role is now granted to the manager role (functional
role)
 To grant a role, you must have the GRANT ANY
ROLE system privilege or have been granted the
role with the ADMIN OPTION
Managing Users and Their Roles
Revoking Roles
 Roles are revoked in the same way as system
privileges
Revoke cashiers From fred;
Immediately disables any activity allowed by the role
 To selectively Revoke the ADMIN OPTION only
The system privilege (or role) must be revoked and
then re-granted without the ADMIN OPTION
 Beware of Revoking From (and Granting To)
PUBLIC
 Causes large numbers of dependency tracking
compilations
Managing Users and Their Roles
Pre-Created Roles in Oracle
 Look in dba_roles for these roles
 Dictionary Information on Roles:
Role_sys_privs
Role_tab_privs
Role_role_privs
Session_roles
System privileges granted to roles
Session_privs
User_role_privs
Dba_sys_privs
Privileges currently available
Dba_roles
All roles in the databse
Object level privileges granted to roles
Roles granted to other roles
Roles currently enabled for the user
Roles granted to user
System privileges directly granted to users and roles
Managing Users and Their Roles
Questions?