# data[].attributes.body
Espacio lógico donde se almacenan los objetos/archivos de datos (datafiles) de una base de datos.

# data[].attributes.body
Permite tener tablespaces constituidos por un único archivo grande en lugar de múltiples archivos pequeños.

CREATE BIGFILE TABLESPACE ejemplo_01
  DATAFILE 'ejemplo_f1.dbf'
  SIZE 20M AUTOEXTEND ON;

# data[].attributes.body
Contiene los diccionarios de datos de la base de datos. Se crea automáticamente.

# data[].attributes.body
Refuerza al tablespace SYSTEM almacenando metadatos de la base de datos

# data[].attributes.body
Se utiliza para gestionar acciones de 'deshacer'.

CREATE UNDO TABLESPACE ejemplo_02
   DATAFILE 'ejemplo_1a.dbf'
   SIZE 10M AUTOEXTEND ON
   RETENTION GUARANTEE;

# data[].attributes.body
Se utiliza como almacenamiento temporal predeterminado.

CREATE TEMPORARY TABLESPACE temp_demo
   TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON;

# data[].attributes.body
Bigfile | Smallfile

Datafile | tempfile

Minimum extent

Blocksize

Force logging

Online | Offline

# data[].attributes.attachment_link.readable_text
Purpose

Use the CREATE TABLESPACE statement to create a tablespace, which is an allocation of space in the database that can contain schema objects.

A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.

An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.

A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in temp files.

When you create a tablespace, it is initially a read/write tablespace. You can subsequently use the ALTER TABLESPACE statement to take the tablespace offline or online, add data files or temp files to it, or make it a read-only tablespace.

You can also drop a tablespace from the database with the DROP TABLESPACE statement.

Prerequisites

You must have the CREATE TABLESPACE system privilege. To create the SYSAUX tablespace, you must have the SYSDBA system privilege.

Before you can create a tablespace, you must create a database to contain it, and the database must be open.

To use objects in a tablespace other than the SYSTEM tablespace:

If you are running the database in automatic undo management mode, then at least one UNDO tablespace must be online.

If you are running the database in manual undo management mode, then at least one rollback segment other than the SYSTEM rollback segment must be online.

BIGFILE | SMALLFILE

Use this clause to determine whether the tablespace is a bigfile or smallfile tablespace. This clause overrides any default tablespace type setting for the database.

A bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion (2 ³²) blocks. The minimum size of the single data file or temp file is 12 megabytes (MB) for a tablespace with 32K blocks and 7MB for a tablespace with 8K blocks. The maximum size of the single data file or temp file is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.

A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 data files or temp files, each of which can contain up to approximately 4 million (2 ²²) blocks.

If you omit this clause, then Oracle Database uses the current default tablespace type of permanent or temporary tablespace that is set for the database. If you specify BIGFILE for a permanent tablespace, then the database by default creates a locally managed tablespace with automatic segment-space management.

Restriction on Bigfile Tablespaces

You can specify only one data file in the DATAFILE clause or one temp file in the TEMPFILE clause.

permanent_tablespace_clause

Use the following clauses to create a permanent tablespace. (Some of these clauses are also used to create a temporary or undo tablespace.)

tablespace

Specify the name of the tablespace to be created. The name must satisfy the requirements listed in " Database Object Naming Rules".

Note on the SYSAUX Tablespace

SYSAUX is a required auxiliary system tablespace. You must use the CREATE TABLESPACE statement to create the SYSAUX tablespace if you are upgrading from a release earlier than Oracle Database 11g. You must have the SYSDBA system privilege to specify this clause, and you must have opened the database in UPGRADE mode.

You must specify " EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO for the SYSAUX tablespace. The DATAFILE clause is optional only if you have enabled Oracle Managed Files. See DATAFILE | TEMPFILE Clause" for the behavior of the DATAFILE clause.

Take care to allocate sufficient space for the SYSAUX tablespace. For guidelines on creating this tablespace, refer to Oracle Database Upgrade Guide .

Restrictions on the SYSAUX Tablespace

You cannot specify OFFLINE or TEMPORARY for the SYSAUX tablespace.

DATAFILE | TEMPFILE Clause

Specify the data files to make up the permanent tablespace or the temp files to make up the temporary tablespace. Use the datafile_tempfile_spec form of file_specification to create regular data files and temp files in an operating system file system or to create Oracle Automatic Storage Management (Oracle ASM) disk group files.

You must specify the DATAFILE or TEMPFILE clause unless you have enabled Oracle Managed Files by setting a value for the DB_CREATE_FILE_DEST initialization parameter. For Oracle ASM disk group files, the parameter must be set to a multiple file creation form of Oracle ASM filenames. If this parameter is set, then the database creates a system-named 100 MB file in the default file destination specified in the parameter. The file has AUTOEXTEND enabled and an unlimited maximum size.

Note:

Media recovery does not recognize temp files.

Notes on Specifying Data Files and Temp Files

You can create a tablespace within an Oracle ASM disk group by providing only the disk group name in the datafile_tempfile_spec. In this case, Oracle ASM creates a data file in the specified disk group with a system-generated filename. The data file is auto-extensible with an unlimited maximum size and a default size of 100 MB. You can use the autoextend_clause to override the default size.

If you use one of the reference forms of the ASM_filename, which refers to an existing file, then you must also specify REUSE.

Note:

On some operating systems, Oracle does not allocate space for a temp file until the temp file blocks are actually accessed. This delay in space allocation results in faster creation and resizing of temp files, but it requires that sufficient disk space is available when the temp files are later used. To avoid potential problems, before you create or resize a temp file, ensure that the available disk space exceeds the size of the new temp file or the increased size of a resized temp file. The excess space should allow for anticipated increases in disk space use by unrelated operations as well. Then proceed with the creation or resizing operation.

permanent_tablespace_attrs

Use the permanent_tablespace_attrs clauses to set the attributes of the tablespace.

MINIMUM EXTENT Clause

This clause is valid only for a dictionary-managed tablespace. Specify the minimum size of an extent in the tablespace. This clause lets you control free space fragmentation in the tablespace by ensuring that the size of every used or free extent in a tablespace is at least as large as, and is a multiple of, the value specified in the size_clause.

BLOCKSIZE Clause

Use the BLOCKSIZE clause to specify a nonstandard block size for the tablespace. In order to specify this clause, the DB_CACHE_SIZE and at least one DB_ K_CACHE_SIZE parameter must be set, and the integer you specify in this clause must correspond with the setting of one DB_ K_CACHE_SIZE parameter setting.

Restriction on BLOCKSIZE

You cannot specify nonstandard block sizes for a temporary tablespace or if you intend to assign this tablespace as the temporary tablespace for any users.

Note:

Oracle recommend that you do not store tablespaces with a 2K block size on 4K sector size disks, because performance degradation can result.

logging_clause

Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. This clause is not valid for a temporary or undo tablespace.

If you omit this clause, then the default is LOGGING. The exception is creating a tablespace in a PDB. In this case, if you omit this clause, then the tablespace uses the logging attribute of the PDB. Refer to the logging_clause of CREATE PLUGGABLE DATABASE for more information.

The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.

FORCE LOGGING

Use this clause to put the tablespace into FORCE LOGGING mode. Oracle Database will log all changes to all objects in the tablespace except changes to temporary segments, overriding any NOLOGGING setting for individual objects. The database must be open and in READ WRITE mode.

This setting does not exclude the NOLOGGING attribute. You can specify both FORCE LOGGING and NOLOGGING. In this case, NOLOGGING is the default logging mode for objects subsequently created in the tablespace, but the database ignores this default as long as the tablespace or the database is in FORCE LOGGING mode. If you subsequently take the tablespace out of FORCE LOGGING mode, then the NOLOGGING default is once again enforced.

Restriction on Forced Logging

You cannot specify FORCE LOGGING for an undo or temporary tablespace.

tablespace_encryption_clause

Use this clause to specify whether to create an encrypted or unencrypted tablespace. If you create an encrypted tablespace, then Transparent Data Encryption (TDE) is applied to all data files of the tablespace.

Specify ENCRYPT to create an encrypted tablespace. Specify DECRYPT to create an unencrypted tablespace.

If you omit this clause, then the value of the ENCRYPT_NEW_TABLESPACES initialization parameter determines whether the tablespace is encrypted upon creation. Refer to Oracle Database Reference for more information on the ENCRYPT_NEW_TABLESPACES initialization parameter.

Before issuing this clause, you must already have loaded the TDE master key into database memory or established a connection to the HSM. For more information, see the " open_keystore clause of ADMINISTER KEY MANAGEMENT or SET ENCRYPTION WALLET Clause" of ALTER SYSTEM.

tablespace_encryption_spec

Specify USING ' encrypt_algorithm' to indicate the name of the encryption algorithm to be used. Valid algorithms are AES256, AES192, AES128 and 3DES168. If the COMPATIBLE initialization parameter is set to 12. 2 or higher, then the following algorithms are also valid: ARIA128, ARIA192, ARIA256, GOST256, and SEED128.. If you omit this clause, then the database uses AES128.

default_tablespace_params

The DEFAULT clause lets you specify default parameters for the tablespace.

default_table_compression

Use this clause to specify default compression of data for all tables created in the tablespace. This clause is not valid for a temporary tablespace. The subclauses of this clause have the same semantics as they have for the table_compression clause of the CREATE TABLE statement, with one exception: The COMPRESS FOR OLTP clause here is equivalent to the ROW STORE COMPRESS ADVANCED clause of CREATE TABLE. Refer to the table_compression clauses of CREATE TABLE for the full semantics of these subclauses.

default_index_compression

Use this clause to specify default compression of data for all indexes created in the tablespace. This clause is not valid for a temporary tablespace. The subclauses of this clause have the same semantics as they have for the advanced_index_compression clause of the CREATE INDEX statement. Refer to the advanced_index_compression clause of CREATE INDEX for the full semantics of these subclauses.

inmemory_clause

Use the inmemory_clause to specify the default In-Memory Column Store (IM column store) settings for all tables and materialized views created in the tablespace. This clause is not valid for a temporary tablespace.

Specify INMEMORY to enable all tables and materialized views for the IM column store.

You can optionally use the inmemory_attributes clause to specify how the table or materialized view data is stored in the IM column store. The inmemory_attributes clause has the same semantics in CREATE TABLE and CREATE TABLESPACE. Refer to the inmemory_attributes clause of CREATE TABLE for the full semantics of this clause.

Specify NO INMEMORY to disable all tables and materialized views for the IM column store. This is the default.

Use the to specify default Automatic Data Optimization settings for all tables created in the tablespace. This clause is not valid for a temporary tablespace. Refer to the ilm_clause of CREATE TABLE for the full semantics of this clause.

storage_clause

Use the storage_clause to specify storage parameters for all objects created in the tablespace. This clause is not valid for a temporary tablespace or a locally managed tablespace. For a dictionary-managed tablespace, you can specify the following storage parameters with this clause: ENCRYPT, INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, MAXSIZE, and PCTINCREASE. Refer to storage_clause for more information.

Note:

The ENCRYPT clause of the storage_clause is supported for backward compatibility. However, beginning with Oracle Database 12c Release 2 (12.2), you can instead specify ENCRYPT in the tablespace_encryption_clause. Refer to tablespace_encryption_clause for more information.

ONLINE | OFFLINE Clauses

Use these clauses to determine whether the tablespace is online or offline. This clause is not valid for a temporary tablespace.

Specify ONLINE to make the tablespace available immediately after creation to users who have been granted access to the tablespace. This is the default.

Specify OFFLINE to make the tablespace unavailable immediately after creation.

The data dictionary view DBA_TABLESPACES indicates whether each tablespace is online or offline.

extent_management_clause

The extent_management_clause lets you specify how the extents of the tablespace will be managed.

Note:

After you have specified extent management with this clause, you can change extent management only by migrating the tablespace.

AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. You cannot specify AUTOALLOCATE for a temporary tablespace.

UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes.The default SIZE is 1 megabyte. All extents of temporary tablespaces are of uniform size, so this keyword is optional for a temporary tablespace. However, you must specify UNIFORM in order to specify SIZE. You cannot specify UNIFORM for an undo tablespace.

If you do not specify AUTOALLOCATE or UNIFORM, then the default is UNIFORM for temporary tablespaces and AUTOALLOCATE for all other types of tablespaces.

If you do not specify the extent_management_clause, then Oracle Database interprets the MINIMUM EXTENT clause and the DEFAULT storage_clause to determine extent management.

Note:

The DICTIONARY keyword is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you create locally managed tablespaces. Locally managed tablespaces are much more efficiently managed than dictionary-managed tablespaces. The creation of new dictionary-managed tablespaces is scheduled for desupport.

Restrictions on Extent Management

Extent management is subject to the following restrictions:

A permanent locally managed tablespace can contain only permanent objects. If you need a locally managed tablespace to store temporary objects, for example, if you will assign it as a user's temporary tablespace, then use the temporary_tablespace_clause.

If you specify this clause, then you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or the temporary_tablespace_clause.

segment_management_clause

The segment_management_clause is relevant only for permanent, locally managed tablespaces. It lets you specify whether Oracle Database should track the used and free space in the segments in the tablespace using free lists or bitmaps. This clause is not valid for a temporary tablespace.

Specify AUTO if you want the database to manage the free space of segments in the tablespace using a bitmap. If you specify AUTO, then the database ignores any specification for PCTUSED, FREELIST, and FREELIST GROUPS in subsequent storage specifications for objects in this tablespace. This setting is called automatic segment-space management and is the default.

Specify MANUAL if you want the database to manage the free space of segments in the tablespace using free lists. Oracle strongly recommends that you do not use this setting and that you create tablespaces with automatic segment-space management.

To determine the segment management of an existing tablespace, query the SEGMENT_SPACE_MANAGEMENT column of the DBA_TABLESPACES or USER_TABLESPACES data dictionary view.

Note:

If you specify AUTO segment management, then:

If you set extent management to LOCAL UNIFORM, then you must ensure that each extent contains at least 5 database blocks.

If you set extent management to LOCAL AUTOALLOCATE, and if the database block size is 16K or greater, then Oracle manages segment space by creating extents with a minimum size of 5 blocks rounded up to 64K.

Restrictions on Automatic Segment-Space Management

This clause is subject to the following restrictions:

flashback_mode_clause

Use this clause in conjunction with the ALTER DATABASE FLASHBACK clause to specify whether the tablespace can participate in FLASHBACK DATABASE operations. This clause is useful if you have the database in FLASHBACK mode but you do not want Oracle Database to maintain Flashback log data for this tablespace.

This clause is not valid for temporary or undo tablespaces.

FLASHBACK ON

Specify FLASHBACK ON to put the tablespace in FLASHBACK mode. Oracle Database will save Flashback log data for this tablespace and the tablespace can participate in a FLASHBACK DATABASE operation. If you omit the flashback_mode_clause, then FLASHBACK ON is the default.

FLASHBACK OFF

Specify FLASHBACK OFF to take the tablespace out of FLASHBACK mode. Oracle Database will not save any Flashback log data for this tablespace. You must take the data files in this tablespace offline or drop them prior to any subsequent FLASHBACK DATABASE operation. Alternatively, you can take the entire tablespace offline. In either case, the database does not drop existing Flashback logs.

Note:

The FLASHBACK mode of a tablespace is independent of the FLASHBACK mode of an individual table.

lost_write_protection

Specify the lost_write_protection clause to create a storage area for lost write records. This storage area or shadow tablespace must be created, before you can enable lost write protection on datafiles and databases.

You may create as many shadow tablespaces as you need, and name them as you would any other tablespace.

Create a Shadow Tablespace: Example

CREATE BIGFILE TABLESPACE <shadow_tablespace_name>
  DATAFILE <datafile_name>
  SIZE 100000000 BLOCKSIZE 8K
  LOST WRITE PROTECTION

This command creates the shadow tablespace. To enable lost write protection on datafiles and databases, you must specify the lost_write_protection clause with the ALTER TABLESPACE, ALTER DATABASE, and ALTER PLUGGABLE DATABASE statements.

undo_tablespace_clause

Specify UNDO to create an undo tablespace. When you run the database in automatic undo management mode, Oracle Database manages undo space using the undo tablespace instead of rollback segments. This clause is useful if you are now running in automatic undo management mode but your database was not created in automatic undo management mode.

Oracle Database always assigns an undo tablespace when you start up the database in automatic undo management mode. If no undo tablespace has been assigned to this instance, then the database uses the SYSTEM rollback segment. You can avoid this by creating an undo tablespace, which the database will implicitly assign to the instance if no other undo tablespace is currently assigned.

The " DATAFILE clause is described in DATAFILE | TEMPFILE Clause".

extent_management_clause

It is unnecessary to specify the extent_management_clause when creating an undo tablespace, because undo tablespaces must be locally managed tablespaces that use AUTOALLOCATE extent management. If you do specify this clause, then you must specify EXTENT MANAGEMENT LOCAL or EXTENT MANAGEMENT LOCAL AUTOALLOCATE, both of which are the same as omitting this clause. Refer to extent_management_clause for the full semantics of this clause.

tablespace_retention_clause

This clause is valid only for undo tablespaces.

RETENTION GUARANTEE specifies that Oracle Database should preserve unexpired undo data in all undo segments of tablespace even if doing so forces the failure of ongoing operations that need undo space in those segments. This setting is useful if you need to issue an Oracle Flashback Query or an Oracle Flashback Transaction Query to diagnose and correct a problem with the data.

RETENTION NOGUARANTEE returns the undo behavior to normal. Space occupied by unexpired undo data in undo segments can be consumed if necessary by ongoing transactions. This is the default.

tablespace_encryption_clause

This clause has the same semantics for undo tablespaces as for permanent tablespaces. Refer to tablespace_encryption_clause in the documentation on permanent tablespaces for full information.

Restrictions on Undo Tablespaces

Undo tablespaces are subject to the following restrictions:

You cannot create database objects in this tablespace. It is reserved for system-managed undo data.

The only clauses you can specify for an undo tablespace are the DATAFILE clause, the tablespace_retention_clause, the tablespace_encryption_clause, and the extent_management_clause to specify local AUTOALLOCATE extent management. You cannot specify local UNIFORM extent management or dictionary extent management using the extent_management_clause. All undo tablespaces are created permanent, read/write, and in logging mode. Values for MINIMUM EXTENT and DEFAULT STORAGE are system generated.

temporary_tablespace_clause

Use this clause to create a temporary tablespace, which is an allocation of space in the database that can contain transient data that persists only for the duration of a session. This transient data cannot be recovered after process or instance failure.

The transient data can be user-generated schema objects such as temporary tables or system-generated data such as temp space used by hash joins and sort operations. When a temporary tablespace, or a tablespace group of which this tablespace is a member, is assigned to a particular user, then Oracle Database uses the tablespace for sorting operations in transactions initiated by that user.

You can create two types of temporary tablespaces:

You can create a shared temporary tablespace by specifying the TEMPORARY TABLESPACE clause. A shared temporary tablespace stores temp files on shared disk, so that the temporary space is accessible to all database instances. Shared temporary tablespaces were available in prior releases of Oracle Database and were called "temporary tablespaces." Elsewhere in this guide, the term "temporary tablespace" refers to a shared temporary tablespace unless specified otherwise.

Starting with Oracle Database 12c Release 2 (12.2), you can create a local temporary tablespace by specifying the LOCAL TEMPORARY TABLESPACE clause. Local temporary tablespaces are useful in an Oracle Clusterware environment. They store a separate, nonshared temp files for each database instance, which can improve I/O performance. A local temporary tablespace must be a BIGFILE tablespace.

Specify FOR ALL to instruct the database to create separate, nonshared temp files for all HUB and LEAF nodes.

Specify FOR LEAF to instruct the database to create separate nonshared temp files for only LEAF nodes.

The " TEMPFILE clause is described in DATAFILE | TEMPFILE Clause".

tablespace_group_clause

This clause is relevant only for temporary tablespaces. Use this clause to determine whether tablespace is a member of a tablespace group. A tablespace group lets you assign multiple temporary tablespaces to a single user and increases the addressability of temporary tablespaces.

Specify a group name to indicate that tablespace is a member of this tablespace group. The group name cannot be the same as tablespace or any other existing tablespace. If the tablespace group already exists, then Oracle Database adds the new tablespace to that group. If the tablespace group does not exist, then the database creates the group and adds the new tablespace to that group.

Specify an empty string (' ') to indicate that tablespace is not a member of any tablespace group.

Restriction on Tablespace Groups

Tablespace groups support only shared temporary tablespaces. You cannot add a local temporary tablespace to a tablespace group.

extent_management_clause

The extent_management_clause is described in extent_management_clause.

tablespace_encryption_clause

This clause has the same semantics for temporary tablespaces as for permanent tablespaces. Refer to tablespace_encryption_clause in the documentation on permanent tablespaces for full information.

Restrictions on Temporary Tablespaces

The data stored in temporary tablespaces persists only for the duration of a session. Therefore, only a subset of the CREATE TABLESPACE clauses are relevant for temporary tablespaces. The only clauses you can specify for a temporary tablespace are the TEMPFILE clause, the tablespace_group_clause, the extent_management_clause, and the tablespace_encryption_clause.

Examples

These examples assume that your database is using 8K blocks.

Creating a Bigfile Tablespace: Example

The following example creates a bigfile tablespace bigtbs_01 with a data file bigtbs_f1.dbf of 20 MB:

CREATE BIGFILE TABLESPACE bigtbs_01
  DATAFILE 'bigtbs_f1.dbf'
  SIZE 20M AUTOEXTEND ON;

Creating an Undo Tablespace: Example

The following example creates a 10 MB undo tablespace undots1:

CREATE UNDO TABLESPACE undots1
   DATAFILE 'undotbs_1a.dbf'
   SIZE 10M AUTOEXTEND ON
   RETENTION GUARANTEE;

Creating a Temporary Tablespace: Example

This statement shows how the temporary tablespace that serves as the default temporary tablespace for database users in the sample database was created:

CREATE TEMPORARY TABLESPACE temp_demo
   TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON;

Assuming that the default database block size is 2K, and that each bit in the map represents one extent, then each bit maps 2,500 blocks.

The following example sets the default location for data file creation and then creates a tablespace with an Oracle-managed temp file in the default location. The temp file is 100 M and is autoextensible with unlimited maximum size. These are the default values for Oracle Managed Files:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '$ORACLE_HOME/rdbms/dbs';

CREATE TEMPORARY TABLESPACE tbs_05;

Adding a Temporary Tablespace to a Tablespace Group: Example

The following statement creates the tbs_temp_02 temporary tablespace as a member of the tbs_grp_01 tablespace group. If the tablespace group does not already exist, then Oracle Database creates it during execution of this statement:

CREATE TEMPORARY TABLESPACE tbs_temp_02
  TEMPFILE 'temp02.dbf' SIZE 5M AUTOEXTEND ON
  TABLESPACE GROUP tbs_grp_01;

Creating Basic Tablespaces: Examples

This statement creates a tablespace named tbs_01 with one data file:

CREATE TABLESPACE tbs_01
   DATAFILE 'tbs_f2.dbf' SIZE 40M
   ONLINE;

This statement creates tablespace tbs_03 with one data file and allocates every extent as a multiple of 500K:

CREATE TABLESPACE tbs_03
   DATAFILE 'tbs_f03.dbf' SIZE 20M
   LOGGING;

Enabling Autoextend for a Tablespace: Example

This statement creates a tablespace named tbs_02 with one data file. When more space is required, 500 kilobyte extents will be added up to a maximum size of 100 megabytes:

CREATE TABLESPACE tbs_02
   DATAFILE 'diskb:tbs_f5.dbf' SIZE 500K REUSE
   AUTOEXTEND ON NEXT 500K MAXSIZE 100M;

Creating a Locally Managed Tablespace: Example

The following statement assumes that the database block size is 2K.

CREATE TABLESPACE tbs_04 DATAFILE 'file_1.dbf' SIZE 10M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

This statement creates a locally managed tablespace in which every extent is 128K and each bit in the bit map describes 64 blocks.

The following statement creates a locally managed tablespace with uniform extents and shows an example of a table stored in that tablespace:

CREATE TABLESPACE lmt1 DATAFILE 'lmt_file2.dbf' SIZE 100m REUSE
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

CREATE TABLE lmt_table1 (col1 NUMBER, col2 VARCHAR2(20))
  TABLESPACE lmt1 STORAGE (INITIAL 2m);

The initial segment size of the table is 2M.

The following example creates a locally managed tablespace without uniform extents:

CREATE TABLESPACE lmt2 DATAFILE 'lmt_file3.dbf' SIZE 100m REUSE
  EXTENT MANAGEMENT LOCAL;

CREATE TABLE lmt_table2 (col1 NUMBER, col2 VARCHAR2(20))
  TABLESPACE lmt2 STORAGE (INITIAL 2m MAXSIZE 100m);

The initial segment size of the table is 2M. Oracle Database determines the size of each extent and the total number of extents allocated to satisfy the initial segment size. The segment's maximum size is limited to 100M.

Creating an Encrypted Tablespace: Example

In the following example, the first statement enables encryption for the database by opening the wallet. The second statement creates an encrypted tablespace.

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet_password"; CREATE TABLESPACE encrypt_ts DATAFILE '$ORACLE_HOME/dbs/encrypt_df.dbf' SIZE 1M ENCRYPTION USING 'AES256' ENCRYPT;

Specifying Segment Space Management for a Tablespace: Example

The following example creates a tablespace with automatic segment-space management:

CREATE TABLESPACE auto_seg_ts DATAFILE 'file_2.dbf' SIZE 1M
   EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO;

Creating Oracle Managed Files: Examples

The following example sets the default location for data file creation and creates a tablespace with a data file in the default location. The data file is 100M and is autoextensible with an unlimited maximum size:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '$ORACLE_HOME/rdbms/dbs';

CREATE TABLESPACE omf_ts1;

The following example creates a tablespace with an Oracle-managed data file of 100M that is not autoextensible:

CREATE TABLESPACE omf_ts2 DATAFILE AUTOEXTEND OFF;

# data[].attributes.attachment_link.description
Purpose Use the CREATE TABLESPACE statement to create a tablespace, which is an allocation of space in the database that can contain schema objects. A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.

# data[].attributes.body
Un datafile es un fichero físico en el que se almacenan los objetos que forman parte de un tablespace.

# data[].attributes.body
Un datafile pertenece solamente a un tablespace y a una instancia de base de datos.

Un tablespace puede estar formado por uno o varios datafiles.

# data[].attributes.body
Un usuario es una cuenta a través de la cual se puede iniciar sesión en una base de datos.

# data[].attributes.body
Un esquema es una colección de objetos de base de datos. Los objetos de esquema son estructuras lógicas creadas por los usuarios.

# data[].attributes.body
Un esquema es propiedad de un usuario de la base de datos y tiene el mismo nombre que ese usuario.

# data[].attributes.body
Identified

No authentication

Default collation

Default tablespace

Quota

Profile

Password expire

Account

Enable editions

Container

# data[].attributes.attachment_link.readable_text
Purpose

Use the DROP USER statement to remove a database user and optionally remove the user's objects.

In an Oracle Automatic Storage Management (Oracle ASM) cluster, a user authenticated AS SYSASM can use this clause to remove a user from the password file that is local to the Oracle ASM instance of the current node.

When you drop a user, Oracle Database also purges all of that user's schema objects from the recycle bin.

Note:

Do not attempt to drop the users SYS or SYSTEM. Doing so will corrupt your database.

Prerequisites

You must have the DROP USER system privilege. In an Oracle ASM cluster, you must be authenticated AS SYSASM.

Specify the user to be dropped. Oracle Database does not drop users whose schemas contain objects unless you specify CASCADE or unless you first explicitly drop the user's objects.

Restriction on Dropping Users

You cannot drop a user whose schema contains a table that uses a flashback data archive for historical tracking. You must first disable the table's use of the flashback data archive.

CASCADE

Specify CASCADE to drop all objects in the user's schema before dropping the user. You must specify this clause to drop a user whose schema contains any objects.

If the user's schema contains tables, then Oracle Database drops the tables and automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables.

If this clause results in tables being dropped, then the database also drops all domain indexes created on columns of those tables and invokes appropriate drop routines.

Oracle Database invalidates, but does not drop, the following objects in other schemas:

Views or synonyms for objects in the dropped user's schema

Stored procedures, functions, or packages that query objects in the dropped user's schema

Oracle Database does not drop materialized views in other schemas that are based on tables in the dropped user's schema. However, because the base tables no longer exist, the materialized views in the other schemas can no longer be refreshed.

Oracle Database drops all triggers in the user's schema.

Oracle Database does not drop roles created by the user.

Note:

Oracle Database also drops with FORCE all types owned by the user. See the FORCE keyword of DROP TYPE.

Examples

Dropping a Database User: Example

If user Sidney's schema contains no objects, then you can drop sidney by issuing the statement:

DROP USER sidney;

If Sidney's schema contains objects, then you must use the CASCADE clause to drop sidney and the objects:

DROP USER sidney CASCADE;

# data[].attributes.attachment_link.description
Purpose Use the DROP USER statement to remove a database user and optionally remove the user's objects. In an Oracle Automatic Storage Management (Oracle ASM) cluster, a user authenticated AS SYSASM can use this clause to remove a user from the password file that is local to the Oracle ASM instance of the current node.

# data[].attributes.attachment_link.readable_text
Purpose

Use the ALTER USER statement:

To change the authentication or database resource characteristics of a database user

To permit a proxy server to connect as a client without authentication

In an Oracle Automatic Storage Management (Oracle ASM) cluster, to change the password of a user in the password file that is local to the Oracle ASM instance of the current node

Prerequisites

In general, you must have the ALTER USER system privilege. However, the current user can change his or her own password without this privilege.

To change the password of a SYS user, the password file must exist.

You must be authenticated AS SYSASM to change the password of a user other than yourself in an Oracle ASM instance password file.

To specify the CONTAINER clause, you must be connected to a multitenant container database (CDB). If the current container is the root, then you can specify CONTAINER = ALL or CONTAINER = CURRENT. If the current container is a pluggable database (PDB), then you can specify only CONTAINER = CURRENT.

To set and modify CONTAINER_DATA attributes using the container_data_clause, you must be connected to a CDB and the current container must be the root.

Semantics

The keywords, parameters, and clauses described in this section are unique to ALTER USER or have different semantics than they have in CREATE USER. Keywords, parameters, and clauses that do not appear here have the same meaning as in the CREATE USER statement.

Note:

Oracle recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform.

See Also:

CREATE USER for information on the keywords and parameters and CREATE PROFILE for information on assigning limits on database resources to a user

IDENTIFIED Clause

Specify BY to specify a new password for the user. Passwords are case sensitive. Any subsequent CONNECT string used to connect this user to the database must specify the password using the same case (upper, lower, or mixed) that is used in this ALTER USER statement. Passwords can contain single-byte, or multibyte characters, or both from your database character set.

Note:

Oracle Database expects a different timestamp for each resetting of a particular password. If you reset one password multiple times within one second (for example, by cycling through a set of passwords using a script), then the database may return an error message that the password cannot be reused. For this reason, Oracle recommends that you avoid using scripts to reset passwords.

You can omit the REPLACE clause if you are setting your own password or you have the ALTER USER system privilege and you are changing another user's password. However, unless you have the ALTER USER system privilege, you must always specify the REPLACE clause if a password complexity verification function has been enabled, either by running the UTLPWDMG.SQL script or by specifying such a function in the PASSWORD_VERIFY_FUNCTION parameter of a profile that has been assigned to the user.

In an Oracle ASM cluster, you can use this clause to change the password of a user in the password file that is local to an Oracle ASM instance of the current node. You must be authenticated AS SYSASM to specify IDENTIFIED BY without the REPLACE old_password clause. If you are not authenticated AS SYSASM, then you can only change your own password by specifying REPLACE old_password.

Oracle Database does not check the old password, even if you provide it in the REPLACE clause, unless you are changing your own existing password.

Changing a Password to Begin the Gradual Database Password Rollover Period

Prerequisite

After you set the time for the gradual password rollover period, you can use the ALTER USER statement to change the user's password and propagate the new password to all clients before the PASSWORD_ROLLOVER_TIME ends.

Changing a Password During the Gradual Database Password Rollover Period

You can change the password before the rollover period expires using ALTER USER with or without the REPLACE clause.

For example, say user u1 has an original password p1, and p2 is the new password that started the rollover process. Now you want to switch to p3 instead of p2. You can use any one of the statements to change the password to p3:

ALTER USER u1 IDENTIFIED BY p3;

ALTER USER u1 IDENTIFIED BY p3 REPLACE p1;

ALTER USER u1 IDENTIFIED BY p3 REPLACE p2;

After you change the password to p3, the user can log in using either p1 or p3. Logging in with p2 returns error ORA-1017 Invalid Username/Password and is recorded as a failed login attempt.

The rollover start time is fixed the first time a user changes their password. The start time is not affected by further password changes during the password rollover period. This design limits the length of time the old password can be used .

Refer to CREATE USER for more information on this clause.

You can change a user's access verification method from IDENTIFIED GLOBALLY to either IDENTIFIED BY or IDENTIFIED EXTERNALLY. You can change a user's access verification method to IDENTIFIED GLOBALLY from one of the other methods only if all external roles granted explicitly to the user are revoked.

EXTERNALLY

Refer to CREATE USER for more information on this clause.

NO AUTHENTICATION Clause

Use this clause to change an existing user account with authentication to a schema account without authentication to prevent logins to the account.

DEFAULT COLLATION Clause

Use this clause to change the default collation for the schema owned by the user. The new default collation is assigned to tables, views, and materialized views that are subsequently created in the schema. It does not influence default collations for existing tables views, and materialized views. Refer to the DEFAULT COLLATION Clause clause of CREATE USER for the full semantics of this clause.

DEFAULT TABLESPACE Clause

Use this clause to assign or reassign a tablespace for the user's permanent segments. This clause overrides any default tablespace that has been specified for the database.

Restriction on Default Tablespaces

You cannot specify a locally managed temporary tablespace, including an undo tablespace, or a dictionary-managed temporary tablespace, as a user's default tablespace.

[LOCAL] TEMPORARY TABLESPACE Clause

Use this clause to assign or reassign a temporary tablespace or tablespace group for the user's temporary segments.

Specify tablespace to indicate the user's temporary tablespace. Specify TEMPORARY TABLESPACE to indicate a shared temporary tablespace. Specify LOCAL TEMPORARY TABLESPACE to indicate a local temporary tablespace. If you are connected to a CDB, then you can specify CDB$DEFAULT to use the CDB-wide default temporary tablespace.

