Securing the oracle database
This presentation is the property of its rightful owner.
Sponsored Links
1 / 44

Securing the Oracle Database PowerPoint PPT Presentation


  • 85 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

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