GetSchema returns too many Views

[crazypit]

Hello,

I use the latest version of dotConnect for Oracle. I need to get the names of the Views that belong to the schema related to the user defined in my connection string. I need to do it through LINQ, so i use the DataContext.Connection.GetSchema method.

For now, i don't want to use the schema name restriction, because there is not an elegant way to obtain it. I don't want to use the OracleConnection.UserId property, because ideally i would like to port the application to an other database type like SQL Server when the new LinqConnect product is ready.

Without the schema name restriction, i get lots of system Views like the ones beginning with DBA_ and V_$. Nevertheless, the user has only the CONNECT role and the CREATE SESSION, CREATE SEQUENCE and SELECT ANY TABLE privileges!

Is this possible? I really need to have access only to the Views located under the current schema...

[AndreyR]

In case you have the SELECT ANY TABLE privilege you will see all views including the system ones.
I recommend you to create a user having privileges to select the set of views you wish.

[crazypit]

I really don't see any connection between the SELECT ANY TABLE privilege and the problem i have. Nevertheless, i removed it from my user and the problem persists...

[AndreyR]

This is a designed behaviour. For more information refer to
http://www.devart.com/dotconnect/oracle ... aData.html
As for obtaining the schema name, you can use the context.Connection.UserId property, this is an appropriate way in the Oracle case.

[crazypit]

I know about context.Connection.UserId but this is Oracle specific. In order to access it, i need to cast the DbConnection to OracleConnection. Is there a way to access schema information in LinqConnect product, independent of the type of database?

[AndreyR]

Try to get the database name using the following code:

Code: Select all

string name = dataContext.Mapping.DatabaseName;

[crazypit]

This is completely off...

It returns the database name configured in the lqml file and used by the designer which of course is completely different than the runtime schema name. If i remove the relevant Database attribute, it returns the custom datacontext class name...

[AndreyR]

If you have changed the database name in runtime, you have an opportunity to take the schema name of the concrete table (if you know the schema it is located in). The following code implements this:

Code: Select all

db.Mapping.GetTable(typeof(Dept)).TableName.Split('.')[0]

One more apparoach is to execute the query

Code: Select all

dataContext.ExecuteQuery("begin :Result := SYS_CONTEXT (''USERENV'', ''CURRENT_SCHEMA''); end;");

[crazypit]

Second approach is Oracle specific and as i said before, i need a database agnostic way to obtain the schema/database name.

I have the same feeling for the first approach too...

I think that we somehow missed my original request. Let me phrase it again:

For the LinqConnect product, i would like for the GetSchema method to return, BY DEFAULT, the Views that are under the User (in Oracle) or the Database (in SQL Server) or whatever else is common for MySQL etc. It is incorrect behavior to return ALL the system Views even if the User does not have the relevant privileges, as it breaches the enforced security. I really should not have to provide the schema name in order to get the Views to which i originally set my User to have access to.

If one does not provide a schema name for GetSchema method, then the method should assume that the schema is the one defined in the current connection through the User Id parameter (for Oracle installations)

[AndreyR]

This functionality will not be changed because of compatibility reasons.
Sorry for the inconvenience.