Specify tablespace_group_name to indicate that the user can save temporary segments in any tablespace in the tablespace group specified by tablespace_group_name. Local temporary tablespaces cannot be part of a tablespace group.

Restriction on User Temporary Tablespace

Any individual tablespace you assign or reassign as the user's temporary tablespace must be a temporary tablespace and must have a standard block size.

DEFAULT ROLE Clause

Specify the roles enabled by default for the user at logon.This clause can contain only roles that have been granted directly to the user with a GRANT statement, or roles created by the user with the CREATE ROLE privilege. You cannot use the DEFAULT ROLE clause to specify:

Roles not granted to the user

Roles granted through other roles

Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory

Roles that are enabled by the SET ROLE statement, such as password-authenticated roles and secure application roles

Assigning Default Roles to Common Users in a CDB

You can modify the default role assigned to a common user both in the current container and across all containers in a CDB.

While assigning a default role to a common user across all containers, role must be a common role that was commonly granted to the common user.

While assigning a default role to a common user in the current container, role must be one of the following:

A local role that was granted to the common user in the current container

A common role that was granted to the common user, either commonly or locally in the current container

ENABLE EDITIONS

This clause is not reversible. Specify ENABLE EDITIONS to allow the user to create multiple versions of editionable objects in this schema using editions. Editionable objects in non-editions-enabled schemas cannot be editioned.

Use the FOR clause to specify one or more object types for which the user can create editionable objects. For a list of valid values for object_type, query the V$EDITIONABLE_TYPES dynamic performance view. If you omit the FOR clause, then the user can create editionable objects for all editionable object types.

If you omit the FOR clause, then the types that become editionable in the schema are VIEW, SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, TYPE, TYPE BODY, and LIBRARY.

To enable edition for other object types that are not enabled by default, you must explicitly specify the object type in the FOR clause.

Example: Enable Edition Not Enabled By Default

ALTER USER username ENABLE EDITIONS FOR SQL TRANSLATION PROFILE;

If the schema to be editions-enabled contains any objects that are not editionable and that depend on editionable type objects in the schema, then you must specify FORCE to enable editions for this schema. In this case, all the objects that are not editionable and that depend on the editionable type objects in the schema being editions-enabled become invalid.

[HTTP] DIGEST Clause

This clause lets you enable or disable HTTP Digest Access Authentication for the user.

Specify ENABLE to enable HTTP Digest Access Authentication. After you specify this clause, the user's password must be changed. This causes the database to generate an HTTP Digest verifier for the new password. Only then will HTTP Digest Access Authentication take effect. One way to ensure that the user's password is changed after you issue this clause is to specify the PASSWORD EXPIRE clause in the same statement with the HTTP DIGEST ENABLE clause, as follows:

ALTER USER user PASSWORD EXPIRE HTTP DIGEST ENABLE;

This causes the database to prompt the user for a new password on his or her next attempt to log in to the database. After that, HTTP Digest Access Authentication will take effect for the user.

Specify DISABLE to disable HTTP Digest Access Authentication for the user. You do not need to change the user's password in order for this clause to take effect. Specifying the DISABLE clause removes the HTTP Digest from dictionary tables.

ALTER USER user PASSWORD EXPIRE HTTP DIGEST DISABLE;

Refer to [HTTP] DIGEST Clause in the documentation on CREATE USER for more information on this clause.

CONTAINER Clause

If the current container is a PDB, then you can specify CONTAINER = CURRENT to change the attributes of a local user, or the container-specific attributes (such as the default tablespace) of a common user, in the current container. If the current container is the root, then you can specify CONTAINER = ALL to change the attributes of a common user across the entire CDB. If you omit this clause and the current container is a PDB, then CONTAINER = CURRENT is the default. If you omit this clause and the current container is the root, then CONTAINER = ALL is the default.

Restriction on Modifying Common Users in a CDB

Certain attributes of a common user must be modified for all the containers in a CDB and not for only some containers. Therefore, when you use any of the following clauses to modify a common user, ensure that you modify all of the containers by connecting to the root and specifying CONTAINER=ALL:

container_data_clause

The container_data_clause allows you the set and modify CONTAINER_DATA attributes for a common user. Use the FOR clause to indicate whether to set or modify the default CONTAINER_DATA attribute or an object-specific CONTAINER_DATA attribute. These attributes determine the set of containers (which can never exclude the root) whose data will be visible via CONTAINER_DATA objects to the specified common user when the current session is the root.

To specify the container_data_clause, the current session must be the root and you must specify CONTAINER = CURRENT.

SET CONTAINER_DATA

Use container_name to specify one or more containers that will be accessible to the user.

Use ALL to specify that all current and future containers in the CDB will be accessible to the user.

Use DEFAULT to specify the default behavior, which is as follows:

For a default CONTAINER_DATA attribute, the current container, that is, the root, and the CDB as a whole will be accessible to the user.

For an object-specific CONTAINER_DATA attribute, the database will use the user's default CONTAINER_DATA attribute.

Note:

CONTAINER_DATA attributes that are set to DEFAULT are not visible in the DBA_CONTAINER_DATA view.

ADD CONTAINER_DATA

Use this clause to add containers to the default CONTAINER_DATA attribute or an object-specific CONTAINER_DATA attribute for a common user. Use container_name to specify one or more containers to add.

You cannot use this clause if the default CONTAINER_DATA attribute is set to ALL. If you use this clause when the default CONTAINER_DATA attribute is set to DEFAULT, then CDB$ROOT will automatically be added to the set of containers, unless the set already contains CDB$ROOT.

REMOVE CONTAINER_DATA

Use this clause to remove containers from the default CONTAINER_DATA attribute or an object-specific CONTAINER_DATA attribute for a common user. Use container_name to specify one or more containers to remove.

You cannot use this clause if the default CONTAINER_DATA attribute or object-specific CONTAINER_DATA attribute is set to ALL or DEFAULT.

If you specify the FOR clause, then you can set and modify the object-specific CONTAINER_DATA attribute for container_data_object for a common user. container_data_object must be a CONTAINER_DATA table or view. If you omit , then Oracle Database assumes that container_data_object is in your own schema.

If you omit the FOR clause, then you can set and modify the default CONTAINER_DATA attribute for a common user.

proxy_clause

The proxy_clause lets you control the ability of an enterprise user (a user outside the database) or a database proxy (another database user) to connect as the database user being altered.

GRANT CONNECT THROUGH

REVOKE CONNECT THROUGH

ENTERPRISE USER

This clause lets you expose to proxy use by enterprise users. The administrator working in Oracle Internet Directory must then grant privileges for appropriate enterprise users to act on behalf of .

This clause lets you expose to proxy use by database user db_user_proxy (the proxy).

The proxy will have all privileges that were directly granted to user.

The proxy will have all roles associated with user, unless you specify the WITH clauses of db_user_proxy_clauses to limit the proxy to some or none of the roles of user. For each role associated with the proxy, if the role is enabled by default for at login, then that role will also be enabled by default for the proxy at login.

db_user_proxy_clauses

You can enable password-protected roles in a proxy session. Both secure application role and password-protected roles provide a secure method for enabling a role in a session. Oracle recommends using secure password roles instead of password protected roles in instances where the password has to be maintained and transmitted over insecure channels, or if more than one person needs to know the password. Password-protected roles in a proxy session are suitable for situations where automation is used to set the role.

Proxy users can access password-protected roles. Specify the WITH clauses to limit the proxy to some or none of the roles associated with , and the AUTHENTICATION REQUIRED clause to specify whether authentication is required.

WITH ROLE permits the proxy to connect as the specified user and to activate only the roles that are specified by . This clause can contain only roles that are associated with . Password protected roles and secure application roles also need to be listed in the WITH ROLE clause if the Proxy user will need to use these secure roles. These secure roles will be included with the WITH ROLE ALL clause (the default if WITH ROLE is not specified). If WITH ROLE doesn't specify the secure roles, then those cannot be enabled even with right password.

WITH ROLE ALL EXCEPT

WITH ROLE ALL EXCEPT permits the proxy to connect as the specified user and to activate all roles associated with that user except those specified for . This clause can contain only roles that are associated with .

WITH NO ROLES

AUTHENTICATION REQUIRED

Oracle Database does not expect the proxy to authenticate the user unless you specify the AUTHENTICATION REQUIRED clause. This clause ensures that authentication credentials for the user must be presented when the user is authenticated through the specified proxy. The credential is a password.

AUTHENTICATED USING

The AUTHENTICATED USING clauses, which appeared in the syntax of earlier releases, have been deprecated and are no longer needed. If you specify the AUTHENTICATED USING PASSWORD clause, then Oracle Database converts it to the AUTHENTICATION REQUIRED clause. Specifying the AUTHENTICATED USING CERTIFICATE clause or the AUTHENTICATED USING DISTINGUISHED NAME clause is equivalent to omitting the AUTHENTICATION REQUIRED clause.

Examples

Changing User Identification: Example

The following statement changes the password of the user " sidney (created in Creating a Database User: Example") second_2nd_pwd and default tablespace to the tablespace example:

ALTER USER sidney
    IDENTIFIED BY second_2nd_pwd
    DEFAULT TABLESPACE example;

The following statement assigns the " new_profile profile (created in Creating a Profile: Example") to the sample user sh:

ALTER USER sh
    PROFILE new_profile;

In subsequent sessions, sh is restricted by limits in the new_profile profile.

The following statement makes all roles granted directly to sh default roles, except the dw_manager role:

ALTER USER sh
    DEFAULT ROLE ALL EXCEPT dw_manager;

Changing User Authentication: Examples

The following statement changes the authentication mechanism of user " app_user1 (created in Creating a Database User: Example" ):

ALTER USER app_user1 IDENTIFIED GLOBALLY AS 'CN=tom,O=oracle,C=US';

The following statement causes user sidney's password to expire:

ALTER USER sidney PASSWORD EXPIRE;

If you cause a database user's password to expire with PASSWORD EXPIRE, then the user (or the DBA) must change the password before attempting to log in to the database following the expiration. However, tools such as SQL*Plus allow the user to change the password on the first attempted login following the expiration.

Assigning a Tablespace Group: Example

The following statement assigns " tbs_grp_01 (created in Adding a Temporary Tablespace to a Tablespace Group: Example") as the tablespace group for user sh:

ALTER USER sh
  TEMPORARY TABLESPACE tbs_grp_01;

Proxy Users: Examples

The following statement alters the user " app_user1. The example permits the app_user1 to connect through the proxy user sh. The example also allows app_user1 to enable its warehouse_user role (created in Creating a Role: Example") when connected through the proxy sh:

ALTER USER app_user1
   GRANT CONNECT THROUGH sh
   WITH ROLE warehouse_user;

To show basic syntax, this example uses the sample database Sales History user ( sh) as the proxy. Normally a proxy user would be an application server or middle-tier entity. For information on creating the interface between an application user and a database by way of an application server, refer to Oracle Call Interface Programmer's Guide.

The following statement takes away the right of user app_user1 to connect through the proxy user sh:

ALTER USER app_user1 REVOKE CONNECT THROUGH sh;

The following hypothetical examples shows another method of proxy authentication:

ALTER USER sully GRANT CONNECT THROUGH OAS1
   AUTHENTICATED USING PASSWORD;

The following example exposes the user app_user1 to proxy use by enterprise users. The enterprise users cannot act on behalf of app_user1 until the Oracle Internet Directory administrator has granted them appropriate privileges:

ALTER USER app_user1
   GRANT CONNECT THROUGH ENTERPRISE USERS;

