Category: Security

Database, Security

Creating a Read-Only Account on Database with VPD or Label Security

 ,

Hi all,

This is an interesting case, specific to be understood by easy to be resolved.

The whole story started when a client asked for a Read-Only account (let’s call it RO_USER) with access to objects under another schema (let’s call it SCHEMA_OWNER). Easy going, right?

  • Create user
  • Grant select on SCHEMA_OWNER tables
  • Possibly grant execute on SCHEMA_OWNER procedures/packages/functions
  • Possibly private synonyms on RO_USER for SCHEMA_OWNER objects

However, when connecting with RO_USER and executing a query on a table, that’s what happened:

select count(*) from SCHEMA_OWNER.TABLE_EXAMPLE;

COUNT(*)
----------
0

When connecting with SCHEMA_OWNER and executing the same query:

select count(*) from SCHEMA_OWNER.TABLE_EXAMPLE;

COUNT(*)
----------
9255013

Hm, in general, the known limitations for this approach are:

  • Private database links: In case this is the issue, the only alternative is using Proxy Connection. Trying this:
SQL> alter user SCHEMA_OWNER grant connect through RO_USER;

User altered.

SQL> conn RO_USER[SCHEMA_OWNER]/***********
Connected.
SQL> select count(*) from SCHEMA_OWNER.TABLE_EXAMPLE;

  COUNT(*)
----------
   9255013

WORKING!

However, when checking for the scenario, noticed this is a real table, not a synonym or view using a private database link. Why is that?

Also, this alternative creates some problems, as the RO user would now have access to DML on SCHEMA_OWNER tables, not Read-Only access anymore.

Well, the other option:

  • VPD or Label Security: Limit access to data depending on the current schema. That’s a match:
SQL> select object_owner,object_name,policy_name,function, PACKAGE from dba_policies where object_name='TABLE_EXAMPLE';

OBJECT_OWNER	     OBJECT_NAME		    POLICY_NAME 		   FUNCTION	         PACKAGE
-------------------- ------------------------------ ------------------------------ --------------------- ---------
SCHEMA_OWNER	     TABLE_EXAMPLE		     POLICY_EXAMPLE	           FCN_TABLE_EXAMPLE	 PKG_EXAMPLE

OK!

So what to do?

Here is the trick: https://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvpoli.htm#i1006985

Using EXEMPT ACCESS POLICY. As per Oracle Document “[…] database users granted the EXEMPT ACCESS POLICY privilege, either directly or through a database role, are exempt from VPD enforcements.”

This is also valid for Datapump and Legacy Export as per MOS When Is Privilege “Exempt Access Policy” Needed For Export? (Doc ID 2339517.1).

Let’s check for it:

SQL> GRANT EXEMPT ACCESS POLICY TO RO_USER;
Grant succeeded.

SQL> select count(*) from SCHEMA_OWNER.TABLE_EXAMPLE;
COUNT(*)
----------
 9255015

And what about Label Security?

That was not my case, as you could see, but as per the same Oracle Document: ” They are also exempt from some Oracle Label Security policy enforcement controls, such as READ_CONTROL and CHECK_CONTROL, regardless of the export mode, application, or utility used to access the database or update its data.”

I hope it helps you!

Security, SQLServer, Windows

Removing user from SQL Server database

 , ,

So, have you ever been in the need to drop a user from the SQL Server database, but you stuck with the following related error?

Error: 15138 The database principal owns a schema in the database, and cannot be dropped.

If you don’t have access to the SSMS to see which schema or objects the user owns, the following SQL should do the job.

USE [DATABASENAME]
GO
SELECT so.name OBJECT, su.name OWNER
FROM sys.schemas so
INNER JOIN sysusers su ON (so.principal_id = su.uid)
WHERE su.name = 'username'
GO
SELECT so.name OBJECT, su.name OWNER, so.xtype TP
FROM sys.sysobjects so
INNER JOIN sysusers su ON (so.uid = su.uid)
WHERE su.name = 'username'
GO

Once you have the owned objects/schemas, you can change them with the following SQL (schema example):

USE [DATABASENAME]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [newowner] /* new owner username */
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [newowner]
GO

