oracle 11g oracle label security

一、oracle 11g 启用OLS

1、Registering Oracle Label Security with the Database
[ oracle@localhost ~]$ chopt enable lbac 

Writing to /u01/app/oracle/product/11.2.0.3/db_1/install/enable_lbac.log... 
/usr/bin/make -f /u01/app/oracle/product/11.2.0.3/db_1/rdbms/lib/ins_rdbms.mk lbac_on ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1 
/usr/bin/make -f /u01/app/oracle/product/11.2.0.3/db_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1 

[ oracle@localhost ~]$ sqlplus / as sysdba 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 31 13:41:32 2015 

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

Connected. 
SQL> show user; 
USER is "SYS" 
SQL> exit 
Disconnected 
[ oracle@localhost ~]$ sqlplus sys/ oracle@prod1 as sysdba 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 31 13:42:05 2015 

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

ERROR: 
ORA-12560: TNS:protocol adapter error 


Enter user-name: 
ERROR: 
ORA-01017: invalid username/password; logon denied 


Enter user-name: 
ERROR: 
ORA-01017: invalid username/password; logon denied 


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

2、重启数据库

重启数据库之后，发现启用了ols

3、配置ols

4、Enabling the LBACSYS User Account

SQL> alter user LBACSYS identified by LBACSYS account unlock;

User altered.

SQL> grant select any dictionary to  LBACSYS; 

二、OLS使用sample

1、对hr的locations表使用标签安全策略：

2、安全策略：

Label	Privileges
CONFIDENTIAL	Read access to the cities Munich, Oxford, and Rome
SENSITIVE	Read access to the cities Beijing, Tokyo, and Singapore
PUBLIC	Read access to all other cities listed in HR.LOCATIONS

3、 Step 1: Creating Users for the Oracle Label Security Example

SQL> conn / as sysdba

Connected.

SQL> create user tom identified by tom account unlock;

User created.

SQL> create user peter identified by peter account unlock;

User created.

SQL> create user rose identified by rose account unlock;

User created.

SQL> grant connect,resource to tom;

Grant succeeded.

SQL> grant connect,resource to peter;

Grant succeeded.

SQL> grant connect,resource to rose;

Grant succeeded.

SQL> grant select on hr.locations to tom;

Grant succeeded.

SQL> grant select on hr.locations to peter;

Grant succeeded.

SQL> grant select on hr.locations to rose;

Grant succeeded.

4. Step 2: Creating the ACCESS_LOCATIONS Policy 创建访问表locations的策略

SQL> CONN LBACSYS/LBACSYS

Connected.

SQL> EXEC LBACSYS.SA_SYSDBA.CREATE_POLICY(policy_name => 'ACCESS_LOCATIONS', column_name => 'OLS_COLUMN', default_options => 'READ_CONTROL,');

5、Step 3: Defining the ACCESS_LOCATIONS Policy-Level Components  定义标签组件

定义标签级别。 级别是表示其标记的信息的敏感度的排名。信息越敏感, 则其级别越高。每个标签都必须包括一个级别。虽然级别 (以及其他每个标签组件) 的长名称和短名称均可定义, 不过在检索时只显示短名称。标签操纵期间只使用短名称

SQL> exec LBACSYS.SA_COMPONENTS.CREATE_LEVEL(policy_name => 'ACCESS_LOCATIONS', level_num => 3000, short_name => 'SENS', long_name => 'SENSITIVE');

PL/SQL procedure successfully completed.

SQL> exec LBACSYS.SA_COMPONENTS.CREATE_LEVEL(policy_name => 'ACCESS_LOCATIONS', level_num => 2000, short_name => 'CONF', long_name => 'CONFIDENTIAL');

exec LBACSYS.SA_COMPONENTS.CREATE_LEVEL(policy_name => 'ACCESS_LOCATIONS', level_num => 1000, short_name => 'PUBL', long_name => 'PUBLIC');

PL/SQL procedure successfully completed.

6、 Step 4: Creating the ACCESS_LOCATIONS Policy Data Labels 

创建数据标签, 每个标签都应有一个级别字段, 并且可以选择性地具有一个或多个划分和/或组。此外, 每个标签还都需要有一个与其关联的数字标记, 该标记在数据库的所有策略中唯一地标识此标签

SQL> exec SA_LABEL_ADMIN.CREATE_LABEL(policy_name => 'ACCESS_LOCATIONS', label_tag => 1000, label_value => 'PUBL', data_label => TRUE);

