Рубрика: Администрирование СУБД

Создание и управление аккаунтами пользователей

• Пользователи подключаются к аккаунту, который сгруппирован со схемой
• Пользователи должны аутентифицироваться перед подключением
• У пользователя должен быть установлен лими на табличное пространтсво перед созданием объектов
• Пользователь у когорого созданы объекты не может быть удален без использование директивы CASCADE

 

Назначение и отзыв прав

• По умолчание пользователь не может даже подключаться к БД
• Напрямую назначенные права всегда доступны
• Удаление системных привилегий не работает каскадно, объектные привилегии отзываются каскадно

 

Создание и управление ролями

• Роль – не объект схемы
• Роль содержит и системные и объектные привилегии, также роли наследуются
• Роль может быть включена и отключена для сессии

 

Создание и управление профилями

• Профиль управляет паролями и ресурсами системы
• Ограничения на пароли работают всегда, ограничения ресурсов зависят от параметра экземпляра
• Каждый пользователь имеет профиль, по умочланию DEFAULT

 

Безопасность и принцип минимума прав

• Все что прямо не разрешено должно быть запрещено
• Администратор базы и системный администратор – это две разные обязанности и в идеале должны быть разные люди
• Права роли PUBLIC необходимо отслеживать
• Параметры экземпляра влияющие на безопасность необходимо контролировать и они не могут быть изменены без перезапуска экземпляра

 

Аудит

• Аудит может быть настроен на права, запросы или объекты
• Данные аудита можно записывать в БД или в файл
• Записи аудита в БД хранятся в SYS.AUD$
• FGA может быть настроена для конкретных строк и столбцов
• Аудит можно реализовать используя триггеры

Наиболее безопасный прицип для для определения доступа к компьютерным системам это принцип минимума прав (least privilege): никто не должен иметь доступ больше, чем абсолютный минимум необходимый для выполнения работы и доступно только то – что разрешено, всё остальное недосутпно. Oracle придерживается этого принципа. По умолчанию никто (за исключением системных аккаунтов SYSTEM и SYS) не имеют прав ни на что. Ни один аккаунт не может даже подключиться. В дополнение к использованию профилей, лучшей практикой в достижении принципа наименьших привилегий рекомендуется ограничивать права роли PUBLIC и устанавливать параметр экземпляра.

Права PUBLIC

Роль PUBLIC назначается всем пользователям. Любые права назначенные PUBLIC назначаются всем кто может подключиться к БД. По умолчанию у роли PUBLIC достаточно много прав. В частности у этой роли есть права на выполнение PL/SQL пакетов, как показано на рисунке 6-9.

Вы должны рассматривать возможность отзыва прав на выполнение UTL пакетов, но помнить что некоторые приложения рассчитывают на наличие этих прав. Права на выполнение могут быть отозваны к примеру таким запросом

 

REVOKE EXECUTE ON UTL_FILE FROM PUBLIC;

 

 

У профиля два назначения: управление использованием пароля и ограничением на кол-во используемых ресурсов сессии. Проверка использования пароля всегда включена, проверка использования ресурсов доступна только при включенном параметре RESOURCE_LIMIT=TRUE (по-умолчанию выключено). Профили используются по умолчанию, но профили по умолчанию (применяются ко всем пользователям, в том числе и к SYS и SYSTEM) проверяют очень мало.

EXAM TIP

Profile password limits are always enforced; profile resource limits

are enforced only if the instance parameter RESOURCE_LIMIT is TRUE.

Управление паролями

К паролю могут быть применены следующие ограничения

• FAILED_LOGIN_ATTEMPTS – устанавливает число последовательно неправильных вводов пароля перед блокировкой аккаунта. Если вводится корректный пароль до блокировки, счётчик обнуляется
• PASSWORD_LOCK_TIME – устанавливает количество (в днях) до блокировки аккаунта после FAILED_LOGIN_ATTEMPTS
• PASSWORD_LIFE_TIME – количество (в днях) до окончания жизни пароля. После этого пароль ещё может быть активным какое-то время в зависимости от PASSWORD_GRACE_TIME
• PASSWORD_GRACE_TIME – количество (в днях) после первой успешной попытки подключения после окончания жизни пароля пока будет выводиться предложение сменить пароль. Старый пароль действителен в этом время
• PASSWORD_REUSE_TIME – Количество дней перед тем как пароль может быть использован заново (значение пароля)
• PASSWORD_REUSE_MAX – устанавливает сколько раз может быть использован один и тот же пароль
• PASSWORD_VERIFY_FUNCTION – имя функции для выполнения при смене пароля. Целью предполагается проверка нового пароля на необходимый уровень безопасности, но вы можете использовать для любых целей.

Ограничение ресурсов

 Ограничениями накладывами на использование ресурсов сервера (kernel limits) являются

SESSION_PER_USER количество параллельных подключений которые может создать один и тот же аккаунт. Сессии по достижении этого значения будут блокироваться

CPU_PER_SESSION – время процессора (в сентисекундах) которое серверный процесс сессии может использовать до принудительного завершения

CPU_PER_CALL — время процессора (в сентисекундах) которое серверный процесс может использовать для выполнения одной SQL команды до принудительной остановки выполнения запроса

LOGICAL_READS_PER_SESSION – количество блоков которое может считать сессия (независимо из буффера или с диска) перед принудительным отключением сессии

LOGICAL_READS_PER_CALL – количество блоков которое может считать запрос (независимо из буффера или с диска) перед принудительной остановкой выполнения запроса

PRIVATE_SGA – для сессий подключенных используя архитектуру общего сервера, количесво килобайт которое сессия может использоватья в SGA

CONNECT_TIME – в минутах, масимальная длительность сессии

IDLE_TIME – в минутах, длительность бездействия по достижении которой сессия принудительно отключается

COMPOSITE_LIMIT – взвешенная сумма других атрибутов. Не рассматриваем в этом курсе

Все ограничения по использования ресурсов не будут выполняться до установки параметра экземпляра RESOURCE_LIMIT. По умолчанию значение FALSE. Включить можно выполнив запрос

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

Когда сессия отключается из-за достижения лимита, если была неподтвержденная транзакция – она отменяется. Если останавливается выполнение запроса – то изменения сделанные этим запросом отменяются, но все выполненные команды до этого остаются в силе и неподтверждёнными.

TIP

Profiles can be used to limit resource usage, but a much more

sophisticated tool is the Resource Manager

Создание и назначение профилей

Управлять профилями можно через Database Control или SQL *Plus. Для просмотра текущих профилей пользователей выполните запрос

 

select username,profile from dba_users;

 

По умолчанию все аккаунты (за исключением двух внутренних аккаунтов DBSNMP и WKSYS) будут использовать профиль DEFAULT. Запрос для просмотра профиля

 

select * from dba_profiles where profile=’DEFAULT’;

 

Профиль DEFAULT не имеет ограничений на ресурсы, но существуют несколько ограничения на пароли

Эти ограничения не слишком сильные: пароль может вводится неверно 10 раз перед блокировкой аккаунта на один день, и пароль истекает через полгода с грейс периодом в неделю. Простейший способ применить более строгие ограничения к паролям это выполнить скрипт поставляемый с Oracle $ORACLE_HOME/rdbms/admin/utlpwdmg.sql. Этот скрипт создаст функции VERIFY_FUNCTION и VERIFY_FUNCTION_11G и затем выполните запрос

ALTER PROFILE DEFAULT LIMIT

PASSWORD_LIFE_TIME 180

PASSWORD_GRACE_TIME 7

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_REUSE_MAX UNLIMITED

FAILED_LOGIN_ATTEMPTS 10

PASSWORD_LOCK_TIME 1

PASSWORD_VERIFY_FUNCTION verify_function_11G;

 

Эта команда изменит профиль DEFAULT. Все пользователи с профилем DEFAULT (все пользователи по умолчанию) сразу подхватят новые значения. Единственным изменение будет использование функции verify_function_11G. Эта функция будет проверять пароль на соответствие определённым критериям, а именно

• Новый пароль должен быть длинной не менее 8 символов
• Пароль не должен совпадать с именем пользователя
• Часто использумые простые пароли (к примеру oracle) будут отклонены
• Новый пароль должен содержать минимум одну заглавную букву и одну цифру
• Пароль должен отличаться минимум в три символа от предыдущего

Можно рассмотреть эту функцию как пример и изменить согласно вашим требованиям. Вообще желательно создать отдельные профили для разныз групп пользователей.

Для создания профиля используется команду CREATE PROFILE, устанавливая необходимые ограничения. Неустановленные значения будут выставлены в зависимости от значений в профиле DEFAULT. Например рассмотрим сценарий где пользователи могут открывать только одну сессию, администраторы могут создавать сколько угодно сессий и должны менять пароль раз в неделю с грейс периодом в день, а программисты могут открывать две сессии. Для этого можно выполнить команды

alter profile default limit sessions_per_user 1;

Затем создать профиль dba_profile и назначить его пользователю system

create profile dba_profile limit sessions_per_user unlimited password_life_time 7 password_grace_time 1;

alter user system profile dba_profile;

И создать профиль для программистов

create profile programmers_profile limit sessions_per_user 2;

alter user jon profile programmers_profile;

alter user sue profile programmers_profile;

Для вступления ограничений по ресурсам в силу необходимо установить параметр экземпляра

alter system set resource_limit=true;

Если экземпляр использует SPFILE это изменение применится к файлу параметров и будет использоваться даже после перезапуска.

Профиль нельзя удалить если он назначен какому-либо пользователю. Можно либо вначале перевести пользователей на другой профиль, либо использовать директиву CASCADE использование которой автоматически переназначит пользователей использующих удаляемый профиль назад на профиль DEFAULT.

Управление безопасностью через назначение прав напрямую пользователям имеет место быть но имеет два недостатка. Во первых это может быть затратной задачей: приложения с тысячами таблиц и пользователей требуют миллионов разрешений. Во вторых, если права были выданы пользователю, то они работают всегда вне зависимости от обстоятельств. Обе эти проблемы решаются использованием ролей (roles). Роль – это набор системных и объектных прав, которые могут быть выданы и отозваны как единое целое, и после добавления этой роли, могут временно быть активированы и деактивированы во время существования сессии.

Создание и назначение ролей

Роль – это не объект схемы: у них нет владельца и поэтому нет префикса владельца. Но они используют ту же область хранения как и пользователи: поэтому невозможно создать роль с именем уже существующего пользователя и наоборот.

Для создания роли используется команды

CREATE ROLE rolename;

Затем права назначаются для роли используя обычные синтаксис команд включая WITH ADMIN или WITH GRANT OPTION если нобходимо.

Например преположим что схема HR используется как хранидище данных для трех групп пользователей. Управляющие персонал имеет полные доступ, менеджера среднего звена имеют ограниченный доступ, и менеджмент низшего уровня имеет очень ограниченный доступ. Для начала создадим роль для низшего уровня: всё что они могут делать это выполнять запросы к таблицам

create role hr_junior;

grant create session to hr_junior;

grant select on hr.regions to hr_junior;

grant select on hr.locations to hr_junior;

grant select on hr.countries to hr_junior;

grant select on hr.departments to hr_junior;

grant select on hr.job_history to hr_junior;

grant select on hr.jobs to hr_junior;

grant select on hr.employees to hr_junior;

Каждый пользователь назначенный на эту роль имеет доступ для подключения к БД и выполнению SELECT команд к таблицам HR. Затем создадим роль для среднего звена, кто также может изменять данные в таблицах EMPLOYEES и JOB_HISTORY

create role hr_senior;

grant hr_junior to hr_senior with admin option;

grant insert, update, delete on hr.employees to hr_senior;

grant insert, update, delete on hr.job_history to hr_senior;

Эта роль вначале наследует роль HR_JUNIOR (можно наследовать роль от роли) с возможностью назначать роль junior другим аккаунтам или ролям. Затем назначаются права на выполнение DML команд для двух таблиц. И наконец создадим роль топ менеджмента, которая может изменять все таблицы

create role hr_manager;

grant hr_senior to hr_manager with admin option;

grant all on hr.regions to hr_manager;

grant all on hr.locations to hr_manager;

grant all on hr.countries to hr_manager;

grant all on hr.departments to hr_manager;

grant all on hr.job_history to hr_manager;

grant all on hr.jobs to hr_manager;

grant all on hr.employees to hr_manager;

Третья роль наследует роль HR_SENIOR с возможностью назначения, и затем получает контроль над данными во всех таблицах. Единственная системная привелегия для этой роли это CREATE_SESSION, полученная от HR_SENIOR, которая в свою очередь получила этот доступ от HR_JUNIOR. Даже эта роль не может создавать или удалять таблицы: это может быть сделано либо аккаунтом HR, либо аккаунтом с правами CREATE ANY TALBE или DROP ANY TABLE.

Синтаксис WITH ADMIN OPTION работает так же как и для системных прав. Не хранится кто назначил эту роль и для кого и удаление не будет каскадным.

И назначение ролей для аккаунтов может быть к примеру таким: если SCOTT это топ-менеджер, SUE – менеджер среднего звена и JON и ROOP рядовые сотрудники, то назначение ролей может быть как показано на рисунке 6-8.

Предопределённые роли

Существует как минимум 50 предопределённых ролей в БД (может быть и больше в зависимости от установки). Роли которые должен знать каждый DBA это

CONNECT – эта роль существует только для обратной совместимости. Раньше эта роль содержала системные привелении необходимые для хранения объектов. Теперь у этой роли только CREATE SESSION права.

RESOURCES – также используетя для обратной совместимости. Эта роль может создавать объекты и PL/SQL объекты. Также эта роль влючает UNLIMITED TABLESPACE права.

DBA – роль с наибольшим количеством системных привилегий, и некоторыми объектными правами и ролями. Любой пользователь с ролью DBA может управлять работой БД, за исключением команд STARTUP и SHUTDOWN.

SELECT_CATALOG_ROLE – роль имеет более двух тысяч объектных прав над словарём данных, но не имеет системных привилегий и привилегий над пользовательскими данными. Используется для младшего административного персонала к примеру выполнения задач мониторинга и отчётности.

SCHEDULER_ADMIN – роль владеет системными правами необходимыми для управления выполнением задач по расписанию.

Также существует предопределённая роль PUBLIC, которая назначается по умолчанию всем аккаунтам БД. Отсюда и следует что если права назначены для PUBLIC, то эти права автоматически доступны всем пользователям. После выполнение команды

grant select on hr.regions to public;

все пользователи смогут выполнять select к таблице HR.REGIONS.

 

TIP

The PUBLIC role is treated differently from any other role. It does not,

for example, appear in the view DBA_ROLES. This is because the source code

for DBA_ROLES, which can be seen in the cdsec.sql script called by the

catalog.sql script, specifically excludes it.

Включение ролей

По умолчанию если пользователю была назначена роль, то эта роль будет доступна (enabled).  Это значит что когда сессия создаётся (подключается аккаунт) все права (и другие роли) назначенные роли будут доступны. Такое поведение можно изменить установив роль в значение «не по-умолчанию».  Согласно правам с прошлых примеров

JON назначена роль HR_JUNIOR. У него нет прав на назначение этой роли другим пользователям, но это его роль по умолчанию – ему будет назначена эта роль при любом подключении. Такой вариант событий может быть не совсем вам нужным. Напирмер JON должен иметь доступ к данным в таблицах HR, но это не значит что вы хотите чтобы он мог смотреть данные к примеру из дома, в полночь. Вы хотите ограничить доступ таким образом, чтобы он мог иметь доступ к данным только из офиса, запуская специальную программу в рабочее время.

