Shulou(Shulou.com)06/01 Report--

"Oracle Security Management and Audit (2)" New year recruitment: China Oracle Elite Alliance 170513055 Group introduction: this group is a technology sharing community, where you can enjoy master-level technical lectures, and have the opportunity to participate in the Oracle technology salon, laugh with like-minded partners, and understand the feelings of the workplace!

A database version SYS@LEO1 > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0-Production

NLSRTL Version 11.2.0.1.0-Production

Operating system information

[oracle@leonarding1 admin] $uname-a

Linux leonarding1.oracle.com 2.6.32-200.13.1.el5uek # 1 SMP Wed Jul 27 21:02:33 EDT 2011 x86 "64 GNU/Linux

Second, talk about Oracle security management again.

1. In the last article, we talked about "the Security Management of Hongxing Society's Oracle plot (1)" http://space.itpub.net/26686207/viewspace-763470

This paper mainly introduces the Oracle security technology of "security authentication", "TDE transparent data encryption" and "fine-grained authority control". From principle to practice, we have analyzed the technical details and application scenarios from shallowness to depth, so that we can understand the development status of Oracle technology as a whole. Let's show the development route of Oracle security products.

Here are some common security solutions for Oracle

This article focuses on the content list

Access Control of 1.VPN Virtual Private Database

2.OLS tag Security access Control

3.Database vault database safe

4.FGA fine-grained audit

5.SYS administrator level audit

What is the focus: the principle of Oracle products is not complicated, the key is to know what kind of products, product features, and effects in what situation.

Demonstrate an example of VPD for data access control

1. What is a VPD:Virtual Private Database virtual private database? it sounds like an independent database, but it is logically independent and physically a database. The principle is to filter the data by specifying the filtering policy and adding predicate conditions to the user's SQL.

Advantages of 2.VPD:

Refined access

Transparent to the business, different customers issue the same SQL statement, and the query result set is different, so that different customers can only look at their relevant data.

Transparent to users and imperceptible to users

Do not do anything to the data itself, only filter at the SQL level

VPD comes with the database by default and does not need to be installed separately.

3. Experiment

We set up a business table car, which has three fields: car name, number of cars and car price. Nine records are inserted and divided into three grades: high, middle and low.

LEO1@LEO1 > create table car (name varchar2 (20), num number,cost number)

Table created.

LEO1@LEO1 > insert into car values ('toyota',10,30); premium car

1 row created.

LEO1@LEO1 > insert into car values ('volvo',50,30)

1 row created.

LEO1@LEO1 > insert into car values ('honda',60,30)

1 row created.

LEO1@LEO1 > insert into car values ('biaozhi',70,20); intermediate car

1 row created.

LEO1@LEO1 > insert into car values ('xuetielong',80,20)

1 row created.

LEO1@LEO1 > insert into car values ('polo',90,20)

1 row created.

LEO1@LEO1 > insert into car values ('xiali',20,10); low-end car

1 row created.

LEO1@LEO1 > insert into car values ('jili',30,10)

1 row created.

LEO1@LEO1 > insert into car values ('byd',40,10)

1 row created.

LEO1@LEO1 > commit

Commit complete.

LEO1@LEO1 > select * from car

NAME NUM COST

--

Toyota 10 30

Volvo 50 30

Honda 60 30

Biaozhi 70 20

Xuetielong 80 20

Polo 90 20

Xiali 20 10

Jili 30 10

Byd 40 10

9 rows selected.

Our idea: add filter policies "filter_name" and "filter_num" to the car table, trigger the "filter_name" policy when there is a name field in the select statement, trigger the "filter_num" policy when there is a num field in the select statement, and the filtering policy is implemented by the functions "fun_name" and "fun_num".

Create the function "fun_name"

LEO1@LEO1 > create or replace function fun_name (fun_scheme varchar2,fun_object varchar2)

Return varchar2 as fun_cost varchar2 (20)

Begin

Fun_cost:='cost=30'

Return (fun_cost)

End fun_name

/

2 3 4 5 6 7

