bordoloi and bock copyright 2004 prentice hall, inc

Bordoloi and BockBordoloi and Bock Copyright 2004 Prentice Hall, Inc. 12-1

CHAPTER 12:CHAPTER 12:ORACLE DATABASE ORACLE DATABASE ADMINISTRATIONADMINISTRATION


• Database administrationDatabase administration is a specialized area within a is a specialized area within a large information systems department that is separate large information systems department that is separate from the application development area.from the application development area.

• Application development includes systems analysis, Application development includes systems analysis, systems design, programming, and systems testing.systems design, programming, and systems testing.

• Database administration is concerned with Database administration is concerned with administrative tasks that must be accomplished in order administrative tasks that must be accomplished in order for application developers and information system users for application developers and information system users to access an organization's databases. to access an organization's databases.

• A database administrator (DBA) is neither superior to A database administrator (DBA) is neither superior to nor inferior to an application developer. nor inferior to an application developer.

Contd. Contd.

DATABASE ADMINISTRATION OVERVIEWDATABASE ADMINISTRATION OVERVIEW


• DBAs and application developers must be both DBAs and application developers must be both technically competent and capable of working closely technically competent and capable of working closely with other professionals in a support type of with other professionals in a support type of relationship.relationship.

• One of the primary roles of a DBA is to provide the One of the primary roles of a DBA is to provide the support needed by an application developer so that the support needed by an application developer so that the application developer can accomplish the task of application developer can accomplish the task of building and maintaining information systems.building and maintaining information systems.

DATABASE ADMINISTRATION OVERVIEWDATABASE ADMINISTRATION OVERVIEW


• Install relational database management system Install relational database management system software and upgrades.software and upgrades.

• Design and create a database including the Design and create a database including the allocation of system disk storage for current and allocation of system disk storage for current and future database storage requirements. future database storage requirements.

• Start up and shut down a database.Start up and shut down a database.

• Create user accounts and monitor user activities.Create user accounts and monitor user activities.

• Grant database privileges to control data security Grant database privileges to control data security and data access. and data access.

DBA DutiesDBA Duties


• Backup and recover a database in the event of Backup and recover a database in the event of system failure.system failure.

• Tune a database to optimize database Tune a database to optimize database performance. performance.

• Manage database network connectivity.Manage database network connectivity.

• Migrate a database to a new version of the Migrate a database to a new version of the DBMS software.DBMS software.

DBA DutiesDBA Duties


ORGANIZATION OF AN ORACLE DATABASEORGANIZATION OF AN ORACLE DATABASE

• An Oracle relational database is typical of databases running on An Oracle relational database is typical of databases running on larger relational database management systems, and includes larger relational database management systems, and includes both memory and disk storage components. both memory and disk storage components.

• Figure 12.1 provides a fairly detailed conceptual model of what Figure 12.1 provides a fairly detailed conceptual model of what is termed an is termed an OracleOracle InstanceInstance..


• The large rectangle in the figure labeled The large rectangle in the figure labeled Memory Memory Allocated to theAllocated to the InstanceInstance represents part of a server represents part of a server computer's random access memory (RAM).computer's random access memory (RAM).

• This part of RAM is strictly allocated to a running This part of RAM is strictly allocated to a running instanceinstance of an Oracle database. of an Oracle database.

• The instance includes the The instance includes the system global area (SGA)system global area (SGA), and , and the SGA is divided into the the SGA is divided into the database buffer cachedatabase buffer cache, , redo redo log bufferlog buffer, , shared poolshared pool, , large poollarge pool, and , and JAVA poolJAVA pool. .

• The The instanceinstance also includes memory allocated for also includes memory allocated for computer programs called computer programs called background processesbackground processes..

• The background processes are part of the Oracle The background processes are part of the Oracle instanceinstance, and are actually computer programs., and are actually computer programs.

Oracle InstanceOracle Instance


• These programs provide the services needed to enable These programs provide the services needed to enable system developers and system users to save database system developers and system users to save database objects such as data tables and data rows in tables.objects such as data tables and data rows in tables.

• The background processes also enable database The background processes also enable database recovery, database backup, data archiving, and other recovery, database backup, data archiving, and other

data management tasks as necessary.data management tasks as necessary. • An Oracle database does not normally run on a personal An Oracle database does not normally run on a personal

computer, although there is a version of Oracle called computer, although there is a version of Oracle called Personal Oracle that will do just that.Personal Oracle that will do just that.

