Oracle user and resource學習與測試_20100110

Oracle user and resource       學習與測試　

參考文件

<>P595

<>P721

 

目錄

1 overview user security

2 User Authentication

3 Authentication of Database Administrator

4 The User Group PUBLIC

5 User Resource Limits and profiles

6 Security Checklist

7 Notes

 

1 overview user security

 

when concerning a user’s security domain, something must be considered include:

1) user authentication

2) user default tablespace and default temporary tablespace

3) quotas for each tablespace

4) user profile about system resource limit

5) privileges ,roles and security policies for user.

(當談及到使用者相關的問題時，必須清晰的東西)

 

2 User Authentication

 

1) The operating system OS

2) A network service

以上兩種歸為External Authentication Method.

When you choose external authentication for a user, the user account is maintained by Oracle, but password administration and user authentication is performed by an external service. This external service can be the operating system or a network service, such as Oracle Net.(使用者由Oracle維護，但密碼由作業系統或網路服務維護)

 

OS authentication method

1) 設定OS_AUTHENT_PREFIX= OPS$(大小寫敏感)

 

2) 設定REMOTE_OS_AUTHENT=true.(必須重啟生效)

 

3) 建立作業系統使用者test

 

4) 建立Oracle使用者CREATE USER OPS$test IDENTIFIED EXTERNALLY

 

5) 以test使用者登入作業系統和Oracle

Conn test 不用指定密碼.

Oracle系統組合作業系統使用者test 加字首OPS$,即OPS$test,在Oracle中查詢是否有對應的使用者OPS$test.如存在則登入成功.

 

Setting the initialization parameter REMOTE_OS_AUTHENT to TRUE allows the RDBMS to accept the client operating system user name received over a nonsecure connection and use it for account access. The change take effect the next time you start the instance and mount the  database.

OS authentications Advantages優勢: 連線更快且方便,不用單獨指定使用者密碼;資料庫和作業系統雙重審計.

 

Network authentication method

 

Network authentication is performed using Oracle Advanced Security,which can be configured to use a third party service such as Kerberos.The settiing of the parameter REMOTE_OS_AUTHENT is irrelevant.

Network authentication Advantages: More choice of authentication mechanism are available, such as smart cards, fingerprints, or the operating system.

 

3) The associated Oracle database

平時所用的就是這種方式

CREATE USER scott IDENTIFIED BY tiger;

 

Advantages: 不依賴於外部程式,提供了安全的策略保證.

 

4) The Oracle database of a middle-tier application that performs transactions on behalf of the user.

5) The Secure Socket Layer(SSL) protocol

 

3 Authentication of Database Administrator

 

Database administrators perform. special operation that should not be performed by normal database users. Oracle provides a more secure authentication schema for database administrator usernames. You can choose between operating system authentication or password files to authenticate database administrator.(這就是平常所討論的關於使用者認證方式．其實是針對於Database Administrator．詳細參考<<作業系統認證與ORACLE密碼檔案認證方式20090920.doc>>)

On most operating systems, operating system authentication for database administrators involves placint the operating system username of the database administrator in a special group(oracle in dba group) or giving that operating system username a special process right.(作業系統認證方式，將oracle使用者放到dba組)

 

The database uses password file to keep track of database usernames who have been granted the SYSDBA and SYSOPER privileges.(orapwSID file.平常用的就是這種方式)

 

1) SYSOPER lets database administrators perfore STARTUP, SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP,ARCHIVE LOG, and RECOVER, and includes the RESTRICTED SESSION privileges

2) SYSDBA contains all system privileges with ADMIN OPTION, and SYSOPER system privileges.Permits CREATE DATABASE and TIME-BASED revovery.

 

這個應與User Authentication Methods區分開來．即2, 3應區分開來．

 

4 The User Group PUBLIC

 

Each database contains a user group called PUBLIC. Every user automatically belongs to the PUBLIC user group.As members of PUBLIC, users can see(select from) all data dictionary tables prefixed with USER and ALL. Additionally, a user can grant a privilege or a role to PUBLIC. All users can use the privileges granted to PUBLIC.

PUBLIC has the following restrictions:

1)      you cannot assign tablespace quotas to PUBLIC, although you can assign the UNLIMITED TABLESPACE system privileges to PUBLIC.

2)      You can create database links and sysnonyms as PUBLIC(create public database link /synonym), but no other schema object can be owned by PUBLIC. (CREATE TABLE PUBLIC.employees xxx is illegal)

 

5 User Resource Limits and profiles

 

5.1 User Resource Limts

You can set limits on the amount of various system resources available to each user as part of a user’s security domain. A slight degradation in performance occurs when users create sessions.This is because Oracle loads all resource limit data for the user when a user connects to a database.(效能有影響．建立session時，裝載所有資源)

You can control each of these resources at the session level, the call level, or both.

 

1)      Session Level(會話級, 在每一個Session內部，就是每一條語句)