# data[].attributes.attachment_link.description
Purpose Use the ALTER USER statement: To change the authentication or database resource characteristics of a database user To permit a proxy server to connect as a client without authentication In an Oracle Automatic Storage Management (Oracle ASM) cluster, to change the password of a user in the password file that is local to the Oracle ASM instance of the current node Prerequisites In general, you must have the ALTER USER system privilege.

# data[].attributes.body
Los permisos o privilegios se otorgan a los usuarios para limitar el acceso a los datos.

# data[].attributes.body
Update y Select son los permisos más habituales para definir un usuario que pueda realizar consultas y actualizaciones de la base de datos.

# data[].attributes.body
Existe un rol predefinido llamado DBA que se crea con cada base de datos Oracle. Este rol contiene TODOS los privilegios del sistema.

# data[].attributes.attachment_link.readable_text
Purpose

Use the GRANT statement to grant:

System privileges to users and roles. Table 18-1 lists the system privileges (organized by the database object operated upon).

Roles to users, roles, and program units. The granted roles can be either user-defined (local or external) or predefined. For a list of predefined roles, refer to Oracle Database Security Guide .

Object privileges for a particular object to users and roles. Table 18-2 lists the object privileges (organized by the database object operated upon).

Note:

Global roles (created with IDENTIFIED GLOBALLY) are granted through enterprise roles and cannot be granted using the GRANT statement.

Notes on Authorizing Database Users

You can authorize database users through means other than the database and the GRANT statement.

Note on Oracle Automatic Storage Management

A user authenticated AS SYSASM can use this statement to grant the system privileges SYSASM, SYSOPER, and SYSDBA to a user in the Oracle ASM password file of the current node.

Note on Editionable Objects

A GRANT operation to grant object privileges on an editionable object actualizes the object in the current edition. See Oracle Database Development Guide for more information about editions and editionable objects.

Prerequisites

To grant a system privilege, one of the following conditions must be met:

You must have been granted the system privilege with the ADMIN OPTION. In this case, if you grant the system privilege to a role, then a user to whom the role has been granted has the privilege regardless whether the role is enabled in the user's session.

To grant a role to a user or another role, you must have been directly granted the role with the ADMIN OPTION, or you must have been granted the GRANT ANY ROLE system privilege, or you must have created the role.

To grant a role to a program unit in your own schema, you must have been directly granted the role with either the ADMIN OPTION or the DELEGATE OPTION, or you must have been granted the GRANT ANY ROLE system privilege, or you must have created the role.

To grant a role to a program unit in another user's schema, you must be the user SYS and the role must have been created by the schema owner or directly granted to the schema owner.

To grant an object privilege on a user, by specifying the ON USER clause of the on_object_clause, you must be the user on whom the privilege is granted, or you must have been granted the object privilege on that user with the WITH GRANT OPTION, or you must have been granted the GRANT ANY OBJECT PRIVILEGE system privilege. If you can grant an object privilege on a user only because you have the GRANT ANY OBJECT PRIVILEGE, then the GRANTOR column of the *_TAB_PRIVS views displays the user on whom the privilege is granted rather than the user who issued the GRANT statement.

To grant an object privilege on all other types of objects, you must own the object, or the owner of the object must have granted you the object privileges with the WITH GRANT OPTION, or you must have been granted the GRANT ANY OBJECT PRIVILEGE system privilege. If you have the GRANT ANY OBJECT PRIVILEGE, then you can grant the object privilege only if the object owner could have granted the same object privilege. In this case, the GRANTOR column of the *_TAB_PRIVS views displays the object owner rather than the user who issued the GRANT statement.

grant_system_privileges

Use these clauses to grant system privileges.

Specify the system privilege you want to grant. Table 18-1 lists the system privileges, organized by the database object operated upon.

If you grant a privilege to a user, then the database adds the privilege to the user's privilege domain. The user can immediately exercise the privilege. Oracle recommends that you only grant the ANY privileges to trusted users.

If you grant a privilege to a role, then the database adds the privilege to the privilege domain of the role. Users who have been granted and have enabled the role can immediately exercise the privilege. Other users who have been granted the role can enable the role and exercise the privilege.

If you grant a privilege to , then the database adds the privilege to the privilege domains of each user. All users can immediately perform operations authorized by the privilege. Oracle recommends against granting system privileges to PUBLIC.

Specify the role you want to grant. You can grant an Oracle Database predefined role or a user-defined role.

If you grant a role to a user, then the database makes the role available to the user. The user can immediately enable the role and exercise the privileges in the privilege domain of the role.

In the case of a secure application role, you need not grant such a role directly to the user. You can let the associated PL/SQL package do this, assuming the user passes appropriate security policies. For more information, see the CREATE ROLE semantics for USING package and Oracle Database Security Guide

If you grant a role to another role, then the database adds the privilege domain of the granted role to the privilege domain of the grantee role. Users who have been granted the grantee role can enable it and exercise the privileges in the granted role's privilege domain.

If you grant a role to , then the database makes the role available to all users. All users can immediately enable the role and exercise the privileges in the privilege domain of the role.

ALL PRIVILEGES

Specify ALL PRIVILEGES to grant all of the system privileges listed in Table 18-1, except the SELECT ANY DICTIONARY, ALTER DATABASE LINK, and ALTER PUBLIC DATABASE LINK privileges.

However, granting and revoking ALL PRIVILEGES does not apply to ADMINISTER KEY MANAGEMENT. Granting ALL PRIVILEGES does not grant ADMINISTER KEY MANAGEMENT. Similarly, revoking ALL PRIVILEGES does not revoke ADMINISTER KEY MANAGEMENT.

Use the grantee_clause to specify the users or roles to which the system privilege, role, or object privilege is granted.

Specify PUBLIC to grant the privileges to all users. Oracle recommends against granting system privileges to PUBLIC.

Restriction on Grantees

A user, role, or PUBLIC cannot appear more than once in the grantee_clause.

The grantee_identified_by clause lets you assign passwords to users when granting them system privileges and roles. You must specify an equal number of users and passwords. The first password is assigned to the first user, the second password is assigned to the second user, and so on. If a specified user exists, then the database resets the user's password. If a specified user does not exist, then the database creates the user with the password.

WITH ADMIN OPTION

Grant the privilege or role to another user or role, unless the role is a GLOBAL role

Revoke the privilege or role from another user or role

Alter the privilege or role to change the authorization needed to access it

Drop the privilege or role

Grant the role to a program unit in the grantee's schema.

Revoke the role from a program unit in the grantee's schema.

If you grant a system privilege or role to a user without specifying WITH ADMIN OPTION, and then subsequently grant the privilege or role to the user WITH ADMIN OPTION, then the user has the ADMIN OPTION on the privilege or role.

WITH DELEGATE OPTION

You can specify this clause only when granting a role to a user.

If you grant a role to a user without specifying WITH DELEGATE OPTION, and then subsequently grant the role to the user WITH DELEGATE OPTION, then the user has the DELEGATE OPTION on the role.

Restrictions on Granting System Privileges and Roles

Privileges and roles are subject to the following restrictions:

A privilege or role cannot appear more than once in the list of privileges and roles to be granted.

You cannot grant a role to itself.

You cannot grant a role IDENTIFIED GLOBALLY to anything.

You cannot grant a role IDENTIFIED EXTERNALLY to a global user or global role.

You cannot grant roles circularly. For example, if you grant the role banker to the role teller, then you cannot subsequently grant teller to banker.

You cannot grant an IDENTIFIED BY role, IDENTIFIED USING role, or IDENTIFIED EXTERNALLY role to another role.

grant_object_privileges

Use these clauses to grant object privileges.

Specify the object privilege you want to grant. Table 18-2 lists the object privileges, organized by the type of object on which they can be granted. When you grant an object privilege on a editionable object, either to a user or to a role, the object is actualized in the edition in which the grant is made. Refer to CREATE EDITION for information on editionable object types and editions.

Note:

To grant SELECT on a view to another user, either you must own all of the objects underlying the view or you must have been granted the SELECT object privilege WITH GRANT OPTION on all of those underlying objects. This is true even if the grantee already has SELECT privileges on those underlying objects.

To grant READ on a view to another user, either you must own all of the objects underlying the view or you must have been granted the READ or SELECT object privilege WITH GRANT OPTION on all of those underlying objects. This is true even if the grantee already has the READ or SELECT privilege on those underlying objects.

Restriction on Object Privileges

A privilege cannot appear more than once in the list of privileges to be granted.

ALL [PRIVILEGES]

Specify ALL to grant all the privileges for the object that you have been granted with the GRANT OPTION. The user who owns the schema containing an object automatically has all privileges on the object with the GRANT OPTION. The keyword PRIVILEGES is provided for semantic clarity and is optional.

The on_object_clause identifies the object on which the privileges are granted. Users, directory objects, editions, data mining models, Java source and resource schema objects, and SQL translation profiles are identified separately because they reside in separate namespaces.

Specify the schema object on which the privileges are to be granted. If you do not qualify with , then the database assumes the object is in your own schema. The object can be one of the following types:

Table, view, or materialized view

Procedure, function, or package

User-defined type

Synonym for any of the preceding items

Directory, library, operator, or indextype

Java source, class, or resource

You cannot grant privileges directly to a single partition of a partitioned table.

Specify the database user you want to grant privileges to.

Restriction on Granting Privileges on Users

You cannot grant privileges on user PUBLIC.

ON DIRECTORY

Specify the name of the directory object on which privileges are to be granted. You cannot qualify directory_name with a schema name.

Specify the name of the edition on which the USE object privilege is to be granted. You cannot qualify edition_name with a schema name.

ON MINING MODEL

Specify the name of the mining model on which privileges are to be granted. If you do not qualify mining_model_name with , then the database assumes that the mining model is in your own schema.

ON JAVA SOURCE | RESOURCE

Specify the name of the Java source or resource schema object on which privileges are to be granted. If you do not qualify with , then the database assumes that the object is in your own schema.

ON SQL TRANSLATION PROFILE

Specify the name of the SQL translation profile on which privileges are to be granted. If you do not qualify with , then the database assumes that the profile is in your own schema.

WITH HIERARCHY OPTION

Specify WITH HIERARCHY OPTION to grant the specified object privilege on all subobjects of , such as subviews created under a view, including subobjects created subsequent to this statement.

This clause is meaningful only in combination with the READ or SELECT object privilege.

WITH GRANT OPTION

If you grant an object privilege to a user without specifying WITH GRANT OPTION, and then subsequently grant the privilege to the user WITH GRANT OPTION, then the user has the GRANT OPTION on the privilege.

Restriction on Granting WITH GRANT OPTION

grant_roles_to_programs

Use this clause to grant roles to program units. Such roles are called code based access control (CBAC) roles.

Specify the role you want to grant. You can grant an Oracle Database predefined role or a user-defined role. The role must have been created by or directly granted to the schema owner of the program unit.

Specify the program unit to which the role is to be granted. You can specify a PL/SQL function, procedure, or package. If you do not specify , then Oracle Database assumes the function, procedure, or package is in your own schema.

CONTAINER Clause

If the current container is a pluggable database (PDB):

If the current container is the root:

Note:

If you specify the CONTAINER clause when granting a privilege or role, then the current container must be the same container and you must specify the same CONTAINER clause when you revoke the privilege or role. Refer to the CONTAINER Clause of the REVOKE statement for more information.

Listings of System and Object Privileges

Table 18-1 System Privileges (Organized by the Database Object Operated Upon)

System Privilege Name

Operations Authorized

Advisor Framework Privileges: All of the advisor framework privileges are part of the DBA role.

Create, drop, select (read), load (write), and delete SQL tuning sets owned by the grantee through the DBMS_SQLTUNE package.