• An Oracle database is very large and resides on a An Oracle database is very large and resides on a powerful server computer.powerful server computer.



• A server computer is typically connected to many disk A server computer is typically connected to many disk drives.drives.

• TheThe database files database files shown in the figure typically include shown in the figure typically include many different physical files stored on many disk drives.many different physical files stored on many disk drives.

• Database files provide the primary storage for all objects Database files provide the primary storage for all objects stored in a database. This is where user tables and data stored in a database. This is where user tables and data are stored.are stored.

• The The redo log filesredo log files in the figure store information that is in the figure store information that is used to recover a database if some type of database used to recover a database if some type of database failure occurs.failure occurs.

• The The control filescontrol files store information about the actual store information about the actual physical organization of an Oracle database. physical organization of an Oracle database.



• The The parameter fileparameter file stores information that Oracle stores information that Oracle needs in order to operate the database as specified by needs in order to operate the database as specified by the DBA.the DBA.

• When a system user logs onto an Oracle database or When a system user logs onto an Oracle database or when an application program executes, Oracle when an application program executes, Oracle allocates a memory object called a allocates a memory object called a server processserver process as as shown in the figure.shown in the figure.

• The dedicated server version of Oracle allocates one The dedicated server version of Oracle allocates one server process to each system server process to each system user process.user process.

• The multi-threaded server version of Oracle allows The multi-threaded server version of Oracle allows server processes to be shared by more than one system server processes to be shared by more than one system user process. user process.



• A server process communicates with a user process in A server process communicates with a user process in order to service the data requests from the connected order to service the data requests from the connected user process. user process.

• The server process actually interprets the SQL The server process actually interprets the SQL commands passed from the user process through the commands passed from the user process through the server process to Oracle, and moves the requested data server process to Oracle, and moves the requested data from the database files into the database buffer cache.from the database files into the database buffer cache.

• Suppose that a system user executes an SQL command Suppose that a system user executes an SQL command that will modify an existing data row stored in an Oracle that will modify an existing data row stored in an Oracle database, the data row is moved from a database file to database, the data row is moved from a database file to the server process allocated to the system user, and then the server process allocated to the system user, and then to the to the database buffer cachedatabase buffer cache in the in the system global areasystem global area. .



• The database buffer cache is a memory object that is The database buffer cache is a memory object that is shared by all system users.shared by all system users.

• This sharing improves system performance because more This sharing improves system performance because more than one user process may need to access the same data than one user process may need to access the same data row.row.

• Data inside the database buffer cache are constantly being Data inside the database buffer cache are constantly being modified as data rows are added, deleted, and updated.modified as data rows are added, deleted, and updated.

• Modified data blocks (a single data block typically Modified data blocks (a single data block typically contains many rows of data) are written from the database contains many rows of data) are written from the database buffer cache by a background process called the buffer cache by a background process called the database database writerwriter (DBWn)(DBWn) back to the appropriate disk data file, back to the appropriate disk data file, thereby saving the data to the magnetic hard disk. thereby saving the data to the magnetic hard disk.



• As existing data rows in the database buffer cache are As existing data rows in the database buffer cache are modified or new data rows are created, memory images of modified or new data rows are created, memory images of these modifications and new rows are stored to the these modifications and new rows are stored to the redo redo log bufferlog buffer in the system global area. in the system global area.

• As the redo log buffer in memory fills up, the modified As the redo log buffer in memory fills up, the modified row images are written to the redo log files on disk by a row images are written to the redo log files on disk by a different background process called the different background process called the log writer log writer (LGWR)(LGWR)..

• The redo log files are magnetic hard disk objects; thus, The redo log files are magnetic hard disk objects; thus, their data are not lost if electrical power fails.their data are not lost if electrical power fails.

• Over time, the redo log files can be archived to provide a Over time, the redo log files can be archived to provide a more permanent record of database changes.more permanent record of database changes.



• The data in redo log buffers and in redo log files are used The data in redo log buffers and in redo log files are used to recover a database in the event of some type of system to recover a database in the event of some type of system failure.failure.

• In addition to the database writer and log writer In addition to the database writer and log writer background processes, there are other background background processes, there are other background processes that perform additional database administration processes that perform additional database administration tasks such as managing memory space allocated to Oracle tasks such as managing memory space allocated to Oracle by the operating system, archiving data, and performing by the operating system, archiving data, and performing backup and recovery. backup and recovery.

