securing the oracle database
Download
Skip this Video
Download Presentation
Securing the Oracle Database

Loading in 2 Seconds...

play fullscreen
1 / 44

Securing the Oracle Database - PowerPoint PPT Presentation


  • 125 Views
  • Uploaded on

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

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

PowerPoint Slideshow about ' Securing the Oracle Database' - orlando-lynch


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 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.
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‘;

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
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.
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;
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 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

ad