Securing the Oracle Database - PowerPoint PPT Presentation

Securing the oracle database
Download
1 / 44

  • 100 Views
  • Uploaded on
  • Presentation posted in: General

Securing the Oracle Database. 中原大學應用數學系 劉 立 民. Introducation. Passwords and Users Oracle Password Management Features Default Oracle Users Privileges, Grants, Roles, and Views Oracle9i Security Products and Features. Password features. Password enhancements. User PROFILE

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Download Presentation

Securing the Oracle Database

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Securing the oracle database

Securing the Oracle Database

中原大學應用數學系

劉 立 民


Introducation

Introducation

  • Passwords and Users

    • Oracle Password Management Features

    • Default Oracle Users

  • Privileges, Grants, Roles, and Views

  • Oracle9i Security Products and Features


Password features

Password features


Password enhancements

Password enhancements

  • User PROFILE

    • Default profile

    • Create/alter profile

      SQL> describe DBA_PROFILES;

      名稱 空值? 類型

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

      PROFILE NOT NULL VARCHAR2(30)

      RESOURCE_NAME NOT NULL VARCHAR2(32)

      RESOURCE_TYPE VARCHAR2(8)

      LIMIT VARCHAR2(40)

      SQL> select * from DBA_PROFILES;

      PROFILE RESOURCE_NAME RESOURCE LIMIT

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

      DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED

      DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED

      DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED

      DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

      DEFAULT CPU_PER_SESSION KERNEL UNLIMITED

      DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

      DEFAULT CPU_PER_CALL KERNEL UNLIMITED

      DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

      DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

      DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL


Password enhancements con t

Password enhancements (Con’t)

  • Resource_type

    • Password

    • Kernel

      SQL> select * from dba_profiles where resource_type = 'PASSWORD'

      PROFILE RESOURCE_NAME RESOURCE LIMIT

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

      DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED

      DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

      DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

      DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

      DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL

      DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED

      DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED

      已選取 7 個資料列.

  • Execute ultpwdmg.sql at $ORACLE_HOME/rdbms/admin

    @”D:\Oracle\ora92\rdbms\admin\utlpwdmg.sql”


Password enhancements con t1

Password enhancements (Con’t)

SQL> select * from dba_profiles where resource_type = 'PASSWORD'

PROFILE RESOURCE_NAME RESOURCE LIMIT

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

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3

DEFAULT PASSWORD_LIFE_TIME PASSWORD 60

DEFAULT PASSWORD_REUSE_TIME PASSWORD 1800

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION

DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006

DEFAULT PASSWORD_GRACE_TIME PASSWORD 10

已選取 7 個資料列.


Aging and expiration

Aging and expiration

  • Password_life_time

    • Number of days the same password can be used for authentication

  • Password_grace_time

    • Number of days in the grace period

      Ex: Password_life_time = 90

      Password_grace_time = 15

      After day 90+15, the account will be locked.


Password history

Password History

  • Parameters prevents users from avoiding password expiration by changing a password and changing it back to the original password.

  • Password_reuse_time

    • Number of days until a password cannot be resued.

  • Password_reuse_max

    • Number of password changes required before the current password can be reused.

  • Setting both to UNLIMITED allows passwords to be reused immediately.


Account locking

Account locking

  • Failed_login_attempts

    • Number of attempts to log in that can fail before the user account is locked

  • Password_lock_time

    • Amount of time an account remains locked after the specified number of consecutive failed login attempts is reached

    • If set to UNLIMITED, the account will no unlock automatically.


Password profile parameters

Password profile parameters


Password profile parameters con t

Password profile parameters (Con’t)


Enabling password parameters

Enabling password parameters

  • Create a user-defined profile

    create profile MY_PROFILE limit

    failed_login_attempts 5

    password_lock_time .5

    password_reuse_max UNLIMITED

    password_life_time 90

    password_reuse_time 60

    password_verify_function MY_PASSWORD_FUNCT

    password_grace_time 15;

  • Create users with specific profile

    create user NELSON

    identified by nel_123

    default tablespace USERS

    temporary tablespace TMP

    password expire

    profile MY_PROFILE;


Default oracle users

Default Oracle Users

  • Several default users are created when you create your database.

  • Check these users via DBA_USERS

    describe DBA_USERS

  • Lock those unused users

  • Verify users’ granted_role, and privileges

    select * from dba_role_privs where grantee = 'HR';

    select * from dba_sys_privs where grantee = 'HR';

    select * from dba_tab_privs where grantee = 'HR‘;


Dba views that display user privileges

DBA_ views that display user privileges


Database objects

Database objects


Object privileges

Object privileges


Controlling user access

Controlling user access

  • Product-level Security

    • Users access can be restricted based on the SQL*Plus product usage.

    • DBAs can use PRODUCT_USER_PROFILE to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis.

    • PRODUCT_USER_PROFILE table consists of the following columns:


Product user profile

PRODUCT_USER_PROFILE

  • PRODUCT_USER_PROFILE table is owned by SYSTEM

  • Commands that can be applied in PRODUCT_USER_PROFILE table includes: ALTER, AUDIT, ANALYZE, CREATE, DELETE, DROP, INSERT, LOCK, NOAUDIT, RENAME, SELECT, UPDATE, VALIDATE, etc.

  • Creating the PRODUCT_USER_PROFILE table by Execute pupbld.sql at $ORACLE_HOME/sqlplus/admin

    @”D:\Oracle\ora92\sqlplus\admin\pupbld.sql”

  • Example:

    insert into product_user_profile(product, userid, attribute, char_value) values('SQL*Plus', 'APPS', 'CREATE', 'DISABLED');

    insert into product_user_profile(product, userid, attribute, char_value) values('SQL*Plus', 'APPS', 'UPDATE', 'DISABLED');

    insert into product_user_profile(product, userid, attribute, char_value) values('SQL*Plus', 'APPS', 'DELETE', 'DISABLED');

    insert into product_user_profile(product, userid, attribute, char_value) values('SQL*Plus', 'APPS', 'INSERT', 'DISABLED');

    insert into product_user_profile(product, userid, attribute, char_value) values('SQL*Plus', 'APPS', 'SELECT', 'DISABLED');


Preventing access using pl sql

Preventing access using PL/SQL

  • The DELETE privilege disabled for user APPS can easily be executed through a PL/SQL block.

  • This can be avoided by removing the PL/SQL block creation access itself.

    insert into product_profile (product, userid, attribute, char_value) values ('SQL*Plus', 'APPS', 'DECLARE', 'DISABLED');

    insert into product_profile (product, userid, attribute, char_value) values ('SQL*Plus', 'APPS', 'BEGIN', 'DISABLED');

    • PRODUCT_PROFILE is a synonym of PRODUCT_USER_PROFILE exists to ensure backwards compatibility.


With grant option

With Grant Option

  • DBAs can grant different privileges to different users.

    SQL> CREATE USER TOM IDENTIFIED BY ICE;

    User created.

    SQL> GRANT CREATE SESSION TO TOM;

    Grant succeeded.

    SQL> GRANT CREATE TABLE TO TOM;

    Grant succeeded.

    SQL> GRANT CREATE SEQUENCE TO TOM;

    Grant succeeded.

  • DBAs can also grant users “with grant option” to allow them to grant access to other users without any action on the DBA parts.

    SWL> CREATE TABLE TEST (COL NUMBER);

    Table created.

    SQL> GRANT SELECT, UPDATE ON TEST TO TOM WITH GRANT OPTION;

    Grant succeeded.


With admin option

