Oracle 12c создать пользователя
How it works.
In step 1, you used OS authentication to connect to the database.
In step 2, you created a password-authenticated user jessica with simpler password.
In step 3, you created a password-authenticated user tom with more complex password. In this case (because a password contains special characters), you are using quotation marks (") to enclose the password.
Both of these users are using the default password profile.
In step 4, you created a password-authenticated user with the assigned password profile userprofile.
In step 5, you created user john. This user has to change his password at the first database login.
In step 6, you created the user richard. In the create user statement, quota unlimited on users means that you want to let the user allocate space in the tablespace without bound. The quota clause lets you define the maximum amount of space the user can allocate in the tablespace. You can have multiple quota clauses for multiple tablespaces within one create user statement. The unlimited tablespace system privilege enables users to have an unlimited quota on all tablespaces in the database.
Note If you grant unlimited tablespace system privilege to a user and afterwards you revoke it, all explicitly granted quotas will also be revoked.
How to create a user using Oracle EM Express
You can also create users using Oracle Enterprise Manager Cloud Control 12c or Oracle Enterprise Manager Database Express 12c (EM Express). Oracle Enterprise Manager Database Control is no longer available in Oracle Database 12c.
- Start EM Express and log in to it using the user that has either EM_EXPRESS_BASIC or EM_EXPRESS_ALL role (you can use sysor system users, but that isn't recommended):
2. Select Users from the Security drop-down menu:
3. Click on the Create User tab:
4. Enter user details in the pop-up dialog (for example, username: ted , password: oracle_123 , here you can also choose the authentication method, password profile, lock account, expire password) leave the default values and click on the Nextbutton (see image here) as follows:
5. In this step, you can choose default tablespace and temporary tablespace from the drop-down lists. Leave the default values, as shown in the following screenshot:
6. In this step, you can grant privileges to user ted by selecting them in the left pane and moving them to the right pane (use > button). If you want to revoke privileges, do the opposite (select them in right pane and use < button). When you are satisfied with the list of privileges in the right pane (the ones you are going to grant to user ted ), click on the OK button as follows:
7. A pop-up window confirmation should appear with the following message: SQL statement has been processed successfully.
Click on the OK button to close the window.
Oracle CREATE USER examples
Let’s practice with the CREATE USER statement.
1) Using Oracle CREATE USER statement to create a new local user example
This example uses the CREATE USER statement to create a new local user named john with the password abcd1234 :
Oracle issues the following output indicating that user john has been created successfully.
To find a list of users with the OPEN status, you query the information from the dba_users :
As you can see from the output, user john has a default tablespace as USERS , profile as DEFAULT , and log in to the database using a PASSWORD .
Let’s use the john account to log in the database.
Launch the SQL*Plus program and enter the following information:
Oracle issued the following error:
To enable the user john to log in, you need to grant the CREATE SESSION system privilege to the user john by using the following statement:
Now, the user john should be able to log in the database.
Introduction to Oracle CREATE USER statement
The CREATE USER statement allows you to create a new database user which you can use to log in to the Oracle database.
The basic syntax of the CREATE USER statement is as follows:
2) Using Oracle CREATE USER statement to create a new local user with password expired example
First, use the CREATE USER statement to create a new user jane :
Second, verify if the user has been created successfully:
Third, grant the CREATE SESSION privilege to the user jane so that you can use this user to log in the Oracle database.
Finally, use the user jane to log in to the database via the SQL*plus program:
Oracle requested for changing the password for jane , you must provide the new password and confirm it before you can log in:
In this tutorial, you have learned how to use the Oracle CREATE USER statement to create a new user in the Oracle database.
In this task, you will create several users. To complete this recipe, you'll need an existing user who has create user privilege (you may use the OS-authenticated user who has the DBA role).
IDENTIFIED BY password
Specify a password for the local user to use to log on to the database. Note that you can create an external or global user, which is not covered in this tutorial.
DEFAULT TABLESPACE
Specify the tablespace of the objects such as tables and views that the user will create.
If you skip this clause, the user’s objects will be stored in the database default tablespace if available, typically it is USERS tablespace; or the SYSTEM tablespace in case there is no database default tablespace.
QUOTA
Specify the maximum of space in the tablespace that the user can use. You can have multiple QUOTA clauses, each for a tablespace.
Use UNLIMITED if you don’t want to restrict the size in the tablespace that user can use.
Oracle 12c управление пользователями и безопасностью
Многопользовательская среда Oracle 12c очень похожа на архитектуру SQL Server: CDB $ ROOT похож на master, PDB $ SEED похож на модель, а каждая подключаемая база данных эквивалентна обычной бизнес-библиотеке.
Чиновник продвигал множество преимуществ 12c, но он был бесполезен. 12c предусматривал объединение нескольких библиотек, чтобы избежать проблем с производительностью DBLINK, но на самом деле это было достигнуто путем сжатия одного сервера. Другие преимущества, такие как простая миграция PDB, поддержка JSON, группы ресурсов Сжатие индекса, поддержка в памяти и т. Д. - это либо жареный рис, либо несколько сценариев практического применения, но эти новые функции по-прежнему необходимо изучать шаг за шагом.
Первое, что необходимо решить, - это проблема с входом в систему. В многопользовательской среде 12c пользователи отличаются от прежних. Характер предыдущих пользователей такой же. В 12c может быть несколько подключаемых баз данных в одном экземпляре, поэтому управление пользователями Он сильно отличается от предыдущего. В этой статье представлены пользователи 12c и их управление. Взяв за пример версию 12.2.0.1, все взгляды приходят по следующим ссылкам на официальный сайт:
- Простые инструкции по безопасности пользователя
- Создать пользователя
- Сменить пользователя
- Настройте квоту пользовательских ресурсов
- Удалить пользователя
- Словарь данных пользователя и профиля
Каждая база данных имеет свой собственный список пользователей. При создании пользователя вы можете установить ряд ограничений для входа в систему или ресурсов для пользователя или использовать профиль для достижения этой функции. Профиль представляет собой набор атрибутов, принадлежащих пользователю, вы можете просмотреть dba_profiles, чтобы узнать больше.
Кроме того, вы также можете предоставить пользователям ряд разрешений и ролей, см. Configuring Privilege and Role Authorization
- Об обычном и локальном пользователях
В многопользовательской среде обычный пользователь может получить доступ ко всей CDB, а локальный пользователь может получить доступ только к определенной PDB. Отношения между CDB и PDB представлены на официальном веб-сайте:
Можно видеть, что CDB фактически ссылается на все базы данных в данном экземпляре, включая ROOT, Seed и все другие PDB. Весь экземпляр можно рассматривать как контейнер, то есть Container DB (CDB).
на Common user:
Common user ID и пароль Он виден каждой PDB, и обычные пользователи могут работать с PDB, если у них есть соответствующие разрешения.
Common user Обычно используется для работы с PDB в корне, например, для подключения и отключения базы данных PDB, изменения статуса PDB или назначения временного табличного пространства для CDB и т. Д.
Например, обычный пользователь может выполнять следующие операции: создавать или изменять общего / локального пользователя, предоставлять разрешения или роли другим общим / локальным пользователям, выполнять инструкцию восстановления в начале ALTER DATABASE на всем CDB и проходить через инструкцию ALTER PLUGGABLE DATABASE Измените состояние PDB (должно быть подключено к библиотеке CDB $ ROOT). Конечно, локальный пользователь может изменить состояние своей собственной PDB, если у него есть разрешение.
Системные пользователи Oracle, такие как sys, system и т. Д., Являются обычными пользователями, которые могут управлять базой данных ROOT или любой PDB, если только определенная PDB не включает Vault-включена для ограничения разрешений пользователей системы.
Когда вы вставляете базу данных без CDB как PDB в CDB:
- Пользователи системы без CDB будут объединены с обычным пользователем CDB, то есть пользователи системы без CDB будут добавлены в CDB. Если имя пользователя конфликтует, они не будут добавлены. Все пароли этих конфликтующих пользователей системы соответствуют CDB. Оригинальный пароль недействителен.
- Если вы изменили разрешения исходного пользователя системы без CDB, эти дифференциальные разрешения вступят в силу только для этой PDB после слияния и не будут действовать для других PDB. Я не знаю, будет ли меньше разрешений после слияния. При необходимости протестируйте снова.
- Обычный пользователь оригинальной PDB потеряет все свои публичные разрешения, включая разрешения установленного контейнера.
- Если целевой CDB имеет общего пользователя с тем же именем, что и PDB, два общих пользователя объединяются, и общий пользовательский пароль CDB вступает в силу первым. Другие обычные пользователи с другими именами в PDB будут заблокированы. Вы можете сделать любого из этих заблокированных обычных пользователей следующим образом:
- Не работайте с этими заблокированными пользователями, вы все равно можете использовать объекты в этих схемах с соответствующими разрешениями.
- Используйте EXPDP, чтобы экспортировать данные этих пользователей и импортировать их другому пользователю, а затем удалить заблокированного пользователя.
- Выключите PDB, подключитесь к CDB $ ROOT, создайте обычного пользователя с тем же именем, а затем снова откройте PDB. Oracle автоматически обработает разницу разрешений. В это время вы можете разблокировать этих пользователей, и их локальные разрешения остаются без изменений.
Локальный пользователь - это пользователь, который существует только в PDB Локальный пользователь может иметь права управления, но такие разрешения действительны только для PDB, где расположен локальный пользователь.
- Локальные пользователи не могут создавать обычных пользователей и не могут получать разрешения в cdb $ root. Обычный пользователь может создавать и изменять общих / локальных пользователей, если у них есть соответствующие разрешения, и может предоставлять публичные / локальные разрешения на предоставление / отзыв. Если локальный пользователь имеет достаточные разрешения, вы можете создать и изменить локального пользователя или предоставить разрешения обычному / локальному пользователю в PDB.
- Вы можете предоставить локальным пользователям публичные роли или разрешения (аналогично выбору любой таблицы), но разрешения, содержащиеся в этих общих ролях или разрешениях, действительны только для той PDB, к которой они принадлежат.
- Локальный пользователь должен быть уникальным в PDB.
- Если локальный пользователь имеет соответствующие разрешения, он также может получить доступ к объектам обычного пользователя.
- Локальные пользователи могут быть созданы только в PDB, и только локальные пользователи могут быть созданы в PDB, а не обычные пользователи.
- Вы можете выбрать, следует ли отключить локального пользователя в соответствии с версией, но вы не можете управлять обычным пользователем таким образом.
- Создать пользовательский пример
Создать локального пользователя:
Вообще говоря, нет необходимости создавать обычного пользователя, отличного от системы, или нужно только создавать обычного пользователя, который не имеет права управлять конкретной PDB. Обычный пользователь не должен использоваться для работы с определенными бизнес-данными. Как заявлено официальным, обычный пользователь обычно Операция должна быть:
Создайте или измените общего / локального пользователя, предоставьте разрешения или роли другим общим / локальным пользователям, выполните инструкцию восстановления в начале ALTER DATABASE на всем CDB и измените состояние PDB с помощью инструкции ALTER PLUGGABLE DATABASE (должен быть подключен к CDB $ ROOT когда библиотека).
Давайте рассмотрим частный случай, если нам нужно получить доступ к данным в другой PDB в одной PDB?
Невозможно использовать локального пользователя, потому что локальный пользователь полностью изолирован от PDB. Даже если локальный пользователь получает публичные разрешения, такие как dba, и выбирает любую таблицу, он действителен только в PDB и недоступен для всех библиотек, поэтому может использоваться только обычный пользователь.
Далее приведен пример общего доступа пользователей через PDB:
Хотите использовать пункт КОНТЕЙНЕРЫ должны соответствовать следующим условиям:
1. Таблица, представление или синоним в предложении CONTAINERS должны существовать в ROOT и во всех PDB.
2. Владелец таблиц и представлений, включенных в предложение CONTAINERS, должен быть обычным пользователем, выполняющим инструкцию SQL, если он является синонимом, то этим синонимом должна быть таблица или представление в соответствии с указанным общим пользователем.
3. SQL, содержащий предложение CONTAINERS, должен выполняться с использованием обычного пользователя в базе данных CDB $ ROOT.
Если таблица, включенная в предложение CONTAINERS, имеет следующие типы, она будет автоматически отфильтрована:
Прочитав приведенные выше 5 требований, я неожиданно обнаружил, что эти требования очень странные: если таблица, представление или синоним в предложении CONTAINERS должны существовать в ROOT и во всех PDB, то почему я должен пересекать запросы PDB непосредственно в ROOT? Вы не должны?
Немного подумав и потренировавшись, я обнаружил, что условия использования условия CONTAINERS должны быть:
- Необходимо использовать обычный пользователь для запроса в CDB $ ROOT
- Ввод метода CONTAINERS () представляет собой таблицу, представление или синоним. Этот объект должен иметь объект с таким же именем и структурой в CDB $ ROOT. Будь то таблица, представление или синоним, таблица не может иметь данных.
- Вы должны использовать обычного пользователя, который выполняет этот запрос, чтобы создать представление с тем же именем в PDB, где находится таблица. Это требует, чтобы обычный пользователь имел разрешение запрашивать эту таблицу.
Тогда приведенный выше пример может легко выполнить кросс-PDB-запрос:
Кроме того, пароль обычного пользователя можно изменить с помощью инструкции alter user, а пароль sys можно изменить с помощью инструмента orapwd:
Use the CREATE USER statement to create and configure a database user , which is an account through which you can log in to the database, and to establish the means by which Oracle Database permits access by the user.
You can issue this statement in an Oracle Automatic Storage Management (Oracle ASM) cluster to add a user and password combination to the password file that is local to the Oracle ASM instance of the current node. Each node's Oracle ASM instance can use this statement to update its own password file. The password file itself must have been created by the ORAPWD utility.
You can enable a user to connect to the database through a proxy application or application server. For syntax and discussion, refer to ALTER USER.
You must have the CREATE USER system privilege. When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle Database, a user must have the CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION system privilege. Refer to GRANT for more information.
Only a user authenticated AS SYSASM can issue this command to modify the Oracle ASM instance password file.
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 user to be created. This name can contain only characters from your database character set and must follow the rules described in the section "Database Object Naming Rules". Oracle recommends that the user name contain at least one single-byte character regardless of whether the database character set also contains multibyte characters.
In a CDB, the requirements for a user name are as follows:
Starting with Oracle Database 12 c Release 1 (12.1.0.2):
Oracle recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform.
The IDENTIFIED clause lets you indicate how Oracle Database authenticates the user.
Oracle Database Security Guide for more information about case-sensitive passwords, password complexity, and other password guidelines
Passwords must follow the rules described in the section "Database Object Naming Rules", unless you are using one of the three Oracle Database password complexity verification routines. These routines requires a more complex combination of characters than the normal naming rules permit. You implement these routines with the UTLPWDMG.SQL script, which is further described in Oracle Database Security Guide .
Oracle recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform.
Oracle Database Security Guide to for a detailed discussion of password management and protection
Specify EXTERNALLY to create an external user. Such a user must be authenticated by an external service, such as an operating system or a third-party service. In this case, Oracle Database relies on authentication by the operating system or third-party service to ensure that a specific external user has access to a specific database user.
AS ' certificate_DN ' This clause is required for and used for SSL-authenticated external users only. The certificate_DN is the distinguished name in the user's PKI certificate in the user's wallet. The maximum length of certificate_DN is 1024 characters.
AS ' kerberos_principal_name ' This clause is required for and used for Kerberos-authenticated external users only. The maximum length of kerberos_principal_name is 1024 characters.
Oracle strongly recommends that you do not use IDENTIFIED EXTERNALLY with operating systems that have inherently weak login security.
Restrictions on Creating External Users The following restrictions apply to creating external users:
The user SYS cannot be an external user.
Oracle ASM does not support the creation of external users.
The GLOBALLY clause lets you create a global user . Such a user must be authorized by the enterprise directory service (Oracle Internet Directory).
The directory_DN string can take one of two forms:
The X.509 name at the enterprise directory service that identifies this user. It should be of the form CN= username,other_attributes , where other_attributes is the rest of the user's distinguished name (DN) in the directory. This form creates a private global schema .
A null string (' ') indicating that the enterprise directory service will map authenticated global users to this database schema with the appropriate roles. This form is the same as specifying the GLOBALLY keyword alone and creates a shared global schema .
The maximum length of directory_DN is 1024 characters.
You can control the ability of an application server to connect as the specified user and to activate that user's roles using the ALTER USER statement.
Restriction on Creating Global Users Oracle ASM does not support the creation of global users.
Oracle Database Security Guide for more information on global users
DEFAULT TABLESPACE Clause
Specify the default tablespace for objects that are created in the user's schema. If you omit this clause, then the user's objects are stored in the database default tablespace. If no default tablespace has been specified for the database, then the user's objects are stored in the SYSTEM tablespace.
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.
CREATE TABLESPACE for more information on tablespaces in general and undo tablespaces in particular
Oracle Database Security Guide for more information on assigning default tablespaces to users
TEMPORARY TABLESPACE Clause
Specify the tablespace or tablespace group for the user's temporary segments. If you omit this clause, then the user's temporary segments are stored in the database default temporary tablespace or, if none has been specified, in the SYSTEM tablespace.
Specify tablespace to indicate the user's 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 .
Restrictions on Temporary Tablespace This clause is subject to the following restrictions:
The tablespace must be a temporary tablespace and must have a standard block size.
The tablespace cannot be an undo tablespace or a tablespace with automatic segment-space management.
Oracle Database Administrator's Guide for information about tablespace groups and Oracle Database Security Guide for information on assigning temporary tablespaces to users
CREATE TABLESPACE for more information on undo tablespaces and segment management
Use the QUOTA clause to specify the maximum amount of space the user can allocate in the tablespace.
A CREATE USER statement can have multiple QUOTA clauses for multiple tablespaces.
UNLIMITED lets the user allocate space in the tablespace without bound.
The maximum amount of space that you can specify is 2 terabytes (TB). If you need more space, then specify UNLIMITED .
Restriction on the QUOTA Clause You cannot specify this clause for a temporary tablespace.
size_clause for information on that clause and Oracle Database Security Guide for more information on assigning tablespace quotas
Specify the profile you want to assign to the user. The profile limits the amount of database resources the user can use. If you omit this clause, then Oracle Database assigns the DEFAULT profile to the user.
Oracle recommends that you use the Database Resource Manager rather SQL profiles to establish database resource limits. The Database Resource Manager offers a more flexible means of managing and tracking resource use. For more information on the Database Resource Manager, refer to Oracle Database Administrator's Guide.
PASSWORD EXPIRE Clause
Specify PASSWORD EXPIRE if you want the user's password to expire. This setting forces the user or the DBA to change the password before the user can log in to the database.
Specify ACCOUNT LOCK to lock the user's account and disable access. Specify ACCOUNT UNLOCK to unlock the user's account and enable access to the account. The default is ACCOUNT UNLOCK .
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 schemas that are not editions-enabled cannot be editioned.
Restriction on Enabling Editions You cannot enable editions for any schemas supplied by Oracle except for the sample schemas in the seed database.
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 user, 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 user, you must be connected to a PDB. You can optionally specify CONTAINER = CURRENT , which is the default when you are connected to a PDB.
While creating a common user, any default tablespace, temporary tablespace, or profile specified using the following clauses must exist in all the containers belonging to the CDB:
If these objects do not exist in all the containers, the CREATE USER statement fails.
All of the following examples use the example tablespace, which exists in the seed database and is accessible to the sample schemas.
Creating a Database User: Example If you create a new user with PASSWORD EXPIRE , then the user's password must be changed before the user attempts to log in to the database. You can create the user sidney by issuing the following statement:
The user sidney has the following characteristics:
The password out_standing1
Default tablespace example , with a quota of 10 megabytes
Temporary tablespace temp
Access to the tablespace SYSTEM , with a quota of 5 megabytes
Limits on database resources defined by the profile app_user (which was created in "Creating a Profile: Example")
An expired password, which must be changed before sidney can log in to the database
Creating External Database Users: Examples The following example creates an external user, who must be identified by an external source before accessing the database:
The user app_user1 has the following additional characteristics:
Default tablespace example
Default temporary tablespace example
5M of space on the tablespace example and unlimited quota on the temporary tablespace of the database
Limits on database resources defined by the app_user profile
To create another user accessible only by an operating system account, prefix the user name with the value of the initialization parameter OS_AUTHENT_PREFIX . For example, if this value is " ops$ ", then you can create the externally identified user external_user with the following statement:
Creating a Global Database User: Example The following example creates a global user. When you create a global user, you can specify the X.509 name that identifies this user at the enterprise directory server:
Use the CREATE USER statement to create and configure a database user , which is an account through which you can log in to the database, and to establish the means by which Oracle Database permits access by the user.
You can issue this statement in an Oracle Automatic Storage Management (Oracle ASM) cluster to add a user and password combination to the password file that is local to the Oracle ASM instance of the current node. Each node's Oracle ASM instance can use this statement to update its own password file. The password file itself must have been created by the ORAPWD utility.
You can enable a user to connect to the database through a proxy application or application server. For syntax and discussion, refer to ALTER USER.
You must have the CREATE USER system privilege. When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle Database, a user must have the CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION system privilege. Refer to GRANT for more information.
Only a user authenticated AS SYSASM can issue this command to modify the Oracle ASM instance password file.
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 user to be created. This name can contain only characters from your database character set and must follow the rules described in the section "Database Object Naming Rules" . Oracle recommends that the user name contain at least one single-byte character regardless of whether the database character set also contains multibyte characters.
A multitenant container database is the only supported architecture in Oracle Database 20c. While the documentation is being revised, legacy terminology may persist. In most cases, "database" and "non-CDB" refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, "non-CDB" refers to a non-CDB from a previous release.
In a CDB, the requirements for a user name are as follows:
Oracle recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform.
The IDENTIFIED clause lets you indicate how Oracle Database authenticates the user.
Oracle Database Security Guide for more information about case-sensitive passwords, password complexity, and other password guidelines
Passwords must follow the rules described in the section "Database Object Naming Rules" , unless you are using one of the three Oracle Database password complexity verification routines. These routines requires a more complex combination of characters than the normal naming rules permit. You implement these routines with the UTLPWDMG.SQL script, which is further described in Oracle Database Security Guide .
Oracle recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform.
Oracle Database Security Guide to for a detailed discussion of password management and protection
You cannot specify this clause for external or global users.
Specify EXTERNALLY to create an external user. Such a user must be authenticated by an external service, such as an operating system or a third-party service. In this case, Oracle Database relies on authentication by the operating system or third-party service to ensure that a specific external user has access to a specific database user.
This clause is required for and used for SSL-authenticated external users only. The certificate_DN is the distinguished name in the user's PKI certificate in the user's wallet. The maximum length of certificate_DN is 1024 characters.
This clause is required for and used for Kerberos-authenticated external users only. The maximum length of kerberos_principal_name is 1024 characters.
Oracle strongly recommends that you do not use IDENTIFIED EXTERNALLY with operating systems that have inherently weak login security.
Restriction on Creating External Users
Oracle ASM does not support the creation of external users.
The GLOBALLY clause lets you create a global user . Such a user must be authorized by the enterprise directory service (Oracle Internet Directory).
The directory_DN string can take one of two forms:
The X.509 name at the enterprise directory service that identifies this user. It should be of the form CN= username,other_attributes , where other_attributes is the rest of the user's distinguished name (DN) in the directory. This form uses the LDAP Data Interchange Format (LDIF) and creates a private global schema .
A null string (' ') indicating that the enterprise directory service will map authenticated global users to this database schema with the appropriate roles. This form is the same as specifying the GLOBALLY keyword alone and creates a shared global schema .
The maximum length of directory_DN is 1024 characters.
You can control the ability of an application server to connect as the specified user and to activate that user's roles using the ALTER USER statement.
Restriction on Creating Global Users
Oracle ASM does not support the creation of global users.
Oracle Database Security Guide for more information on global users
NO AUTHENTICATION Clause
Use the NO AUTHENTICATION clause to create a schema that does not have a password and cannot be logged into. This is intended for schema only accounts and reduces maintenance by removing default passwords and any requirement to rotate the password.
DEFAULT COLLATION Clause
This clause lets you specify the default collation for the schema owned by the user. The default collation is assigned to tables, views, and materialized views that are subsequently created in the schema.
For collation_name , specify a valid named collation or pseudo-collation.
If you omit this clause, then the default collation for the schema owned by the user is set to the USING_NLS_COMP pseudo-collation.
You can override this clause and assign a different default collation to a particular table, materialized view, or view by specifying the DEFAULT COLLATION clause of the CREATE or ALTER statement for the table, materialized view, or view. You can also override the default collations of all schemas for the duration of a database session by setting the default collation for the session. See the DEFAULT_COLLATION clause of ALTER SESSION for more details.
You can specify the DEFAULT COLLATION clause only if the COMPATIBLE initialization parameter is set to 12.2 or greater, and the MAX_STRING_SIZE initialization parameter is set to EXTENDED .
DEFAULT TABLESPACE Clause
Specify the default tablespace for objects that are created in the user's schema. If you omit this clause, then the user's objects are stored in the database default tablespace. If no default tablespace has been specified for the database, then the user's objects are stored in the SYSTEM tablespace.
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.
CREATE TABLESPACE for more information on tablespaces in general and undo tablespaces in particular
Oracle Database Security Guide for more information on assigning default tablespaces to users
[LOCAL] TEMPORARY TABLESPACE Clause
Specify the tablespace or tablespace group for the user's temporary segments. If you omit this clause, then the user's temporary segments are stored in the database default temporary tablespace or, if none has been specified, in the SYSTEM tablespace.
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.
Restrictions on Temporary Tablespace
This clause is subject to the following restrictions:
The tablespace must be a temporary tablespace and must have a standard block size.
The tablespace cannot be an undo tablespace or a tablespace with automatic segment-space management.
Oracle Database Administrator's Guide for information about tablespace groups and Oracle Database Security Guide for information on assigning temporary tablespaces to users
CREATE TABLESPACE for more information on undo tablespaces and segment management
Use the QUOTA clause to specify the maximum amount of space the user can allocate in the tablespace.
A CREATE USER statement can have multiple QUOTA clauses for multiple tablespaces.
UNLIMITED lets the user allocate space in the tablespace without bound.
The maximum amount of space that you can specify is 2 terabytes (TB). If you need more space, then specify UNLIMITED .
Restriction on the QUOTA Clause
You cannot specify this clause for a temporary tablespace.
size_clause for information on that clause and Oracle Database Security Guide for more information on assigning tablespace quotas
Specify the profile you want to assign to the user. The profile limits the amount of database resources the user can use. If you omit this clause, then Oracle Database assigns the DEFAULT profile to the user.
You can use the CREATE USER statement to create a new user, and associate the user with a profile that has the PASSWORD_ROLLOVER_TIME configured.
You must first set the password rollover period using CREATE PROFILE or ALTER PROFILE .
In the example u1 is the user, with password p1 . prof1 is the profile with PASSWORD_ROLLOVER_TIME set.
Oracle recommends that you use the Database Resource Manager to establish database resource limits rather than SQL profiles. The Database Resource Manager offers a more flexible means of managing and tracking resource use. For more information on the Database Resource Manager, refer to Oracle Database Administrator's Guide.
PASSWORD EXPIRE Clause
Specify PASSWORD EXPIRE if you want the user's password to expire. This setting forces the user or the DBA to change the password before the user can log in to the database.
Specify ACCOUNT LOCK to lock the user's account and disable access. Specify ACCOUNT UNLOCK to unlock the user's account and enable access to the account. The default is ACCOUNT UNLOCK .
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 schemas that are not editions-enabled cannot be editioned.
Note the following before enabling editions with ALTER USER :
Enabling editions is not a live operation.
When a database is upgraded from Release 11.2 to Release 12.1, users who were enabled for editions in the pre-upgrade database are enabled for editions in the post-upgrade database and the default schema object types are editionable in their schemas. The default schema object types are displayed by the static data dictionary view DBA_EDITIONED_TYPES . Users who were not enabled for editions in the pre-upgrade database are not enabled for editions in the post-upgrade database and no schema object types are editionable in their schemas.
To see which users already have editions enabled, see the EDITIONS_ENABLED column of the static data dictionary view DBA_USERS or USER_USERS .
Restriction on Enabling Editions
The FOR clause is ignored when used with ENABLE EDITIONS . This only applies to the CREATE USER statement, not the ALTER USER statement.
You cannot enable editions for any schemas supplied by Oracle.
Oracle Database Reference for more information about the V$EDITIONABLE_TYPES dynamic performance view
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 user, 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 user, you must be connected to a PDB. You can optionally specify CONTAINER = CURRENT , which is the default when you are connected to a PDB.
While creating a common user, any default tablespace, temporary tablespace, or profile specified using the following clauses must exist in all the containers belonging to the CDB:
If these objects do not exist in all the containers, the CREATE USER statement fails.
All of the following examples use the example tablespace, which exists in the seed database and is accessible to the sample schemas.
Creating a Database User: Example
If you create a new user with PASSWORD EXPIRE , then the user's password must be changed before the user attempts to log in to the database. You can create the user sidney by issuing the following statement:
The user sidney has the following characteristics:
The password out_standing1
Default tablespace example , with a quota of 10 megabytes
Temporary tablespace temp
Access to the tablespace SYSTEM , with a quota of 5 megabytes
Limits on database resources defined by the profile app_user (which was created in "Creating a Profile: Example" )
An expired password, which must be changed before sidney can log in to the database
Creating External Database Users: Examples
The following example creates an external user, who must be identified by an external source before accessing the database:
The user app_user1 has the following additional characteristics:
Default tablespace example
Default temporary tablespace example
5M of space on the tablespace example and unlimited quota on the temporary tablespace of the database
Limits on database resources defined by the app_user profile
To create another user accessible only by an operating system account, prefix the user name with the value of the initialization parameter OS_AUTHENT_PREFIX . For example, if this value is " ops$ ", then you can create the externally identified user external_user with the following statement:
Creating a Global Database User: Example
The following example creates a global user. When you create a global user, you can specify the X.509 name that identifies this user at the enterprise directory server:
Creating a Common User in a CDB
Summary: in this tutorial, you will learn how to use the Oracle CREATE USER statement to create a new user in the Oracle database.
CREATE USER username
Specify the name of the user to be created.
Create password-authenticated user in SQL command line
1. Connect to the database as a user who has create user privilege:
2. Create a password-authenticated user (for example, username: jessica, password: oracle_1) as follows:
3. Create a password-authenticated user with a more complex password:
4. Create a user that uses a specific password profile:
5. Create a user and force it to change password upon the first login:
6. Create a user richard, whose default tablespace is users, temporary tablespace is temp, and who has their quota set to unlimited on the users tablespace:
PROFILE profile
A user profile limits the database resources or password that the user cannot exceed. You can assign a profile to a newly created user. If you skip this clause, Oracle will assign the DEFAULT profile to the user.
PASSWORD EXPIRE
Use the PASSWORD EXPIRE if you want to force the user to change the password for the first time the user logs in to the database.
ACCOUNTUse ACCOUNT LOCK if you want to lock user and disable access. On the other hand, specify ACCOUNT UNLOCK to unlock user and enable access.
To execute the CREATE USER statement, you must have the CREATE USER system privilege. Once you create the new user, the privilege domain of the user will be empty. Therefore, if you want to the user to be able to login to the database, you should grant the CREATE SESSION system privilege to the user.
Читайте также: