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