Function created.

Create the function "fun_num"

LEO1@LEO1 > create or replace function fun_num (fun_scheme varchar2,fun_object varchar2)

Return varchar2 as fun_cost varchar2 (20)

Begin

Fun_cost:='cost=10'

Return (fun_cost)

End fun_num

/

2 3 4 5 6 7

Function created.

Add filter policy "filter_name"

LEO1@LEO1 > begin

Dbms_rls.add_policy (

Object_schema = > 'leo1'

Object_name = > 'car'

Policy_name = > 'filter_name'

Policy_function = > 'fun_name'

Sec_relevant_cols = > 'name')

End

/

2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

Add filter policy "filter_num"

LEO1@LEO1 > begin

Dbms_rls.add_policy (

Object_schema = > 'leo1'

Object_name = > 'car'

Policy_name = > 'filter_num'

Policy_function = > 'fun_num'

Sec_relevant_cols = > 'num')

End

/

2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

When we want to query the name of the car, the filter_name filtering policy is triggered, and the fun_name function is called to restrict the display of where cost=30 records.

LEO1@LEO1 > select name,cost from car

NAME COST

--

Toyota 30

Volvo 30

Honda 30

When we want to query the number of cars, the filter_num filtering policy is triggered, and the fun_num function is called to limit the display of where cost=10 records.

LEO1@LEO1 > select num,cost from car

NUM COST

--

20 10

30 10

40 10

How do we delete it when we don't want to use a filtering strategy?

Use drop_policy stored procedures to delete, filter_name, and filter_num filtering policies

LEO1@LEO1 > execute dbms_rls.drop_policy ('leo1','car','filter_name')

PL/SQL procedure successfully completed.

LEO1@LEO1 > execute dbms_rls.drop_policy ('leo1','car','filter_num')

PL/SQL procedure successfully completed.

LEO1@LEO1 > select * from car

NAME NUM COST

Toyota 10 30

Volvo 50 30

Honda 60 30

Biaozhi 70 20

Xuetielong 80 20

Polo 90 20

Xiali 20 10

Jili 30 10

Byd 40 10

9 rows selected.

Using VPD to hide sensitive column Information

We design a new "filter_num" strategy that shows only the records of cost=10 and hides the number of cars in the num column.

LEO1@LEO1 > begin

Dbms_rls.add_policy (

Object_schema = > 'leo1'

Object_name = > 'car'

Policy_name = > 'filter_num'

Policy_function = > 'fun_num'

Sec_relevant_cols = > 'num'

Sec_relevant_cols_opt = > dbms_rls.all_rows); display only relevant line information

End

/

2 3 4 5 6 7 8 9 10

PL/SQL procedure successfully completed.

LEO1@LEO1 > select * from car

NAME NUM COST

Toyota 30

Volvo 30

Honda 30

Biaozhi 20

Xuetielong 20

Polo 20

Xiali 20 10

Jili 30 10

Byd 40 10

9 rows selected.

Summary: VPD is a row-level security control that is easy to operate and can be implemented without adding any components.

Fourth, demonstrate an example of OLS for data access control

1. What is OLS:Oracle Label Security Oracle tag security access control, by creating tags to filter the result set, this way will modify the table structure, will add a column called "tag column" or "pseudo column", by assigning different tags to different records, so as to display different result sets. OLS is a little more powerful than VPD and is not included with the database by default and needs to be installed separately.

Installation of 2.Oracle Label Security

Oracle 10g install OLS

Run the Oracle program installation file

. / runInstaller installation components-> Select "Oracle Label Security 10.2.0.1.0"

Oracle 11g install OLS

11g does not need to install OLS components, has been installed, just need to start.

Red Hat Linux

(1) [oracle@leonarding1 oracle] $chopt enable lbac starts OLS and writes to OLS log

Writing to / u02/app/oracle/product/11.2.0/db_1/install/enable_lbac.log...

% unixOSDMakePath%-f / u02/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk lbac_on

% unixOSDMakePath%-f / u02/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk ioracle

