Oracle 10g Database Administrator: Implementation and Administration

Oracle 10g Database Administrator: Implementation and Administration Chapter 12 Security Management

Objectives Create, modify, and remove users Discover when and how to create, use, and drop profiles Manage passwords View information about users, profiles, passwords, and resources Oracle 10g Database Administrator: Implementation and Administration 2

Objectives (continued) Identify and manage system and object privileges Grant and revoke privileges to users Understand auditing capabilities and practice using auditing commands Discover when and why to use roles Oracle 10g Database Administrator: Implementation and Administration 3

Objectives (continued) Learn how to create, modify, and remove roles Learn how to assign roles Examine data dictionary views of roles Assign roles and privileges using the Enterprise Management console Oracle 10g Database Administrator: Implementation and Administration 4

Users and Resource Control With a new DB instance, two users are created: – SYS Owns most of tables needed to run SB, and data dictionary views Owns a host of packages and procedures built into DB Can perform high-level tasks (e.g., starting up and shutting down DB instance), and backup/recovery tasks – Do not log on as SYS for routine tasks – SYSTEM Owns some tables, packages, and procedures Has the DBA role: it can perform routine DB administration tasks – Log on as SYSTEM to perform these routine tasks Oracle 10g Database Administrator: Implementation and Administration 5

Users and Resource Control (continued) During DB creation, Oracle creates other users to help it install some DB features – E.g., MDSYS owns objects related to Oracle Spatial – After DB creation, these users are disabled to prevent anyone from logging to DB with their accounts After the DB instance is up and running, you create users that own tables and other objects – So system and user tables are in distinct logical groups – You can limit the ability of each user to create objects You can create a profile, and assign it to any user After creating users to own the business tables, you must create users who access these tables Oracle 10g Database Administrator: Implementation and Administration 6

Creating New Users Oracle 10g Database Administrator: Implementation and Administration 7

Creating New Users (continued) GRANT CREATE SESSION TO STUDENTA, STUDENTB; Oracle 10g Database Administrator: Implementation and Administration 8

Modifying User Settings with the ALTER USER Statement Oracle 10g Database Administrator: Implementation and Administration 9

Modifying User Settings with the ALTER USER Statement (continued) Oracle 10g Database Administrator: Implementation and Administration 10

Modifying User Settings with the ALTER USER Statement (continued) ALTER QUOTA ALTER QUOTA USER STUDENTA UNLIMITED ON USER AUTO; USER STUDENTA 0 ON USERS; Oracle 10g Database Administrator: Implementation and Administration 11

Removing Users Removing users requires the DROP USER system privilege, which the SYSTEM user has. DROP USER user CASCADE; – Use CASCADE if user owns tables or DB objects If a user has created other users, those users are not dropped when the creating user is dropped – The new users do not belong to the original user’s schema If a user has created tables you want to keep, do not drop the user – Instead, change the user account to LOCK status Oracle 10g Database Administrator: Implementation and Administration 12

Removing Users (continued) Oracle 10g Database Administrator: Implementation and Administration 13

Introduction to Profiles Specify a profile when you create/alter a DB user Profile: collection of settings that limits the use of system resources and the database – A profile can be assigned to any number of users A user can be assigned only one profile at a time – A newly assigned profile overrides the old one » User’s current session isn’t affected by profile change – DEFAULT profile has no resource or DB use limits As a system grows, resources may become stretched – Profiles can be used for managing passwords too Oracle 10g Database Administrator: Implementation and Administration 14

Creating Profiles CREATE PROFILE profile LIMIT password setting . resource setting limit .; – Password settings: FAILED LOGIN ATTEMPTS, PASSWORD LIFE TIME, PASSWORD REUSE TIME, PASSWORD REUSE MAX, PASSWORD LOCK TIME, FAILED LOGIN ATTEMPTS, PASSWORD GRACE TIME, PASSWORD VERIFY FUNCTION – You can limit nine resources: SESSSIONS PER USER, CPU PER SESSION, CPU PER CALL, CONNECT TIME, IDLE TIME, LOGICAL READS PER SESSION, LOGICAL READS PER CALL, PRIVATE SGA, COMPOSITE LIMIT Oracle 10g Database Administrator: Implementation and Administration 15