Чтобы изменить назначение ролей по умолчанию выполните команду

alter user jon default role none;

Теперь когда JON попробует подключиться к БД у него не будет никаких ролей по умолчанию. Но это значит что он даже не сможет подключиться – только роль HR_JUNIOR давала JON права на подключение к БД. Легко исправимо

Теперь когда JON подключится к БД у него будет подключен с ролью CONNECT – и текщая версия роли CONNECT даёт только доступ к созданию сессии. Команда для смены роли для сессии

SET ROLE rolename;

и может быть вызвана пользователем в любое время. Т.е. всё ещё не безопасно, но если роль была создана с помощью команды

CREATE ROLE rolename IDENTIFIED USING procedure_name ;

то эта роль может быть активирована только выполнение процедуры PL/SQL указанной в procedure_name. Эта процедура может делать какие –угодно проверки: проверка макси сети, имени процесса, времени и т.п. Встраивание вызовов процедур для проверки ролей в приложении может включать роли и отключать их по необходимости, оставляя их выключенными при работе в SQL *PLUS или других приложениях.

 

TIP

It can be very difficult to work out why you can see certain data. You may

have been granted the SELECT privilege on specific objects; you may have

been granted the ALL privilege; you may have SELECT ANY; SELECT may have

been granted to PUBLIC; or you may have a role to which SELECT has been

granted. You may have all of these, in which case they would all have to be

revoked to prevent you from seeing the data.

По умолчанию аккаунт не имеет никаких прав в БД Oracle. Невозможно даже создать подключения без назначенных прав. И даже после получения прав на подключения, аккаунт не может сделать ничего полезного (или опасного) без получения соответсвующих прав. Права назначаются с помощью команды GRANT и убираются с помощью команды REVOKE. Дополнительные директивы команды используются для разрешения аккаунта делится правами которые у него есть с другими пользователями. По умолчанию только аккаунта администратора (SYS и SYSTEM) владеют правами назначения прав. Пользователь который назначает права другому пользователю называется grantor когда получатель прав – grantee. Права разбиты на две группы: системные права, которые грубо говоря позволяют пользователю совершать действия влияющие на словарь данных, и права над объектами, которые позволяют пользователю совершать действия влияющие на данные.

Системные права

Всего доступно около двух сотен системных прав. Большинство из них влияет на действия затрагивающие словать данных (такие как создание таблиц или пользователей). Остальные влияют на экземпляр или БД (создание табличны пространств, изменение параметров БД и создание сессий). Наиболее часто используемые права это

• CREATESESSION – права на подключения. Без этих прав вы даже не сможете подключиться к БД
• RESTRICTEDSESSION – Если БД запущена с директивой STARTUPRESTRICT или применялась команда ALTERSYSTEMENABLERESTRICTEDSESSION, то только пользователи с этими правами смогут подключаться к БД
• ALTERDATABASE – разрешает выполнять команды влияющие на физические структуры
• ALTERSYSTEM – разрешает изменять параметры экземпляра и структуры памяти
• CREATETABLESPACE – вместе с ALTERTABLESPACE и DROPTABLESPACE позволяют пользователю управлять табличными пространтсвами
• CREATETABLE – позволяет gratee создавать таблицы в своей схеме; включает возможность создавать, изменять и удалять таблицы, выполнять команды DML и select и управлять индексами
• GRANTANYOBJECTPRIVILEGE – позволяет grantee управлять правами объектов которые ему не принаджлежат, но не даёт прав ему самому
• CREATEANYTABLE – grantee может создавать таблицы которые принадлежат другим аккаунтам
• DROPANYTABLE – grantee позволяется удалять таблицы которые принадлежат другим аккаунтам
• INSERTANYTABLE, UPDATEANYTABLE, DELETEANYTABLE – даёт grantee право выполнять DML команды над объектами которые ему не принадлежат
• SELECTANYTABLE – Даёт право grantee выполнять SELECT к любмы таблицам.

Синтаксис для назначения прав

 

GRANT privilege [,privilege…] TO username;

 

После создания аккаунта, обычно назначаются права часто используемые пользователями кто вовлечён в разработку приложения

 

grant create session, alter session,

create table, create view, create synonym, create cluster,

create database link, create sequence,

create trigger, create type, create procedure, create operator

to username ;

 

Эти права позволяют подключаться и настраивать сессию, создавать объекты и хранить PL/SQL объекты. Объекты могут быть созданы только в схеме аккаунта; нет прав к схемам других аккаунтов. Также создание объектов ограничивается лмимитами табличных пространств.

Другим вариантом назначения прав будет назначение grantee доступа для переназначения прав другим аккаунтам. Например

 

connect system/oracle;

grant create table to scott with admin option;

connect scott/tiger;

grant create table to jon;

 

Выполнение этих команд позволит SCOTT создавать таблицы в совей схеме, и выполнять команду GRANT. SCOTT даёт права пользователю JON создавать таблицы – но JON сможет создавать таблицы только в схеме JON. На рисунке 6-5 показаны права пользователя в Database Control; ту же информацию можно получить выполнив запрос к представлению DBA_SYS_PRIVS.

