1

1
Chapter Twenty ThreeUser Access Security

• Objectives
• How to create users
• How to create profiles
• How to create roles
• How to Grant, Revoke Privileges

2
Database Security

• System Security
• Username
• Password
• Disk space allocation to users
• System operation by users
• Data Security
• Access use of db objects

3
System Privileges

• Access to DB
• Over 100 privileges are available
• DBA has high-level of system privileges such as
• Create new users
• Remove users
• Remove tables
• Backup tables

4
Create User

• CREATE USER name IDENTIFIED By password
  EXTERNALLY
• SQL CREATE USER John IDENTIFIED By SSSS
• SQL ALTER USER John IDENTIFIED By Apple

5
CREATE USER

• SQL CREATE USER John
• IDENTIFIED By SSSS
• DEFAULT TABLESPACE data_ts
• TEMPORARY TABLESPACE temp_ts
• QUOTA 15M ON data_ts --QUOTA UNLIMITED
• PASSWORD EXPIRE

6
Create a Limit on the Use of Database Resource

• CREATE PROFILE name LIMIT Resource_Parameter
  password_Parameter
• Where Resource_Parameters are
• SESSIONS_PER_USER
• CPU_PER_SESSION
• CPU_PER_CALL
• CONNECT_TIME
• IDLE_TIME
• LOGICAL_READS_PER_SESSION
• LOGICAL_READS_PER_CALL
• PRIVATE_SGA integer KM UNLIMITED
  DEFAULT
• CONTINUED

7
Create Profile

• CREATE PROFILE name LIMIT Resource_Parameter
  Password_Parameter
• Where Password_Parameter are
• FAILED_LOGIN_ATTEMPTS
• PASSWORD_LIFE_TIME
• PASSWORD_REUSE_TIME
• PASSWORD_REUSE_MAX
• PASSWORD_LOCK_TIME
• PASSWORD_GRACE_TIME
• PASSWORD_VERIFY_FUNCTIONfunction_name NULL
  DEFAULT

8
Example of Profile

• CREATE PROFILE first_Limit LIMIT
• FAILED_LOGIN_ATTEMPTS 4
• CONNECT_TIME 600
• IDLE_TIME 30
• PASSWORD_LOCK_TIME 1 --days account is locked
• PASSWORD_LIFE_TIME 120 --days password expired
• PASSWORD_GRACE_TIME 2 --days
• CREATE USER Mark IDENTIFIED BY horse12 PROFILE
  first_Limit
• GRANT CREATE SESSION TO Mark

9
Password

• CONNECT Mark/horse12
• PASSWORD
• To change password
• PASSWORD newPass
• or
• CHANGE PASSWORD for Mark
• To lock an account
• ALTER USER Mark ACCOUNT LOCK -- or UNLOCK
• To expire a password
• ALTER USER Mark PASSWORD EXPIRE

10
Drop User

• SQL DROP USER John
• SQL DROP USER John CASCADE

11
Your Account Information

• SELECT Expiry _Date, UserName, User_ID,
  Default_Tablespace, Created_Date,
  Account_Status
• FROM USER_USERS
• ALL_USERS

12
User Account Information

• SELECT UserName, Profile, Account_Status
• FROM DBA_USERS
• USERNAME PROFILE ACCOUNT_STATUS
• ____________________________________________
• SYS DEFAULT OPEN
• SYSTEM DEFAULT OPEN
• CS640F01 first_Limit OPEN
• CS640F02 irst_Limit OPEN

13
Privileges

• Privilege is a right to execute a particular type
  of SQL statement, or access another object
• An owner has all the privileges on the created
  object.
• An owner can give specific privileges on that
  owners object

14
Privileges

• SQL GRANT SELECT, INSERT
• ON dept
• TO Mark, John WITH GRANT OPTION
• SQL GRANT SELECT (name, ID)
• ON Student
• TO PUBLIC

15
Grant System Privileges

• GRANT system_privilege role ALL
  PRIVILEGES TO
• user role PUBLIC IDENTIFIED BY password
  WITH ADMIN OPTION