Creating Profiles (continued) Examples: CREATE PROFILE PROGRAMMER LIMIT SESSIONS PER USER 2; CREATE PROFILE POWERUSER LIMIT PASSWORD LIFE TIME 60; Oracle 10g Database Administrator: Implementation and Administration 16

Managing Passwords There are three different areas to examine when working with passwords: – Changing a password and making it expire – Enforcing password time limits, history, and other settings – Enforcing password complexity Uses a combination of a function and a profile – Predefined SQL script to verify the complexity of a password – Adjust the PASSWORD VERIFY FUNCTION setting in a profile and assign that profile to a user Oracle 10g Database Administrator: Implementation and Administration 17

Managing Passwords (continued) Oracle 10g Database Administrator: Implementation and Administration 18

Managing Passwords (continued) Oracle 10g Database Administrator: Implementation and Administration 19

Managing Passwords (continued) Oracle 10g Database Administrator: Implementation and Administration 20

Managing Passwords (continued) Oracle 10g Database Administrator: Implementation and Administration 21

Managing Passwords (continued) Oracle 10g Database Administrator: Implementation and Administration 22

Controlling Resource Usage ALTER PROFILE, with resource clauses listed: ALTER PROFILE profile LIMIT password setting . SESSIONS PER USER concurrent sessions CPU PER SESSION hundredths of seconds CPU PER CALL hundredths of seconds CONNECT TIME minutes IDLE TIME minutes LOGICAL READS PER SESSION data blocks LOGICAL READS PER CALL data blocks PRIVATE SGA bytes COMPOSITE LIMIT service units Example: ALTER SYSTEM SET RESOURCE LIMIT TRUE; ALTER PROFILE PROGRAMMER LIMIT IDLE TIME 15 CPU PER CALL 100; ALTER RESOURCE COST CPU PER SESSION 1000 PRIVATE SGA 1; Oracle 10g Database Administrator: Implementation and Administration 23

Controlling Resource Usage (continued) Oracle 10g Database Administrator: Implementation and Administration 24

Dropping a Profile The syntax of DROP PROFILE is similar to the syntax for dropping a user in that it includes a CASCADE parameter: DROP PROFILE profile CASCADE; You must add CASCADE if any users have been assigned the profile being dropped – Oracle automatically resets these users to the DEFAULT profile For example, if three users have been assigned to the ACCT MGR profile, drop the profile like this: DROP PROFILE ACCT MGR CASCADE; Oracle 10g Database Administrator: Implementation and Administration 25

Obtaining User, Profile, Password, and Resource Data You have already seen the following data dictionary views while going through the chapter: – DBA USERS View user profile, password expiration date, and account status – DBA TS QUOTAS View the storage quotas of each user – RESOURCE COST View the weight setting for each resource used in calculating COMPOSITE COST – DBA PROFILES View the settings for each profile Oracle 10g Database Administrator: Implementation and Administration 26

Obtaining User, Profile, Password, and Resource Data (continued) Oracle 10g Database Administrator: Implementation and Administration 27

Obtaining User, Profile, Password, and Resource Data (continued) Oracle 10g Database Administrator: Implementation and Administration 28

Obtaining User, Profile, Password, and Resource Data (continued) Oracle 10g Database Administrator: Implementation and Administration 29

Obtaining User, Profile, Password, and Resource Data (continued) Oracle 10g Database Administrator: Implementation and Administration 30

Obtaining User, Profile, Password, and Resource Data (continued) Oracle 10g Database Administrator: Implementation and Administration 31

Obtaining User, Profile, Password, and Resource Data (continued) Oracle 10g Database Administrator: Implementation and Administration 32

Obtaining User, Profile, Password, and Resource Data (continued) Oracle 10g Database Administrator: Implementation and Administration 33

Obtaining User, Profile, Password, and Resource Data (continued) Oracle 10g Database Administrator: Implementation and Administration 34

Obtaining User, Profile, Password, and Resource Data (continued) Oracle 10g Database Administrator: Implementation and Administration 35

System and Object Privileges After a user has been created, the user must be assigned the ability to log on to the database – Once logged on, the user cannot perform any other tasks unless given the privilege to do so It is possible to give a privilege to all users Most privileges are given to specific users or roles – Role: named group of privileges that can be assigned to a user as a set rather than individually Two types of privileges: – System privileges – Object privileges Oracle 10g Database Administrator: Implementation and Administration 36

Identifying System Privileges SYSTEM has privileges needed for DBA activities There are over 100 system privileges; for example: – – – – – – – – – – SYSDBA SYSOPER CREATE SESSION CREATE TABLE and CREATE VIEW CREATE USER CREATE ANY TABLE DROP ANY TABLE SELECT ANY TABLE GRANT ANY [OBJECT] PRIVILEGE BACKUP ANY TABLE Oracle 10g Database Administrator: Implementation and Administration 37

Using Object Privileges Oracle 10g Database Administrator: Implementation and Administration 38

Managing System and Object Privileges When you grant a privilege, you assign a privilege to a user or a role, whether it is a system privilege or an object privilege When you revoke a privilege, you take away the privilege Granting privileges to roles is covered later in this chapter Oracle 10g Database Administrator: Implementation and Administration 39

Granting and Revoking System Privileges The basic syntax of the GRANT command for system privileges is: GRANT systempriv , systempriv ,. ALL PRIVILEGES TO user , user . PUBLIC WITH ADMIN OPTION; Revoking a system privilege is simple: REVOKE systempriv , systempriv ,. ALL PRIVILEGES FROM user , user ,. PUBLIC; Oracle 10g Database Administrator: Implementation and Administration 40

Granting and Revoking System Privileges (continued) Oracle 10g Database Administrator: Implementation and Administration 41

Granting and Revoking System Privileges (continued) Oracle 10g Database Administrator: Implementation and Administration 42

Granting and Revoking System Privileges (continued) Oracle 10g Database Administrator: Implementation and Administration 43

Granting and Revoking System Privileges (continued) Oracle 10g Database Administrator: Implementation and Administration 44

Granting and Revoking System Privileges (continued) Oracle 10g Database Administrator: Implementation and Administration 45

Granting and Revoking Object Privileges The syntax for granting object privileges looks like this: GRANT objectpriv , objectpriv ,. ALL ( colname ,.) ON schema . object TO user ,. PUBLIC WITH GRANT OPTION WITH HIERARCHY OPTION; Oracle 10g Database Administrator: Implementation and Administration 46

Granting and Revoking Object Privileges (continued) Oracle 10g Database Administrator: Implementation and Administration 47

Granting and Revoking Object Privileges (continued) Oracle 10g Database Administrator: Implementation and Administration 48

Granting and Revoking Object Privileges (continued) Oracle 10g Database Administrator: Implementation and Administration 49

Granting and Revoking Object Privileges (continued) Oracle 10g Database Administrator: Implementation and Administration 50

Granting and Revoking Object Privileges (continued) Oracle 10g Database Administrator: Implementation and Administration 51

Granting and Revoking Object Privileges (continued) Oracle 10g Database Administrator: Implementation and Administration 52

