db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
Date Thu, 03 Apr 2008 12:34:24 GMT

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

Dag H. Wanvik commented on DERBY-3200:
--------------------------------------

Some comments on the initial description section.

> For example, if you set the default connection mode to noAccess, a
> user with fullAccess can't grant any privileges to a user with
> noAccess. 

I guess he could, but it would be of little use, since the noAccess
would override the privilege.

> And presumably if the default connection mode is readOnlyAccess, a
> user with fullAccess can't grant any privileges beyond SELECT, 

Again, the full access user could grant update privilege but it would
be of no use, since the grantee and only read (connection level
authorization overrides again).

> which the user has anyway.

No, that is not the case: when sqlAuthorization mode is active, a user
only has access to her own tables by default (and read access to
system tables it seems).

Cf section "How user authorization properties work together" in the
dev guide: 

(quote)

- The access mode specified for the
  derby.database.defaultConnectionMode property overrides the
  permissions that are granted by the owner of a database object. For
  example, if a user is granted INSERT privileges on a table but the
  user only has read-only connection authorization, the user cannot
  insert data into the table.

I interpret this "overrides" as "further restricts".

> Only if the default connection mode is fullAccess is SQL authorization
> mode meaningful. 

No, even for connections with readOnly connection access, the
GRANT/REVOKE machinery can be used to further limit access (but not to
broaden it beyond readOnly).

> That means that a fullAccess user can use GRANT to restrict another
> user's privileges on a particular database that the user owns.

GRANT/REVOKE provides more fine graned access control that connection
level authorization. The way I think of this is that, when both are
used, the more limiting access provided by each authorization
mechanism (connection or sqlAUthorization) rules the day in any
particular case.

Hope this makes sense :) It is a bit confusing, so making running
examples to check understanding is very useful here. Sorry if I
misconstrued something.



> Developer's Guide: Add examples showing use of SQL authorization with user authentication
> -----------------------------------------------------------------------------------------
>
>                 Key: DERBY-3200
>                 URL: https://issues.apache.org/jira/browse/DERBY-3200
>             Project: Derby
>          Issue Type: Improvement
>          Components: Documentation
>            Reporter: Kim Haase
>            Assignee: Kim Haase
>            Priority: Minor
>         Attachments: auth2.log, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java,
AuthExampleEmbeddedSQLAuth.java, rdevcsecuresqlauthembeddedex.dita
>
>
> This is the followup to DERBY-1823 that Francois Orsini suggested.
> I've been experimenting and reading the Developer's Guide section on SQL authorization
(User authorizations, cdevcsecure36595).
> It appears that the only use of SQL authorization mode is to restrict user access, not
to expand it.
> For example, if you set the default connection mode to noAccess, a user with fullAccess
can't grant any privileges to a user with noAccess. And presumably if the default connection
mode is readOnlyAccess, a user with fullAccess can't grant any privileges beyond SELECT, which
the user has anyway.
> Only if the default connection mode is fullAccess is SQL authorization mode meaningful.
That means that a fullAccess user can use GRANT to restrict another user's privileges on a
particular database that the user owns.
> I'm running into a problem at the end, though. At the beginning of the program, as nobody
in particular, I was able to create several users, some of them with full access. But at the
end of the program, it seems that even a user with full access isn't allowed to turn off those
database properties:
> Message:  User 'MARY' does not have execute permission on PROCEDURE 'SYSCS_UTIL'.'SYSCS_SET_DATABASE_PROPERTY'.
> This seems a bit extreme. I know that with SQL authorization on, "the ability to read
from or write to database objects is further restricted to the owner of the database objects."
But the ability to execute built-in system procedures? Can I log in as SYSCS_UTIL? How? 
> I realize that having access to SYSCS_SET_DATABASE_PROPERTY would allow me to in effect
delete myself -- but that's essentially what I do at the end of the program that sets derby.connection.requireAuthentication
but not derby.database.sqlAuthorization. 
> The documentation does say that once you have turned on SQL authorization, you can't
turn it off. But it doesn't say that you can't turn anything else off, either!
> I'll attach the program I've been using. Most of the stacktraces are expected, but I'm
stumped by that last one.

-- 
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