• Some of these processes manage the Some of these processes manage the shared pool, large shared pool, large pool, and JAVA pool.pool, and JAVA pool.



• The shared pool is shared by all server processes in order The shared pool is shared by all server processes in order to improve performance efficiency of an Oracle database.to improve performance efficiency of an Oracle database.

• The large pool is memory that can be used to store The large pool is memory that can be used to store variables and other memory values that would be part of variables and other memory values that would be part of an individual system user's allocated server process.an individual system user's allocated server process.

• The Java Pool is used for memory allocation in support of The Java Pool is used for memory allocation in support of the execution of Java commands for Internet-based the execution of Java commands for Internet-based applications accessing an Oracle database. applications accessing an Oracle database.



• An Oracle database is normally divided into logical An Oracle database is normally divided into logical components termed components termed tablespacestablespaces. .

• Most tablespaces are created by a DBA when a database is Most tablespaces are created by a DBA when a database is initially created. initially created.

• As the term implies, a tablespace is used to store tables.As the term implies, a tablespace is used to store tables.• But, tablespaces are actually used to store But, tablespaces are actually used to store allall types of types of

database objects.database objects.

• These objects include indexes, sequences, procedures, These objects include indexes, sequences, procedures,

views, and other database objects.views, and other database objects.

TABLESPACES AND FILESTABLESPACES AND FILES


• A typical Oracle database will have most of the A typical Oracle database will have most of the tablespaces listed here.tablespaces listed here.

– SYSTEM SYSTEM – USERUSER– DATA DATA – INDEXESINDEXES– TEMP TEMP – UNDO UNDO – IDS IDS – SPECIAL_APPSSPECIAL_APPS



• Tablespaces are Tablespaces are logicallogical, not , not physicalphysical objects. objects. • Tablespaces are actually stored in Tablespaces are actually stored in data filesdata files..• Data files are the corresponding physical objects.Data files are the corresponding physical objects.• A very large tablespace may require more than one data A very large tablespace may require more than one data

file in order to store all of its objects. file in order to store all of its objects. • A data file can only store data for a single tablespace. A data file can only store data for a single tablespace. • Tablespaces are created with the CREATE Tablespaces are created with the CREATE

TABLESPACE command. TABLESPACE command.



• The Oracle The Oracle data dictionarydata dictionary consists of consists of read-only, base read-only, base tablestables that store information about the database. that store information about the database.

• The data dictionary is stored in the The data dictionary is stored in the systemsystem tablespace. tablespace. • Information stored in the data dictionary is termed Information stored in the data dictionary is termed

metadatametadata; that is, data about data. ; that is, data about data. • The read-only, base tables that comprise the data The read-only, base tables that comprise the data

dictionary are rarely ever accessed by system developers dictionary are rarely ever accessed by system developers or system users.or system users.

• Usually only the various Oracle processes will access Usually only the various Oracle processes will access these tables.these tables.

DATA DICTIONARYDATA DICTIONARY


• In order to make it easier to access information and In order to make it easier to access information and manage a database, the tables of the data dictionary are manage a database, the tables of the data dictionary are organized into various organized into various user-accessible viewsuser-accessible views..

• These views are also part of the data dictionary.These views are also part of the data dictionary.



• Some of the information stored in the data dictionary Some of the information stored in the data dictionary includes:includes:

– all schema object definitions – the definitions of the all schema object definitions – the definitions of the tables, indexes, sequences, views, and other database tables, indexes, sequences, views, and other database objects.objects.

– the amount of space allocated for each object, and the the amount of space allocated for each object, and the amount of space currently used by each object.amount of space currently used by each object.

– the names of the Oracle user accounts, and the the names of the Oracle user accounts, and the privileges and roles granted to each account.privileges and roles granted to each account.

– information needed to enforce integrity constraints.information needed to enforce integrity constraints.– other database information.other database information.



• When an Oracle database is created, two special user When an Oracle database is created, two special user accounts named SYS and SYSTEM are created.accounts named SYS and SYSTEM are created.

• The user account SYS is the owner of all base tables and The user account SYS is the owner of all base tables and user-accessible views in the data dictionary.user-accessible views in the data dictionary.

• The security of the SYS account is quite tight and only The security of the SYS account is quite tight and only DBAs can access this account. DBAs can access this account.

• During database operation, Oracle accesses the base tables During database operation, Oracle accesses the base tables through the SYS account.through the SYS account.

