I am using ADO.NET with C # 4 to set up a test context for a component.
I am executing the following SQL as sysdba:
new[]
{
"create user \"{0}\" identified externally default tablespace USER_DATA temporary tablespace TEMP profile DEFAULT"
.FormatWith( Config.QualifiedUserName ),
"create role {0}"
.FormatWith( Config.RoleName ),
"grant {0} to \"{1}\""
.FormatWith( Config.RoleName, Config.QualifiedUserName ),
"insert into doc.application( id, name ) values( {0}, '{1}' )"
.FormatWith( Config.AppId, Config.AppName ),
"insert into doc.appl_role( application, role, description ) values( {0}, '{1}', '{2}' )"
.FormatWith( Config.AppId, Config.RoleName, Config.RoleDescription ),
"create table {0}.{1} ( ID number primary key, VERSION number, NAME varchar(100) )"
.FormatWith( Config.TestSchema, Config.TestTable ),
"insert into {0}.{1} (ID, VERSION, NAME) values ('{2}', '{3}', '{4}')"
.FormatWith( Config.TestSchema, Config.TestTable, Config.TestRowId, 1, Config.TestRowName ),
"grant select, insert, update, delete on {0}.{1} to {2}"
.FormatWith( Config.TestSchema, Config.TestTable, Config.RoleName ),
"grant create session to \"{0}\""
.FormatWith( Config.QualifiedUserName )
}
and the Config class looks like this:
public struct Config
{
public const Int32 TestRowId = 1;
public const Int32 AppId = 99999;
public const String AppName = "OraceUtils";
public const String RoleName = "ORACLE_UTILS_BASE";
public static readonly String RoleDescription = "For testing {0}".FormatWith( AppName );
public static readonly String QualifiedUserName = @"{0}\{1}".FormatWith( Domain, UserName );
public const String DataSource = "TESTDB";
public const String Domain = "BI";
public const String UserName = "ORACLE_TEST_USER";
public const String UserPassword = [for my eyes only];
public const String TestSchema = "CI";
public const String TestTable = "ROLE_PROVIDER_TEST_TABLE";
public const String TestRowName = "Arne And";
}
From what I read, simply providing the role to the user is not turned on. However, after executing the SQL above, the BI \ ORACLE_TEST_USER user can use the ROLE_PROVIDER_TEST_TABLE table just fine. The ORACLE_UTILS_BASE role is also mapped to SESSION_ROLES.
If after that I issue "SET ROLES", the above user will not be able to access the table.
I thought it was the other way around, i.e. that the user will not have access until it issues "SET ROLES ORACLE_UTILS_BASE, [any other roles]".