Statement stmt = con.createStatement();
stmt.executeUpdate("create table foo (id integer not null)");
ResultSet rs = con.getMetaData().getTablePrivileges(null, null, "FOO");
while (rs.next())
{
System.out.println(rs.getString("PRIVILEGE") + " to " + rs.getString("GRANTEE"));
}
The output is:
SELECT to DBA
INSERT to DBA
UPDATE to DBA
DELETE to DBA
REFERENCES to DBA
TRIGGER to DBA
Note that I never granted anything for that table, nevertheless grants are returned.
If this information is used to re-generate needed GRANTs for a table the SQL will fail for two reasons:
granting a privilege to the role DBA generates an error:
grant select on foo to dba;
--> invalid authorization specification - system identifier: DBA in statement [grant select on foo to dba]
Apparently the TRIGGER privilege is not recognized
grant trigger on foo to dba;
--> invalid authorization specification - system identifier: DBA in statement [grant select on foo to dba]
Am I missing something in the syntax, or is the information returned by getTablePrivileges() incorrect?
When looking into INFORMATION_SCHEMA.TABLE_PRIVILEGES I see all those privileges and the GRANTOR for them is _SYSTEM so maybe getTablePrivileges() should not return them as they seem to be some internal default grants.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thomas, these privileges are granted by _SYSTEM to the DBA role, which is a system role. The points 1 and 2 are not strange as the DBA is not a role you have created or one you can drop. This information is needed for you to figure out which privileges the DBA role has.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
results in: unexpected token: TRIGGER [SQL State=42581, DB Errorcode=-5581]
This information is needed for you to figure out which privileges the DBA role has.
Hmm, but if I'm logged in as SA, then why can I not grant those privileges to DBA? How would I ever be able to use that information? As these grants only seem to be usable "internally", I'm not sure it makes sense to return them to the end user.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You cannot grant anything to DBA because all SA's privileges come from the DBA role that it has. SA can grant the DBA role, but not change it.
I don't know how you would want to use the information. It is up to you.
As for GRANT TRIGGER, it is a privilege that has been granted by _SYSTEM to the DBA role but cannot be granted at all to any other role. Hence the error message.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Consider the following code:
Statement stmt = con.createStatement(); stmt.executeUpdate("create table foo (id integer not null)"); ResultSet rs = con.getMetaData().getTablePrivileges(null, null, "FOO"); while (rs.next()) { System.out.println(rs.getString("PRIVILEGE") + " to " + rs.getString("GRANTEE")); }The output is:
Note that I never granted anything for that table, nevertheless grants are returned.
If this information is used to re-generate needed GRANTs for a table the SQL will fail for two reasons:
granting a privilege to the role DBA generates an error:
grant select on foo to dba;
--> invalid authorization specification - system identifier: DBA in statement [grant select on foo to dba]
Apparently the TRIGGER privilege is not recognized
grant trigger on foo to dba;
--> invalid authorization specification - system identifier: DBA in statement [grant select on foo to dba]
Am I missing something in the syntax, or is the information returned by
getTablePrivileges()incorrect?When looking into
INFORMATION_SCHEMA.TABLE_PRIVILEGESI see all those privileges and theGRANTORfor them is_SYSTEMso maybegetTablePrivileges()should not return them as they seem to be some internal default grants.Thomas, these privileges are granted by _SYSTEM to the DBA role, which is a system role. The points 1 and 2 are not strange as the DBA is not a role you have created or one you can drop. This information is needed for you to figure out which privileges the DBA role has.
Thanks for the quick answer.
I just realized I pasted the wrong code for 2)
results in:
unexpected token: TRIGGER [SQL State=42581, DB Errorcode=-5581]Hmm, but if I'm logged in as SA, then why can I not grant those privileges to DBA? How would I ever be able to use that information? As these grants only seem to be usable "internally", I'm not sure it makes sense to return them to the end user.
You cannot grant anything to DBA because all SA's privileges come from the DBA role that it has. SA can grant the DBA role, but not change it.
I don't know how you would want to use the information. It is up to you.
As for GRANT TRIGGER, it is a privilege that has been granted by _SYSTEM to the DBA role but cannot be granted at all to any other role. Hence the error message.
I'm using this information in SQL Workbench/J to reconstruct the complete SQL for a table.
If I understand you correctly, I can safely ignore anything where
_SYSTEMis the grantor, right? Or even anything that is granted to DBA?Yes, you can ignore these grants. You are interested in grants to roles other than DBA and in grants to users.