• For this reason, only Oracle can write new data or modify For this reason, only Oracle can write new data or modify existing data in the base tables.existing data in the base tables.

• Individual system users should never be given privileges to Individual system users should never be given privileges to UPDATE, INSERT, or DELETE rows for these data UPDATE, INSERT, or DELETE rows for these data dictionary tables. dictionary tables.

THE SYSTEM AND SYS ACCOUNTSTHE SYSTEM AND SYS ACCOUNTS


• When Oracle software or third-party software adds new When Oracle software or third-party software adds new tables and views to the data dictionary, the owner of tables and views to the data dictionary, the owner of these new objects is the user SYSTEM.these new objects is the user SYSTEM.

• Again, access to the SYSTEM account is also tight and Again, access to the SYSTEM account is also tight and is typically restricted to DBAs. is typically restricted to DBAs.

THE SYSTEM AND SYS ACCOUNTSTHE SYSTEM AND SYS ACCOUNTS


• There are many different types of data dictionary views.There are many different types of data dictionary views.• In order to assist system users in using the data In order to assist system users in using the data

dictionary, the views are divided into three different dictionary, the views are divided into three different categories: USER, ALL, and DBA.categories: USER, ALL, and DBA.

– USER – The USER – The useruser prefix is added to all views that display prefix is added to all views that display information about objects that belong to an individual user. information about objects that belong to an individual user. We term this the user's schema of the database.We term this the user's schema of the database.

– ALL – The ALL – The allall prefix is added to all views that display prefix is added to all views that display information about all objects in the global database schema. information about all objects in the global database schema. This expands on an individual user's perspective of the This expands on an individual user's perspective of the database.database.

– DBA – The DBA – The dbadba prefix is added to all views that fall within the prefix is added to all views that fall within the database administrator's schema of the database.database administrator's schema of the database.

TYPES OF VIEWS –USER, ALL, and DBATYPES OF VIEWS –USER, ALL, and DBA


• Individual accounts, including those that belong to Individual accounts, including those that belong to DBAs must be created.DBAs must be created.

• When a database is initially created, a DBA will logon to When a database is initially created, a DBA will logon to the database as the user SYS or SYSTEM, and create a the database as the user SYS or SYSTEM, and create a personal DBA account.personal DBA account.

• The DBA account will be granted all of the privileges The DBA account will be granted all of the privileges needed to administer the database.needed to administer the database.

USER ACCOUNTS AND PRIVILEGESUSER ACCOUNTS AND PRIVILEGES


• The DBA creates individual accounts for each system The DBA creates individual accounts for each system user.user.

• A simple form of the CREATE USER command is A simple form of the CREATE USER command is shown here:shown here:

• CREATE USER bock IDENTIFIED BY secret_password;CREATE USER bock IDENTIFIED BY secret_password;

• A DBA or other system administrator must have the A DBA or other system administrator must have the CREATE USER system privilege in order to create a CREATE USER system privilege in order to create a user account.user account.

• Even though Even though bockbock now has an account, now has an account, bockbock still cannot still cannot connect to the database until the DBA grants connect to the database until the DBA grants bockbock the the CREATE SESSION system privilege. CREATE SESSION system privilege.

Creating, Altering, and Dropping User AccountsCreating, Altering, and Dropping User Accounts


• The following SQL Example gives a more complete The following SQL Example gives a more complete form of the CREATE USER command.form of the CREATE USER command.

• This CREATE USER command creates a user account This CREATE USER command creates a user account and also allocates space for the storage of objects in and also allocates space for the storage of objects in various tablespaces.various tablespaces.

• This command will only execute if your database has the This command will only execute if your database has the tablespaces named here.tablespaces named here.

CREATE USER bock IDENTIFIED BY secret_passwordCREATE USER bock IDENTIFIED BY secret_password DEFAULT TABLESPACE usersDEFAULT TABLESPACE users TEMPORARY TABLESPACE tempTEMPORARY TABLESPACE temp QUOTA 10M ON usersQUOTA 10M ON users QUOTA UNLIMITED ON tempQUOTA UNLIMITED ON temp QUOTA 5M ON dataQUOTA 5M ON data PASSWORD EXPIRE;PASSWORD EXPIRE;

Creating, Altering, and Dropping User AccountsCreating, Altering, and Dropping User Accounts


• Suppose that we suspect Suppose that we suspect bockbock is doing something with is doing something with the database that is unauthorized! We can stop the database that is unauthorized! We can stop bockbock from creating additional objects by altering the from creating additional objects by altering the bockbock account.account.