Description of Auditing Capabilities Monitoring activity in a database is called auditing – Three types can be run by Oracle 10g automatically: Statement auditing: AUDIT UPDATE TABLE BY JACK; Privilege auditing: AUDIT CREATE TABLE; Object auditing: AUDIT SELECT ON EE PRIVATE; Auditing commands have no effect until you set the AUDIT TRAIL initialization parameter – Modify the init.ora file or the spfile – Valid settings for AUDIT TRAIL: TRUE or DB, FALSE or NONE, OS Oracle 10g Database Administrator: Implementation and Administration 53

Description of Auditing Capabilities (continued) Syntax of AUDIT command for object auditing: AUDIT objpriv , objpriv ,. ALL ON schema . object DEFAULT NOT EXISTS BY SESSION BY ACCESS WHENEVER SUCCESSFUL WHENEVER NOT SUCCESSFUL; AUDIT syntax for auditing privileges: AUDIT priv , priv ,. ALL PRIVILEGES CONNECT RESOURCE DBA BY username BY SESSION BY ACCESS WHENEVER SUCCESSFUL WHENEVER NOT SUCCESSFUL; The syntax for auditing SQL statements is: AUDIT sql , sql . ALL BY username BY SESSION BY ACCESS WHENEVER SUCCESSFUL WHENEVER NOT SUCCESSFUL; Oracle 10g Database Administrator: Implementation and Administration 54

Description of Auditing Capabilities (continued) Oracle 10g Database Administrator: Implementation and Administration 55

Description of Auditing Capabilities (continued) Oracle 10g Database Administrator: Implementation and Administration 56

Description of Auditing Capabilities (continued) Oracle 10g Database Administrator: Implementation and Administration 57

Description of Auditing Capabilities (continued) Oracle 10g Database Administrator: Implementation and Administration 58

Description of Auditing Capabilities (continued) Data dictionary views you can query for audit trail results: – – – – – DBA AUDIT EXISTS DBA AUDIT OBJECT DBA AUDIT SESSION DBA AUDIT STATEMENT DBA AUDIT TRAIL The above metadata views have a corresponding USER counterpart, except DBA AUDIT EXISTS Oracle 10g Database Administrator: Implementation and Administration 59

Description of Auditing Capabilities (continued) You may want to turn off auditing or change what you are auditing – This is done with the NOAUDIT command Its structure is exactly like the AUDIT command; it turns off the auditing it names Example: NOAUDIT SELECT TABLE BY STUDENTB; NOAUDIT SELECT, UPDATE ON CLASSMATE.EMPLOYEE; Oracle 10g Database Administrator: Implementation and Administration 60

Database Roles A role is a collection of privileges that is named and assigned to users or even to another role A role can help you simplify database maintenance by giving you an easy way to assign a set of privileges to new users Oracle 10g Database Administrator: Implementation and Administration 61

How to Use Roles Oracle 10g Database Administrator: Implementation and Administration 62

How to Use Roles (continued) Oracle 10g Database Administrator: Implementation and Administration 63

Using Predefined Roles Oracle 10g Database Administrator: Implementation and Administration 64

Using Predefined Roles (continued) Oracle 10g Database Administrator: Implementation and Administration 65

Creating and Modifying Roles To create a role: CREATE ROLE name NOT IDENTIFIED IDENTIFIED BY password To assign privileges to a role: GRANT privilege TO role ; To assign the role to a user: GRANT role TO user role WITH ADMIN OPTION; The only part of a role you can change is whether it uses a password: ALTER ROLE name NOT IDENTIFIED IDENTIFIED BY password ALTER ROLE UPDATEALL IDENTIFIED BY U67DATR; Oracle 10g Database Administrator: Implementation and Administration 66

Creating and Assigning Privileges to a Role Example: CREATE ROLE SELALL; GRANT GRANT GRANT GRANT GRANT GRANT SELECT SELECT SELECT SELECT SELECT SELECT ON ON ON ON ON ON CLASSMATE.CLASSIFIED AD TO SELALL; CLASSMATE.CLASSIFIED SECTION TO SELALL; CLASSMATE.CUSTOMER TO SELALL; CLASSMATE.CUSTOMER ADDRESS TO SELALL; CLASSMATE.NEWS ARTICLE TO SELALL; CLASSMATE.EMPLOYEE TO SELALL; Oracle 10g Database Administrator: Implementation and Administration 67