Create, drop, select (read), load (write), and delete SQL tuning sets owned by any user through the DBMS_SQLTUNE package.

Accept a SQL Profile recommended by the SQL Tuning Advisor, which is accessed through Enterprise Manager or by the DBMS_SQLTUNE package.

Note: This privilege has been deprecated in favor of ADMINISTER SQL MANAGEMENT OBJECT.

Alter the attributes of an existing SQL Profile.

Note: This privilege has been deprecated in favor of ADMINISTER SQL MANAGEMENT OBJECT.

Drop existing SQL Profiles.

Note: This privilege has been deprecated in favor of ADMINISTER SQL MANAGEMENT OBJECT.

Create, alter, and drop SQL Profiles owned by any user through the DBMS_SQLTUNE package.

Modify a fixed-user database link when the password of the connection or authentication user changes.

Modify a public fixed-user database link when the password of the connection or authentication user changes.

Debug all PL/SQL and Java code in any database object. Display information on all SQL statements executed by the application.

Note: Granting this privilege is equivalent to granting the DEBUG object privilege on all applicable objects in the database.

The following privileges are needed to execute procedures in the DBMS_SCHEDULER package. This privileges do not apply to lightweight jobs, which are not database objects. Refer to Oracle Database Administrator's Guide for more information about lightweight jobs.

Create, alter, or drop jobs, chains, schedules, programs, credentials, resource objects, or incompatibility resource objects in any schema except .

Note: This extremely powerful privilege allows the grantee to execute code as any other user. It should be granted with caution.

Manage keys and keystores.

Caution: CREATE LIBARARY, CREATE ANY LIBRARY, ALTER ANY LIBRARY, and EXECUTE ANY LIBRARY are extremely powerful privileges that should be granted only to trusted users. Refer to Oracle Database Security Guide before granting these privileges.

Create a PDB.

Plug in a PDB that was previously unplugged from a CDB.

Clone a PDB.

Allow a common user to switch into the container for which this privilege was granted. This privilege can be granted only to a common user or common role.

Execute procedures or functions, either standalone or packaged.

Reference public package variables in any schema except .

Create SQL translation profiles in the grantee's schema.

Caution: CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM are extremely powerful privileges that should be granted only to trusted users. Refer to Oracle Database Security Guide before granting these privileges.

Create users. This privilege also allows the creator to:

Assign quotas on any tablespace.

Set default and temporary tablespaces.

Assign a profile as part of a CREATE USER statement.

Alter any user except SYS. This privilege authorizes the grantee to:

Change another user's password or authentication method.

Assign quotas on any tablespace.

Set default and temporary tablespaces.

Assign a profile and default roles.

If a user has been granted the MERGE ANY VIEW privilege, then for any query issued by that user, the optimizer can use view merging to improve query performance without performing the checks that would otherwise be performed to ensure that view merging does not violate any security intentions of the view creator. See also Oracle Database Reference for information on the OPTIMIZER_SECURE_VIEW_MERGING parameter and Oracle Database SQL Tuning Guide for information on view merging.

Allow users of the Data Pump Import utility (impdp) and the original Import utility (imp) to assume the identity of another user in order to perform operations that cannot be directly performed by a third party (for example, loading objects such as object privilege grants).

Allow Streams administrators to create or alter capture users and apply users in a Streams environment. By default this privilege is part of the DBA role. Database Vault removes this privileges from the DBA role. Therefore, this privilege is needed by Streams only in an environment where Database Vault is installed.

Create a registration on queries and receive database change notifications in response to DML or DDL changes to the objects associated with the registered queries. Refer to Oracle Database Development Guide for more information on database change notification.

Bypass fine-grained access control.

Caution: This is a very powerful system privilege, as it lets the grantee bypass application-driven security policies. Database administrators should use caution when granting this privilege.

Grant any object privilege that the object owner is permitted to grant.

The SYSDATE and SYSTIMESTAMP functions return their original values during replay for Application Continuity when the grantee is running the application. This privilege is useful for providing bind variable consistency after recoverable errors.

Note: If this privilege is granted or revoked between runtime and failover of a request, then the original values are not returned during replay for Application Continuity for that request.

The SYS_GUID function returns its original value during replay for Application Continuity when the grantee is running the application. This privilege is useful for providing bind variable consistency after recoverable errors.

Note: If this privilege is granted or revoked between runtime and failover of a request, then the original value is not returned during replay for Application Continuity for that request.

Query any data dictionary object in the SYS schema, with the exception of the following objects: DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS.

SELECT ANY TRANSACTION

Query the contents of the FLASHBACK_TRANSACTION_QUERY view.

Caution: This is a very powerful system privilege, as it lets the grantee view all data in the database, including past data. This privilege should be granted only to users who need to use the Oracle Flashback Transaction Query feature.

Perform the following Oracle Data Guard operations:

Create, use, view, and drop restore points (including guaranteed restore points).

Perform the following encryption key management operations:

Connect to the database even if the database is not open.

Table 18-2 Object Privileges (Organized by the Database Object Operated Upon)

Object Privilege Name

Operations Authorized

Query the object with the SELECT statement.

Write files in the directory. This privilege is useful only in connection with external tables. It allows the grantee to determine whether the external table agent can write a log file or a bad file to the directory.

Restriction: This privilege does not allow the grantee to write to a BFILE.

Execute a preprocessor program that resides in the directory. A preprocessor program converts data to a supported format when loading data records from an external table with the ORACLE_LOADER access driver. Refer to Oracle Database Utilities for more information. This privilege does not implicitly allow READ access on the external table data.

Use and reference the specified object and invoke its methods.

Caution: This extremely powerful privilege should be granted only to trusted users. Refer to Oracle Database Security Guide before granting this privilege.

MATERIALIZED VIEW PRIVILEGES

The following materialized view privileges authorize operations on a materialized view. The DELETE, INSERT, and UPDATE privileges can be granted only to updatable materialized views.

PROCEDURE, FUNCTION, PACKAGE PRIVILEGES

The following procedure, function, and package privileges authorize operations on procedures, functions, and packages. These privileges also apply to Java sources, classes, and resources, which Oracle Database treats as though they were procedures for purposes of granting object privileges.

Access, through a debugger, public variables, types, and methods defined on the procedure, function, or package. This privilege grants access to the declarations in the method or package specification only.

Note: Users do not need this privilege to execute a procedure, function, or package indirectly.

Job scheduler objects are created using the DBMS_SCHEDULER package. After these objects are created, you can grant the following privileges.

The sequence pseudocolumn NEXTVAL retains its original value during replay for Application Continuity when the grantee is running the application. This privilege is useful for providing bind variable consistency when replaying after recoverable errors.

If this privilege is granted or revoked between runtime and failover of a request, then the original value of NEXTVAL is not retained during replay for Application Continuity for that request.

Note: This privilege is not granted by the GRANT ALL PRIVILEGES ON statement. You must explicitly grant this privilege.

Note: This privilege is part of the DBA role.

SQL TRANSLATION PROFILE PRIVILEGES

The following SQL translation profile privileges authorize operations on a SQL translation profile.

The following table privileges authorize operations on a table. Any one of following object privileges, except the READ privilege, allows the grantee to lock the table in any lock mode with the LOCK TABLE statement.

Note: For external tables, the only valid object privileges are ALTER, READ, and SELECT.

Access, through a debugger:

Remove rows from the table with the DELETE statement.

Note: You must grant the SELECT privilege on the table along with the DELETE privilege if the table is on a remote database.

Add new rows to the table with the INSERT statement.

Note: You must grant the SELECT privilege on the table along with the INSERT privilege if the table is on a remote database.

Change data in the table with the UPDATE statement.

Note: You must grant the SELECT privilege on the table along with the UPDATE privilege if the table is on a remote database.

To allow access to a specific table during queries, grant the FLASHBACK privileges on the table.

Issue a SQL Flashback Query on the table.

Execute invoker's rights procedures or functions owned by the grantee with the privileges of the invoker when the invoker is the user on whom this privilege is granted.

Translate SQL through the grantee's SQL translation profile for the user on whom this privilege is granted.

The following view privileges authorize operations on a view. Any one of the following object privileges, except the READ privilege, allows the grantee to lock the view in any lock mode with the LOCK TABLE statement.

To grant a privilege on a view, you must have that privilege with the GRANT OPTION on all of the base tables of the view.

Access, through a debugger:

This object privilege has the same behavior as the system privilege MERGE ANY VIEW, except that the privilege is limited to the views specified in the ON clause. For any query issued by the grantee on the specified views, the optimizer can use view merging to improve query performance without performing the checks that would otherwise be performed to ensure that view merging does not violate any security intentions of the view creator.

See Also:  object_privilege for additional information on granting this object privilege on a view

To allow access to a specific view during queries, grant the FLASHBACK privileges on the view.

Issue a SQL Flashback Query on the view.

Examples

Granting a System Privilege to a User: Example

GRANT CREATE SESSION
   TO hr;

Assigning User Passwords When Granting a System Privilege: Example

Assume that user hr exists and user newuser does not exist. The following statement resets the user hr password to , creates user newuser with , and grants both users the CREATE SESSION system privilege:

GRANT CREATE SESSION
  TO hr, newuser IDENTIFIED BY password1, password2;

Granting System Privileges to a Role: Example

The following statement grants appropriate system privileges to a data warehouse manager role, which was created in the " Creating a Role: Example":

GRANT
     CREATE ANY MATERIALIZED VIEW
   , ALTER ANY MATERIALIZED VIEW
   , DROP ANY MATERIALIZED VIEW
   , QUERY REWRITE
   , GLOBAL QUERY REWRITE
   TO dw_manager
   WITH ADMIN OPTION;

The dw_manager privilege domain now contains the system privileges related to materialized views.

Granting a Role with the ADMIN OPTION: Example

GRANT dw_manager
   TO sh
   WITH ADMIN OPTION;

User sh can now perform the following operations with the dw_manager role:

Granting a Role with the DELEGATE OPTION: Example

GRANT dw_manager
   TO sh
   WITH DELEGATE OPTION;

Granting Object Privileges to a Role: Example

The following example grants the " SELECT object privileges to a data warehouse user role, which was created in the Creating a Role: Example":

GRANT SELECT ON sh.sales TO warehouse_user;

Granting a Role to a Role: Example

The following statement grants the " warehouse_user role to the dw_manager role. Both roles were created in the Creating a Role: Example":

GRANT warehouse_user TO dw_manager;

The dw_manager role now contains all of the privileges in the domain of the warehouse_user role.

Granting an Object Privilege on a User: Example

GRANT INHERIT PRIVILEGES ON USER sh TO hr;

Granting an Object Privilege on a Directory: Example

To grant READ on directory bfile_dir to user hr, with the GRANT OPTION, issue the following statement:

GRANT READ ON DIRECTORY bfile_dir TO hr
   WITH GRANT OPTION;

Granting Object Privileges on a Table to a User: Example

To grant all privileges on the table " oe.bonuses, which was created in Merging into a Table: Example", to the user hr with the GRANT OPTION, issue the following statement:

GRANT ALL ON bonuses TO hr
   WITH GRANT OPTION;

The user hr can subsequently perform the following operations:

Granting Object Privileges on a View: Example

To grant " SELECT and UPDATE privileges on the view emp_view, which was created in Creating a View: Example", to all users, issue the following statement:

GRANT SELECT, UPDATE
   ON emp_view TO PUBLIC;

All users can subsequently query and update the view of employee details.

Granting Object Privileges to a Sequence in Another Schema: Example

GRANT SELECT
   ON oe.customers_seq TO hr;

The user hr can subsequently generate the next value of the sequence with the following statement:

SELECT oe.customers_seq.NEXTVAL
   FROM DUAL;

