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