• The commands shown in the following SQL Example The commands shown in the following SQL Example will alter will alter bock'sbock's quota on the quota on the usersusers and and datadata tablespaces. tablespaces.

• Existing objects Existing objects bockbock has created will not be modified, has created will not be modified, but he will be unable to create new objects.but he will be unable to create new objects.

• ALTER USER bock QUOTA 0 ON users;ALTER USER bock QUOTA 0 ON users;• ALTER USER bock QUOTA 0 ON data;ALTER USER bock QUOTA 0 ON data;

• You can also use the ALTER USER command to You can also use the ALTER USER command to allocate additional space and to restore quota allocations. allocate additional space and to restore quota allocations.

Altering User AccountsAltering User Accounts


• The DROP USER command will delete a user account.The DROP USER command will delete a user account.• It is necessary to use the CASCADE option if the user It is necessary to use the CASCADE option if the user

has created any objects. has created any objects. • DROP USER bock; DROP USER bock;

• DROP USER bock CASCADE;DROP USER bock CASCADE;

• If you fail to specify CASCADE, then the DROP USER If you fail to specify CASCADE, then the DROP USER command will fail if the user has created objects.command will fail if the user has created objects.

• We need to EXERCISE CAUTION while dropping user We need to EXERCISE CAUTION while dropping user accounts! accounts!

• What if What if bockbock is a system developer who has created is a system developer who has created some critical objects such as tables that our organization some critical objects such as tables that our organization uses to store inventory and payroll information?uses to store inventory and payroll information?

Dropping User AccountsDropping User Accounts


• If we drop If we drop bockbock with a CASCADE, the applications will with a CASCADE, the applications will fail because the tables and indexes that fail because the tables and indexes that bockbock created will created will be destroyed. be destroyed.

• A better alternative is to lock A better alternative is to lock bockbock out of his account out of his account because his employment has terminated.because his employment has terminated.

• We can do this by revoking his privilege to connect to We can do this by revoking his privilege to connect to the system.the system.

Dropping User AccountsDropping User Accounts


• The GRANT command in the following SQL Example The GRANT command in the following SQL Example will grant user will grant user bockbock the CREATE SESSION privilege the CREATE SESSION privilege need to logon to the SQL*PLUS or any of the Oracle’s need to logon to the SQL*PLUS or any of the Oracle’s tools.tools.

• GRANT PRIVILEGE create session TO bock;GRANT PRIVILEGE create session TO bock;

• There are many different privileges.There are many different privileges.• They are divided into two categories: They are divided into two categories: system privilegessystem privileges

and and object privilegesobject privileges..• System privileges allow a system user to perform System privileges allow a system user to perform

specific types of operations such as creating, dropping, specific types of operations such as creating, dropping, and altering objects.and altering objects.

• Object privileges allow a system user to perform a Object privileges allow a system user to perform a specific operation on a specific object such as a view, specific operation on a specific object such as a view, table, or index. table, or index.

Granting and Revoking PrivilegesGranting and Revoking Privileges


• System privileges are also termed system-wide System privileges are also termed system-wide privileges, and include privileges that focus on privileges, and include privileges that focus on managing objects that you own, and privileges that focus managing objects that you own, and privileges that focus on managing objects that any system user may own.on managing objects that any system user may own.

• Here are example system privileges:Here are example system privileges:– CREATE SESSION; ALTER SESSIONCREATE SESSION; ALTER SESSION

– CREATE TABLECREATE TABLE

– CREATE ANY TABLE; ALTER ANY TABLECREATE ANY TABLE; ALTER ANY TABLE

– CREATE ANY INDEXCREATE ANY INDEX

– UNLIMITED TABLESPACEUNLIMITED TABLESPACE

System PrivilegesSystem Privileges


• Basically, if you can create an object, you can also drop the Basically, if you can create an object, you can also drop the object.object.

• The CREATE TABLE privilege also includes the privilege to The CREATE TABLE privilege also includes the privilege to create indexes for a table and to subsequently drop those indexes create indexes for a table and to subsequently drop those indexes as necessary.as necessary.

• Some example GRANT commands are shown here.Some example GRANT commands are shown here.• Note that the privileges being granted are separated by a Note that the privileges being granted are separated by a

comma as are the user account names:comma as are the user account names:

– GRANT create session TO bock, bordoloi, user001;GRANT create session TO bock, bordoloi, user001;