With Admin Option

  • DBA can grant users administration privileges using “with admin option”

    SQL> GRANT CREATE TABLE TO TOM WITH ADMIN OPTION;

    Grant succeeded.

  • Revoke uses’ administration privileges

    SQL> REVOKE CREATE TABLE FROM TOM;

    Revoke succeeded.

  • Privileges granted by TOM still retain after DBA revoke TOM’s administration privileges.


Using role

Using Role

  • Why using roles

    • Grant privileges to individual directly can be very tedious.

    • Example, 100 objects with 100 users needs to make 10,000 grants; one people leave the company needs to make 100 revokes.

  • Steps of using roles

    • Group users into categories

    • Define one or more roles for each category

    • Grant proper privileges to each role

    • Assign roles to users in each category


Create roles

Create roles

  • Assume table EMP has two kinds of users: query EMP and update EMP.

    SQL> create role report_writer identified by rep123;

    Role created.

    SQL> create role data_changer identified by your_pwd;

    Role created.

  • Grant privileges to roles

    SQL> GRANT SELECT ON EMP TO REPORT_WRITER;

    Grant succeeded.

    SQL> GRANT UPDATE, DELETE, INSERT ON EMP TO DATA_CHANGER;

    Grant succeeded.


Assign roles to users

Assign roles to users

  • Once we create roles with proper privileges, we can assign roles to users.

    SQL> GRANT REPORT_WRITER TO TOM;

    Grant succeeded.

    SQL> GRANT DATA_CHANGER TO HENRY;

    Grant succeeded.

  • Revoke roles from users to maintain the proper privileges

    SQL> REVOLE DATA_CHANGE FROM HENRY;

    Revoke succeeded.

  • DBA_ROLES

    SQL> select * from dba_roles;


Oracle supplied roles

Oracle-supplied roles

  • CONNECT

    • Gives the user the ability to access the database

  • RESOURCE

    • Gives the user the ability to create objects and use space in the database

  • DBA

    • Gives the user administrative privileges in the database


Querying roles

Querying roles


Set roles

Set roles

  • Default roles will be enabled automatically and accessible when users connect to the database

  • For roles requiring password, users need to use set role to enable the role privileges.

    SQL> set role DATA_CHANGE identified by your_pwd;


Using views to manage privileges

Using Views to Manage Privileges

  • Rather than granting users privileges on a particular table, you can give them access to a view of the table.

  • Views add two more levels of security:

    • A view can limit access to only selected columns of the base table.

    • A view can provide value-based security for the information in a table. Thus a WHERE clause in the definition of a view can display only selected rows of a base table.

  • The user need not be given privileges on base objects underlying the view.


An example of a view

An Example of a View


View privileges

View privileges

  • View creation

    SQL> CREATE VIEW STAFF AS (

    2 SELECT EMPNO, ENAME, JOB, MGR, DEPTNO FROM EMP

    3 );

    View created.

  • Grant privileges to roles

    SQL> GRANT SELECT ON STAFF TO REPORT_WRITER;

    Grant succeeded.


Row level security

Row Level Security

  • Two more effective approaches to implement row level security:

    • Virtual Private Database (VPD), in which you create your own implementation of row level security; and

    • Label-based access control, in which you customize a ready-made VPD policy to accomplish this.


Virtual private database vpd

Virtual Private Database (VPD)

  • Virtual Private Database is the ability to perform query modification based on a security policy you have defined in a package, and associated with a table, view.

  • You can implement VPD by writing a stored procedure to append a SQL predicate to each SQL statement to control row level access for that statement.

  • For example, SQL SELECT * FROM emp; will be modified, say SELECT * FROM emp where deptno = 10;


Virtual private database customers see only their own orders

Virtual Private Database: Customers see Only Their Own Orders


Example of vpd

Example of VPD

  • Choose the tables or views to protect at the row level

    SQL> create table employee (

    2 trndate date,

    3 credit_val number(12,2),

    4 debit_val number(12,2),

    5 trn_type varchar2(10),

    6 cost_center varchar2(10) ) tablespace users;

    Table created.

  • Define the business rules that will be followed for accessing data in these tables

  • Create a security context to manage application sessions

    SQL> create or replace context vpd_test using set_vpd_context;

    Context created.