Assigning Roles to Users and to Other Roles Oracle 10g Database Administrator: Implementation and Administration 68

Assigning Roles to Users and to Other Roles (continued) Oracle 10g Database Administrator: Implementation and Administration 69

Assigning Roles to Users and to Other Roles (continued) Oracle 10g Database Administrator: Implementation and Administration 70

Limiting Availability and Removing Roles You can control when a role becomes enabled for a user in these ways: – Default roles: Creator or the DBA can adjust roles for a user using ALTER USER ALTER USER username DEFAULT ROLE role ,. ALL ALL EXCEPT role ,. NONE – Enable roles: User role can enable or disable his role with the SET ROLE command SET ROLE role IDENTIFIED BY password ,. ALL ALL EXCEPT NONE – Drop roles: DBA can drop the role from the DB and thereby cancel the role for all users who had it DROP ROLE role Oracle 10g Database Administrator: Implementation and Administration 71

Limiting Availability and Removing Roles (continued) Oracle 10g Database Administrator: Implementation and Administration 72

Limiting Availability and Removing Roles (continued) Oracle 10g Database Administrator: Implementation and Administration 73

Limiting Availability and Removing Roles (continued) Oracle 10g Database Administrator: Implementation and Administration 74

Data Dictionary Information About Roles Oracle 10g Database Administrator: Implementation and Administration 75

Roles in the Enterprise Manager Console Oracle 10g Database Administrator: Implementation and Administration 76

Roles in the Enterprise Manager Console (continued) Oracle 10g Database Administrator: Implementation and Administration 77

Roles in the Enterprise Manager Console (continued) Oracle 10g Database Administrator: Implementation and Administration 78

Roles in the Enterprise Manager Console (continued) Oracle 10g Database Administrator: Implementation and Administration 79

Roles in the Enterprise Manager Console (continued) Oracle 10g Database Administrator: Implementation and Administration 80

Roles in the Enterprise Manager Console (continued) Oracle 10g Database Administrator: Implementation and Administration 81

Roles in the Enterprise Manager Console (continued) Oracle 10g Database Administrator: Implementation and Administration 82

Roles in the Enterprise Manager Console (continued) Oracle 10g Database Administrator: Implementation and Administration 83

Summary Users are created to either own a schema or access another user’s schema Users identified externally or globally are validated outside the database Tablespace quotas limit a user’s storage space Profiles store password and resource limits – Passwords can be changed by DBA and by user – Limits include how long a password can stay the same and when it can be reused Can limit CPU usage, connect time, and more Oracle 10g Database Administrator: Implementation and Administration 84

Summary (continued) System privileges allow user to manage some part of the database system – E.g., SYSDBA and SYSOPER allow user to start up and shut down the DB, and high-level tasks – A grant made to PUBLIC gives all users the privilege – Revoked privileges do not cascade to other users Object privileges allow a user to work with an object – Revoked object privileges cascade to other users – Object privileges can be granted on columns – Table owner can grant object privileges on that table Grantor grants privilege and grantee receives privilege – Querying an object without privileges to query causes an error stating that the object does not exist Oracle 10g Database Administrator: Implementation and Administration 85

Summary (continued) Auditing types: – – – – Statement: activity monitoring on a type of statement Privilege: audits commands authorized by privilege Object: generates audit trail records on object use A group of data dictionary views shows audit trail records for each type of auditing Roles simplify security administration – – – – – Can be granted other roles and privileges Predefined roles help speed up administration Roles with passwords add security to the roles Default roles are roles enabled when you log on Dropped roles are revoked from users and other roles Oracle 10g Database Administrator: Implementation and Administration 86