Managing Users,Objectives,After completing this lesson, you should be able to do the following:Create new database usersAlter and drop existing database usersMonitor information about existing users,Accountlocking,Tablespacequotas,Temporarytablespace,Defaulttablespace,Roleprivileges,Resourcelimits,Securitydomain,Directprivileges,Authenticationmechanism,Users and Security,Database Schema,A schema is a named collection of objectsA user is created, and a corresponding schema is createdUser can be associated only with one schemaUsername and schema are often used interchangeably,Checklist for Creating Users,Identify tablespaces in which the user needs to store objects.Decide on quotas for each tablespace.Assign a default tablespace and temporary tablespace.Create a user.Grant privileges and roles to the user.,Creating a New User: Database Authentication,Set the initial password:,CREATE USER aaronIDENTIFIED BY soccerDEFAULT TABLESPACE dataTEMPORARY TABLESPACE tempQUOTA 15m ON dataPASSWORD EXPIRE;,Creating a New User: Operating System Authentication,CREATE USER aaronIDENTIFIED EXTERNALLYDEFAULT TABLESPACE USERSTEMPORARY TABLESPACE tempQUOTA 15m ON dataPASSWORD EXPIRE;,OS_AUTHENT_PREFIX initialization parameter specifies the format of the usernamesDefaults to OPS$,Changing User Quota on Tablespace,ALTER USER aaronQUOTA 0 ON USERS;,Dropping a User,Use the CASCADE clause to drop all objects in the schema if the schema contains objects.Users currently connected to the Oracle server cannot be dropped,DROP USER aaron;,DROP USER aaron CASCADE;,Obtaining User Information,Information about users can be obtained by querying the data dictionary.DBA_USERSDBA_TS_QUOTAS,Summary,In this lesson, you should have learned how to:Create users specifying the appropriate password mechanism Control usage of space by users,Practice 15 Overview,This practice covers the following topics:Creating usersDisplaying data dictionary information about users Removing user quotas,Managing Privileges,Objectives,After completing this lesson, you should be able to do the following:Identify system and object privilegesGrant and revoke privilegesIdentify auditing capabilities,Managing Privileges,Two types of Oracle user privileges: System: Enables users to perform particular actions in the databaseObject: Enables users to access and manipulate a specific object,System Privileges,There are over 100 distinct system privilegesThe ANY keyword in the privileges signifies that users have the privilege in any schemaThe GRANT command adds a privilege to a user or a group of usersThe REVOKE command deletes the privileges,System Privileges: Examples,CategoryExamples INDEXCREATE ANY INDEXALTER ANY INDEXDROP ANY INDEX TABLE CREATE TABLECREATE ANY TABLEALTER ANY TABLEDROP ANY TABLESELECT ANY TABLEUPDATE ANY TABLEDELETE ANY TABLESESSIONCREATE SESSIONALTER SESSIONRESTRICTED SESSIONTABLESPACECREATE TABLESPACEALTER TABLESPACEDROP TABLESPACEUNLIMITED TABLESPACE,Granting System Privileges,GRANT CREATE SESSION TO emi;,GRANT CREATE SESSION TO emi WITH ADMIN OPTION;,SYSDBA and SYSOPERPrivileges,System Privilege Restrictions,O7_DICTIONARY_ACCESSIBILITY parameterControls restrictions on SYSTEM privilegesIf set to TRUE, access to objects in SYS schema is allowedDefault is FALSE Ensures that system privileges that allow access to any schema do not allow access to SYS schema,Revoking System Privileges,REVOKE CREATE TABLE FROM emi;,Revoking System Privileges WITH ADMIN OPTION,DBA,GRANT,REVOKE,Jeff,Emi,Jeff,Emi,DBA,Object Privileges,Object priv.TableViewSequenceProcedureALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE ,Granting Object Privileges,GRANT EXECUTE ON dbms_output TO jeff;,GRANT UPDATE ON emi.customers TO jeff WITH GRANT OPTION;,GRANT object_privilege (column_list) , object_privilege (column_list) .|ALL PRIVILEGESONschema.objectTOuser|role|PUBLIC, user|role|PUBLIC .WITH GRANT OPTION,Revoking Object Privileges,REVOKE SELECT ON emi.orders FROM jeff;,REVOKE object_privilege, object_privilege .| ALL PRIVILEGES ONschema.objectFROM user|role|PUBLIC, user|role|PUBLIC .CASCADE CONSTRAINTS,GRANT,REVOKE,Revoking Object Privileges WITH GRANT OPTION,Bob,Jeff,Emi,Emi,Jeff,Bob,Obtaining Privileges Information,Data Dictionary ViewsDBA_SYS_PRIVSSESSION_PRIVSDBA_TAB_PRIVSDBA_COL_PRIVS,Auditing,Auditing is the monitoring of selected user database actionsUsed toInvestigate suspicious database activityGather information about specific database activities,Auditing Guidelines,Define what you want to auditAudit users, statements, or objectsStatement executionsSuccessful statement executions, unsuccessful statement executions or bothManage your audit trailMonitor the growth of the audit trailProtect the audit trail from unauthorized access,