Then you’re ready to drop the user DROP USER [username].

Bonus SQL: The following will list the schemas and their owner.

SELECT schema_name(schema_id) as SCHEMANAME,
user_name(s.principal_id) as USERNAME
FROM sys.schemas AS s
Cloud Services, Cloud Services | Exadata | Linux, Database, Security, SQL | PLSQL

AWS: ALTER SYSTEM and Managing SYS Objects in RDS

 , , ,

I’m very often managing services over EC2 and there are a few actions clients are often getting some issues to perform in RDS. So I decided to list here 5 of them:

Kill sessions:

begin
rdsadmin.rdsadmin_util.kill(
sid => &sid,
serial => &serial,
method => 'IMMEDIATE');
end;
/

Flush shared_pool or buffer_cache:

exec rdsadmin.rdsadmin_util.flush_shared_pool;
exec rdsadmin.rdsadmin_util.flush_buffer_cache;

Grant Privileges to SYS Objects

# Grant

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_grantee   => 'GREPORA',
        p_privilege => 'SELECT');
end;
/

# Grant with Grant Option

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name     => 'V_$SESSION',
        p_grantee      => 'GREPORA',
        p_privilege    => 'SELECT',
        p_grant_option => true);
end;
/

# Revoke

begin
    rdsadmin.rdsadmin_util.revoke_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_revokee   => 'GREPORA',
        p_privilege => 'SELECT');
end;
/

 

Hope it Helps!

Cloud Services, Cloud Services | Exadata | Linux, Security

AWS EC2: Custom Functions to Verify Passwords

 , ,

Hello all!
Looking forward to create password functions on EC2? Easy, we just need to use AWS internal functions for it. Check below an example:

# To create it:

begin
    rdsadmin.rdsadmin_password_verify.create_verify_function(
        p_verify_function_name => 'CUSTOM_PASSWORD_FUNCTION', 
        p_min_length           => 12, 
        p_min_uppercase        => 2, 
        p_min_digits           => 1, 
        p_min_special          => 1,
        p_disallow_at_sign     => true);
end;
/

In case you want to double check the code created, here goes a quick trick: Check on DBA_SOURCE:

col text format a150
  select TEXT  from DBA_SOURCE 
   where OWNER = 'SYS' and NAME = 'CUSTOM_PASSWORD_FUNCTION' order by LINE;

Hope it helps. Cheers!

Cloud Services, Cloud Services | Exadata | Linux, Security

AWS EC2: Grant Privileges to SYS Objects

 , , ,

Hello all!
So quick one today: How to grant and revoke privileges from/to SYS objects on EC2 instances? Do we have the ‘grant option’ for those grants?

Easy, have a look:

# Grant

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_grantee   => 'GREPORA',
        p_privilege => 'SELECT');
end;
/

# Grant with Grant Option

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name     => 'V_$SESSION',
        p_grantee      => 'GREPORA',
        p_privilege    => 'SELECT',
        p_grant_option => true);
end;
/

# Revoke

begin
    rdsadmin.rdsadmin_util.revoke_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_revokee   => 'GREPORA',
        p_privilege => 'SELECT');
end;
/
Database, Security, SQL | PLSQL

Oracle 11g / 12c identified by values – set an Oracle password to its current value without knowing the password

 ,

This post will be a quick one. I needed to set a Oracle password as it was about to expire but the client did not want to change the password itself.

The simplest way to get the encrypted password was:

select name, password from sys.user$ where name=‘USER’;

But after Oracle 11g/12c seems a better option for security ends to use DBMS_METADATA.get_ddl or select the spare4 column on sys.user$

select name, spare4 from sys.user$ where name=‘USER’;

or

set long 9999999
set longc 9999999
select dbms_metadata.get_ddl('USER','DBSNMP') from dual;

And how to set this password back to the user?

alter user [USER] identified by values '[encrypted password]';

 

Hope it helps!

Database, Security

11g Improvements: New Grants

 , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about some of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes, today actually 2 small things:

Execute Grant on Directories

In 10g was possible to grant READ and/or WRITE, but this also allowed executing the ORACLE_LOADER access driver. In 11g, only a user that has been given EXECUTE access to the directory object is allowed to run programs in it. This should be granted using:

grant EXECUTE on DIR_EXAMPLE;

DATABASE_ROLE constant for SYS_CONTEXT

In 11g the context procedure can also give DATABASE_ROLE, as it might be used as check for certain procedures to run only on standbys or to avoid it, for example.

Between values are: PRIMARY, PHYSICAL STANDBY, LOGICAL STANDBY and SNAPSHOT STANDBY. This can be executed this way:

SELECT sys_context('USERENV', 'DATABASE_ROLE') FROM dual;

You probably know about that, right?
Anyway, always good to remember!

Cheers!

Database, Security

Oracle Security: Immediate Protection for JVM Exploits (CVE-2018-3110)

 , , ,

Hello all!

Now that CVE-2018-3110 is a hot topic, I think this is a pretty interesting topic to go on.

So, we all know this is consistently one of the components with more CVEs for Oracle Databases. Basically because you can create Java objects in the database (which I think is an abomination :D) and run this code there, usually doing some tricks to escalate privileges to DBA, to PDB, to CDB, to host and other CDBs…

The problem is that (before 18c) OJVM PSU Patches are not RAC Rolling installable. Which means will need a maintenance window to apply fixes for this component. Quite bad, hãn… And if you discover a vulnerability and the PSU window is only in a month or so?

Well, we have a solution 🙂
It is well described in MOS Oracle Recommended Patches — “Oracle JavaVM Component Database PSU and Update” (OJVM PSU and OJVM Update) Patches (Doc ID 1929745.1), under the name “Mitigation Patch”.

It basically consists in install a patch Patch 19721304: SCRIPT TO LOCK DOWN JAVA DEVELOPMENT, which is a Rolling Patch, which allows you to disable any new Java object to be created. This is, so, assuming exploits can be done by creating new java objects on DB (as most of Java CVEs). Also, this patch don’t have any version requirement (after 9i).

Having the patch, however, doesn’t mean you are automatically protected against any vulnerability, but means you can protect yourself temporary by disabling new java objects creation with “exec dbms_java_dev.disable;” anytime.

The Mitigation Patch does not remove Java objects or block any java execution, it only disable the creation of new Java objects, so if the exploit is already planted, it’s not a viable solution. The mitigation patch can be used in any scenario where the PSU or proper JVM fixes cannot be applied at the moment but it’s wanted to prevent against JVM vulnerabilities.

Now, before downloading the patch, first check if it’s not already installed to your home, as it’s part of some Boundle Patches, like “Database Bundle Patch : 12.1.0.2.180417 (27338029)“.

Important Note: The intent is to be like a “workaround” while the PSU is planned. This was not built to be definitive solution. The idea is to just disable new Java objects to be created until the fix is applied on the proper planned maintenance window.

Hope it helps!

Database, Security

Oracle SecureFiles | In 11.2 extended to Oracle Database File System – DBFS

 , , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Oracle SecureFiles | In 11.2 extended to Oracle Database File System – DBFS

In version 11g Oracle introduced SecureFiles, a new LOB storage architecture as replacement for BASICFILES LOBs’storage, being faster than Unix files to read/write. Lots of potential benefits for OLAP analytic workspaces are expected, as the LOBs used to hold AWs have historically been very slow to write. In addition, this object type is compliant to other mechanisms like deduplication, compression and encryption. Besides that, lock and concurrency model has been improved to manage those kind of objects. Other improvements like space management, reduced fragmentation, intelligent pre-fetching, new network layer, no LOB index contention, no high water mark contention and being easier to manage are important to mention.

More“Oracle SecureFiles | In 11.2 extended to Oracle Database File System – DBFS”