SQL> exec SA_LABEL_ADMIN.CREATE_LABEL(policy_name => 'ACCESS_LOCATIONS', label_tag => 2000, label_value => 'CONF', data_label => TRUE);

PL/SQL procedure successfully completed.

SQL> exec SA_LABEL_ADMIN.CREATE_LABEL(policy_name => 'ACCESS_LOCATIONS', label_tag => 3000, label_value => 'SENS', data_label => TRUE);

7、Step 5: Creating the ACCESS_LOCATIONS Policy User Authorizations 

指定用户以过滤结果集中显示的数据

EXEC SA_USER_ADMIN.SET_USER_LABELS(POLICY_NAME => 'ACCESS_LOCATIONS',USER_NAME => 'TOM',MAX_READ_LABEL => 'SENS');

EXEC SA_USER_ADMIN.SET_USER_LABELS(POLICY_NAME => 'ACCESS_LOCATIONS',USER_NAME => 'PETER',MAX_READ_LABEL => 'CONF');

EXEC SA_USER_ADMIN.SET_USER_LABELS(POLICY_NAME => 'ACCESS_LOCATIONS',USER_NAME => 'ROSE',MAX_READ_LABEL => 'PUBL');

8、Step 6: Applying the ACCESS_LOCATIONS Policy to the HR.LOCATIONS Table  

ready to apply the policy to the  HR.LOCATIONS  table, 将策略应用于表可以根据数据行的用户授权和标签实施指定的选项 (例如读取, 写入等)。策略列将添加到表中。将策略应用于表时, 此列可以存储与数据行关联的标签。它在默认情况下是启用的

SQL> EXEC SA_POLICY_ADMIN.APPLY_TABLE_POLICY('ACCESS_LOCATIONS','HR','LOCATIONS',null,null,null);

PL/SQL procedure successfully completed.

9、Step 7: Adding Policy Labels to Table Data 

After you have applied the ACCESS_LOCATIONS policy to the  HR.LOCATIONS  table, you need to label the rows in the table.  HR  is the owner of the LOCATIONS table.  HR  needs to have the  FULL  security privilege for the ACCESS_LOCATIONS policy, in order to successfully update the LOCATIONS table with the policy labels.

1）To grant HR FULL Privilege for the OLS Policy:

SQL> EXEC SA_USER_ADMIN.SET_USER_PRIVS(POLICY_NAME => 'ACCESS_LOCATIONS',USER_NAME => 'HR',privileges => 'FULL');

2）To update the OLS_COLUMN table in HR.LOCATIONS

SQL> conn hr/ hr@prod1 
Connected. 
SQL> UPDATE LOCATIONS 
2 SET ols_column = CHAR_TO_LABEL('ACCESS_LOCATIONS','SENS') 
3 WHERE UPPER(city) IN ('BEIJING', 'TOKYO', 'SINGAPORE'); 

3 rows updated. 

SQL> UPDATE LOCATIONS 
2 SET ols_column = CHAR_TO_LABEL('ACCESS_LOCATIONS','CONF') 
3 WHERE UPPER(city) IN ('MUNICH', 'OXFORD', 'ROME'); 

2 rows updated. 

SQL> UPDATE LOCATIONS 
2 SET ols_column = CHAR_TO_LABEL('ACCESS_LOCATIONS','PUBL') 
3 WHERE ols_column IS NULL; 

18 rows updated. 

SQL> commit; 

Commit complete.

3） check that the columns were updated

SQL> SELECT LABEL_TO_CHAR (OLS_COLUMN) FROM LOCATIONS;

LABEL_TO_CHAR(OLS_CO

--------------------

PUBL

PUBL

SENS

PUBL

PUBL

PUBL

PUBL

PUBL

PUBL

PUBL

SENS

LABEL_TO_CHAR(OLS_CO

--------------------

PUBL

PUBL

SENS

PUBL

CONF

PUBL

CONF

PUBL

PUBL

PUBL

PUBL

LABEL_TO_CHAR(OLS_CO

--------------------

PUBL

10、Step 8: Testing the ACCESS_LOCATIONS Policy 测试

1）tom关联sens标签，所有数据都能看到

2）peter关联conf标签

同理可以验证rose只能看到18条数据

11、删除相关策略信息

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28194062/viewspace-1757995/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28194062/viewspace-1757995/