Chapter 7 - PowerPoint Presentation

Slide1

Chapter 7User Creation and Management

Jason C. H. Chen

,

Ph.D.

Professor of MIS

School of Business

Gonzaga University

Spokane, WA 99258 USA

chen@gonzaga.eduSlide2

-- Oracle_ch4_Spool_Chen_Jason.txtSQL> --7.SQL> ALTER TABLE book_stores

2 DROP CONSTRAINT

book_stores

_rep_id_fk

;

Table altered.

SQL>

SQL> ALTER TABLE

book_stores

2 ADD CONSTRAINT

book_stores

_rep_id_fk

FOREIGN KEY(

rep_id

)

3 REFERENCES

store_reps

(

rep_id

) ON DELETE CASCADE;

Table altered.Slide3

HW!!Practice all the examples in the text.A Script file is available on the Bb (file name:

Ch4Queries.sql

)

After completing all examples, do the HW (hint: see the tables below for the final schema and the sample output on the Bb under “Assignments”

rep_idlastfirstcommbase_salaryNUMBER(5)VARCHAR2(15)VARCHAR2(10)CHAR(1)NUMBER(7,2)

store_idnamecontactrep_idNUMBER(8)VARCHAR2(30)VARCHAR2(30)NUMBER(5)

store_idnamequarterrep_idNUMBER(8)VARCHAR2(5)CHAR(3)NUMBER(5)

pk

store_reps

pk

fk

book_stores

cpk

, fk

cpk

cpk,

fk

rep_contractsSlide4

SQL> --7.SQL> ALTER TABLE book_stores 2 DROP CONSTRAINT bookstores

_rep_id_fk

;

Table altered.SQL> SQL> ALTER TABLE book_stores 2 ADD CONSTRAINT bookstores_rep_id_fk FOREIGN KEY(

rep_id) 3 REFERENCES store_reps (rep_id) ON DELETE CASCADE;Table altered.Discuss Oracle-4:#7 – why DROP first?Slide5

Discuss Oracle-4:#7 – why DROP first? And

How was

tablename

saved in the constraint tableSlide6

select constraint_name from user_constraints where table_name='BOOK_STORES

';

CONSTRAINT_NAME

--------------------------------------------------------------------------------SYS_C0090515BOOKSTORES_REP_ID_FKBOOKSTORES_STORE_D_PKBOOKSTORES_NAME_UK

SQL> --7(new) 9/28/2017SQL> ALTER TABLE book_stores 2 DROP CONSTRAINT bookstores_rep_id_fk;Table altered.SQL> ALTER TABLE book_stores 2 ADD CONSTRAINT bookstores_rep_id_fk FOREIGN KEY(rep_id) 3 REFERENCES store_reps (rep_id) ON DELETE CASCADE;Table altered.SQL> select constraint_name from user_constraints where table_name='BOOK_STORES';CONSTRAINT_NAME--------------------------------------------------------------------------------BOOKSTORES_REP_ID_FKSYS_C0090515BOOKSTORES_NAME_UKBOOKSTORES_STORE_ID_PKSlide7

ObjectivesExplain the concept of data security

Create a new user account

Identify two types of privileges: system and object

Grant privileges to a userAddress password expiration requirements

Change the password of an existing accountSlide8

Objectives (continued)

Create a role

Grant privileges to a role

Assign a user to a roleView privilege informationRevoke privileges from a user and a role

Remove a user and rolesSlide9

Basic SQL Concepts

DDL (Data Definition Language)

commands that work with the objects (tables, indexes, views, etc.) in the database. e..g., CREATE, ALTER, DROP, and RENAME.

DML (Data Manipulation Language)

commands that work with the (physical) data in the database. e.g., SELECT, INSERT, UPDATE, and DELETEDCL ( )commands that control a database, including administering privileges. e.g., GRANT, REVOKE.Data Control LanguageSlide10

Data Security

User accounts provide a method of authentication

They can grant access to specific objects

They identify owners of objectsSlide11

Creating a User

The CREATE USER command gives each user a user name and password

Figure 7-1 Syntax of the CREATE USER command

Can you perform this command? Why?Slide12

Assigning User Privileges

There are two types of privileges

System

privilegesAllow access to the database and execution of DDL operations

Object privileges Allow a user to perform DML and query operations Slide13

Practice

Type the following command

SELECT * FROM

location; I grant the following to all of you:

GRANT SELECT ON location TO PUBLIC;You type the following again SELECT * FROM c##chen.location;I revoke the following from you: REVOKE SELECT ON location FROM PUBLIC;You type the following again SELECT * FROM c##chen.location;Just for the instructor: @ c:\oradata\NW_CW\northwoods.sqlSlide14

Examples of Object Privileges

Object Type

Privilege

Description

Table, SequenceALTERAllows user to change object’s structure using the ALTER commandTable, SequenceDROPAllows user to drop objectTable, SequenceSELECT

Allows user to view objectTableINSERT, UPDATE, DELETEAllows user to insert, update, delete table dataAny database objectALLAllows user to perform any operation on objectSlide15

Pseudo-columns

Acts like a column in a database query

Actually a command that returns a specific values

Used to retrieve:Current system date Name of the current database userNext value in a sequence

PseudocolumnNameOutputCURRVALMost recently retrieved sequence valueNEXTVALNext value in a sequenceSYSDATECurrent system date from database serverUSERUsername of current userSlide16

Retrieving the current system date:

SELECT SYSDATE

FROM DUAL;

Retrieving the name of the current user:SELECT USER

FROM DUAL;DUAL is a system table that is used with pseudo-columnsUsing Pseudo-columnsSlide17

Your Turn (and Job)Read chapter 7 (both pptx

file and Oracle text)

Practice all examples (script file is available in the Bb, file name: Ch7_Queries.sql)Slide18

Assigning User Privileges (continued)

Even with a valid user name and password, a user still needs the CREATE SESSION privilege to connect to a database

Figure 7-5 Command to grant the CREATE SESSION privilegeSlide19

System Privileges

Affect a user’s ability to create, alter, and drop objects

Use of

ANY keyword with an object privilege (INSERT ANY TABLE) is considered a system privilege

List of all available system privileges available through SYSTEM_PRIVILEGE_MAPSlide20

SYSTEM_PRIVILEGE_MAP

Figure 7-3 A partial list of available system privelagesSlide21

Granting System Privileges

System privileges are given through the GRANT command

Figure 7-4 Syntax of the GRANT command for system privalegesSlide22

Granting System Privileges (continued)GRANT clause – identifies system privileges being granted

TO clause – identifies receiving user or role

WITH ADMIN OPTION clause – allows a user to grant privilege to other database usersSlide23

Object PrivilegesSELECT – display data from table, view, or sequence

INSERT – insert data into table or view

UPDATE – change data in a table or view

DELETE – remove data from a table or viewALTER – change definition of table or viewSlide24

Granting Object Privileges

Grant object privileges through the GRANT command

Figure

7-6

Syntax of the GRANT command for object privilegesSlide25

Granting Object Privileges (continued)

GRANT clause – identifies object privileges

ON clause – identifies object

TO clause – identifies user or role receiving privilegeWITH GRANT OPTION clause – gives a user the ability to assign the same privilege to other users

GRANT Command ExamplesTable 7-2 Examples of Granting Object Privileges to a User Slide26

Password Management

To change a user password, use the PASSWORD command or the ALTER USER command

Figure 7-12 Command to change a passwordSlide27

Utilizing Roles

A role is a group, or collection, of privileges

Figure 7-16 Command for creating the ORDERENTRY role

Figure 7-17 Commands for granting privileges to the ORDERENTRY roleSlide28

Utilizing Roles (continued)

Roles can be assigned to users or other roles

Figure 7-18 Command for granting the ORDERENTRY role to RTHOMASSlide29

Utilizing Roles (continued)A user can be assigned several roles

All roles can be enabled at one time

Only one role can be designated as the default role for each user

Default role can be assigned through the ALTER USER commandSlide30

Utilizing Roles (continued)

Roles can be modified with the ALTER ROLE command

Roles can be assigned passwords

Figure 7-23 Syntax of the ALTER ROLE commandSlide31

Viewing Privilege InformationROLE_SYS_PRIVS lists all system privileges assigned to a role

SESSION_PRIVS lists a user’s currently enabled roles Slide32

ROLE_TAB_PRIVS Example

Figure 7-24 Verifying privileges assigned to a roleSlide33

Removing Privileges and Roles

Revoke system privileges with the REVOKE command

Figure 7-26 Syntax for revoking a system privilegeSlide34

Removing Privileges and Roles (continued)

Revoking an object privilege

– if the privilege was originally granted using WITH GRANT OPTION, the effect cascades and is revoked from subsequent recipients

Figure 7-27 Syntax for revoking an object privilegeFigure 7-28 Syntax for removing a role from an accountSlide35

Dropping a Role

Users receiving privileges via a role that is dropped will no longer have those privileges available

Figure 7-31 Syntax of the DROP ROLE command

Figure 7-32 Command for dropping the ORDERENTRY roleSlide36

Dropping a User

The DROP USER command is used to remove a user account

Figure 7-33 Syntax of the DROP USER commandSlide37

Summary

Database account management is only one facet of data security

A new user account is created with the CREATE USER command

The IDENTIFIED BY clause contains the password for the accountSystem privileges are used to grant access to the database and to create, alter, and drop database objects

The CREATE SESSION system privilege is required before a user can access his account on the Oracle serverThe system privileges available in Oracle 11g can be viewed through the SYSTEM_PRIVILEGE_MAPSlide38

Summary (continued)Object privileges allow users to manipulate data in database objects

Privileges are given through the GRANT command

The ALTER USER command, combined with the PASSWORD EXPIRE clause, can be used to force a user to change her password upon the next attempted login to the database

The ALTER USER command, combined with the IDENTIFIED BY clause, can be used to change a user’s password

Privileges can be assigned to roles to make the administration of privileges easierSlide39

Summary (continued)Roles are collections of privileges

The ALTER USER command, combined with the DEFAULT ROLE keywords, can be used to assign a default role(s) to a user

Privileges can be revoked from users and roles using the REVOKE command

Roles can be revoked from users using the REVOKE commandA role can be deleted using the DROP ROLE command

A user account can be deleted using the DROP USER command