Example of vpd con t

Example of VPD (Con’t)

  • Create a procedure or function to manage setting of the security context for users

    SQL> CREATE TABLE emp ( empno NUMBER(4) NOT NULL,

    2 ename VARCHAR2(10),

    3 job VARCHAR2(9),

    4 mgr NUMBER(4),

    5 sal NUMBER(7,2),

    6 deptno NUMBER(2));

    Table created.

  • Define the business rules that will be followed for accessing data in these tables

  • Create a security context to manage application sessions

    CREATE OR REPLACE CONTEXT HR USING HR.Context_Package;


Example of vpd con t1

Example of VPD (Con’t)

  • Create a procedure or function to manage setting of the security context for users

    CREATE OR REPLACE PACKAGE Context_Package AS PROCEDURE Set_Context;

  • Write a package to generate the dynamic access predicates for access to each table

    CREATE OR REPLACE PACKAGE BODY Context_Package IS

    PROCEDURE Set_Context ISThis_PERSON_ID NUMBER;BEGINDbms_Session.Set_Context('HR','SETUP','TRUE');IF SUBSTR(SYS_CONTEXT('USERENV','SESSION_USER'),1,2)='OE' :

    Dbms_Session.Set_Context('HR','SETUP','FALSE');

    END Set_Context;

    END Context_Package;


Example of vpd con t2

Example of VPD (Con’t)

  • Make the packages public by executing the following sql.

    GRANT EXECUTE ON hr.Context_Package to Public;

  • Automate the setting of the security context

    CREATE OR REPLACE TRIGGER HR.Set_Security_Context

    AFTER LOGON ON DATABASE

    BEGINHR.Context_Package.Set_Context;

    END;


Label based access control

Label-based access control

  • Label-based access control allows organizations to assign sensitivity labels to data rows.

  • Control access to data based on those labels, and ensure that data is marked with the appropriate sensitivity label.

  • Oracle9i Label Security is not installed by default with the Oracle9i Enterprise Edition.


Oracle label security

Oracle Label Security


Oracle label security1

Oracle Label Security

  • Oracle Label Security is built on the Virtual Private Database toolkit,

  • Requires no programming whatsoever.

  • Oracle Label Security delivers a data dictionary and administrative tools you can use to

    • construct valid labels,

    • set user label authorizations and privileges, and

    • apply the resulting Oracle Label Security policy to tables and schemes.


Oracle9i security products

Oracle9i Security Products

  • Oracle9i Standard Edition

    • Integrity

    • Authentication and Access Controls in Oracle9i

    • Privileges

    • Roles

    • Auditing

    • Views, Stored Program Units, Triggers

    • Data Encryption

    • High Availability

    • Proxy Authentication in Oracle9i


Oracle9i security products con t

Oracle9i Security Products (Con’t)

  • Oracle9i Enterprise Edition

    • Internet Scale Security Features

    • Application Security

    • Virtual Private Database in Oracle9i

    • Secure Application Role

    • Fine-Grained Auditing

    • Oracle Auditing for Three-Tier Applications

    • Java Security Implementation in the Database


Other oracle security products

Other Oracle security products

  • Oracle Advanced Security

  • Oracle Label Security

  • Oracle Internet Directory

  • Oracle Net Services

  • Oracle9i Application Server


References

References

  • Oracle Security Handbook by Theriault

  • Oracle SQL*Plus: The Definitive Guide by Gennick

  • Oracle 9i: Security Overview (9.2) by Oracle

  • Oracle 9i Application Server: Security Guide (9.0.2) by Oracle

  • Oracle 9i: Database Administration Guide (9.2) by Oracle

  • Oracle 9i Net Services: Administrator’s Guide (9.2) by Oracle

Thank you very much


  • Login