Если системные разрешения были отозваны, все действия которые вы выполнили пока у вас были права остаются в силе. Если у вас были права с ADMIN OPTION то у всех пользователей которым вы назначили права – права остаются, несмотря на то что у вас права отозвали. Не остаётся записей кто именно назначил системные привилегии, таким образом невозможно забрать права CASCADE как показано на рисунке 6-6

 

EXAM TIP

Revocation of a system privilege will not cascade (unlike

revocation of an object privilege).

 

Права ANY дают доступ ко всем объектам в БД. Таким образом

grant select any table to scott

позволить аккаунту SCOTT выполнять запрос SELECT ко всем таблицам во всех схемах БД. Такое назначение прав считается дурным тоном и ANY права назначаются только DBA.

TIP

In fact, ANY is not as dangerous now as with earlier releases. It no longer

includes tables in the SYS schema, so the data dictionary is still protected. But

ANY should still be used with extreme caution, as it removes all protection

from user tables.

Объектные права

Объектные права дают доступ к выполнению команд DML и SELECT к соответствующим объектам и выполнению PL/SQL объектов. Эти права не существуют для объектов в схеме аккаунта; если у пользователя есть системные права CREATE TABLE – это значит что он может выполнять SELECT и DML запросы к таблицам которые он создал без дополнительных прав.

 

EXAM TIP

The ANY privileges, that grant permissions against objects in

every user account in the database, are not object privileges—they are

system privileges.

 

Объектные права применяются к разным группам объектов

Синтаксис команды

GRANT privilege ON [schema.]object TO username [WITH GRANT OPTION];

Например

grant select on store.customers to scott;

Можно использовать ALL чтобы применить права для всех операций, или использовать конкретное указание столбца таблицы или представления.

 

grant select on store.orders to scott;

grant update (order_status) on store.orders to scott;

grant all on store.regions to scott;

Эти команды позволят аккаунту SCOTT выполнять запрос SELECT ко всем столбцам таблицы ORDERS в схеме STORE но обновлять данные только в одном столбце. Также у аккаунта SCOTT есть доступ ко всем операциям к таблице REGIONS. На рисунке 6-7 отображается результат назначения прав при просмотре в Database Control

 

TIP

Granting privileges at the column level is often said to be bad practice

because of the massive workload involved. If it is necessary to restrict peoples’

access to certain columns, creating a view that shows only those columns will

often be a better alternative.

Использование директивы WITH GRANT OPTION позволит пользователю передавать свои права другим аккаунта. Оракл хранит информацию о том кто и кому дал доступ на объектном уровне; это позволяет отзывать права учитывая эту информацию. Рассмотрим пример

 

connect store/admin123;

grant select on customers to sales with grant option;

connect sales/sales;

grant select on store.customers to webapp with grant option;

conn webapp/oracle;

grant select on store.customers to scott;

connect store/admin123;

revoke select on customers from sales;

 

После выполнения этих команд, ни у пользователя SALES ни у пользователя WEBAPP ни у пользователя SCOTT нет прав на выполнение команд SELECT к таблице STORE.CUSTOMERS.

 

EXAM TIP

Revocation of an object privilege will cascade (unlike revocation of

a system privilege).

Когда пользователь подключается к БД, он подключается используя пользовательский аккаунт (user account) указывая имя аккаунта и метод аутентификации. Пользовательский аккаунт определяет первоначальные права доступа и атрибуты сессии. С пользовательским аккаунтам свзязывается схема (schema). Термины пользователь, пользовательский аккаунт и схема часто используется вместо друг друга в окружении Oracle, но это не одно и тоже. Пользователь – это человек который подключается к пользовательскому аккаунту устанавливая сессию к экземпляру БД и авторизуется используя имя аккаунта. Схема – это набор объектов принадлежащих пользовательскому аккаунту и мы рассмотрим детальнее в следующей главе. В зависимости от того как создался аккаунт будут установлены определнные аттрибуты для сесиий, некоторые из которых могут быть изменены позднее, во время существования сессии. Некоторые аккаунты создаются во время создания БД и DBA затем создаёт остальные аккаунты.

В некоторых приложениях, каждый пользователь использует свой аккаунт. Т.е. БД знает кто именно владелец каждой сессии. Такая модель хорошо работает для небольших систем но практически невозможна для среды в которой с БД работают сотник и тысячи пользователей. В больших системах пользователи подключаются к БД используя один аккаунт, и это добавляет сложностей для обеспечения безопаности сессии и аудита БД. Мы будем рассматривать модель где каждый пользователь имеет свой аккаунт.

Аттрибуты пользовательского аккаунта

У аккаунта сущуествует набор аттрибутов которые задаются при создании. Эти значения используются для сессии, и некоторые могут быть изменены либо самой сессией, либо DBA изменит значение во время существования сессии. Этими атрибутами являются

• Имя
• Метод аутентификации
• Табличное пространство по умолчанию
• Лимит табличного пространтсва
• Временное табличное пространство
• Статус

Все эти аттрибуты должны указываться в момент создания аккаунта, несмотря на то что только имя и метод аутентификации являются обязательными а для остальных существуют значения по умолчанию.