Granting Multiple Object Privileges on Individual Columns: Example

To grant to user oe the REFERENCES privilege on the employee_id column and the UPDATE privilege on the employee_id, salary, and commission_pct columns of the employees table in the schema hr, issue the following statement:

GRANT REFERENCES (employee_id),
      UPDATE (employee_id, salary, commission_pct)
   ON hr.employees
   TO oe;

The user oe can subsequently update values of the employee_id, salary, and commission_pct columns. User oe can also define referential integrity constraints that refer to the employee_id column. However, because the GRANT statement lists only these columns, oe cannot perform operations on any of the other columns of the employees table.

For example, oe can create a table with a constraint:

CREATE TABLE dependent
   (dependno   NUMBER,
    dependname VARCHAR2(10),
    employee   NUMBER
   CONSTRAINT in_emp REFERENCES hr.employees(employee_id) );

# data[].attributes.attachment_link.description
Purpose Use the GRANT statement to grant: System privileges to users and roles. Table 18-1 lists the system privileges (organized by the database object operated upon). Roles to users, roles, and program units. The granted roles can be either user-defined (local or external) or predefined.

# data[].attributes.attachment_link.readable_text
Purpose

Use the REVOKE statement to:

Revoke system privileges from users and roles

Revoke roles from users, roles, and program units.

Revoke object privileges for a particular object from users and roles

Note on Oracle Automatic Storage Management

Note on Editionable Objects

A REVOKE operation to revoke object privileges on an editionable object actualizes the object in the current edition. See Oracle Database Development Guide for more information about editions and editionable objects.

Prerequisites

To revoke a system privilege, you must have been granted the privilege with the ADMIN OPTION. You can revoke any privilege if you have the GRANT ANY PRIVILEGE system privilege.

To revoke a role from a user or another role, you must have been directly granted the role with the ADMIN OPTION or you must have created the role. You can revoke any role if you have the GRANT ANY ROLE system privilege.

To revoke a role from a program unit, you must be the user SYS or you must be the schema owner of the program unit.

To revoke an object privilege, one of the following conditions must be met:

You must previously have granted the object privilege to the user or role.

You must have the GRANT ANY OBJECT PRIVILEGE system privilege. In this case, you can revoke any object privilege that was granted by the object owner or on behalf of the owner by a user with the GRANT ANY OBJECT PRIVILEGE. However, you cannot revoke an object privilege that was granted by way of a WITH GRANT OPTION grant.

The REVOKE statement can revoke only privileges and roles that were previously granted directly with a GRANT statement. You cannot use this statement to revoke:

Privileges or roles not granted to the revokee

Roles or object privileges granted through the operating system

Privileges or roles granted to the revokee through roles

revoke_roles_from_programs::=

revoke_system_privileges

Use these clauses to revoke system privileges.

Specify the system privilege to be revoked. Refer to Table 18-1 for a list of the system privileges.

If you revoke a system privilege from a user, then the database removes the privilege from the user's privilege domain. Effective immediately, the user cannot exercise the privilege.

If you revoke a system privilege from a role, then the database removes the privilege from the privilege domain of the role. Effective immediately, users with the role enabled cannot exercise the privilege. Also, other users who have been granted the role and subsequently enable the role cannot exercise the privilege.

If you revoke a system privilege from , then the database removes the privilege from the privilege domain of each user who has been granted the privilege through PUBLIC. Effective immediately, such users can no longer exercise the privilege. However, the privilege is not revoked from users who have been granted the privilege directly or through roles.

Oracle Database provides a shortcut for specifying all system privileges at once: Specify ALL PRIVILEGES to revoke all the system privileges listed in Table 18-1.

Restriction on Revoking System Privileges

A system privilege cannot appear more than once in the list of privileges to be revoked.

Specify the role to be revoked.

If you revoke a role from a user, then the database makes the role unavailable to the user. If the role is currently enabled for the user, then the user can continue to exercise the privileges in the role's privilege domain as long as it remains enabled. However, the user cannot subsequently enable the role.

If you revoke a role from another role, then the database removes the privilege domain of the revoked role from the privilege domain of the revokee role. Users who have been granted and have enabled the revokee role can continue to exercise the privileges in the privilege domain of the revoked role as long as the revokee role remains enabled. However, other users who have been granted the revokee role and subsequently enable it cannot exercise the privileges in the privilege domain of the revoked role.

If you revoke a role from , then the database makes the role unavailable to all users who have been granted the role through PUBLIC. Any user who has enabled the role can continue to exercise the privileges in its privilege domain as long as it remains enabled. However, users cannot subsequently enable the role. The role is not revoked from users who have been granted the role directly or through other roles.

Restriction on Revoking System Roles

A system role cannot appear more than once in the list of roles to be revoked. For information on the predefined roles, refer to Oracle Database Security Guide .

Use the revokee_clause to specify the users or roles from which the system privilege, role, or object privilege is to be revoked.

Specify PUBLIC to revoke the privileges or roles from all users.

revoke_object_privileges

Use these clauses to revoke object privileges.

Specify the object privilege to be revoked. The object privileges, categorized by the type of object to which they apply, are described in Table 18-2.

Note:

Each privilege authorizes some operation. By revoking a privilege, you prevent the revokee from performing that operation. However, multiple users may grant the same privilege to the same user, role, or PUBLIC. To remove the privilege from the grantee's privilege domain, all grantors must revoke the privilege. If even one grantor does not revoke the privilege, then the grantee can still exercise the privilege by virtue of that grant.

If you revoke an object privilege from a user, then the database removes the privilege from the user's privilege domain. Effective immediately, the user cannot exercise the privilege.

If that user has granted that privilege to other users or roles, then the database also revokes the privilege from those other users or roles.

If that user's schema contains a procedure, function, or package that contains SQL statements that exercise the privilege, then the procedure, function, or package can no longer be executed.

If that user's schema contains a view on that object, then the database invalidates the view.

If you revoke the REFERENCES object privilege from a user who has exercised the privilege to define referential integrity constraints, then you must specify the CASCADE CONSTRAINTS clause.

If you revoke an object privilege from a role, then the database removes the privilege from the privilege domain of the role. Effective immediately, users with the role enabled cannot exercise the privilege. Other users who have been granted the role cannot exercise the privilege after enabling the role.

If you revoke an object privilege from PUBLIC, then the database removes the privilege from the privilege domain of each user who has been granted the privilege through PUBLIC. Effective immediately, all such users are restricted from exercising the privilege. However, the privilege is not revoked from users who have been granted the privilege directly or through roles.

ALL [PRIVILEGES]

Specify ALL to revoke all object privileges that you have granted to the revokee. (The keyword PRIVILEGES is provided for semantic clarity and is optional.)

If no privileges have been granted on the object, then the database takes no action and does not return an error.

Restriction on Revoking Object Privileges

A privilege cannot appear more than once in the list of privileges to be revoked. A user, a role, or PUBLIC cannot appear more than once in the FROM clause.

CASCADE CONSTRAINTS

This clause is relevant only if you revoke the REFERENCES privilege or ALL [ PRIVILEGES]. It drops any referential integrity constraints that the revokee has defined using the REFERENCES privilege, which might have been granted either explicitly or implicitly through a grant of ALL [ PRIVILEGES].

The on_object_clause identifies the objects on which privileges are to be revoked.

Specify the object on which the object privileges are to be revoked. This object can be:

A table, view, sequence, procedure, stored function, package, or materialized view

A synonym for a table, view, sequence, procedure, stored function, package, materialized view, or user-defined type

A library, indextype, or user-defined operator

If you do not qualify object with , then the database assumes the object is in your own schema.

Specify the database user you want to revoke privileges from.

ON DIRECTORY

Specify the name of the directory object on which privileges are to be revoked. You cannot qualify directory_name with a schema name.

Specify the name of the edition on which the USE object privilege is to be revoked. You cannot qualify edition_name with a schema name.

ON MINING MODEL

Specify the name of the mining model on which privileges are to be revoked. If you do not qualify mining_model_name with , then the database assumes that the mining model is in your own schema.

ON JAVA SOURCE | RESOURCE

Specify the name of the Java source or resource schema object on which privileges are to be revoked. If you do not qualify with , then the database assumes that the object is in your own schema.

ON SQL TRANSLATION PROFILE

Specify the name of the SQL translation profile on which privileges are to be revoked. If you do not qualify with , then the database assumes the profile is in your own schema.

revoke_roles_from_programs

Use this clause to revoke code based access control (CBAC) roles from program units.

Specify the role you want to revoke.

Specify ALL to revoke all roles that are granted to the program unit.

Specify the program unit from which the role is to be revoked. You can specify a PL/SQL function, procedure, or package. If you do not specify , then Oracle Database assumes the function, procedure, or package is in your own schema.

CONTAINER Clause

If the current container is a pluggable database (PDB):

Specify CONTAINER = CURRENT to revoke a locally granted system privilege, object privilege, or role from a local user, common user, local role, or common role. The privilege or role is revoked from the user or role only in the current PDB. This clause does not revoke privileges granted with CONTAINER = ALL.

If the current container is the root:

Specify CONTAINER = CURRENT to revoke a locally granted system privilege, object privilege, or role from a common user or common role. The privilege or role is revoked from the user or role only in the root. This clause does not revoke privileges granted with CONTAINER = ALL.

Specify CONTAINER = ALL to revoke a commonly granted system privilege, object privilege on a common object, or role from a common user or common role. The privilege or role is revoked from the user or role across the entire CDB. This clause can revoke only a privilege or role granted with CONTAINER = ALL from the specified common user or common role. This clause does not revoke privileges granted locally with CONTAINER = CURRENT. However, any locally granted privileges that depend on the commonly granted privilege being revoked are also revoked.

Examples

Revoking a System Privilege from a User: Example

REVOKE DROP ANY TABLE
    FROM hr, oe;

The users hr and oe can no longer drop tables in schemas other than their own.

Revoking a Role from a User: Example

The following statement revokes the role dw_manager from the user sh:

REVOKE dw_manager
    FROM sh;

The user sh can no longer enable the dw_manager role.

Revoking a System Privilege from a Role: Example

REVOKE CREATE TABLESPACE
   FROM dw_manager;

Enabling the dw_manager role no longer allows users to create tablespaces.

Revoking a Role from a Role: Example

To revoke the role dw_user from the role dw_manager, issue the following statement:

REVOKE dw_user
  FROM dw_manager;

The dw_user role privileges are no longer granted to dw_manager.

Revoking an Object Privilege from a User: Example

You can grant DELETE, INSERT, READ, SELECT, and UPDATE privileges on the table orders to the user hr with the following statement:

GRANT ALL
   ON orders TO hr;

REVOKE DELETE
   ON orders FROM hr;

Revoking All Object Privileges from a User: Example

To revoke the remaining privileges on orders that you granted to hr, issue the following statement:

REVOKE ALL
   ON orders FROM hr;

Revoking Object Privileges from PUBLIC: Example

GRANT SELECT, UPDATE
    ON emp_details_view TO public;

The following statement revokes UPDATE privilege on emp_details_view from all users:

REVOKE UPDATE
    ON emp_details_view FROM public;

Revoking an Object Privilege on a User from a User: Example

GRANT INHERIT PRIVILEGES ON USER sh TO hr;

REVOKE INHERIT PRIVILEGES ON USER sh FROM hr;

Revoking an Object Privilege on a Sequence from a User: Example

GRANT SELECT
    ON hr.departments_seq TO oe;

REVOKE SELECT
    ON hr.departments_seq FROM oe;

However, if the user hr has also granted SELECT privilege on departments to sh, then sh can still use departments by virtue of hr's grant.

Revoking an Object Privilege with CASCADE CONSTRAINTS: Example

