Oracle 11g OCP Notes (6)-Oracle Security

1. Create and manage accounts

User, user account, pattern terminology
Users, user accounts, patterns are often replaced by use, but these terms are not exactly the same.
The user connects to the user account by establishing a session to the instance and logs on based on the user account name.
A schema is a set of objects owned by a user account.

Properties of the user account:

User name

Identity authentication method

Default table Space

Table Space Quotas

Temporary table space

User Configuration Files

State

1) User name: The beginning of the letter, can contain only letters, numbers, dollar characters $, underscores _, cannot be reserved words, letters automatically converted to uppercase, not according to the rules need double quotation marks specified, cannot exceed 30 characters. Once created cannot be modified.
• Authentication methods:

2) Default tablespace: Use the default tablespace for the database if not specified, or use the system table space if the database is not specified.

ALTER DATABASE default Tablespace tablespace_name; --Modify the default tablespace for the database
Alter user username default tablespace tablespace_name; --Modify the default tablespace for a user account

3) Quota: Select User_name,default_tablespace,temporary_tablespace from Dba_users.

Alter USER [username] quota 100M on [Tablespace_name]

Alter USER [username] quota Unlimited on [Tablespace_name]

4) temporary table space

• Temporal table space alter user username temporary tablespace tablespace_name; --Modify the temporary tablespace for the user. The user does not need quotas on the staging table space.

5) configuration file

6) User status (Dba_users.account_status)
Open: Opening
LOCKED: Lock
EXPIRED: Expired
EXPIRED & LOCKED: Expired and Locked
EXPIRED (GRACE): normal expiration, you can change the password within the configured time period
LOCKED (TIMED): Failed login attempt, account locked
EXPIRED & LOCKED (TIMED): Expired and timed out lock
EXPIRED (GRACE) & LOCKED (TIMED): normal expiration and timeout lockout

Alter user username account lock; --Lock Account
Alter user username account unlock; --Unlock Account
Alter user username password expire; --forcing the user to change the password
Alter user username identified by password; --Change user password
Alter user username identified by value password; --Change the user password (directly changing the encrypted value).

Identity authentication Method:

Compare user name and password

Operating system Certification

Password file authentication

(1) Operating system and password file authentication
To enable operating system and password file authentication (both are compatible), the user must be granted SYSDBA or Sysoper permissions:
Grant [Sysdba|sysoper] to username;
If a user is granted one or all of these permissions, the user's password is copied from the data dictionary to the external password file, and the instance can read the password even if the database is not open.

SELECT * from V$pwfile_users; --Password file users to see which users are granted SYSDBA or Sysoper


--Rebuild Password file (password file default location: $ORACLE _home/dbs/orapwsid)
Orapwd file=<filename> passwod=<password> entries=<users>


Operating system authentication:
Connect/as [Sysdba|sysoper];
Password file authentication
Connect username/password[@db_alias] as [sysdba|sysoper];


(2) Password Authentication
Connect username/password[@db_alias];
When you connect using password authentication, the database must be open and the instance validates the user's password stored in the data dictionary.
SYS users are not allowed to connect in a password-authenticated manner, and SYS can only use password files, operating systems, or LDAP authentication.
Any user can change his or her user account password at any time, and users with sufficient privileges (such as system) can change any user accounts account order.
Alter user username identified by password; --Change user password

(3) External authentication:
If you create a user account with external authentication, Oracle delegates authentication to the external service.
Enable Advanced Security options: Available External services: Kerberose Server, RADIUS server, Windows Local Authentication service.
The Advanced security option is not enabled and can only be used for operating system authentication, as well as for ordinary users.


Normal users use operating system authentication:
Create an Oracle user account with the same name as the operating system user account, but the created user needs to prefix ops$, with the prefix set to the value of the parameter Os_authent_prefix by default:
Select value from V$parameter where name= ' os_authent_prefix ';
Create user ops$username identified externally;
Grant create session to Ops$username;
Log in to Linux as username and log in to the Oracle database as the operating system.
Sqlplus/

(4) Global authentication
Use an LDAP server.

1.3 Creating and deleting user accounts
To create a user account:
Create user Scott identified by Tiger
Default tablespace users Temporary tablespace temp
Quota 100m on users, quota unlimited on example
Profile default
Password expire
account unlock;


To delete a user account:
Drop user Scott Cascade;

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

5. Granting and revoking permissions
System permissions: Allows the user to perform operations that affect the data dictionary.
Object permissions: Allows the user to perform actions that affect the data.