Имя аккаунта

Имя аккаунта должно быть уникальным для всей БД и соблюдать определенные правила. Имя аккаунта должно начинаться с буквы, длина не более 30 символов и может состоять только из букв, цифр, знака $ и символа подчёркивания. Также именем аккаунта не может быть зарезервированное слово. Символы чувствительны к регистру но будут автоматически преобразованы к верхнему регистру. Все эти правила (за исключением длинны) можно обойти если использовать имя внутри двойных кавычек, как показано на рисунке 6-1.

В первом примере создается аккаунт JOHN. Имя было введено строчными буквами но было преобразовано в прописные как видно в результате выполнения запроса. Второй аккаунт был создан с использованием того же имени и двойных кавычек. Третий и четвертый пример показывают что можно обойти правила неиспользуемых символов и зарезервированных слов используя кавычки.

Имя не может быть изменено после создания. Если необходимо изменить его, то можно удалить старый аккаунт и создать новый. Это критическое действие так как все объекты в схеме аккаунта будут удалены.

Табличные пространтсва по умолчанию и лимиты

У каждого аккаунта есть табличное пространство по умолчанию. Это табличное пространство где создаются объекты схемы (такие как таблицы, индексы) создаваемые этим аккаунтом. Аккаунт может создать объекты (быть владельцем) во всех табличных пространствах к которым у него есть  лимит (квота), но если явно не указывать табличное пространтсво при создании объекта – будет использователья табличное пространство аккаунта по умолчанию.

Существует значение по умолчанию для БД которое будет использоваться для всех пользователей созданных без указания табличное пространства. Это значение можно установить во время создания БД или изменить выполнив команду

 

ALTER DATABASE DEFAULT TABLESPACE tablespace_name ;

 

Если у БД нет табличного пространства по умолчанию – используется SYSTEM.

Лимит (quota)  — это размер пространства которое объекты схемы аккаунта могут использовать. Вы можете создавать объекты и выделять экстенты пока не достигните лимита. Если у вас нет квоты для табличного пространтсва – вы не можете создавать объекты. Квоты можно изменять в любое время если у вас есть права для этого. Если квота пользователя была изменена в меньшую сторону, а объекты уже занимают больше места – то существующие объекты можно будет использовать,но нельзя добавить элементы или создать новые объекты.

На рисунке 6-2 отображено как проверять и устанавливать лимиты

Первая команда проверяет представление DBA_USERS и определяет табличные пространства пользователя JOHN. DBA_USERS хранит по одной строке для каждого пользователя БД. Пользователь JOHN получил значения временного и табличного пространства из значения по умолчанию БД (которые видны в результате выполнения запроса к database_properties).

Две команды ALTER USER позволяют аккаунту JOHN использовать 10 МБ пространтсва в табличном пространстве USERS и неограниченный доступ к пространтсву EXAMPLE. Запрос к DBA_TS_QUOTAS отображает затем эту информацию. -1 обозначает неограниченный лимит. Во время выполнения запроса у аккаунта JOHN нет созданных объектов, и поэтому BYTES=0, что значит пространство ещё не используется.

 

EXAM TIP

Before you can create a table, you must have both permission to

execute CREATE TABLE and quota on a tablespace in which to create it.

 

TIP

Most users will not need any quotas, because they will never create

objects. They will only have permissions against objects owned by other

schemas. The few object-owning schemas will probably have QUOTA

UNLIMITED on the tablespaces where their objects reside.

Временное табличное пространтсво

Постоянные объекты (такие как таблицы) хранятся в постоянных табличных пространствах; временные объекты хранятся во временном табличном пространстве. Сессии нужно место во временном табличном пространстве если будет использоваться места больше чем доступно в PGA сессии. Операции которым нужно временное место (в памяти если хватает PGA или во временном таблично пространтсве) включают в себя: сортировку строк, объекдинение таблиц, построение индексов и использование временных таблиц. Каждому аккаунту выделяется временное табличное пространство и все пользовательские сессии подключенные к аккаунту будут использовать одно и тоже временное табличное пространство.

Запрос к представлению DBA_USERS на рисунке 6-2 показывает временное пространство пользователя JOHN, которое также является временным табличным простратсвном по умолчанию для БД.

Управление пространством во временных табличных пространствах полностью автоматическое. Объекты создаются и удаляются при необходимости самой БД. Пользователю не нужен лимит на временное таблично пространство, так как все объекты создаются (и он же является владельцем) аккаунтом SYS, у которого неограниченные лимиты для всех табличных пространств.

 

EXAM TIP

Users do not need a quota on their temporary tablespace.

 

Для изменения временного пространства польователя (что затронет все сессии которые подключается в будущем используя этот аккаунт) используйте команду ALTER USER

 

ALTER USER username TEMPORARY TABLESPACE tablespace_name;

 

TIP

If many users are logging on to the same user account, they will share the

use of one temporary tablespace. This can be a performance bottleneck, which

may be avoided by using temporary tablespace groups.

Профили

Пользовательские профили управляют настройками паролей и позволяют контролировать использование ресурсов. Более детально профили рассмотрим чуть ниже.