If a user exceeds a session-level resource limit, Oracle terminates(rollback) the current statement and returns a message indicating the session limit has been reached. At this point, all previous statements in the current transaction are intact.(如果超出限制，則只回滾當前語句不是當前事務所有的語句)

 

2) Call Level(呼叫級，在每一個語句內部)

Each time a SQL statement is executed, several steps are taken to process the statement. During this processing, several calls are made to the database as part of the different execution phases. To prevent any one call from using the system excessively, Oracle lets you set several resource limits at the call level. If a user exceeds a call-level resource limit, Oracle halts the processing of the statement, rolls back the statement, and returns an error. However, all previous statements of the current transaction remain intact, and the user’s session remains connected.

5.2 Types of System Resource

1)      CPU t ime

2)      Logical reads(memory and disk)

3)      Other resource(concurrent sessions for each user, idle time for a session, elapsed time for each session,Private SQL areas)

 

5.3 Profile

A Profile is a named set of specified resource limits that can be assigned to a valid username of an Oracle database. Profile provide for easy management of resource limits. Profile are also the way in which you administer password policy.

Usually, the best way to determine the appropriate resource limit values for a given user profile is to gather historical information about each type of resource usage.(分配資源前先做好相關統計，實際應用中，一般在大系統中才會做資源限制，普通的系統很少做)

 

6 Security Checklist

 

1) Install only what is required

 

2) Lock and expire default user accounts

 

3) Change Default user password

 

4) Enable data dictionary protection

To enable dictionary protection, set the O7_DICTIONARY_ACCESSIBILITY

initialization parameter, in the following manner:

O7_DICTIONARY_ACCESSIBILITY = FALSE

By doing so, only those authorized users making DBA-privileged (for example CONNECT / AS SYSDBA) connections can use the ANY system privilege on the data dictionary. If this parameter is not set to the value recommended above, any user with a DROP ANY TABLE (for example) system privilege will be able to maliciously drop parts of the data dictionary.(9i 及以後版本預設為False)

 

5) practice principle of least privilege(最小特權原則授權)

n         Grant necessary privileges only,

n         Revoke unnecessary privileges from PUBLIC

 

6) enforce access controls effectively

To restrict remote authentication and thereby defer client truct to the databae, set the REMOTE_OS_AUTHENT initialization parameter in the following mananer:

REMOTE_OS_AUTHENT = FALSE;

關掉遠端OS認證方式

 

7) RESTRICT OPERATING SYSTEM ACCESS

Restricting the ability to modify the default file and directory permissions for the ORACLE_HOME(installation) directory or its contents.Even privileged operating system users and the Oracle owner should not modify these permissions.(限制更改ORACLE_HOME目錄的許可權和內容)

This recommendation applies to all types of files: datafiles, log files, trace files, external tables, bfiles,etc.

在作業系統層面保護ORACLE相關的目錄和內容．

 

 

8) RESTRICT NETWORK ACCESS
Utilize a firewall

Never poke a hole through a firewall

 

Prevent unauthorized administration of the Oracle Listener

Set the listener.ora security configuration parameter in the following manner.

ADMIN_RESTRICTIONS_listener_name = ON

Doing so will also prevent unauthorized administration of the Oracle Listener.

 

Check network IP addresses

Utilize the Oracle Net "valid node checking" security feature to allow or deny access to Oracle server processes from network clients with specified IP addresses. To use this feature, set the following protocol.ora (Oracle Net configuration file) parameters: (沒看到這個檔案?)

tcp.validnode_checking = YES

tcp.excluded_nodes = {list of IP addresses}

tcp.invited_nodes = {list of IP addresses}

 

Harden the operating system

Close the UDP and TCP ports for each service that is being disabled.

 

9) apply all security patches and workarounds

打安全相關的補丁

 

10) Contact oracle security products

 

 

 

7 Notes

 

1 關於臨時段的一些東西

1) Temporary segments do not consume any quota that a user might posses.The schema objects that Oracle automatically creates iin temporary segments are owned by SYS and therefore are not subject to quotas.(臨時段不佔用使用者擁有的限額，臨時段上的物件歸SYS所有)

2) Temporary segments can be created in a tablespace for which a user has no quota.(臨時段建立在使用者沒有限額的表空間上，原因見1.但並不是說臨時段不佔用空間，只是不佔用Quota)

 

2關於使用者限額

1) 收回使用者在某個表空間上建立物件的許可權可以透過將此使用者在該表空間上的限額設定為0

2) unlimited tablespace system privilege

To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the users.if you later revoke the privilege, explicti quotas again take effect.you can grant this privilege only to user, not to roles.

(授予UNLIMITED TABLESPACE 系統許可權會override所有顯式分配的在各個表空間上的限額)

測試:

SQL> alter user test quota 10M on tools;

 

User altered.

SQL>  grant unlimited tablespace to test;

 

Grant succeeded.

 

SQL> select * from user_ts_quotas;

 

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS

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

USERS                             2752512         -1        336         -1

TOOLS                                   0   10485760          0       1280

 

 

測試結果表明並不是這樣的．???