[Experiment 4 Oracle Database Security Management] Create a user named Tom, use password authentication, the password is Tom, the default table space is USERS table space, the temporary table space is TEMP, and the quota on the USERS table space is 10M.

Experiment 4 Oracle Database Security Management

1. Purpose of the experiment

(1) Master the implementation of Oracle database security control.

(2) Master Oracle database user management.

(3) Master Oracle database permission management.

(4) Master Oracle database role management.

(5) Understand the management of Oracle database profiles.

(6) Understand Oracle database auditing.

2. Experimental requirements

(1) Create a user for the ORCL database.

(2) Grant and revoke permissions for ORCL database users

(3) Create roles for the ORCL database and use roles to authorize users.

(4) Create a profile for the ORCL database and assign it to the user.

(5) Audit user operations in the ORCL database.

3. Experimental content

(1) Create a user named Tom, use password authentication, the password is Tom, the default table space is USERS table space, the temporary table space is TEMP, the quota on the USERS table space is 10M, and the quota on the BOOKTBS1 table space is 50M.

create tablespace BOOKTBS1 datafile 'd:/tbs1.dbf' size 50M;

create user Tom
identified by Tom
default tablespace USERS
temporary tablespace TEMP
quota 10M on USERS
quota 50M on BOOKTBS1;

(2) Create a user named Joan, use password authentication, the password is Joan, the default table space is BOOKTBS2 table space (question 2 in experiment 3 has been created), the default temporary table space is TEMP, and the quota is set on the USERS table space is 10MB, and the quota on the BOOKTBS2 table space is 20MB. The user’s initial status is locked.

create user Joan
identified by Joan
default tablespace BOOKTBS1
temporary tablespace TEMP
quota 10 on USERS
quota 20M on BOOKTBS1 account lock;

(3) To facilitate the login of users in the database, grant CREATE SESSION system permission to all users in the ORCL database.

grant create session to public;

(4) Use Tom user and Joan user to log in to the ORCL database respectively and test whether it is successful.

conn Joan/Joan@orcl

conn Tom/Tom@orcl

(5) Unlock the Joan user account and log in again.

alter user Joan account unlock;

(6) Grant the CREATE TABLE and CREATE VIEW system permissions to Tom user, and enable permission transfer;

grant create table,create view to Tom with admin option;

(7) User Tom revokes the system permission of CREATE VIEW granted to user Joan.

conn Tom/Tom@orcl as sysdba

grant create view to Joan;

revoke create view from Joan;

(8) Query the object permissions and system permission details of user Tom and Joan respectively.

 conn Tom/Tom@orcl

System permissions: select * from user_sys_privs;

Object permissions:desc user_tab_privs;

conn Joan/Joan@orcl

System permissions: select * from user_sys_privs;

Object permissions:desc user_tab_privs;

(9) Grant the bs_role role to user Joan, and grant the CREATE SESSION, RESOURCE, and bs_role roles to user Tom.

create role bs_role;

grant bs_role to Joan;

grant create session,resource,bs_role to Tom;

(10) Create a bs_profile1 profile, limit the user’s maximum session time to 30 minutes, and end the session if it is idle for 10 consecutive minutes. At the same time, the password validity period is limited to 20 days. After two consecutive failed logins, the account will be locked and automatically unlocked after 10 days.

create profile bs_profile1 limit
connect_time 30
idle_time 10
password_life_time 20
failed_login_attempts 2
password_lock_time 10;

(11) Create a profile file bs_profile2, which requires that the maximum number of sessions for each user is 3, the maximum connection time is 60 minutes, the maximum idle time is 20 minutes, and the maximum time each session occupies the CPU is 10 seconds; User The maximum number of login attempts is 3, and the account will be locked for 7 days after failed login.

create profile bs_profile2 limit
sessions_per_user 3
connect_time 60
idle_time 20
cpu_per_session 1000
failed_login_attempts 3
password_lock_time 7;

(12) Assign profile bs_profile1 to user Tom, and assign profile bs_profile2 to user Joan.

alter user Tom profile bs_profile1;

alter user Joan profile bs_profile2;

(13) Use Tom user to log in to the ORCL database, enter the wrong password twice in a row, and check the account status; use Joan user to log in to the ORCL database and test the maximum number of sessions that can be started.

conn Joan/Joan as sysdba

select count(*) from v$session;

4. Questions and answers and experimental results

Create a new word and name it “Class

  • Experimental experience and gains