db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-6971) Grant permission based on Schema
Date Wed, 18 Oct 2017 01:41:00 GMT

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

Rick Hillegas edited comment on DERBY-6971 at 10/18/17 1:40 AM:
----------------------------------------------------------------

Thanks for those pointers. The following MySQL-inspired syntax makes sense to me:

{noformat}
  GRANT <privilege> ON <objectType> <schemaName>.* TO <grantee>

and

  REVOKE <privilege> ON <objectType> <schemaName>.* FROM <grantee>
RESTRICT

where

  <privilege> ::=
    <routinePrivilege> | <sequencePrivilege> | <typePrivilege> |
    <aggregatePrivilege> | <tablePrivilege> | <allPrivilege> | <roleName>

  <routinePrivilege> ::= EXECUTE
  <sequencePrivilege> ::= <usagePrivilege>
  <typePrivilege> ::= <usagePrivilege>
  <aggregatePrivilege> ::= <usagePrivilege>
  <usagePrivilege> ::= USAGE
  <allPrivilege> ::= ALL PRIVILEGES

  <tablePrivilege> ::= DELETE | INSERT | REFERENCES | SELECT | TRIGGER | UPDATE

  <objectType> ::= TABLE | FUNCTION | PROCEDURE | SEQUENCE | TYPE | DERBY AGGREGATE
| ALL
{noformat}

A more detailed functional spec would be need to be written. But the following examples suggest
how this feature would behave:

{noformat}
1) Grant read access to all tables in a schema to all users:

  GRANT SELECT ON TABLE mySchema.* TO PUBLIC

2) Grant all privileges on all objects in a schema to the userAdmin role:

  GRANT ALL PRIVILEGES ON ALL mySchema.* TO userAdmin
{noformat}

Would this satisfy your needs?



was (Author: rhillegas):
Thanks for those pointers. The following MySQL-inspired syntax makes sense to me:

<noformat>
  GRANT <privilege> ON <objectType> <schemaName>.* TO <grantee>

and

  REVOKE <privilege> ON <objectType> <schemaName>.* FROM <grantee>
RESTRICT

where

  <privilege> ::=
    <routinePrivilege> | <sequencePrivilege> | <typePrivilege> |
    <aggregatePrivilege> | <tablePrivilege> | <allPrivilege> | <roleName>

  <routinePrivilege> ::= EXECUTE
  <sequencePrivilege> ::= <usagePrivilege>
  <typePrivilege> ::= <usagePrivilege>
  <aggregatePrivilege> ::= <usagePrivilege>
  <usagePrivilege> ::= USAGE
  <allPrivilege> ::= ALL PRIVILEGES

  <tablePrivilege> ::= DELETE | INSERT | REFERENCES | SELECT | TRIGGER | UPDATE

  <objectType> ::= TABLE | FUNCTION | PROCEDURE | SEQUENCE | TYPE | DERBY AGGREGATE
| ALL
<noformat>

A more detailed functional spec would be need to be written. But the following examples suggest
how this feature would behave:

<noformat>
1) Grant read access to all tables in a schema to all users:

  GRANT SELECT ON TABLE mySchema.* TO PUBLIC

2) Grant all privileges on all objects in a schema to the userAdmin role:

  GRANT ALL PRIVILEGES ON ALL mySchema.* TO userAdmin
<noformat>

Would this satisfy your needs?


> Grant permission based on Schema
> --------------------------------
>
>                 Key: DERBY-6971
>                 URL: https://issues.apache.org/jira/browse/DERBY-6971
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: haojie ma
>
> Right now, Derby doesn't support grant permission based on schema, it only support on
the table level. It is easier for the users if derby can have this feature.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message