GRANT REFERENCES, UPDATE
    ON hr.employees TO oe;

CREATE TABLE dependent
(dependno   NUMBER,
 dependname VARCHAR2(10),
 employee   NUMBER
    CONSTRAINT in_emp REFERENCES hr.employees(employee_id) );

REVOKE REFERENCES
    ON hr.employees
    FROM oe
    CASCADE CONSTRAINTS;

Revoking an Object Privilege on a Directory from a User: Example

REVOKE READ ON DIRECTORY bfile_dir FROM hr;

Revoke Operations that Use GRANT ANY OBJECT PRIVILEGE: Example

Suppose that the database administrator has granted GRANT ANY OBJECT PRIVILEGE to user sh. Now suppose that user hr grants the update privilege on the employees table to oe:

CONNECT hr
GRANT UPDATE ON employees TO oe WITH GRANT OPTION;

This grant gives user oe the right to pass the object privilege along to another user:

CONNECT oe
GRANT UPDATE ON hr.employees TO pm;

User sh, who has the GRANT ANY OBJECT PRIVILEGE, can now act on behalf of user hr and revoke the update privilege from user oe, because oe was granted the privilege by hr:

CONNECT sh
REVOKE UPDATE ON hr.employees FROM oe;

User sh cannot revoke the update privilege from user pm explicitly, because pm received the grant neither from the object owner ( hr), nor from sh, nor from another user with GRANT ANY OBJECT PRIVILEGE, but from user oe. However, the preceding statement cascades, removing all privileges that depend on the one revoked. Therefore the object privilege is implicitly revoked from pm as well.

# data[].attributes.attachment_link.description
Purpose Use the REVOKE statement to: Revoke system privileges from users and roles Revoke roles from users, roles, and program units. Revoke object privileges for a particular object from users and roles Note on Oracle Automatic Storage Management Note on Editionable Objects A REVOKE operation to revoke object privileges on an editionable object actualizes the object in the current edition.

# data[].attributes.body
Un rol es un conjunto de privilegios que se pueden otorgar a usuarios o a otros roles. Puede usar roles para administrar los privilegios de la base de datos.

# data[].attributes.body
Puede agregar privilegios a un rol y luego otorgar el rol a un usuario.

# data[].attributes.body
Es común crear roles de usuario y roles de aplicaciones.

# data[].attributes.attachment_link.readable_text
Purpose

Use the CREATE ROLE statement to create a role, which is a set of privileges that can be granted to users or to other roles. You can use roles to administer database privileges. You can add privileges to a role and then grant the role to a user. The user can then enable the role and exercise the privileges granted by the role.

A role contains all privileges granted to the role and all privileges of other roles granted to it. A new role is initially empty. You add privileges to a role with the GRANT statement.

If you create a role that is NOT IDENTIFIED or is IDENTIFIED EXTERNALLY or BY , then Oracle Database grants you the role with ADMIN OPTION. However, if you create a role IDENTIFIED GLOBALLY, then the database does not grant you the role. A global role cannot be granted to a user or role directly. Global roles can be granted only through enterprise roles.

Prerequisites

You must have the CREATE ROLE system privilege.

To specify the CONTAINER clause, you must be connected to a multitenant container database (CDB). To specify CONTAINER = ALL, the current container must be the root. To specify CONTAINER = CURRENT, the current container must be a pluggable database (PDB).

Specify the name of the role to be created. The name must satisfy the requirements listed in " Database Object Naming Rules". Oracle recommends that the role contain at least one single-byte character regardless of whether the database character set also contains multibyte characters. The maximum length of the role name is 30 bytes. The maximum number of user-defined roles that can be enabled for a single user at one time is 148.

In a non-CDB, a role name cannot begin with C## or c##.

In a CDB, the requirements for a role name are as follows:

The name of a common role must begin with characters that are a case-insensitive match to the prefix specified by the COMMON_USER_PREFIX initialization parameter. By default, the prefix is C##.

The name of a local role must not begin with characters that are a case-insensitive match to the prefix specified by the COMMON_USER_PREFIX initialization parameter. Regardless of the value of COMMON_USER_PREFIX, the name of a local role can never begin with C## or c##.

Note:

If the value of COMMON_USER_PREFIX is an empty string, then there are no requirements for common or local role names with one exception: the name of a local role can never begin with C## or c##. Oracle recommends against using an empty string value because it might result in conflicts between the names of local and common roles when a PDB is plugged into a different CDB, or when opening a PDB that was closed when a common user was created.

Some roles are defined by SQL scripts provided on your distribution media.

See Also:

GRANT for a list of these predefined roles and SET ROLE for information on enabling and disabling roles for a user

NOT IDENTIFIED Clause

Specify NOT IDENTIFIED to indicate that this role is authorized by the database and that no password is required to enable the role.

IDENTIFIED Clause

Use the IDENTIFIED clause to indicate that a user must be authorized by the specified method before the role is enabled with the SET ROLE statement.

BY password

You can create a local role with a password with the BY clause. This means that you must specify the password to the database at the time you enable the role.

The password can contain any characters from the database character set except the " NULL character (CHR(0)) and the double-quote. The password is syntactically an identifier, and may need to be enclosed in double-quotes as required by the Database Object Naming Rules". You must ensure that your database, and the clients that need to enable the role are configured to support all the characters comprising the password.

You can enable password-protected roles in a proxy session. Both secure application role and password-protected roles provide a secure method for enabling a role in a session. Oracle recommends using secure password roles instead of password protected roles in instances where the password has to be maintained and transmitted over insecure channels, or if more than one person needs to know the password. Password-protected roles in a proxy session are suitable for situations where automation is used to set the role.

USING package

The USING clause lets you create a secure application role, which is a role that can be enabled only by applications using an authorized package. If you do not specify , then the database assumes the package is in your own schema.

EXTERNALLY

Specify EXTERNALLY to create an external role. An external user must be authorized by an external service, such as an operating system or third-party service, before enabling the role.

Depending on the operating system, the user may have to specify a password to the operating system before the role is enabled.

GLOBALLY

Specify GLOBALLY to create a global role . A global user must be authorized to use the role by the enterprise directory service before the role is enabled at login.

Specify GLOBALLY with AS to map a directory group to a global role when using centrally managed users. The directory group is identified by its domain name.

Example: Map a Directory User to a Global User

  CREATE USER scott_global IDENTIFIED GLOBALLY AS 'cn=scott taylor,ou=sales,dc=abccorp,dc=com';

This effectively maps a directory user named 'scott taylor' in the 'sales' organization unit of the abccorp.com domain to a database global user 'scott_global'.

CONTAINER Clause

The CONTAINER clause applies when you are connected to a CDB. However, it is not necessary to specify the CONTAINER clause because its default values are the only allowed values.

To create a common role, you must be connected to the root. You can optionally specify CONTAINER = ALL, which is the default when you are connected to the root.

To create a local role, you must be connected to a PDB. You can optionally specify CONTAINER = CURRENT, which is the default when you are connected to a PDB.

Examples

Creating a Role: Example

The following statement creates the role dw_manager:

CREATE ROLE dw_manager;

Users who are subsequently granted the dw_manager role will inherit all of the privileges that have been granted to this role.

You can add a layer of security to roles by specifying a password, as in the following example:

CREATE ROLE dw_manager
   IDENTIFIED BY warehouse;

Users who are subsequently granted the dw_manager role must specify the password warehouse to enable the role with the SET ROLE statement.

The following statement creates global role warehouse_user:

CREATE ROLE warehouse_user IDENTIFIED GLOBALLY;

The following statement creates the same role as an external role:

CREATE ROLE warehouse_user IDENTIFIED EXTERNALLY;

The following statement creates local role role1 in the current PDB. The current container must be a PDB when you issue this statement:

CREATE ROLE role1 CONTAINER = CURRENT;

The following statement creates common role c##role1. The current container must be the root when you issue this statement:

CREATE ROLE c##role1 CONTAINER = ALL;

# data[].attributes.attachment_link.description
Purpose Use the CREATE ROLE statement to create a role, which is a set of privileges that can be granted to users or to other roles. You can use roles to administer database privileges. You can add privileges to a role and then grant the role to a user.

# data[].attributes.attachment_link.readable_text
Purpose

Use the DROP ROLE statement to remove a role from the database. When you drop a role, Oracle Database revokes it from all users and roles to whom it has been granted and removes it from the database. User sessions in which the role is already enabled are not affected. However, no new user session can enable the role after it is dropped.

Prerequisites

You must have been granted the role with the ADMIN OPTION or you must have the DROP ANY ROLE system privilege.

Specify the name of the role to be dropped.

Examples

Dropping a Role: Example

To drop the role " dw_manager, which was created in Creating a Role: Example", issue the following statement:

DROP ROLE dw_manager;

# data[].attributes.attachment_link.description
Purpose Use the DROP ROLE statement to remove a role from the database. When you drop a role, Oracle Database revokes it from all users and roles to whom it has been granted and removes it from the database. User sessions in which the role is already enabled are not affected.

# data[].attributes.body
GRANT SELECT ON tabla TO rol;

# data[].attributes.body
REVOKE SELECT ON tabla TO rol;

# data[].attributes.body
Transparent Data Encryption permite cifrar datos confidenciales como contraseñas o números de tarjetas de crédito.

# data[].attributes.attachment_link.readable_text
Transparent Data Encryption (TDE) tablespace encryption enables you to encrypt an entire tablespace.

All of the objects that are created in the encrypted tablespace are automatically encrypted. TDE tablespace encryption is useful if your tables contain sensitive data in multiple columns, or if you want to protect the entire table and not just individual columns. You do not need to perform a granular analysis of each table column to determine the columns that need encryption.

In addition, TDE tablespace encryption takes advantage of bulk encryption and caching to provide enhanced performance. The actual performance impact on applications can vary.

TDE tablespace encryption encrypts all of the data stored in an encrypted tablespace including its redo data. TDE tablespace encryption does not encrypt data that is stored outside of the tablespace. For example, BFILE data is not encrypted because it is stored outside the database. If you create a table with a BFILE column in an encrypted tablespace, then this particular column will not be encrypted.

All of the data in an encrypted tablespace is stored in encrypted format on the disk. Data is transparently decrypted for an authorized user having the necessary privileges to view or modify the data. A database user or application does not need to know if the data in a particular table is encrypted on the disk. In the event that the data files on a disk or backup media is stolen, the data is not compromised.

TDE tablespace encryption uses the two-tiered, key-based architecture to transparently encrypt (and decrypt) tablespaces. The TDE master encryption key is stored in an external security module (software or hardware keystore). This TDE master encryption key is used to encrypt the TDE tablespace encryption key , which in turn is used to encrypt and decrypt data in the tablespace.

Figure 2-2 shows an overview of the TDE tablespace encryption process.

Note:

The encrypted data is protected during operations such as JOIN and SORT. This means that the data is safe when it is moved to temporary tablespaces. Data in undo and redo logs is also protected.

TDE tablespace encryption also allows index range scans on data in encrypted tablespaces. This is not possible with TDE column encryption.

Oracle Database implements the following features to TDE tablespace encryption:

It uses a unified TDE master encryption key for both TDE column encryption and TDE tablespace encryption.

You can reset the unified TDE master encryption key. This provides enhanced security and helps meet security and compliance requirements.

# data[].attributes.attachment_link.title
Introduction to Transparent Data Encryption

# data[].attributes.attachment_link.description
Transparent Data Encryption (TDE) tablespace encryption enables you to encrypt an entire tablespace. All of the objects that are created in the encrypted tablespace are automatically encrypted. TDE tablespace encryption is useful if your tables contain sensitive data in multiple columns, or if you want to protect the entire table and not just individual columns.