• system_privilege
• CREATE SESSION
• CREATE TABLE
• CREATE SEQUENCE
• CREATE VIEW
• CREATE PROCEDURE

16

• Example
• GRANT CREATE SESSION, CREATE TABLE, CREATE
  VIEW, CREATE INDEX, CREATE SEQUENCE TO
  Mark

17
Grant Object Privileges

• GRANT object_privilege ALL PRIVILEGES ON
• object_Name TO user rule PUBLIC
• WITH GRANT OPTION
• WITH HIERARCHY OPTION
• Where object_privilege on TABLE are
• SELECT
• DELETE
• UPDATE
• INSERT
• FLASHBACK
• ALTER
• DEBUG
• REFERENCES
• INDEX
• QUERY REWRITE
• ALL

18
Grant Object Privileges

• Where object_privilege on PROCEDURE, FUNCTION,
  PACKAGE, LIBRARY are
• EXECUTE
• DEBUG
• Where Object_privilage on SEQUENCE are
• SELECT
• ALTER

19
Example

• GRANT SELECT ON student TO Mark
• CONNECT Mark/horse12
• SELECT
• FROM Mark.student
• CREATE SYNONYM JohnsTable FROM Mark.student

20
System Revoke Privileges

• REVOKE SELECT, INSERT, DELETE
• ON student
• FROM Mark

21
Revoke Privileges

• REVOKE Privileges , Privilege _ _ _ ALL
• ON object
• FROM user , user, . PUBLIC ROLE
• CASCADE CONSTRAINTS

22
Privileges
23
Privileges

• System Privileges
• USER_SYS_PRIVS
• SELECT UserName, Privilege
• FROM USER_SYS_PRIVS
• USERNAME PRIVILEGE
• JOHN CREATE TABLE
• MARK CREATE TABLE

24
Role

• Name group of related privileges
• SQL CREATE ROLE SuperManager
• SQL CREATE ROLE manager
• SQL GRANT CREATE TABLE, CREATE VIEW TO
  manager
• SQL GRANT manager to John, Mary, Mohsen

25
Create Role

• CREATE ROLE name NOT IDENTIFIED IDENTIFIED
  By password EXTERNALLY

26
Granting Role to Another Role

• GRANT manager TO SuperManager
• GRANT manager TO SuperManager WITH ADMIN OPTION

27
Role

• SQLALTER USER John
• DEFAULT ROLE manager
• SQL ALTER USER John
• DEFAULT ROLE ALL

28
ENABLE DISABLE Role

• SQLSET ROLE manager
• SQLSET ROLE NONE
• SQLREVOKE manager FROM PUBLIC
• SQLDROP ROLE manager

29
Object Privileges
30
Roles

• USER_ROLE_PRIVS
• UserName, Granted_Role, Admin_Option,
  Default_role
• DBA_ROLES
• DBA_ROLE_PRIVS
• SESSION_PRIVS
• SESSION_ROLES

31
Auditing

• Monitoring selected user database actions
• Auditing Categories
• By Default
• Instance startup shutdown
• Administrative privileges
• Database auditing
• Enabled by DBA
• Cannot record column values
• Value-based or application auditing
• Via code
• Can record column value
• Used to track changes to tables

32
Auditing

• AUDIT TABLE
• AUDIT CREATE ANY TRIGGER
• AUDIT SELECT ON id.student

33
Viewing Auditing Options

• Data Dictionary Views
• ALL_DEF_AUDIT_OPTS
• DBA_STMT_AUDIT_OPTS
• DBA_PRIV_AUDIT_OPTS
• DBA_OBJ_AUDIT_OPTS

34
Obtaining Audit Records

• Data Dictionary Views
• DBA_AUDIT_TRAIL
• DBA_AUDIT_EXISTS
• DBA_AUDIT_OBJECT
• DBA_AUDIT_SESSION
• DBA_AUDIT_STATEMENT

35
Object Revoke Privileges

• REVOKE ALL
• ON student
• FROM Mark