db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4295) SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE cannot be executed after granting permission for execute.
Date Mon, 13 Jul 2009 21:35:14 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4295?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12730528#action_12730528
] 

Kathey Marsden commented on DERBY-4295:
---------------------------------------

I talked to Mike who felt that compress should require the same permissions as alter table.
 I checked the SQL spec and found this about ALTER TABLE.
The enabled authorization identifiers shall include the <authorization identifier> that
owns the schema
identified by the <schema name> of the table identified by <table name>.

I verified that this is how it is currently implemented as follows.
derby.properties:
derby.database.sqlAuthorization=true
derby.connection.requireAuthentication=true
derby.infolog.append=true
derby.authentication.provider=BUILTIN
derby.stream.error.logSeverityLevel=0

#derby.language.logStatementText=true

# User's Definitions
derby.user.DBO=Dbo
derby.user.SCHAOWNER=SCHAOwner
derby.user.SCHBOWNER=SCHBOwner
ij> connect 'jdbc:derby:wombat;create=true;user=DBO;password=Dbo';
ij> connect 'jdbc:derby:wombat;user=SCHAOWNER;password=SCHAOwner';
ij(CONNECTION1)> create table atab (i int);
0 rows inserted/updated/deleted
ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1);
0 rows inserted/updated/deleted
ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHBOWNER', 'BTAB', 1);
ERROR 38000: The exception 'java.sql.SQLException: Schema 'SCHBOWNER' does not exist' was
thrown while evaluating an expression.
ERROR 42Y07: Schema 'SCHBOWNER' does not exist
ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1,
1);
0 rows inserted/updated/deleted
ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1,
1);
0 rows inserted/updated/deleted
ij(CONNECTION1)> connect 'jdbc:derby:wombat;user=SCHBOWNER;password=SCHBOwner';
ij(CONNECTION2)> create table btab (i int);
0 rows inserted/updated/deleted
ij(CONNECTION2)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1);
ERROR 38000: The exception 'java.sql.SQLException: User 'SCHBOWNER' can not perform the operation
in schema 'SCHAOWNER'.' was thrown while evaluating an expression.
ERROR 42507: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'.
ij(CONNECTION2)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHBOWNER', 'BTAB', 1);
0 rows inserted/updated/deleted
ij(CONNECTION2)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1,
1);
ERROR 38000: The exception 'java.sql.SQLException: User 'SCHBOWNER' can not perform the operation
in schema 'SCHAOWNER'.' was thrown while evaluating an expression.
ERROR 42507: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'.
ij(CONNECTION2)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1,
1);
ERROR 38000: The exception 'java.sql.SQLException: User 'SCHBOWNER' can not perform the operation
in schema 'SCHAOWNER'.' was thrown while evaluating an expression.
ERROR 42507: User 'SCHBOWNER' can not perform the operation in schema 'SCHAOWNER'.
ij(CONNECTION2)> connect 'jdbc:derby:wombat;user=DBO;password=Dbo';
ij(CONNECTION3)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1);
0 rows inserted/updated/deleted
ij(CONNECTION3)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHBOWNER', 'BTAB', 1);
0 rows inserted/updated/deleted
ij(CONNECTION3)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1,
1);
0 rows inserted/updated/deleted
ij(CONNECTION3)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SCHAOWNER', 'ATAB', 1, 1,
1);
0 rows inserted/updated/deleted
ij(CONNECTION3)> 

At least the DBO can perform the operation. As Lily reported  earlier  no level of grant privilege
will make a difference.   I still would prefer that only grant execute priv be required on
the procedure because even though our code passes through ALTER TABLE, it is not really an
ALTER TABLE from the user perspective.



> SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE cannot be executed after granting permission
for execute. 
> --------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4295
>                 URL: https://issues.apache.org/jira/browse/DERBY-4295
>             Project: Derby
>          Issue Type: Bug
>          Components: Documentation
>    Affects Versions: 10.5.1.1
>         Environment: Windows Vista
>            Reporter: Lily Wei
>            Assignee: Mamta A. Satoor
>            Priority: Minor
>         Attachments: derby4295.diff
>
>
> When using derby.properties file from mailjdbc tests, call SYSCS_UTIL.SYCS_INPLACE_COMPRESs_TABLE
is fine user 'BACKUP' with the following operation. Please see below:
> ij version 10.3
> ij> connect 'jdbc:derby:tpri;user=BACKUP;password=Backup';
> ij> create table a (col1 int, col2 clob);
> 0 rows inserted/updated/deleted
> ij> insert into a values (1, '1');
> 1 row inserted/updated/deleted
> ij> insert into a values (2, '2');
> 1 row inserted/updated/deleted
> ij> grant execute on procedure SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE to BACKUP;
> 0 rows inserted/updated/deleted
> ij version 10.3
> ij> connect 'jdbc:derby:tpri;user=BACKUP;password=Backup';
> ij> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('REFRESH','A',1,1,1);
> 0 rows inserted/updated/deleted              <<<====Operation successful=====
>  
> However, on 10.5, the same operation result ERROR 38000
> ij> connect 'jdbc:derby:tpri;create=true;user=REFRESH;password=Refresh';
> ij> create table a (col1 int, col2 clob);
> 0 rows inserted/updated/deleted
> ij> insert into a values (1, '1');
> 1 row inserted/updated/deleted
> ij> insert into a values (1, '2');
> 1 row inserted/updated/deleted
> ij> grant execute on procedure SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE to BACKUP;
> 0 rows inserted/updated/deleted
> ij> exit;
>  
> ij version 10.5
> ij> connect 'jdbc:derby:tpri;user=BACKUP;password=Backup';
> ij> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('REFRESH','A',1,1,1);
> ERROR 38000: The exception 'java.sql.SQLException: User 'BACKUP' can not perform
>  the operation in schema 'REFRESH'.' was thrown while evaluating an expression.
> ERROR 42507: User 'BACKUP' can not perform the operation in schema 'REFRESH'.  <<<====Operation
result with ERROR 38000
> ERROR 42507: User 'BACKUP' can not perform the operation in schema 'REFRESH'.^M
>         at org.apache.derby.iapi.error.StandardException.newException(StandardEx
> ception.java:303)^M
>         at org.apache.derby.iapi.sql.dictionary.StatementSchemaPermission.check(
> StatementSchemaPermission.java:83)^M
>         at org.apache.derby.impl.sql.conn.GenericAuthorizer.authorize(GenericAut
> horizer.java:186)^M
>         at org.apache.derby.impl.sql.execute.GenericResultSetFactory.getDDLResul
> tSet(GenericResultSetFactory.java:1073)^M
>         at org.apache.derby.impl.sql.execute.ConstantActionActivation.execute(Co
> nstantActionActivation.java:61)^M
>         at org.apache.derby.impl.sql.GenericActivationHolder.execute(GenericActi
> vationHolder.java:352)^M
>         at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Generi
> cPreparedStatement.java:414)^M
>         at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPre
> paredStatement.java:297)^M
>         at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedState
> ment.java:1235)^M
>         at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Em
> bedPreparedStatement.java:1648)^M
>         at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Embed
> PreparedStatement.java:294)^M
>         at org.apache.derby.catalog.SystemProcedures.SYSCS_INPLACE_COMPRESS_TABL
> E(SystemProcedures.java:1145)^M
>         at org.apache.derby.exe.ac83ba410fx0122x159fx9a84x0000004261f80.g0(Unkno
> wn Source)^M
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)^M
>         at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)^M
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)^M
>         at java.lang.reflect.Method.invoke(Unknown Source)^M
>         at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(ReflectMe
> thod.java:46)
>         at org.apache.derby.impl.sql.execute.CallStatementResultSet.open(CallSta
> tementResultSet.java:76)^M
>         at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Generi
> cPreparedStatement.java:416)^M
>         at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPre
> paredStatement.java:297)^M
>         at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedState
> ment.java:1235)^M
>         at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Em
> bedPreparedStatement.java:1648)^M
>         at org.apache.derby.impl.jdbc.EmbedCallableStatement.executeStatement(Em
> bedCallableStatement.java:117)^M
>         at org.apache.derby.impl.jdbc.EmbedPreparedStatement.execute(EmbedPrepar
> edStatement.java:1303)^M
>         at org.apache.derbyTesting.system.mailjdbc.utils.DbTasks.compressTable(D
> bTasks.java:618)^M
>         at org.apache.derbyTesting.system.mailjdbc.tasks.Backup.DoCompress(Backu
> p.java:79)^M
>         at org.apache.derbyTesting.system.mailjdbc.tasks.Backup.doWork(Backup.ja
> va:68)^M
>         at org.apache.derbyTesting.system.mailjdbc.tasks.Backup.run(Backup.java:
> 46)^M
> Cleanup action completed^M
>        I will add the test to test/langGrantRevokeDDLTest.java.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message