You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ranger.apache.org by Shubham Chaurasia <sh...@gmail.com> on 2019/01/04 11:03:43 UTC
Database metadata scenario in Hive plugin
Hi All,
I am encountering a strange scenario when trying to retrieve table names
using jdbc java.sql.Connection.getMetaData().getTables().
Ranger Policy
------------------------------------
Database: ranger_hive_test
Table: *
Hive Column: *
Allow Conditions: All permissions to the user.
------------------------------------
1) Now when I execute the below method:
private static void testWithConnMetaData() throws SQLException {
System.out.println("---------------------------testWithConnMetaData-----------------------------");
try (Connection con = DriverManager.getConnection(url, user, "")) {
ResultSet res = con.getMetaData().getTables(null, "ranger_hive_test",
"t1", null);
while (res.next()) {
System.out.println(res.getString("TABLE_NAME"));
}
}
System.out.println("---------------------------testWithConnMetaData-----------------------------");
}
I get empty resultset.
2) To verify policy and table existence when I execute the same using show
tables query, it gives me right results(table names in ranger_hive_test).
private static void testWithQuery() throws SQLException {
System.out.println("---------------------------testWithQuery-----------------------------");
try (Connection con = DriverManager.getConnection(url, user, "")) {
Statement stmt = con.createStatement();
String sql = "show tables in ranger_hive_test";
ResultSet res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(res.getString("tab_name"));
}
}
System.out.println("---------------------------testWithQuery-----------------------------");
}
Is there something I am missing so that I am not getting any results in
method 1 ? Anything else I need to add to policy?
Also surprisingly, when I add "*" to Database field in policy, I start
getting results in method 1.
Thanks,
Shubham
Re: Database metadata scenario in Hive plugin
Posted by Shubham Chaurasia <sh...@gmail.com>.
Hi Ramesh,
I tried to see audit logs. I was seeing logs of all the other queries which
were which were given access(i.e. 'Allowed'). But for java.sql.
Connection.getMetaData().getTables(), I was not seeing any audit log
entries.
I will try to have a look at hive logs.
Thanks
On Sat, Jan 5, 2019 at 12:38 PM Ramesh Mani <rm...@hortonworks.com> wrote:
> Hi Shubham,
>
> When you do java.sql. Connection.getMetaData().getTables(), did you get
> any ranger audit log in Ranger UI, like the user was authorized or not?
>
> Also if you have hiveserver2.log in debug, we can take a look at it for
> this command.
>
> Thanks,
> Ramesh
>
>
> On 1/4/19, 9:43 PM, "Shubham Chaurasia" <sh...@gmail.com> wrote:
>
> >Any suggestions?
> >
> >On Fri, Jan 4, 2019 at 4:33 PM Shubham Chaurasia
> ><sh...@gmail.com>
> >wrote:
> >
> >> Hi All,
> >>
> >> I am encountering a strange scenario when trying to retrieve table names
> >> using jdbc java.sql.Connection.getMetaData().getTables().
> >>
> >> Ranger Policy
> >> ------------------------------------
> >> Database: ranger_hive_test
> >> Table: *
> >> Hive Column: *
> >> Allow Conditions: All permissions to the user.
> >> ------------------------------------
> >>
> >> 1) Now when I execute the below method:
> >>
> >> private static void testWithConnMetaData() throws SQLException {
> >>
> >>
> >>System.out.println("---------------------------testWithConnMetaData------
> >>-----------------------");
> >> try (Connection con = DriverManager.getConnection(url, user, "")) {
> >>
> >> ResultSet res = con.getMetaData().getTables(null,
> >> "ranger_hive_test", "t1", null);
> >> while (res.next()) {
> >> System.out.println(res.getString("TABLE_NAME"));
> >> }
> >> }
> >>
> >>
> >>System.out.println("---------------------------testWithConnMetaData------
> >>-----------------------");
> >> }
> >>
> >>
> >> I get empty resultset.
> >>
> >> 2) To verify policy and table existence when I execute the same using
> >>show
> >> tables query, it gives me right results(table names in
> >>ranger_hive_test).
> >> private static void testWithQuery() throws SQLException {
> >>
> >>
> >>System.out.println("---------------------------testWithQuery-------------
> >>----------------");
> >> try (Connection con = DriverManager.getConnection(url, user, "")) {
> >> Statement stmt = con.createStatement();
> >>
> >> String sql = "show tables in ranger_hive_test";
> >> ResultSet res = stmt.executeQuery(sql);
> >> while (res.next()) {
> >> System.out.println(res.getString("tab_name"));
> >> }
> >> }
> >>
> >>
> >>System.out.println("---------------------------testWithQuery-------------
> >>----------------");
> >> }
> >>
> >>
> >> Is there something I am missing so that I am not getting any results in
> >> method 1 ? Anything else I need to add to policy?
> >>
> >> Also surprisingly, when I add "*" to Database field in policy, I start
> >> getting results in method 1.
> >>
> >> Thanks,
> >> Shubham
> >>
>
>
Re: Database metadata scenario in Hive plugin
Posted by Shubham Chaurasia <sh...@gmail.com>.
Hi Ramesh,
I tried to see audit logs. I was seeing logs of all the other queries which
were which were given access(i.e. 'Allowed'). But for java.sql.
Connection.getMetaData().getTables(), I was not seeing any audit log
entries.
I will try to have a look at hive logs.
Thanks
On Sat, Jan 5, 2019 at 12:38 PM Ramesh Mani <rm...@hortonworks.com> wrote:
> Hi Shubham,
>
> When you do java.sql. Connection.getMetaData().getTables(), did you get
> any ranger audit log in Ranger UI, like the user was authorized or not?
>
> Also if you have hiveserver2.log in debug, we can take a look at it for
> this command.
>
> Thanks,
> Ramesh
>
>
> On 1/4/19, 9:43 PM, "Shubham Chaurasia" <sh...@gmail.com> wrote:
>
> >Any suggestions?
> >
> >On Fri, Jan 4, 2019 at 4:33 PM Shubham Chaurasia
> ><sh...@gmail.com>
> >wrote:
> >
> >> Hi All,
> >>
> >> I am encountering a strange scenario when trying to retrieve table names
> >> using jdbc java.sql.Connection.getMetaData().getTables().
> >>
> >> Ranger Policy
> >> ------------------------------------
> >> Database: ranger_hive_test
> >> Table: *
> >> Hive Column: *
> >> Allow Conditions: All permissions to the user.
> >> ------------------------------------
> >>
> >> 1) Now when I execute the below method:
> >>
> >> private static void testWithConnMetaData() throws SQLException {
> >>
> >>
> >>System.out.println("---------------------------testWithConnMetaData------
> >>-----------------------");
> >> try (Connection con = DriverManager.getConnection(url, user, "")) {
> >>
> >> ResultSet res = con.getMetaData().getTables(null,
> >> "ranger_hive_test", "t1", null);
> >> while (res.next()) {
> >> System.out.println(res.getString("TABLE_NAME"));
> >> }
> >> }
> >>
> >>
> >>System.out.println("---------------------------testWithConnMetaData------
> >>-----------------------");
> >> }
> >>
> >>
> >> I get empty resultset.
> >>
> >> 2) To verify policy and table existence when I execute the same using
> >>show
> >> tables query, it gives me right results(table names in
> >>ranger_hive_test).
> >> private static void testWithQuery() throws SQLException {
> >>
> >>
> >>System.out.println("---------------------------testWithQuery-------------
> >>----------------");
> >> try (Connection con = DriverManager.getConnection(url, user, "")) {
> >> Statement stmt = con.createStatement();
> >>
> >> String sql = "show tables in ranger_hive_test";
> >> ResultSet res = stmt.executeQuery(sql);
> >> while (res.next()) {
> >> System.out.println(res.getString("tab_name"));
> >> }
> >> }
> >>
> >>
> >>System.out.println("---------------------------testWithQuery-------------
> >>----------------");
> >> }
> >>
> >>
> >> Is there something I am missing so that I am not getting any results in
> >> method 1 ? Anything else I need to add to policy?
> >>
> >> Also surprisingly, when I add "*" to Database field in policy, I start
> >> getting results in method 1.
> >>
> >> Thanks,
> >> Shubham
> >>
>
>
Re: Database metadata scenario in Hive plugin
Posted by Ramesh Mani <rm...@hortonworks.com>.
Hi Shubham,
When you do java.sql. Connection.getMetaData().getTables(), did you get
any ranger audit log in Ranger UI, like the user was authorized or not?
Also if you have hiveserver2.log in debug, we can take a look at it for
this command.
Thanks,
Ramesh
On 1/4/19, 9:43 PM, "Shubham Chaurasia" <sh...@gmail.com> wrote:
>Any suggestions?
>
>On Fri, Jan 4, 2019 at 4:33 PM Shubham Chaurasia
><sh...@gmail.com>
>wrote:
>
>> Hi All,
>>
>> I am encountering a strange scenario when trying to retrieve table names
>> using jdbc java.sql.Connection.getMetaData().getTables().
>>
>> Ranger Policy
>> ------------------------------------
>> Database: ranger_hive_test
>> Table: *
>> Hive Column: *
>> Allow Conditions: All permissions to the user.
>> ------------------------------------
>>
>> 1) Now when I execute the below method:
>>
>> private static void testWithConnMetaData() throws SQLException {
>>
>>
>>System.out.println("---------------------------testWithConnMetaData------
>>-----------------------");
>> try (Connection con = DriverManager.getConnection(url, user, "")) {
>>
>> ResultSet res = con.getMetaData().getTables(null,
>> "ranger_hive_test", "t1", null);
>> while (res.next()) {
>> System.out.println(res.getString("TABLE_NAME"));
>> }
>> }
>>
>>
>>System.out.println("---------------------------testWithConnMetaData------
>>-----------------------");
>> }
>>
>>
>> I get empty resultset.
>>
>> 2) To verify policy and table existence when I execute the same using
>>show
>> tables query, it gives me right results(table names in
>>ranger_hive_test).
>> private static void testWithQuery() throws SQLException {
>>
>>
>>System.out.println("---------------------------testWithQuery-------------
>>----------------");
>> try (Connection con = DriverManager.getConnection(url, user, "")) {
>> Statement stmt = con.createStatement();
>>
>> String sql = "show tables in ranger_hive_test";
>> ResultSet res = stmt.executeQuery(sql);
>> while (res.next()) {
>> System.out.println(res.getString("tab_name"));
>> }
>> }
>>
>>
>>System.out.println("---------------------------testWithQuery-------------
>>----------------");
>> }
>>
>>
>> Is there something I am missing so that I am not getting any results in
>> method 1 ? Anything else I need to add to policy?
>>
>> Also surprisingly, when I add "*" to Database field in policy, I start
>> getting results in method 1.
>>
>> Thanks,
>> Shubham
>>
Re: Database metadata scenario in Hive plugin
Posted by Ramesh Mani <rm...@hortonworks.com>.
Hi Shubham,
When you do java.sql. Connection.getMetaData().getTables(), did you get
any ranger audit log in Ranger UI, like the user was authorized or not?
Also if you have hiveserver2.log in debug, we can take a look at it for
this command.
Thanks,
Ramesh
On 1/4/19, 9:43 PM, "Shubham Chaurasia" <sh...@gmail.com> wrote:
>Any suggestions?
>
>On Fri, Jan 4, 2019 at 4:33 PM Shubham Chaurasia
><sh...@gmail.com>
>wrote:
>
>> Hi All,
>>
>> I am encountering a strange scenario when trying to retrieve table names
>> using jdbc java.sql.Connection.getMetaData().getTables().
>>
>> Ranger Policy
>> ------------------------------------
>> Database: ranger_hive_test
>> Table: *
>> Hive Column: *
>> Allow Conditions: All permissions to the user.
>> ------------------------------------
>>
>> 1) Now when I execute the below method:
>>
>> private static void testWithConnMetaData() throws SQLException {
>>
>>
>>System.out.println("---------------------------testWithConnMetaData------
>>-----------------------");
>> try (Connection con = DriverManager.getConnection(url, user, "")) {
>>
>> ResultSet res = con.getMetaData().getTables(null,
>> "ranger_hive_test", "t1", null);
>> while (res.next()) {
>> System.out.println(res.getString("TABLE_NAME"));
>> }
>> }
>>
>>
>>System.out.println("---------------------------testWithConnMetaData------
>>-----------------------");
>> }
>>
>>
>> I get empty resultset.
>>
>> 2) To verify policy and table existence when I execute the same using
>>show
>> tables query, it gives me right results(table names in
>>ranger_hive_test).
>> private static void testWithQuery() throws SQLException {
>>
>>
>>System.out.println("---------------------------testWithQuery-------------
>>----------------");
>> try (Connection con = DriverManager.getConnection(url, user, "")) {
>> Statement stmt = con.createStatement();
>>
>> String sql = "show tables in ranger_hive_test";
>> ResultSet res = stmt.executeQuery(sql);
>> while (res.next()) {
>> System.out.println(res.getString("tab_name"));
>> }
>> }
>>
>>
>>System.out.println("---------------------------testWithQuery-------------
>>----------------");
>> }
>>
>>
>> Is there something I am missing so that I am not getting any results in
>> method 1 ? Anything else I need to add to policy?
>>
>> Also surprisingly, when I add "*" to Database field in policy, I start
>> getting results in method 1.
>>
>> Thanks,
>> Shubham
>>
Re: Database metadata scenario in Hive plugin
Posted by Shubham Chaurasia <sh...@gmail.com>.
Any suggestions?
On Fri, Jan 4, 2019 at 4:33 PM Shubham Chaurasia <sh...@gmail.com>
wrote:
> Hi All,
>
> I am encountering a strange scenario when trying to retrieve table names
> using jdbc java.sql.Connection.getMetaData().getTables().
>
> Ranger Policy
> ------------------------------------
> Database: ranger_hive_test
> Table: *
> Hive Column: *
> Allow Conditions: All permissions to the user.
> ------------------------------------
>
> 1) Now when I execute the below method:
>
> private static void testWithConnMetaData() throws SQLException {
>
> System.out.println("---------------------------testWithConnMetaData-----------------------------");
> try (Connection con = DriverManager.getConnection(url, user, "")) {
>
> ResultSet res = con.getMetaData().getTables(null,
> "ranger_hive_test", "t1", null);
> while (res.next()) {
> System.out.println(res.getString("TABLE_NAME"));
> }
> }
>
> System.out.println("---------------------------testWithConnMetaData-----------------------------");
> }
>
>
> I get empty resultset.
>
> 2) To verify policy and table existence when I execute the same using show
> tables query, it gives me right results(table names in ranger_hive_test).
> private static void testWithQuery() throws SQLException {
>
> System.out.println("---------------------------testWithQuery-----------------------------");
> try (Connection con = DriverManager.getConnection(url, user, "")) {
> Statement stmt = con.createStatement();
>
> String sql = "show tables in ranger_hive_test";
> ResultSet res = stmt.executeQuery(sql);
> while (res.next()) {
> System.out.println(res.getString("tab_name"));
> }
> }
>
> System.out.println("---------------------------testWithQuery-----------------------------");
> }
>
>
> Is there something I am missing so that I am not getting any results in
> method 1 ? Anything else I need to add to policy?
>
> Also surprisingly, when I add "*" to Database field in policy, I start
> getting results in method 1.
>
> Thanks,
> Shubham
>
Re: Database metadata scenario in Hive plugin
Posted by Shubham Chaurasia <sh...@gmail.com>.
Any suggestions?
On Fri, Jan 4, 2019 at 4:33 PM Shubham Chaurasia <sh...@gmail.com>
wrote:
> Hi All,
>
> I am encountering a strange scenario when trying to retrieve table names
> using jdbc java.sql.Connection.getMetaData().getTables().
>
> Ranger Policy
> ------------------------------------
> Database: ranger_hive_test
> Table: *
> Hive Column: *
> Allow Conditions: All permissions to the user.
> ------------------------------------
>
> 1) Now when I execute the below method:
>
> private static void testWithConnMetaData() throws SQLException {
>
> System.out.println("---------------------------testWithConnMetaData-----------------------------");
> try (Connection con = DriverManager.getConnection(url, user, "")) {
>
> ResultSet res = con.getMetaData().getTables(null,
> "ranger_hive_test", "t1", null);
> while (res.next()) {
> System.out.println(res.getString("TABLE_NAME"));
> }
> }
>
> System.out.println("---------------------------testWithConnMetaData-----------------------------");
> }
>
>
> I get empty resultset.
>
> 2) To verify policy and table existence when I execute the same using show
> tables query, it gives me right results(table names in ranger_hive_test).
> private static void testWithQuery() throws SQLException {
>
> System.out.println("---------------------------testWithQuery-----------------------------");
> try (Connection con = DriverManager.getConnection(url, user, "")) {
> Statement stmt = con.createStatement();
>
> String sql = "show tables in ranger_hive_test";
> ResultSet res = stmt.executeQuery(sql);
> while (res.next()) {
> System.out.println(res.getString("tab_name"));
> }
> }
>
> System.out.println("---------------------------testWithQuery-----------------------------");
> }
>
>
> Is there something I am missing so that I am not getting any results in
> method 1 ? Anything else I need to add to policy?
>
> Also surprisingly, when I add "*" to Database field in policy, I start
> getting results in method 1.
>
> Thanks,
> Shubham
>