(1) System permissions
Common system permissions:
· CREATE SESSION
· RESTRICTED SESSION
· ALTER DATABASE
· ALTER SYSTEM
· CREATE tablespace, ALTER tablespace, DROP tablespace
· CREATE TABLE
· GRANT any OBJECT PRIVILEGE
· CREATE any TABLE
· DROP any TABLE
· INSERT any table, UPDATE any table, DELETE any table
· SELECT any TABLE


User's system permissions:
Select privilege from Dba_sys_privs where grantee= ' username '; --View user-owned system permissions


Authorization and Undo syntax:
Grant Privilege[,privilege ...] to username [with admin option]; --Grant Permissions
Revoke Privilege[,privilege ...] from username; --Revoke permissions


Grant create session, ALTER session, CREATE table to username;


Allows permission delivery (with admin option), and does not cascade undo when revoking permissions.
Connect system/oracle;
Grant CREATE table to Scott with admin option;
Connect Scott/tiger;
Grant CREATE table to username;
Connect system/oracle;
Revoke CREATE table from Scott;

Grant Select any table to Scott. Any of the words will overwrite the original permissions, usually have a bad effect (better than before, not in the SYS mode of the table)

(2) Object permissions
· SELECT: Authorization objects are tables, views, synonyms, sequences
· INSERT: Authorization object for table, view, synonym
· UPDATE: Authorization objects are tables, views, synonyms
· DELETE: The Authorization object is a table, a view, a synonym
· ALTER: The Authorization object is a table, sequence
· EXECUTE: The Authorization object is a procedure, function, package, synonym


Select grantee,privilege,grantor,grantable from Dba_tab_privs; --View Table permissions


Authorization and Undo syntax:
Grant Privilege[,privilege ...] on schema.object to username [with GRANT option]; --Grant Permissions
Revoke Privilege[,privilege ...] on schema.object from username; --Revoke permissions


Grant SELECT on Hr.employees to Scott;
Grant Update (Salary) on hr.employees to Scott;
Grant all on hr.regions to Scott;


Allows permission to be passed (with GRANT option), to revoke the permission when the cascade is undone.
Connect hr/hr;
Grant SELECT on employees to Scott with GRANT option;
Connect Scott/tiger;
Grant SELECT on Hr.employees to username;
Connect hr/hr
Revoke select on employees from Scott;

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

6. Create and manage roles
A role is not a schema object, is not owned by anyone, shares the same namespace with a user, and the role cannot have the same name.
Predefined roles:
· CONNECT: Only the Create session permission, which also has the system permissions for creating the Datastore object before 11g.
· RESOURCE: You can create data objects and process objects, and there are unlimited tablespace permissions before 12c.
· DBA: With most system permissions and multiple object permissions and roles, you can manage all aspects of the database and not start and shut down the database.
· Select_catalog_role
· Scheduler_admin
· Public: This role will be granted to each user, which is more special and does not appear in the Dba_roles view.


Select role from Dba_roles; --View Roles
SELECT * from Dba_role_privs; --View Role permissions

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

7. Create and manage configuration files
Profile feature: Enforces password policies to limit the resources that a session can occupy.
The password policy of the configuration file is always enforced, and the resource limit for the profile is enforced only when the instance parameter is resource_limit=true.


Select Username,profile from Dba_users; --the profile assigned to the user account, default configuration file
SELECT * from Dba_profiles where profile= ' DEFAULT '; --The contents of the configuration file


Password limit:
· failed_login_attempts password allowed consecutive errors at logon
· Password_lock_time the number of days to lock the account after the failed_login_attempts arrives
· Password_lift_time number of days before password expires
· Password_grace_time number of days after the first successful login after the password expires
· Password_reuse_time number of days before password can be re-used
· Password_reuse_max number of times a password is allowed to be reused
· password_verify_function function for verifying the complexity of a password


Resource limits:
· Sessions_per_user
· Cpu_per_session
· Cpu_per_call
· Logical_reads_per_session
· Logical_reads_per_call
· Private_sga
· Connect_time
· Idle_time
· Composite_limit


Opening resource limit parameters for instance parameter settings
alter system set resource_limit=true;


Creation and assignment of configuration files
Create profile Dba_profile limit Sessions_per_user Unlimited
Password_lift_time 7 Password_grace_time 1;
Alter user SYS profile Dba_profile;
Alter user system profile Dba_profile;


Alter profile default limit Sessions_per_user 1; --Modify the configuration file


To enable more advanced password management, run the script:
$ORACLE _home/rdbms/admin/utlpwdmg.sql


Delete the configuration file and reassign the default configuration file.
Drop profile profile_name cascade;

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

Minimize the principle of:

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

Using standard database auditing

