Oracle создание пользователя и настройка прав доступа
Основы Oracle 18c - 19c часть 8 - права доступа, роли, учетные записи
Приведем наиболее часто используемые:
CREATE SESSION – право подключения к БД
ALTER DATABASE – право изменения БД
CREATE TABLESPACE – право создавать табличное пространтсво
ALTER TABLESPACE – право изменять табличное пространтсво
DROP TABLESPACE – право удалять табличное пространтсво
CREATE TABLE – право создавать, изменять, удалять таблицы в своей схеме
INSERT ANYTABLE – право добавлять данные в таблиц, которые не принадлежат учетной записи
UPDATE ANYTABLE – право изменять данные в таблиц, которые не принадлежат учетной записи
DELETE ANYTABLE – право удалять данные в таблиц, которые не принадлежат учетной записи
SELECT ANYTABLE – право выборки данных из таблиц, которые не принадлежат учетной записи
Синтаксис назначения прав:
GRANT privilege [,privilege…] TO User_Name;
Пример создания учетной записи (схемы) User_Name
С паролем User_Pass
Разрешаем занимаемое пространство в 10мб. от пространства по умолчанию USERS
CREATE USER User_Name IDENTIFIED BY User_Pass
DEFAULT TABLESPACE USERS QUOTA 10M ON USERS;
Пример, назначение всех основных привилегий для учетной записи:
GRANT CREATE SESSION, ALTER SESSION,
CREATE TABLE, CREATE VIEW, CREATE TRIGGER, CREATE PROCEDURE,
CREATE CLUSTER, CREATE DATABASE LINK,
CREATE SYNONYM, CREATE SEQUENCE, CREATE TYPE, CREATE OPERATOR
TO User_Name ;
В примере выше разрешено подключаться, настраивать сессию, создавать объекты в БД
Создавать объекты разрешено только в схеме аккаунта
Отсутствуют права к схемам других аккаунтов
Пример предоставления табличного пространства USERS по умолчанию для учетной записи User_Name:
ALTER USER User_Name DEFAULT TABLESPACE USERS
Создание ролей, учетных записей, пароля, связь ролей и учетных записей
Предоставление привилегий
Синтаксис:
GRANT privilege ON [schema.]object TO username [WITH GRANT OPTION];
Информация о имеющихся привилегиях
Отмена, удаление привилегий, ролей, четных записей
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:
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.
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.
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 except for the sample schemas in the seed database.
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
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
Many tasks, with many interwoven considerations, are involved in administering user privileges, roles, and profiles. These necessary operations and principles are discussed in the following sections:
Managing Oracle Users
Each Oracle database has a list of valid database users. To access a database, a user must run a database application and connect to the database instance using a valid user name defined in the database. This section explains how to manage users for a database, and contains the following topics:
Oracle Database SQL Reference for more information about SQL statements used for managing users
Viewing Information About Database Users and Profiles
The wide variety of options that are available for viewing such information are discussed in the following subsections:
Продолжаем работать с SQL Plus! Попробуем сделать следующее, войти пользователем SYSTEM с паролем manager, а затем не закрывая плюс сменим действующего пользователя.
Запускаем плюс, вводим пользователя, пароль и название сетевой службы (proba! или что-то еще!), получилось? Замечательно! Теперь проделаем примерно следующее:
- USER - это пользователь(схема в экземпляре БД).
- PASSWORD - пароль для входа в схему.
- NETWORKSERVICE - имя службы сформированное программой Net8i.
В предыдущем случае мы зашли в схему(пользователя) SCOTT с паролем доступа TIGER с помощью сетевой службы PROBA. Берите сразу на заметку или на память: в Oracle Server в паролях доступа не допускаются цифровые символы! Так же созданный пользователь, создает схему в экземпляре БД и понятие схема и пользователь в Oracle практически тождественны! Сама строка подключения, вами еще не однократно, будет использоваться в дальнейшем! Теперь, я думаю пришло время, создать собственную схему, тем более она нам понадобиться, в дальнейшем, для того, что бы научиться использовать PL/SQL! Первое и самое простое, действие, для создания нашей схемы, ввести следующее:
Для начала заходим на сервер, как администратор.
Вводим ниже приведенную строку, которая создает пользователя MILLER с паролем в системе KOLOBOK (можете написать свое!), который будет жить в табличном пространстве USER владея им целиком и захватив в свое распоряжение еще кусочек табличного пространства TEMP, так на всякий случай, пригодится.
После нажатия Enter на последней строке видим, что все прошло удачно!
Но, это только полдела, теперь этому пользователю, нужно, дать ряд прав и первостепенное, это создавать сессию с сервером! Теперь введем нижеследующее: Можно по очереди или целиком! Главное, чтобы сработал последний опреатор COMMIT. Иначе наши старания пройдут бесследно!
И последнее: "Фиксация обновлений завершена."
Операторы GRANT предоставляют пользователю, определенные привилегии. В типах привилегий пока, предметно разбираться не будем, скажу только, что данное мероприятие можно проделать еще проще, если собрать все строки, которые мы вводили в файл, затем использовав команду START или операцию "@"! Можете проделать это сами, предварительно введя, находясь в схеме SYSTEM:
Затем соберите все строки, в файл, скажем CrMiller.sql, поместите его в каталог, например, Temp, и введите следующее:
Выскочит множество надписей, последняя из которых должна быть: "Фиксация обновлений завершена." Значит, все прошло нормально и пользователь создан! Дальше в схеме MILLER, мы развернем, ряд оьбектов БД и посмотрим как это будет происходить!
Так же на заметку в заключении SQL Plus, есть еще много внутренних команд, например, очень полезной может оказаться SET TIME ON приглашение примет вот такой вид:
Например, можно оценивать время на запрос из таблицы!
Если ввести SET TIME OFF, то все станет по прежнему. Например, если написать SHOW USER (мне напоминает Cisco IOS!), то увидим примерно следующее:
Creating Users
You create a database user with the CREATE USER statement. To create a user, you must have the CREATE USER system privilege. Because it is a powerful privilege, a DBA or security administrator is normally the only user who has the CREATE USER system privilege.
Example 11-1 creates a user and specifies the user password, default tablespace, temporary tablespace where temporary segments are created, tablespace quotas, and profile.
Example 11-1 Create a User and Grant the Create Session System Privilege
A newly created user cannot connect to the database until granted the CREATE SESSION system privilege.
As administrator, you should create your own roles and assign only those privileges that are needed. For example, many users formerly granted the CONNECT privilege did not need the additional privileges CONNECT used to provide. Instead, only CREATE SESSION was actually needed, and in fact that is the only privilege CONNECT presently retains.
Creating its own roles gives an organization detailed control of the privileges it assigns, and protects it in case Oracle changes the roles that it defines. For example, both CONNECT and RESOURCE roles will be deprecated in future Oracle versions.
This section refers to the preceding example as it discusses the following aspects of creating a user:
Granting System Privileges and Roles on page 25-11
Specifying a Name
Within each database, a user name must be unique with respect to other user names and roles. A user and role cannot have the same name. Furthermore, each user has an associated schema. Within a schema, each schema object must have a unique name.
Setting Up User Authentication
In Example 11-1, the new user is to be authenticated using the database. In this case, the connecting user must supply the correct password to the database to connect successfully.
Selecting and specifying the method of user authentication is discussed in "User Authentication Methods".
Assigning a Default Tablespace
Each user should have a default tablespace. When a user creates a schema object and specifies no tablespace to contain it, Oracle Database stores the object in the default user tablespace.
The default setting for the default tablespaces of all users is the SYSTEM tablespace. If a user does not create objects, and has no privileges to do so, then this default setting is fine. However, if a user is likely to create any type of object, then you should specifically assign the user a default tablespace. Using a tablespace other than SYSTEM reduces contention between data dictionary objects and user objects for the same data files. In general, it is not advisable for user data to be stored in the SYSTEM tablespace.
You can create a permanent default tablespace other than SYSTEM at the time of database creation, to be used as the database default for permanent objects. By separating the user data from the system data, you reduce the likelihood of problems with the SYSTEM tablespace, which can in some circumstances cause the entire database to become nonfunctional. This default permanent tablespace is not used by system users, that is, SYS , SYSTEM , and OUTLN , whose default permanent tablespace remains SYSTEM . A tablespace designated as the default permanent tablespace cannot be dropped. To accomplish this goal, another tablespace must first be designated as the default permanent tablespace. It is possible to ALTER the default permanent tablespace to another tablespace, affecting all users or objects created after the ALTER DDL commits.
You can also set a user default tablespace during user creation, and change it later with the ALTER USER statement. Changing the user default tablespace affects only objects created after the setting is changed.
When you specify the default tablespace for a user, also specify a quota on that tablespace.
In Example 11-1, the default tablespace for user jward is data_ts , and his quota on that tablespace is 500K .
Assigning Tablespace Quotas
You can assign each user a tablespace quota for any tablespace (except a temporary tablespace). Assigning a quota does the following things:
Users with privileges to create certain types of objects can create those objects in the specified tablespace.
Oracle Database limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota.
By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, then you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.
You can assign a user either individual quotas for a specific amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces. Specific quotas prevent a user's objects from consuming too much space in the database.
You can assign quotas to a user tablespace when you create the user, or add or change quotas later. If a new quota is less than the old one, then the following conditions hold true:
If a user has already exceeded a new tablespace quota, then the user's objects in the tablespace cannot be allocated more space until the combined space of these objects falls below the new quota.
If a user has not exceeded a new tablespace quota, or if the space used by the user's objects in the tablespace falls under a new tablespace quota, then the user's objects can be allocated space up to the new quota.
Revoking User Ability to Create Objects in a Tablespace
You can revoke the ability of a user to create objects in a tablespace by changing the current quota of the user to zero. After a quota of zero is assigned, the user's objects in the tablespace remain, but new objects cannot be created and existing objects cannot be allocated any new space.
UNLIMITED TABLESPACE System Privilege
To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, then explicit quotas again take effect. You can grant this privilege only to users, not to roles.
Before granting the UNLIMITED TABLESPACE system privilege, you must consider the consequences of doing so.
You can grant a user unlimited access to all tablespaces of a database with one statement.
The privilege overrides all explicit tablespace quotas for the user.
You cannot selectively revoke tablespace access from a user with the UNLIMITED TABLESPACE privilege. You can grant selective or restricted access only after revoking the privilege.
Assigning a Temporary Tablespace
Each user also should be assigned a temporary tablespace . When a user executes a SQL statement that requires a temporary segment, Oracle stores the segment in the user's temporary tablespace. These temporary segments are created by the system when doing sorts or joins and are owned by SYS , which has resource privileges in all tablespaces.
In the previous CREATE USER statement, the temporary tablespace of jward is temp_ts , a tablespace created explicitly to contain only temporary segments. Such a tablespace is created using the CREATE TEMPORARY TABLESPACE statement.
If the temporary tablespace of a user is not explicitly set, then the user is assigned the default temporary tablespace that was specified at database creation, or by an ALTER DATABASE statement at a later time. If there is no default temporary tablespace explicitly assigned, then the default is the SYSTEM tablespace or another permanent default established by the system administrator. It is not advisable for user data to be stored in the SYSTEM tablespace. Also, assigning a tablespace to be used specifically as a temporary tablespace eliminates file contention among temporary segments and other types of segments.
If your SYSTEM tablespace is locally managed, then users must be assigned a specific default (locally managed) temporary tablespace. They may not be allowed to default to using the SYSTEM tablespace because temporary objects cannot be placed in permanent locally managed tablespaces.
You can set the temporary tablespace for a user at user creation, and change it later using the ALTER USER statement. Do not set a quota for temporary tablespaces. You can also establish tablespace groups instead of assigning individual temporary tablespaces.
Multiple Temporary Tablespaces: Using Tablespace Groups
Specifying a Profile
You also specify a profile when you create a user. A profile is a set of limits on database resources and password access to the database. If no profile is specified, then the user is assigned a default profile.
Setting Default Roles
You cannot set default roles for a user in the CREATE USER statement. When you first create a user, the default role setting for the user is ALL , which causes all roles subsequently granted to the user to be default roles. Use the ALTER USER statement to change the default roles for the user.
Altering Users
Users can change their own passwords. However, to change any other option of a user security domain, you must have the ALTER USER system privilege. Security administrators are typically the only users that have this system privilege, as it allows a modification of any user security domain. This privilege includes the ability to set tablespace quotas for a user on any tablespace in the database, even if the user performing the modification does not have a quota for a specified tablespace.
You can alter user security settings with the ALTER USER statement. Changing user security settings affects the future user sessions, not current sessions.
The following statement alters the security settings for the user, avyrros :
The ALTER USER statement here changes the security settings for the user avyrros as follows:
Authentication is changed to use the operating system account of the user avyrros .
The default and temporary tablespaces are explicitly set for user avyrros .
avyrros is given a 100M quota for the data_ts tablespace.
The quota on the test_ts is revoked for the user avyrros .
avyrros is assigned the clerk profile.
Changing User Authentication Mechanism
Most non-DBA users can still change their own passwords with the ALTER USER statement, as follows:
No special privileges (other than those to connect to the database) are required for a user to change passwords. Users should be encouraged to change their passwords frequently.
Users must have the ALTER USER privilege to switch between methods of authentication. Usually, only an administrator has this privilege.
"User Authentication Methods" for information about authentication methods that are available for Oracle Database users
Changing User Default Roles
A default role is one that is automatically enabled for a user when the user creates a session. You can assign a user zero or more default roles.
Dropping Users
When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user schema, if any, are immediately dropped .
If a user schema and associated objects must remain but the user must be denied access to the database, then revoke the CREATE SESSION privilege from the user.
Do not attempt to drop the SYS or SYSTEM user. Doing so will corrupt your database.
A user that is currently connected to a database cannot be dropped. To drop a connected user, you must first terminate the user sessions using the SQL statement ALTER SYSTEM with the KILL SESSION clause.
You can drop a user from a database using the DROP USER statement. To drop a user and all the user schema objects (if any), you must have the DROP USER system privilege. Because the DROP USER system privilege is powerful, a security administrator is typically the only type of user that has this privilege.
If the user's schema contains any dependent schema objects, then use the CASCADE option to drop the user and all associated objects and foreign keys that depend on the tables of the user successfully. If you do not specify CASCADE and the user schema contains dependent objects, then an error message is returned and the user is not dropped. Before dropping a user whose schema contains objects, thoroughly investigate which objects the user's schema contains and the implications of dropping them. Pay attention to any unknown cascading effects. For example, if you intend to drop a user who owns a table, then check whether any views or procedures depend on that particular table.
The following statement drops the user, jones and all associated objects and foreign keys that depend on the tables owned by jones .
Oracle Database Administrator's Guide for more information about terminating sessions
Читайте также: