Accell/SQL & DataServer

From Support Wiki

Security

I have been granted access to a schema, however I cannot see the tables in that schema. Why? And how can I fix this? DataServer security is based on schemas, not users. The tables that a logged-in database user can access are determined by the current schema for that user, not by the user id. In a sense, the user takes on the identity of his/her current schema to determine table access. The user id determines what schemas a specific user can make current, and thus indirectly influences the user's database access.

For example, suppose the DBA ("dbowner") creates a database with the following structure:

$ SQL
Unify DataServer 8.2 SQL/A
Copyright 1986-2002, Unify Corporation
All Rights Reserved


Opening default database
Database opened
sql> display mode;
User Recognized As dbowner(DBA)
Current Schema is PUBLIC (Default)
Transaction Scan Level is 2
Transaction Update Level is 10
Abort action is CONTINUE
sql> create schema sch1;
recognized ddl!
sql> create table tbl1 (fld char(10));
recognized ddl!
sql> create schema sch2;
recognized ddl!
sql> create table tbl2 (fld char(10));
recognized ddl!
sql> grant all privileges on schema sch2 to PUBLIC;
recognized ddl!
sql> create schema sch3;
recognized ddl!
sql> create table tbl3(fld char(10));
recognized ddl!
sql> grant all privileges on schema sch3 to sch1;
recognized ddl!
sql> grant access on database to dbuser;
recognized ddl!
sql> grant access on schema sch1 to dbuser;
recognized ddl!
sql> commit work;
Transaction committed.

Now let's log in as user "dbuser" and see what results we get:

 SQL
Unify DataServer 8.2 SQL/A
Copyright 1986-2002, Unify Corporation
All Rights Reserved


Opening default database
Database opened
sql> display mode;
User Recognized As dbuser
Current Schema is PUBLIC (Default)
Transaction Scan Level is 2
Transaction Update Level is 10
Abort action is CONTINUE

The user was detected as a known user ("dbuser") and is in the PUBLIC schema by default (since ALTER DEFAULT SCHEMA was not used to change the default schema).

sql> tables all;
Number of tables : 30
DBUTIL.UTLATH       DBUTIL.UTLXEC       DBUTIL.UTLMLN       DBUTIL.UTLXECPERM
DBUTIL.UTLNODE      DBUTIL.MENULINES    DBUTIL.FILES        DBUTIL.DIRS
DBUTIL.PATHS        DBUTIL.APPL         DBUTIL.FRM          DBUTIL.ARCHENTS
DBUTIL.LASTAPPL     DBUTIL.ADEVLOCKS    DBUTIL.CLSATTR
SYS.ACCESSIBLE_TABLES                   SYS.ACCESSIBLE_COLUMNS
SYS.ACCESSIBLE_SCHEMAS                  SYS.HASH_INDEXES    SYS.HASH_INDEXES_G
SYS.BTREE_INDEXES   SYS.LINK_INDEXES    SYS.LINK_INDEXES_G  SYS.USER_PRIVILEGES
SYS.SCHEMA_PRIVILEGES                   SYS.TABLE_PRIVILEGES
SYS.COLUMN_PRIVILEGES                   SYS.DATABASE_VOLUMES
SYS.DATABASE_USERS  sch2.tbl2

Note that we see the table sch2.tbl2, but we do not see sch1.tbl1 even though we have been granted access on schema sch1 (neither do we see sch3.tbl3). The important thing to remember is that access on a schema merely allows us to make that schema current - it will not allow us to access objects in that schema unless we are in a schema that has privileges on the object. Since schema PUBLIC was granted access to schema sch2, we can see that table even though we ourselves have not been granted access to schema sch2.

sql> select * from sch1.tbl1;
'sch1.tbl1' is an invalid table, i.e. not found in data dictionary (-10414).

Again, we cannot access sch1.tbl1 because we are not in a schema that has privileges on this table.

sql> select * from sch2.tbl2;
recognized query!
There were no rows selected.

This query succeeds, because schema PUBLIC has privileges on schema sch2.

sql> set current schema to sch1;
sql> display mode;
User Recognized As ussup
Current Schema is sch1 (Default: PUBLIC)
Transaction Scan Level is 2
Transaction Update Level is 10
Abort action is CONTINUE

Now we've switched to schema sch1 - now we have access to any objects that sch1 has access to.

sql> select * from sch1.tbl1;
recognized query!
There were no rows selected.
sql> select * from tbl1;
recognized query!
There were no rows selected.

By definition, a schema has access to all objects within it - therefore we can now access table sch1.tbl1 since we are in schema sch1.

sql> tables all;
Number of tables : 32
DBUTIL.UTLATH       DBUTIL.UTLXEC       DBUTIL.UTLMLN       DBUTIL.UTLXECPERM
DBUTIL.UTLNODE      DBUTIL.MENULINES    DBUTIL.FILES        DBUTIL.DIRS
DBUTIL.PATHS        DBUTIL.APPL         DBUTIL.FRM          DBUTIL.ARCHENTS
DBUTIL.LASTAPPL     DBUTIL.ADEVLOCKS    DBUTIL.CLSATTR
SYS.ACCESSIBLE_TABLES                   SYS.ACCESSIBLE_COLUMNS
SYS.ACCESSIBLE_SCHEMAS                  SYS.HASH_INDEXES    SYS.HASH_INDEXES_G
SYS.BTREE_INDEXES   SYS.LINK_INDEXES    SYS.LINK_INDEXES_G  SYS.USER_PRIVILEGES
SYS.SCHEMA_PRIVILEGES                   SYS.TABLE_PRIVILEGES
SYS.COLUMN_PRIVILEGES                   SYS.DATABASE_VOLUMES
SYS.DATABASE_USERS  sch1.tbl1           sch2.tbl2           sch3.tbl3
sql> select * from sch2.tbl2;
recognized query!
There were no rows selected.
sql> select * from sch3.tbl3;
recognized query!
There were no rows selected.

Interestingly enough, although we are in sch1 we can still access tables in sch2. This is because granting privileges to the PUBLIC schema actually has the effect of granting privileges to all schemas. If you want to allow a schema to only be accessed from another specific schema, make sure you grant access only to that schema. We also have access to tables in sch3, since that schema gave explicit privileges to sch1.

sql> set current schema to sch2;
No permissions. (-1511)
sql> set current schema to sch3;
No permissions. (-1511)
sql> set current schema to PUBLIC;

We can't make schemas sch2 or sch3 current, because we have not been granted access to these schemas.

Edited by Support 11:03, 21 November 2006 (PST)