– GRANT create table, unlimited tablespace TO bock;GRANT create table, unlimited tablespace TO bock;– GRANT create table, create any table TO bock, bordoloi;GRANT create table, create any table TO bock, bordoloi;



• Once a DBA has created a user account, a system Once a DBA has created a user account, a system privilege may be granted to the user account with the privilege may be granted to the user account with the WITH ADMIN OPTION.WITH ADMIN OPTION.

• In fact, this option may be used in any GRANT In fact, this option may be used in any GRANT command that grants a system privilege.command that grants a system privilege.

• The WITH ADMIN OPTION enables the grantee (the The WITH ADMIN OPTION enables the grantee (the account receiving the privilege) to, in turn, grant the account receiving the privilege) to, in turn, grant the privilege to other user accounts.privilege to other user accounts.

• Following SQL Example grants CREATE SESSION Following SQL Example grants CREATE SESSION and CREATE TABLE to and CREATE TABLE to bock,bock, and also gives and also gives bockbock the the authority to grant these two privileges to other system authority to grant these two privileges to other system user accounts.user accounts.

• GRANT create session, create table TO bock WITH ADMIN OPTION;GRANT create session, create table TO bock WITH ADMIN OPTION;



• Object privileges work on a specific object, so the form of Object privileges work on a specific object, so the form of the GRANT command is a bit different. The general form the GRANT command is a bit different. The general form is:is:

GRANT privilege1, privilege2, . . . GRANT privilege1, privilege2, . . . ON object_name TO user1, user2, . . . | public;ON object_name TO user1, user2, . . . | public;

• For example, if For example, if bockbock has created a table named has created a table named employeeemployee and and wants to give the user named wants to give the user named bordoloibordoloi the privilege to select the privilege to select rows from the table, the GRANT command in the following rows from the table, the GRANT command in the following example enables example enables bordoloibordoloi to select from the table. to select from the table.

• GRANT select ON bock.employee TO bordoloi;GRANT select ON bock.employee TO bordoloi;• Suppose that Suppose that bockbock also wants also wants bordoloibordoloi to be able to insert to be able to insert

rows into the rows into the employeeemployee table so as to table so as to relieverelieve bock of some of bock of some of the workload associated with loading new data. the workload associated with loading new data.

• The revised GRANT command in the following SQL The revised GRANT command in the following SQL Example includes the INSERT privilege.Example includes the INSERT privilege.

• GRANT select, insert ON bock.employee TO bordoloi;GRANT select, insert ON bock.employee TO bordoloi;

Object PrivilegesObject Privileges


• System privileges may be revoked with the REVOKE command.System privileges may be revoked with the REVOKE command.• You can only revoke a privilege that was specifically granted You can only revoke a privilege that was specifically granted

previously with a GRANT command.previously with a GRANT command.• There are no cascading effects of revoking privileges.There are no cascading effects of revoking privileges.• This means that if DBA1 grants system privileges WITH ADMIN This means that if DBA1 grants system privileges WITH ADMIN

OPTION to DBA2, and then DBA1 subsequently changes jobs OPTION to DBA2, and then DBA1 subsequently changes jobs and is no longer authorized DBA privileges, all privileges granted and is no longer authorized DBA privileges, all privileges granted to DBA1 can be revoked without worrying about privileges that to DBA1 can be revoked without worrying about privileges that DBA1 might have granted to DBA2 or to any system user DBA1 might have granted to DBA2 or to any system user account, for that matter.account, for that matter.

• Following SQL Example shows the revocation of the SELECT Following SQL Example shows the revocation of the SELECT ANY TABLE privilege from the user account ANY TABLE privilege from the user account bordoloibordoloi..

• REVOKE select any table FROM bordoloi;REVOKE select any table FROM bordoloi;

Revoking PrivilegesRevoking Privileges


• Object privileges are revoked in a similar fashion, except Object privileges are revoked in a similar fashion, except that the object for which privileges are revoked must be that the object for which privileges are revoked must be named in the REVOKE command.named in the REVOKE command.

• The keyword ALL can be used to revoke all privileges The keyword ALL can be used to revoke all privileges for an object from a user account.for an object from a user account.

– REVOKE select ON bock.employee FROM bordoloi;REVOKE select ON bock.employee FROM bordoloi;– REVOKE ALL ON bock.employee FROM bordoloi;REVOKE ALL ON bock.employee FROM bordoloi;

Revoking PrivilegesRevoking Privileges


