/// <summary>
/// Выполняет указанную команду администрирования безопасности
/// </summary>
/// <param name="statement">Команда администрирования безопасности</param>
/// <returns>Объекты безопасности в результате выполнения указанной команды в зависимости от типа команды <see cref="AdminSecurityOperations"/></returns>
protected virtual IEnumerable<SecurityObject> AdminSecurity(SecurityStatement statement)
{
// Команда запроса объектов администрирования
if (statement.Operation == AdminSecurityOperations.GetRolePrivileges ||
statement.Operation == AdminSecurityOperations.GetCurrentPrivileges ||
statement.Operation == AdminSecurityOperations.GetTable)
{
List<SecurityObject> result = new List<SecurityObject>();
SecurityObjectTypes objectType = statement.LeftOperand != null ? statement.LeftOperand.ObjectType : SecurityObjectTypes.UserInfo;
string objectName = statement.LeftOperand != null ? statement.LeftOperand.ObjectName : null;
SecurityObject tableSecurity = null;
switch (statement.Operation)
{
// Запрос привилегий роли
case AdminSecurityOperations.GetRolePrivileges:
foreach (SelectStatementResultRow row in SelectSimple(
GetUserPrivilege("dba_role_privs") ?
"select owner||'.'||table_name table_name from dba_tab_privs where grantee = :pRole" :
"select owner||'.'||table_name table_name from role_tab_privs where role = :pRole",
":pRole", objectName, "table_name").Rows)
if (GetTablePrivilege((string)row.Values[0], out tableSecurity)) result.Add(tableSecurity);
break;
// Запрос текущих привилегий пользователя
case AdminSecurityOperations.GetCurrentPrivileges:
foreach (SelectStatementResultRow row in SelectData(new Query(
"select owner||'.'||view_name from all_views where owner <> 'SYS' and owner <> 'SYSTEM' and view_name like 'VW:_%' escape ':' union all " +
"select owner||'.'||object_name from all_procedures where owner <> 'SYS' and owner <> 'SYSTEM' and object_name like 'P:_%' escape ':'")).Rows)
if (GetTablePrivilege((string)row.Values[0], out tableSecurity)) result.Add(tableSecurity);
break;
// Запрос таблицы для объектов
case AdminSecurityOperations.GetTable:
// Таблица пользователей (Id, IsActive, IsExpired, Locked, Created)
if (objectType == SecurityObjectTypes.User && statement.RightOperand == null)
{
DBColumn username = new DBColumn("username", true, null, 30, DBColumnType.String);
DBColumn isActive = new DBColumn("is_active", false, null, 0, DBColumnType.Boolean);
DBColumn isExpired = new DBColumn("is_expired", false, null, 0, DBColumnType.Boolean);
DBColumn locked = new DBColumn("lock_date", false, null, 0, DBColumnType.DateTime);
DBColumn created = new DBColumn("created", false, null, 0, DBColumnType.DateTime);
DBColumn nullValue = new DBCriteriaColumn("null", false, null, 0, DBColumnType.String, new OperandValue(null));
tableSecurity =
GetSecurityTable("(select username, " +
"cast(decode(instr(account_status,'LOCKED'),0,1,0) as number(1)) is_active, " +
"cast(decode(instr(account_status,'EXPIRED'),0,0,1) as number(1)) is_expired, " +
"lock_date, created from dba_users)",
new string[] { "dba_users" }, username, isActive, isExpired, locked, created) ??
// isActive, isExpired не имеет смысла брать из user_users
GetSecurityTable("all_users", null, username, nullValue, nullValue, nullValue, created) ??
GetSecurityTable("user_users", null, username, nullValue, nullValue, locked, created);
}
// Таблица ролей (Id)
else if (objectType == SecurityObjectTypes.Role && statement.RightOperand == null)
{
tableSecurity =
GetSecurityTable("dba_roles", "role") ??
// Выделена в запрос, чтобы не путать с использованием в таблице ролей пользователей с тремя полями
GetSecurityTable("(select granted_role from user_role_privs)", "granted_role", new string[] { "user_role_privs" });
}
// Таблица иерархии ролей (Parent, Child)
else if (objectType == SecurityObjectTypes.Role && statement.RightOperand != null &&
statement.RightOperand.ObjectType == SecurityObjectTypes.Role)
{
tableSecurity =
GetSecurityTable("(select grantee, granted_role from dba_role_privs, dba_roles where grantee = role)",
"grantee", "granted_role", new string[] { "dba_role_privs", "dba_roles" }) ??
GetSecurityTable("role_role_privs", "role", "granted_role");
}
// Таблица ролей пользователей (Parent, Child)
else if (statement.Operation == AdminSecurityOperations.GetTable && statement.RightOperand != null && (
(statement.LeftOperand.ObjectType == SecurityObjectTypes.Role && statement.RightOperand.ObjectType == SecurityObjectTypes.User) ||
(statement.LeftOperand.ObjectType == SecurityObjectTypes.User && statement.RightOperand.ObjectType == SecurityObjectTypes.Role)))
{
tableSecurity =
GetSecurityTable("(select grantee, granted_role from dba_role_privs, dba_users where grantee = username)",
"grantee", "granted_role", new string[] { "dba_role_privs", "dba_users" }) ??
GetSecurityTable("user_role_privs", "username", "granted_role");
}
if (tableSecurity != null) result.Add(tableSecurity);
break;
}
return result;
}
// Команда модификации объектов администрирования
foreach (string sql in new AdminSecurityGenerator(statement, this).GenerateSqlCommands())
{
IDbCommand command = CreateCommand();
command.CommandText = sql;
try
{
command.ExecuteNonQuery();
Trace.WriteLineIf(xpoSwitch.TraceInfo, sql, "AdminSecurity");
}
catch (Exception e)
{
throw WrapException(e, command);
}
}
return null;
}
/// <summary>
/// Конструктор
/// </summary>
/// <param name="statement">Команда администрирования безопасности</param>
/// <param name="formatter">Представитель форматирования sql-запросов с безопасным доступом к данным</param>
public AdminSecurityGenerator(SecurityStatement statement, ISecuredSqlGeneratorFormatter formatter)
{
this.root = statement;
this.formatter = formatter;
}