(2) start dbca to install Oracle Label Security database objects and dedicated users

[oracle@leonarding1 oracle] $dbca

To configure database options, click "next"

Select which database-> "LEO1" Click "next"

Select the box on the Oracle Label Security component and click "next"

Here is an incredible scene, Oracle Label Security is gray box, can not be checked, if this is a white box then congratulations you can successfully move on to the next step. Of course, do not be discouraged without the installation of the package, all roads lead to Rome, please see the following script method.

Script installation of Oracle Label Security components

(1) We use the OLS installation script that comes with Oracle to deploy the OLS component $ORACLE_HOME/rdbms/admin/catols.sql

[oracle@leonarding1 ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 14 18:57:08 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@LEO1 > @? / rdbms/admin/catols.sql

>

Please note that the database will be closed automatically after the database object is created. You need to restart it.

Database closed.

Database dismounted.

ORACLE instance shut down.

(2) check the status of the OLS exclusive user LBACSYS. After installing the script normally, it will be OPEN. If it is LOCKED, perform the following unlocking steps.

SYS@LEO1 > select username,account_status from dba_users

. Omit useless content.

USERNAME ACCOUNT_STATUS

LBACSYS OPEN

SYS OPEN

SYSTEM OPEN

OUTLN EXPIRED & LOCKED

MGMT_VIEW EXPIRED & LOCKED

33 rows selected.

Unlock and set the password. If the status is OPEN, skip this step.

Alter user lbacsys account unlock

Alter user lbacsys identified by lbacsys

Summary: LBACSYS is an OLS administrator user. It will be created automatically after installing OLS. If there is a lock, please unlock it and use it.

(3) check the database object information involved in LBACSYS users

There are no invalid database objects in the LBACSYS user.

SYS@LEO1 > select object_type,object_name from dba_objects where owner='LBACSYS' and status='INVALID'

No rows selected

SYS@LEO1 > set pagesize 999 set 999 lines to have a delimiter

SYS@LEO1 > select object_type,count (*) from dba_objects where owner='LBACSYS' group by object_type

OBJECT_TYPE COUNT (*)

SEQUENCE 2

PROCEDURE 5

LIBRARY 10

PACKAGE 24

LOB 1

PACKAGE BODY 23

TYPE BODY 5

TRIGGER 3

FUNCTION 35

TABLE 21

INDEX 28

VIEW 57

TYPE 10

13 rows selected.

This is the database object used by Oracle Label Security, and we have completed the installation of the Oracle Label Security components above.

(4) how to uninstall Oracle Label Security components

If there is an installation script, there will be an uninstall script. If you execute the catnools.sql script, you can automatically uninstall OLS.

SYS@LEO1 > @? / rdbms/admin/catnools.sql

PL/SQL procedure successfully completed. Delete stored procedure

PL/SQL procedure successfully completed.

Trigger dropped. Delete trigger

Trigger dropped.

Trigger dropped.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

User dropped. Finally, delete the user

2 rows deleted.

Commit complete.

Delete the database object corresponding to Oracle Label Security before deleting the LBACSYS user.

(5) how to use Oracle Label Security well

Create a label policy

SYS@LEO1 > alter user lbacsys identified by lbacsys; set password

User altered.

SYS@LEO1 > conn lbacsys/lbacsys switch lbacsys users

Connected.

LBACSYS@LEO1 > execute sa_sysdba.create_policy (policy_name = > 'ACCESS_LEO1',column_name = >' OLS_COLUMN')

BEGIN sa_sysdba.create_policy (policy_name = > 'ACCESS_LEO1',column_name = >' OLS_COLUMN'); END

*

ERROR at line 1:

ORA-00439: feature not enabled: Oracle Label Security

ORA-06512: at "LBACSYS.LBAC_SYSDBA", line 113

ORA-06512: at "LBACSYS.SA_SYSDBA", line 44

ORA-06512: at line 1

#

Install database vault

(1) start the chopt enable dv service

(2) rely on Oracle Label Security

(3) restart database startup force

(4) dbca creates database objects

Start the chopt enable dv service

[oracle@leonarding1 ~] $chopt enable dv

Writing to / u02/app/oracle/product/11.2.0/db_1/install/enable_dv.log...

% unixOSDMakePath%-f / u02/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk dv_on

% unixOSDMakePath%-f / u02/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk ioracle

Demonstrate an example of auditing through triggers.

The Car table is a very important table, which records the car sales of 4S stores. We need to audit the operation of this table.

LEO1@LEO1 > select * from car

NAME NUM COST

Toyota 10 30

Volvo 50 30

Honda 60 30

Biaozhi 70 20

Xuetielong 80 20

Polo 90 20

Xiali 20 10

Jili 30 10

Byd 40 10

9 rows selected.

Create audit table car_audit

LEO1@LEO1 > create table car_audit (

Name varchar2 (20)

Num number

Cost number

Uuser varchar2 (20)

Ddate date); 2 3 4 5 6

Table created.

Create an audit trigger

LEO1@LEO1 > create trigger trg_car_audit

After insert or delete or update on car

For each row

Declare

A_name varchar2 (20)

A_num number

A_cost number

Begin

A_name: =: old.name

A_num: =: old.num

A_cost: =: old.cost

Insert into car_audit values (axinamename, mahogany, num, recom, costume, username, sysdate)

End

/

2 3 4 5 6 7 8 9 10 11 12 13 14

Trigger created.

Delete record

LEO1@LEO1 > delete from car where num=100

1 row deleted.

LEO1@LEO1 > commit

Commit complete.

Insert record

LEO1@LEO1 > insert into car values ('kia',100,50)

1 row created.

LEO1@LEO1 > commit

Commit complete.

Update record

LEO1@LEO1 > update car set num=200 where num=100

1 row updated.

LEO1@LEO1 > commit

Commit complete.

Any DML operation on the car table will trigger the audit trigger and leave the audit record in the car_audit table.

LEO1@LEO1 > select * from car_audit

NAME NUM COST UUSER DDATE

--

Kia 100 50 LEO1 15-JUN-13

LEO1 15-JUN-13

Kia 100 50 LEO1 15-JUN-13

Sixth, demonstrate the examples of auditing sys users and ordinary users respectively.

When we operate the database, it is convenient to know what the permissions of SYS users are the most. At the same time, the problem is that it is very dangerous and no one can bind it. So we sometimes need to audit SYS users.

SYS@LEO1 > show parameter audit

NAME TYPE VALUE

-

Audit_file_dest string / u02/app/oracle/admin/LEO1/adump

Audit_sys_operations boolean FALSE defaults that SYS user auditing is off. This parameter enables auditing all SQL statements of SYS users.

Audit_syslog_level string is off by default. This parameter indicates where the audit logs of SYS users are stored.

Audit_trail string DB

SYS@LEO1 > alter system set audit_sys_operations=true scope=spfile; opens SYS user audit

System altered.

SYS@LEO1 > alter system set audit_syslog_level='user.notice' scope=spfile; user log

System altered.

SYS@LEO1 > startup force restart the database to make the static parameters effective

ORACLE instance started.

Total System Global Area 471830528 bytes

Fixed Size 2214456 bytes

Variable Size 285214152 bytes

Database Buffers 176160768 bytes

Redo Buffers 8241152 bytes

Database mounted.

Database opened.

SYS@LEO1 > show parameter audit

NAME TYPE VALUE

-

Audit_file_dest string / u02/app/oracle/admin/LEO1/adump

Audit_sys_operations boolean TRUE audit start

Audit_syslog_level string USER.NOTICE

Audit_trail string DB

Set the output location of the sys user audit log. The syslog.conf file in Linux is configured with the output location and message source of various types of logs.

We just need to add the Oracle log output configuration information to the file.

[root@leonarding1 log] # vi / etc/syslog.conf

Add the following information

# About Oracle SysLog

User.notice / var/log/oracle_dbms

After adding it, we need to reload the configuration information, and the syslog.conf file takes effect.

[root@leonarding1 log] # ps-ef | grep syslogd

Root 2385 1 0 Jun14? 00:00:01 syslogd-m 0

Root 29740 29502 0 17:25 pts/1 00:00:00 grep syslogd

[root@leonarding1 log] # kill-HUP 2385 reload

Ok, the configuration of the operating system is complete.

Let's demonstrate the audit of SYS users.

SYS@LEO1 > create table test as select * from dba_objects; We create a table

Table created.

SYS@LEO1 > drop table test purge; delete another table

Table dropped.

All right, the SYS user operation is done. Let's see if there are any SQL statements crawled in the oracle_dbms audit log.

[root@leonarding1 log] # cat oracle_dbms

Jun 15 18:08:40 leonarding1 Oracle Audit [29903]: LENGTH: '199' ACTION: [46]' create table test as select * from dba_objects' DATABASE USER: [1] / 'PRIVILEGE: [6]' SYSDBA' CLIENT USER: [6] 'oracle' CLIENT TERMINAL: [5]' pts/3' STATUS: [1]'0' DBID: [10] '1692458681'

Jun 15 18:08:49 leonarding1 Oracle Audit [29903]: LENGTH: '174' ACTION: [21]' drop table test purge' DATABASE USER: [1]'/ 'PRIVILEGE: [6]' SYSDBA' CLIENT USER: [6] 'oracle' CLIENT TERMINAL: [5]' pts/3' STATUS: [1]'0' DBID: [10] '1692458681'

Good crawls the commands to create the table test and delete the table test, such as startup shutdown connect and so on.

Summary: through the above test examples, we understand that the audit of database administrators has gradually become an important item of information security, which can supervise administrators, restrict permissions, and improve the level of database security. improve the security management system. Here is a question: why do SYS users' operation records need to be recorded in operating system files? there is a source here. Because SYS users themselves are so powerful that they can delete their own operation records, in order to control SYS users, they put the log of recording operations under the operating system, which can not be accessed by ordinary users, and must be viewed by operating system administrators. Now the students should know the reason.

Example of ordinary user audit

Since we can audit SYS users, in peacetime, it is more about the audit of ordinary users, but the audit of ordinary users is not so strict. Its audit records can be placed in the database base table sys.aud$, and we can view them at the database level.

Standard audit content

(1) Audit session

(2) Audit object

(3) Audit operation

(4) Audit authorization

Experiment

SYS@LEO1 > show user

USER is "SYS"

SYS@LEO1 > alter system set audit_trail=db,extended scope=spfile; starts the audit chase database function

System altered.

SYS@LEO1 > startup force; restart the database to make the static parameters effective

ORACLE instance started.

Total System Global Area 471830528 bytes

Fixed Size 2214456 bytes

Variable Size 285214152 bytes

Database Buffers 176160768 bytes

Redo Buffers 8241152 bytes

Database mounted.

Database opened.

We plan to audit the car table of leo1 users

SYS@LEO1 > audit select,insert,update,delete on leo1.car

Audit succeeded.

Specify the audit object and audit action, and Oracle will audit all four operations of select,insert,update,delete.

Insert record

LEO1@LEO1 > insert into leo1.car values ('mini',150150)

1 row created.

LEO1@LEO1 > commit

Commit complete.

LEO1@LEO1 > select * from car

. Omit.

LEO1@LEO1 > select userid,obj$name,sqltext from sys.aud$

USERID OBJ$NAME SQLTEXT

-

LEO1 CAR select * from car

LEO1 CAR insert into leo1.car values ('mini',150150)

VPD OLS db vault audit sys user

Liu Sheng Leonarding

2013.6.16

Beijing & summer

Share the technology ~ realize the dream

Blog:www.leonarding.com

#

If you like my article, please scan the QR code below! Follow WeChat account: leonarding_public

Here you can get thoughts and opinions on emerging things such as technology, facts, hot news, and things that may not be available elsewhere. I will provide you with the latest technology and information trends to transmit positive energy.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.