Oracle Database User Authentication PDF

Each database user account has a unique username an authentication method (identified by password) default tablespace temporary tablespace a user profile a lock status quota The SYS account -Is granted the DBA role -Has all privileges with ADMIN OPTION -is required for startup, shut...

Each database user account has a unique username an authentication method (identified by password) default tablespace temporary tablespace a user profile a lock status quota The SYS account -Is granted the DBA role -Has all privileges with ADMIN OPTION -is required for startup, shut down, and maintenance commands -owns the data dictionary -the SYS account is granted the DBA role -these accounts are not used for routine operations SYSOPER privilege can start and shutdown the database perform online and offline backups achieve the current redo log files connect to the database when it is in the RESTRICTED SESSION mode the SYSDBA privilege contains all the rights of SYSOPER plus being able to create a database grant the SYSDBA and SYSOPER privilege to other users users who connect with the SYSDBA privilege connect as the SYS user when creating users CREATE USER user name IDENTIFIED by password DEFAULT TABLESPACE tablespace TEMPORARY TABLESPACE tablespace QUOTA 100M on tablespace PROFILE profile_name PASSWORD EXPIRE ACCOUNT {LOCK|UNLOCK} USERS can be Database authenticated Externally authenticated by the OS Globally - externally maintained user directory For database authenticated users YOU MUST ASSIGN a password ORACLE TABLESPACE a tablespace is a logical storage - used to group and manage segments which are the physical storage units for database objects such as tables, indexes, partitions tablespace provide a way to organize and allocate storage for database objects efficiently Default Tablespace - default tablespace for the user when a user creates objects like tables, indexes without explicitly specifying a tablespace, they will be created in this default tablespace Temporary Tablespace - are used for sorting and storing temporary data during query processing QUOTA on tablespace amount of space that user can utilize in specific tablespaces. when the user gets created and specified their default, temporary tablespace, tablespaces quotas allows for initial configuration tailored to the user's requirements and expected workout. Ensures that the user starts with appropriate storage settings from the beginning Authenticated users password is saved in the database requires explicitly defined username and password when connecting to the database Database Authentication With database authentication, users are authenticated by the database itself using credentials stored within the database. When a user attempts to connect to the database, user provides their username and password which are verified against the database's internal authentication mechanism OS_AUTHENT_PREFIX parameter sets the prefix of the user, by default it is OPS$ EXTERNALLY AUTHENTICATED USER with externally authentication, your database relies on the underlying OS or network authentication service to restrict access to database accounts you don't need a database password for this type of login if your OS or network service permits, then you can have it authenticate users Set the initialization parameter OS_AUTHENT_PREFIX, and use this prefix in Oracle Database user names The OS_AUTHENT_PREFIX parameter defines a prefix that Oracle Database adds to the beginning of every user's operating system account name Oracle compares the prefixed user name with the Oracle user names in the database when a user attempts to connect user authentication is delegated to an external source or system outside of the Oracle Database. This external source could be an operating system, an LDAP (Lightweight Directory Access Protocol) directory service, or another authentication service Users are authenticated based on their credentials stored outside the database such as OS credentials or credentials managed by an LDAP directory Oracle Database supports various external authentication methods including -OS authentication -Kerberos authentication -integration with LDAP directory External authentication i commonly used in environments where user authentication is already managed externally, and organizations want to leverage existing authentication mechanisms without duplicating user credentials in the database Data Storage Structure in non Automatic Storage Management (ASM) environment In a non-ASM environment, database administrators manage storage manually using the operating system's file system. NON ASM Components are Tablespaces - logical container, contains segments Datafiles - fundamental units of data storage, contains the actual database data including tables , indexes, and other database objects. Each datafile is associate with a specific tablespace Segments - used to store specific types of database objects, are made up of extents Extents - is a collection of contiguous blocks that are allocated to a segment, inside of a datafile Blocks - smallest units of storage. Also called database blocks or pages. The size of a block is defined at the time of database creation and usually a multiple of the operating system block size. a inside of an extent They represent the basic building blocks of the database's storage infrastructure A tablespaces contain segments segments are made up of extents, extents are composed of blocks and blocks are stored in datafiles an extent is a contiguous set of data blocks allocated to a specific database object such as a table or an index, when a table or index needs more space to store data, oracle database allocates extents to accommodate the additional data USER VIEWS views are database objects that provide a customizable and simplified representation of the underlying data stored in tables, indexes, and other database objects are virtual tables Dictionary Views Data base dictionary views that allow you to get information on database users Dictionary views are database views that store information on the database. They store metadata, which includes details about database objects, users, privileges, and other configurations Roles Role characteristics each role name must be unique among existing usernames and role names just as you can grant a privilege to a user GRANT CREATE TABLE TO user_name you can also do it with roles GRANT role_name to user_name Can consist of both system and object privileges roles can be enabled or disabled for a user can require a password to enable are not owned by anyone, are not in any schema have their description stored in the data dictionary Roles are not allowed WITH ADMIN OPTION or WITH GRANT OPTION Benefits of Roles Reduced granting of privileges Dynamic privilege management Selective availability of privileges No cascading revokes - The CASCADE option revokes the specified privilege and any privileges that are dependent upon the granted privilege this doesn't remove the privilege to a user that has been granted by a user that we just revoked User1 has Role A, which includes privileges like SELECT and INSERT. User1 grants the SELECT privilege (from their role) to User2. If Role A is revoked from User1, User2's SELECT privilege remains intact because revoking the role does not cascade and remove privileges already granted by User1. NOT IDENTIFIED when a user account is created "NOT IDENTIFIED", it means that the user is not required to provide any authentication credentials (such as username or password) typically used for users who will be authenticated externally by the operating system or another external mechanism IDENTIFIED BY password With "IDENTIFIED BY password," the user is required to provide a password when connecting to the database. The password specified in the statement becomes the user's login password. This is the most common method of authentication in Oracle Database. IDENTIFIED USING package "IDENTIFIED USING package" allows for custom authentication mechanisms using a PL/SQL package. When this option is specified, Oracle Database calls the specified PL/SQL package to authenticate the user during the login process. IDENTIFIED EXTERNALLY "IDENTIFIED EXTERNALLY" indicates that the user will be authenticated externally by the operating system or another external mechanism. When a user attempts to connect to the database, Oracle relies on the operating system to authenticate the user based on the operating system user account credentials. OBJECT PRIVILIGE TO A USER GRANT SELECT, UPDATE ON employees(table name) TO hr_clerk (roles) SYSTEM PRIVILEGE GRANT hr_clerk TO David, Rachel (names) ESTABLISHING DEFAULT ROLES SQL> ALTER USER scott DEFAULT ROLE hr_clerk, sales_clerk; a default role is a subset of these roles that is automatically enabled when the user logs on changes the user (scott) to enable the roles when scott logs in ENABLING AND DISABLING ROLES enabling and disabling roles in the user session is controlled by the user disable a role to temporarily revoke the role from the session The SET ROLE command enables and disables roles Default roles are enabled for a user at login A password may be required to enable a role SQL> SET ROLE sales_clerk IDENTIFIED BY commission; enables sales_clerk role with its password REMOVING ROLES SQL> REVOKE hr_clerk FROM david; removes the role hr_clerk from David When you drop a role, the Oracle server revokes it from all users and roles to whom it has been granted and removes it from the database You must have been granted the role WITH ADMIN OPTION or have DROP ANY ROLE system privilege to drop the role. When created, Oracle User does not have any privileges on the database or its objects. The DBA or Security administrator have to assign the least level of privileges that will allow the user to do his/her work CONNECT role allows users to connect to the database RESOURCE role provides basic privileges necessary for creating and managing database objects, such as tables, views, sequences, and procedures. They can also perform common DDL operations within their own schema DBA (Database Administrator): The DBA role is a powerful role that provides extensive privileges for database administration tasks WORKING WITH NON IDENTIFIED DEFAULT ROLES role is considered default role for user when assigned to user Default roles are automatically active when the user connects to the database ALTER USER u1 DEFAULT ROLE ALL EXCEPT R3 this alters u1 and removes R3 as default roles SYSTEM PRIVILEGES GRANT CREATE SESSION TO user1 OBJECT PRIVILEGES GRANT SELECT, INSERT ON table1 TO user1 AUDITING refers to the process of monitoring and recording various activities and events that occur within a system or application auditing can notify administrators of suspicious or questionable activities Types of Auditing Statement Auditing - enables you to audit SQL statements by type of statement Tracks what type of SQL commands are used (CREATE TABLE, DROP TABLE) but does not focus on specific objects (like a particular table or user) Tracks actions regardless of the privilege a user has. It focuses on the type of action Privilege Auditing - tracks the user of specific system privilege, Example: if you enable AUDIT CREATE TABLE, it will track every time a user uses the privilege to create a table If a user has the privilege (e.g., CREATE TABLE) and uses it to create a table, this action will be audited. privilege auditing is more focused than statement auditing to audit a selected user or every user in the database Schema Object Auditing - tracks specific actions like (SELECT, INSERT, UPDATE, or DELETE) on a specific table or object Example: If you enable AUDIT SELECT ON employees, it will track all SELECT statements run on the employees table this type of auditing applies to all users enables you to audit specific statements on a particular schema object such as AUDIT SELECT ON employees. Fine Grained Auditing - Tracks actions based on detailed, custom conditions. Enables auditing based on access to or changes in a column. Audit records include information about the operation that was audited the user performing and the date and time of the operation Audit records can be stored in one of two places Data dictionary table, called the database audit trail Operating system files, called an operating system audit trail Database Audit Trail The database audit trail consists of a single table named SYS.AUD$ in the SYS schema of the data dictionary of each Oracle database Oracle Database allows audit trail records to be directed to an operating system audit trail if the operating system makes such an audit trail available to Oracle Database. If not, then audit records are written to a file outside the database In Windows, the information is normally accessed through Event Viewer Some database-related actions are always recorded into the operating system and syslog audit trails regardless of whether database auditing is enabled The fact that these records are always created is sometimes referred to as mandatory auditing At instance startup, an audit record is generated that includes the operating system user starting the instance, the terminal identifier of the user, and the date and time stamp. This information is recorded into the operating system or syslog audit trails because the database audit trail is not available until after startup has successfully completed. At instance shutdown, an audit record is generated that details the operating system user shutting down the instance, the terminal identifier of the user, and the date and time stamp when a user shutting down the instance an audit record is generated that details the operating system user connecting to Oracle Database with administrator privileges When are Audit Records created? Standard auditing for the entire database is EITHER ENABLED OR DISABLED BY THE SECURITY ADMINISTRATOR if it is disabled, then no audit records are created if database auditing, enabled by the security administrator, then individual audit options become effective. These audit options can be set by any authorized database user for database objects he owns. when auditing is enabled in the database and an action set to be audited occurs, an audit record is generated during the execute phase of the statement SQL statements inside PL/SQL program units are individually audited, as necessary, when the program unit is executed Regardless of whether the transaction is committed or rolled back, the audit record about the transaction is still saved. For statement, privilege, and schema object auditing, Oracle allows the selective auditing of successful executions of statements (using WHENEVER SUCCESSFUL clause) unsuccessful attempts to execute statements (USING WHENEVER NOT SUCCESFFUL clause) both (using neither clause) In Oracle, you can configure statement, privilege, and schema object auditing to track successful or unsuccessful actions (or both) performed by users. Therefore, you can monitor actions even if the audited statements do not complete successfully. Statements that fail to execute because they were not valid cannot be audited SETUP STANDARD AUDITING AUDIT_TRAIL - this parameter specifies where audit records are written. It determines the destination for the audit trail Possible values: OS: Audit records are written to the operating system's audit trail. This option is available only on platforms where the operating system provides auditing capabilities. XML: Audit records are written in XML format to the operating system's audit trail or to the operating system file system AUDIT_FILE_DEST-This parameter specifies the directory location where audit files are stored when AUDIT_TRAIL is set to OS or XML AUDIT_SYS_OPERATIONS: This parameter determines whether to audit operations issued by users connecting as SYSDBA or SYSOPER. What can be audited? Statement Privilege Object Network Restoration is the process of copying backup files to their original locations to make them available to the Oracle database server Recovery involves updating these restored files using archived and online redo logs. These logs contain records of changes made to the database after the backup was taken. This step ensures the database reflects all changes up to the point of failure RMAN (Recovery Manager) can be used to automate the recovery process. It can also utilize incremental backups, which contain only the data blocks that changed since the last backup, making the recovery process faster and more efficient Media recovery process - Media recovery involves applying archived redo logs and online redo logs to roll forward the restored database files. This updates the datafiles to include all changes made since the last backup Oracle Flashback Technology: Oracle Flashback provides tools like Flashback Database and Flashback Table to quickly revert the database to a previous state in case of logical data corruption or user errors, without going through the full recovery process Crash and Instance Recovery: Oracle automatically performs crash recovery and instance recovery after a database failure. Crash recovery is for single-instance databases that have crashed, while instance recovery applies to Oracle Real Application Clusters (RAC) where one or more instances have failed A backup is a copy of data from a database that you save to another location to protect against data loss. There are different types of backups, each serving different purposes Full Backups: A full backup includes every allocated block in a datafile. (It copies everything that is in use.) Incremental Backups Incremental backups copy only the data blocks that have changed since a previous backup Types Differential Incremental Backups: Back up all changes since that last incremental backup at the same or lower level Cumulative Incremental Backups: Back up all changes since the last level 0 backup