I. Audit SYSDBA ACTIVITIES
Enabling audit SYSDBA activity, each statement published by a user who is a sysdba or sysoper connected to a database is written to the operating system's audit trail, giving a complete record of what the DBA is doing.
Audit trails must be protected, and if the DBA is able to delete audit records, it makes no sense to create these audit records. Therefore, DBAs should not have superuser privileges on the operating system.
Parameters about the Audit:
Audit_sys_operations--whether audit SYSDBA activity is enabled, true by default
Audit_file_dest--The directory where the audit trail files are located
Select Name,value from v$parameter where name like ' audit% ';

Two. Database audits
Before setting up a database audit, you must set the Audit_trail parameter, the parameter value:
· NONE (or FALSE): Disable database auditing
· OS: Audit records are written to the operating system's audit trail directory (the directory where audit_file_dest resides).
· DB: Audit records are written to the data dictionary table sys.aud$.
· Db_extended: Same as DB, but contains SQL statements with bound variables that generate audit records.
· XML: The role of the OS is broadly the same, but the format is formatted using XML tags.
· Xml_extended: Much the same as XML, but uses SQL statements and binding variables.
After you set the Audit_trail parameter, you can use database auditing to capture logon attempts, the use of system permissions and object permissions, and the execution of SQL commands, and you can specify whether they are audited if they succeed and/or fail due to permission control. Auditing is particularly useful for commands that are not executed successfully, and any records generated will indicate that the user is attempting to violate his or her access rights.
Use the audit command to configure database auditing:
Sql> audit Create any trigger;
Sql> Audit Select any table by session;
Sql> Audit Select any table by Access;
-by session audits generate an audit record for each session that violates the audit criteria, regardless of the number of violations.
--by Access generates a record for each violation of the condition.
Sql> Audit Insert on ar.hz_parties whenever successful; --An audit record is generated when a row is inserted into the specified table
Sql> audit all on Ar.ra_interface_lines_all; --Audit Each session that executes DDL statements against the specified table.
--Whenever successful audit record limited to record of successful operation
--Whenever not successful
Sql> Audit session whenever not successful; --Audit Login

If you want to audit the operating system (the Audit_trail parameter is set to OS or XML), use the appropriate editor to view the files created in the operating system audit trail and view the audit results.
If the audit is for the database (the Audit_trail parameter is set to DB or db_extended), the audit record is written to the data dictionary table sys.aud$, but the audit results are generally viewed using a view dba_audit_trail.

Audit view
Dba_audit_trail
Dba_audit_object
Dba_audit_statement
Dba_audit_session

Three. Using triggers to perform value-based audits
Database audits can capture the fact that a command is executed against a table, but the affected rows are not tracked. For example, if you execute the audit insert on Hr.employees command, an audit record is generated whenever a row is inserted in the specified table, but the audit record does not contain the actual value of the inserted row. There are times when we want to capture these actual values and we need to use a database trigger at this point.

CREATE OR REPLACE TRIGGER system.creditrating_audit
After UPDATE of creditrating
On Oe.customers
Referencing new as new old as old
For each ROW
BEGIN
IF:old.creditrating! =: new.creditrating Then
INSERT into System.creditrating_audit VALUES (Sys_context (' Userenv ', ' Os_user '), Sys_context (' Userenv ', ' ip_address ') ,: new.cust_id| | ' credite trating changed from ' | |:o ld.creditrating| | ' to ' | |:new.creditrating);
END IF;
END;
/


Four. Fine-grained audit (fine-grained AUDITING,FGA)
Fine-grained auditing can be configured to generate audit records only when specific columns of a particular row or row are accessed, and can also be configured to run a PL/SQL code block when an audit condition is violated.
Configuring fine-grained auditing requires the use of package DBMS_FGA. Create an audit policy using process add_policy.
Parameters of the procedure add_policy:
Object_schema
object_name
Policy_name
audit_condition
Audit_column
Handler_schema-The user name of the process running when the audit condition is met
Handler_module-the PL/SQL process that runs when the audit condition is met
Enable--true/false, enabling and disabling
Statement_types--types of statements audited: SELECT, INSERT, UPDATE, DELETE
Audit_trail
audit_column_opts--Dbms_fga_any_columns/dbms_fga_all_columns

Example of creating an audit policy:
Dbms_fga.add_policy (Object_schema = ' HR ',
object_name = ' EMPLOYEES ',
Policy_name = ' POL1 ',
audit_condition = ' department_id=80 ',
Audit_column = ' SALARY ');

Audit View:
Dba_audit_trail
Dba_fga_audit_trail

Dba_common_audit_trail

To create a role:
Create role RoleName;
Grant create session to RoleName [with admin option|with grant option];
Revoke rolename from rolename2;


Grant SELECT on Hr.regions to RoleName;
Grant RoleName to rolename2 with admin option;
Revoke rolename from Rolename2

Oracle 11g OCP Notes (6)-Oracle Security