Профили полезны для управления паролями и ресурсами но могут использоваться только в среде где у каждого пользователя свой аккаунт. Если много пользователей подключаются к БД под одним аккаунтом, вы не захотите чтобы аккаунт блокировался если ввёл пароль несколько раз один пользователь, потому что этим вы заблокируете доступ всем остальным пользователям. Так же и использование ресурсов часто лучше управлять на уровне сессии а не на уровне аккаунта в целом.

Статус аккаунта

Каждый аккаунт имеет определённый статус, и значение можно увидеть в столбце ACCOUNT_STATUS представления DBA_USERS. Всего существует 9  статусов

• OPEN – аккаунт готов к использованию
• LOCKED – DBA заблокировал аккаунт, пользовтель не можед подключиться используя заблокированный аккаунт
• EXPIRED – Пароль может иметь ограниченное время действия. В данном статусе время действия пароля истекло. Пользователь не может подключиться к аккаунта пока пароль не будет восстановлен
• EXPIRED & LOCKED – Аккаунт заблокирован и время действия пароля истекло
• EXPIRED (GRACE) – Пароль можно настроить таким образом чтобы он не становился EXPIRED сразу, а ещё был так называемый grace период, во время которого можно подключиться и изменить пароль
• LOCKED (TIMED) – это обозначает что аккаунт заблокирвоан в связи с неудачными попытками подключения. Аккаунт можно настроить на автоматическую блокировку на какое-то время после определённого количества неудачных попыток подключения
• EXPIRED & LOCKED (TIMED)
• EXPIRED (GRACE) & LOCKED
• EXPIRED (GRACE) & LOCKED (TIMED)

Для блокировки или разблокирования аккаунта используются команды

ALTER USER username ACCOUNT LOCK;

ALTER USER username ACCOUNT UNLOCK;

Для запроса изменения пароля пользователем при подключении можно выполнить команду

ALTER USER username PASSWORD EXPIRE;

Эта команда автоматически установит грейс пероид, принуждая пользователя изменить пароль при следующей попытке подключения. Нет команды UNEXPIRE. Единственный способ восстановить аккаунт – это изменить пароль.

Методы аутентификации

Аккаунт должен иметь определённый метод аутентификации: что-то что позволяет определить БД есть ли у пользователя пытающегося создать сессию доступ к аккаунту. Самый простой метод это использование пароля который совпадает с паролем хранящемся в БД, но есть и альтернативы. Возможнные варианты это

• Аутентификация ОС
• Аутентификация с помощью файла паролей
• Аутентификация паролем
• Внешняя аутентификация
• Глобальная аутентификация

Первые два метода используются только администраторами, последняя требует установленного LDAP сервера.

Аутентификация ОС и файлом паролей

Для разрешения аккаунту использования этих методов аутентификации (эти два типа используется вместе) необходимо назначить аккаунта расширенные права SYSDBA или SYSOPER

 

GRANT [sysdba|sysoprt] TO username;

 

Назначение этих прав скопирует пароль аккаунта из словаря данных во внешний файл паролей, откуда он может быть считан экземпляром даже если БД ещё не открыта. Это также позволит экземпляру аутентифицировать пользователей путём проверки принадлежит ли пользователь группе-владельцу программ Oracle. После установки БД единственный аккаунт с этими правами – это SYS.

Для использования файла паролей можно использоуть следующий синтаксис

 

CONNECT username/password[@db_alias] AS [SYSOPER|SYSDBA];

 

Аутентификация с помощью файла паролей можно использовать для подключения к удалённой БД используя Oracle Net

Чтобы использовать авторизацию ОС пользователсь должен быть авторизован ОС с доступом к исполняемым файлам Oracle и затем можно выполнить команду

 

CONNECT / AS [SYSOPER|SYSDBA]

 

Пароли ОС не хранятся Oracle и поэтому не может быть проблем со сменой пароля.

Эквивалентом этой команды может быть подключения через Database Control при выбранном значении SYSDBA в списке Connect As. Для определения у кого есть пава SYSDBA и SYSOPER можно выполнить запрос к представлению V$PWFILE_USERS. Подключение с использованием аутентифкации файлом паролей или ОС всегда доступно вне зависимости от состояния экземпляра и БД и такой вид подключения необходим для выполнения команд STARTUP и SHUTDOWN.

Третий вид привилегий SYSASM но это выходит за рамки этого курса.

Аутентификация паролем

Синтаксис для подключения используя аутентификацию паролем используя SQL *Plus

 

CONNECT username/password[@db_alias];

 

Или в Database Control выбрать NORMAL в списке Connect As. Когда подключение происходит используя аутентифкацию паролем, экземпляр проверит пароль в строке подключения с паролем который хранится для этого аккаунта в словаре данных. Для того чтобы был доступен такой метод аутентификации БД должна быть открыта; и используя этот метод невозможно выполнять команды STARTUP и SHUTDOWN.Пользователь SYS не имеет прав подключения используя аутентификацию паролем – только через файл паролей, аутентификацию ОС или LDAP.

Начиная с версии 11g пароли чувствительны к регистру. Пароль хранится именно так как был введён без всяких преобразований регистра.

Когда подключение происходит по сети, 11g всегда использует шифрование перед передачей данных. Для шифрованя данных между пользовательским процессом и серверным процессом необходимо Advanced Security Option, но шифрование пароля включено по умолчанию.

Любой пользователь может изменить пароль аккаунта в любое время, а аккаунт с расширенными правами может изменить пароль любого пользователя. Синтаксис команды

 

ALTER USER username IDENTIFIED BY password;

Внешняя аутентификация

Если аккаунт был создан с директивой внешней аутентификации, Oracle делегирует аутентифкацию внешнему сервису; т.е. не будет запрошен пароль. Если куплена Advanced Security Option, то внешним сервисом может быть сервер Kerberos, сервер RADIUS или сервис аутентификации Windows. Когда пользователь пытается подключиться к аккаунту, вместо аутентификации пользователя, БД будет разрешать (или не разрешать) подключение в зависимости от того авторизован или пользователь во внешнем сервисе. Например если используется Kerberos – БД проверит существует ли у пользователя валидный Kerberos токен. Без Advanced Security Option – единственно доступной формой внешней аутентификации будет аутентификация ОС. Это требует прав SYSDBA или SYSOPER (как описано выше) но может быть использовано и для обычных аккаунтов. Необходимо создать пользователя Oracle с таким же именем как и аккаунт ОС с префиксом указанном в параметре OS_AUTHENT_PREFIX. По умолчанию значение OPS$. Для проверки значения можно использовать запрос

 

select value from v$parameter where name=’os_authent_prefix’

 

В Linux/Unix внешняя аутентификация ОС работает очень просто. Предполагая что значение OS_AUTHENT_PREFIX осталось по умолчанию и есть пользователь ОС с именем jwatson, можно создать пользователя Oracle и дать права подключения следующим образом

create user ops$jwatson identified externally;

grant create session to ops$jwatson;

 

Пользователь подключенный к ОС как jwatson сможет подключиться к БД выполнив команду

sqlplus /

из командной строки ОС и будет подключен к БД как пользователь ops$jwatson.

В Windows обычно используется домен и тогда команда создания пользователя будет вида

 

create user «OPS$JWACER\JOHN WATSON» identified externally;

 

TIP

Using external authentication can be very useful, but only if the users

actually log on to the machine hosting the database. Users will rarely do this,

so the technique is more likely to be of value for accounts used for running

maintenance or batch jobs.

Глобальная аутентификация

Стандартом для управления идентификацией признано использование LDAP серверов. Под глобальным пользователем (global user) подразумевается пользователь определённый в LDAP директории, и глобальная аутентификация (global authentification) значит делегирование аутентификации пользователя серверам LDAP.

Существует два метода для глобальной аутентификации

• Пользователи определены в директории LDAP и в БД. Пользователь будет подключаться к БД используя пользовательский аккаунт с таким же именем как и имя пользователя в LDAP
• Пользователи создаются только в LDAP директории. Все подключения к БД будут созданы используя один аккаунт БД.

Если всё настроено как надо подключение будет создано без запроса пароля.

Создание аккаунтов

У команды CREATE USER всего два обязательных параметра: имя  и метод аутентификации. Дополнительно, можно указать табличное пространство по умолчанию и временное табличное пространство, лимиты, профили и команды блокировки аккаута и управления паролем. Пример команды (номера строк добавлены для удобства)

 

1 create user scott identified by tiger

2 default tablespace users temporary tablespace temp

3 quota 100m on users, quota unlimited on example

4 profile developer_profile

5 password expire

6 account unlock;

 

Только первая строка обязательна – существуют значения по умолчанию для всего остального. Рассмотрим пример построчно

• Имя и пароль для аутентификации паролем
• Табличное пространство по умолчанию и временное табличное пространство
• Лимиты
• Профиль для управления паролем и ресурсами
• Принудительное изменение пароля при первом подключении
• Аккаунт готов к использованию (команда по умолчанию)

 

Каждый параметр может быть изменён командой ALTER USER кроме имени. Для смены пароля выполните команду

alter user scott identified by lion;

Смена табличных пространств

alter user scott default tablespace store_data temporary tablespace temp;

Смена лимитов

alter user scott quota unlimited on store_data, quota 0 on users;

Смена профиля

alter user scott profile prod_profile;

Бывает необходимо удалить аккаунт, используется команда

drop user scott;

Эта команда будет выполнена успешно только если у аккаунта нет объектов: схема пуста. Если вы не хотите вначале удалять все объекты пользователя, можно использовать директиву CASCADE

drop user scott cascade;

Для управления пользователя в Database Control из домашней страницы перейдите на вкладку Server и перейдите по ссылке Users в секции Security. В новом окне отобразятся все пользователи остортированные по дате создания. Для сортировки по какому либо столбцу нажмите на заголовок столбца. На рисунке 6-3 отображается окно Database Control

Первый аккаунт на рисунке – PUBLIC. Это формальный пользователь которому необходимо назначить права для применения прав ко всем пользователям.  Кнопки CREATE и DELETE позволяют создавать и удалять пользователей.

Для изменения аттрибутов аккаунта можно выделить пользователя и нажать EDIT. Октроется окно Edit User, показанное на рисунке 6-4. Это окно можно использовать для изменения аттрибутов кроме лимитов табличных пространств. Для этого есть отдельное окно. Также здесь можно назначать и удалять права и роли.