• The concept of a The concept of a rolerole is a simple one, and its purpose is is a simple one, and its purpose is to make it easier for a DBA to manage privileges.to make it easier for a DBA to manage privileges.

• A role is like a container of a group of privileges for a A role is like a container of a group of privileges for a specific type of system user, such as an inventory specific type of system user, such as an inventory manager. manager.

• Each time we hire an inventory manager, we would Each time we hire an inventory manager, we would assign the manager a new system user account and assign the manager a new system user account and authorize that account all of the privileges contained in authorize that account all of the privileges contained in the role called the role called inventory_mgrinventory_mgr. .

• Further, we can simplify the management of privileges Further, we can simplify the management of privileges because we can allocate a role to another role! because we can allocate a role to another role!

ROLESROLES


ROLESROLES

• The following figure depicts privileges being The following figure depicts privileges being allocated to roles, and the roles being allocated to allocated to roles, and the roles being allocated to system users.system users.


• From studying the figure, it should be obvious that if From studying the figure, it should be obvious that if you add a new system user who works as an Account you add a new system user who works as an Account Manager, then you can allocate almost all of the Manager, then you can allocate almost all of the privileges this user will need by simply allocating the privileges this user will need by simply allocating the role named role named account_mgraccount_mgr to the system user. to the system user.

• Further, if it is determined that all account managers Further, if it is determined that all account managers need an additional privilege, such as DELETE ON need an additional privilege, such as DELETE ON ORDERS, that privilege can be granted to the ORDERS, that privilege can be granted to the account_mgraccount_mgr role, and all of the system users who are role, and all of the system users who are granted the role named granted the role named account_mgraccount_mgr will inherit the new will inherit the new privilege! This considerably simplifies user account privilege! This considerably simplifies user account management.management.

ROLESROLES


• A role name must be unique within the database.A role name must be unique within the database.• A role can be allocated both system and object privileges.A role can be allocated both system and object privileges.• Roles are not owned by anyone so they do not appear in any user Roles are not owned by anyone so they do not appear in any user

account schema.account schema.• You allocate privileges to roles the same way that you allocate You allocate privileges to roles the same way that you allocate

them to a system user account; however, you must first create the them to a system user account; however, you must first create the role by using the CREATE ROLE command.role by using the CREATE ROLE command.

• The following example commands create the role named The following example commands create the role named inventory_mgrinventory_mgr and grant several privileges to the role. and grant several privileges to the role.

• Next the role is allocated to the system user account for Next the role is allocated to the system user account for bordoloibordoloi..• The system user The system user bockbock is granted the is granted the inventory_mgrinventory_mgr role with the role with the

privilege to grant the role to other system users through the WITH privilege to grant the role to other system users through the WITH ADMIN OPTION.ADMIN OPTION.

ROLESROLES


• CREATE ROLE inventory_mgr;CREATE ROLE inventory_mgr;

• GRANT select ON bock.employee TO inventory_mgr;GRANT select ON bock.employee TO inventory_mgr;

• GRANT select, unlimited tablespace TO inventory_mgr;GRANT select, unlimited tablespace TO inventory_mgr;

• GRANT inventory_mgr TO bordoloi;GRANT inventory_mgr TO bordoloi;

• GRANT inventory_mgr TO bock WITH ADMIN OPTION;GRANT inventory_mgr TO bock WITH ADMIN OPTION;

• There are several predefined roles that are created as part There are several predefined roles that are created as part of the task of creating a new database.of the task of creating a new database.

• The CONNECT role is provided for backward The CONNECT role is provided for backward compatibility with earlier versions of Oracle.compatibility with earlier versions of Oracle.

• Anyone granted the DBA role will be granted all system Anyone granted the DBA role will be granted all system privileges needed to function as a DBA and these privileges needed to function as a DBA and these privileges will include the WITH ADMIN OPTION.privileges will include the WITH ADMIN OPTION.

ROLESROLES


• When a role is dropped, Oracle revokes the role and all When a role is dropped, Oracle revokes the role and all privileges granted through the role from all system users and privileges granted through the role from all system users and from other roles.from other roles.

• If you wish to alter a role, the best approach is to create a If you wish to alter a role, the best approach is to create a new role with the desired privileges, grant that role to the new role with the desired privileges, grant that role to the system users/roles that possess the role to be dropped; then, system users/roles that possess the role to be dropped; then, you can drop the first role. you can drop the first role.

• If you were granted a role with the ADMIN OPTION, then If you were granted a role with the ADMIN OPTION, then you can drop the role.you can drop the role.

• You may also drop a role if you have the DROP ANY ROLE You may also drop a role if you have the DROP ANY ROLE system privilege. system privilege.

• The DROP ROLE command is very simple.The DROP ROLE command is very simple.• Following SQL Example shows the command used to drop Following SQL Example shows the command used to drop

the the account_mgraccount_mgr role. role. » DROP ROLE account_mgr;DROP ROLE account_mgr;

ROLESROLES


• There are a number of data dictionary views that will There are a number of data dictionary views that will interest you and further your understanding of database interest you and further your understanding of database administration.administration.

• There is also a special set of views named There is also a special set of views named dynamic dynamic performance viewsperformance views that are used to accumulate that are used to accumulate information and statistics about the performance of the information and statistics about the performance of the database.database.

• Each dynamic performance view begins with a prefix of Each dynamic performance view begins with a prefix of v$v$..

DATA DICTIONARY TABLES AND VIEWSDATA DICTIONARY TABLES AND VIEWS


• v$fixed_table – lists all x$ tables that underline and v$fixed_table – lists all x$ tables that underline and store the dynamic performance information that can be store the dynamic performance information that can be displayed in v$ views. displayed in v$ views.

• v$session – lists information about the current sessions. v$session – lists information about the current sessions. You can use this information to "kill" a session if You can use this information to "kill" a session if necessary where a session may be hung up or where the necessary where a session may be hung up or where the system user may no longer be authorized database system user may no longer be authorized database access.access.

• v$sysstat – this view gives information about system v$sysstat – this view gives information about system performance that is used by a DBA to perform database performance that is used by a DBA to perform database tuning.tuning.

• v$sga – lists summary information about the system v$sga – lists summary information about the system global area.global area.

Dynamic Performance ViewsDynamic Performance Views


• dba_tab_privs – lists all object privileges granted to a dba_tab_privs – lists all object privileges granted to a user. user.

• dba_col_privs – lists all privileges granted on specific dba_col_privs – lists all privileges granted on specific columns of a table. columns of a table.

• session_privs – lists the privileges held by a user for the session_privs – lists the privileges held by a user for the current logon session.current logon session.

• table_privileges – this lists information on object grants table_privileges – this lists information on object grants for which you are the grantor, grantee, or owner, or for which you are the grantor, grantee, or owner, or where PUBLIC is the grantee. where PUBLIC is the grantee.

Object Privilege ViewsObject Privilege Views


• dba_roles – lists all roles in the database. dba_roles – lists all roles in the database. • dba_role_privs – lists roles granted to system users and dba_role_privs – lists roles granted to system users and

to other roles. to other roles. • dba_sys_privs – lists system privileges granted to users dba_sys_privs – lists system privileges granted to users

and roles. and roles. • role_role_privs – lists roles granted to roles. role_role_privs – lists roles granted to roles. • role_sys_privs – lists all system privileges granted to role_sys_privs – lists all system privileges granted to

roles. roles. • role_tab_privs – lists table privileges granted to roles. role_tab_privs – lists table privileges granted to roles.

Role and Privilege ViewsRole and Privilege Views


• all_all_tables – lists information about all object tables and all_all_tables – lists information about all object tables and relational tables that you can access as a system user.relational tables that you can access as a system user.

• all_users – simply lists all users that are visible to you as a all_users – simply lists all users that are visible to you as a system user, but this view does not describe the users.system user, but this view does not describe the users.

• sys.dba_tablespaces – lists information about tablespaces that sys.dba_tablespaces – lists information about tablespaces that exist in the system.exist in the system.

• sys.dba_data_files – lists information about data files sys.dba_data_files – lists information about data files including which tablespaces are allocated to which data files.including which tablespaces are allocated to which data files.

• user_constraints – lists constraint definitions for tables in your user_constraints – lists constraint definitions for tables in your schema.schema.

• user_indexes – lists information about your indexes for tables.user_indexes – lists information about your indexes for tables.• user_sequences – lists information about sequences you have user_sequences – lists information about sequences you have

created.created.• user_tables – lists information about your tables.user_tables – lists information about your tables.

Other Data Dictionary ViewsOther Data Dictionary Views

bordoloi and bock copyright 2004 prentice hall, inc

Documents

oracle database administration

oracle relational database

database files

database objects

database backup

database performance

database recovery

database administrationis