Repository: incubator-ranger Updated Branches: refs/heads/master d586d08e4 -> 8af01a998
RANGER-776 : Write sql patch to create Ranger user 'rangertagsync' for all DB flavors Signed-off-by: Velmurugan Periasamy <[email protected]> Project: http://git-wip-us.apache.org/repos/asf/incubator-ranger/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-ranger/commit/8af01a99 Tree: http://git-wip-us.apache.org/repos/asf/incubator-ranger/tree/8af01a99 Diff: http://git-wip-us.apache.org/repos/asf/incubator-ranger/diff/8af01a99 Branch: refs/heads/master Commit: 8af01a9989c5375bc712d5da6f8a7b7cd7700ec3 Parents: d586d08 Author: Gautam Borad <[email protected]> Authored: Wed Dec 9 13:51:43 2015 +0530 Committer: Velmurugan Periasamy <[email protected]> Committed: Wed Dec 9 14:29:55 2015 -0500 ---------------------------------------------------------------------- .../db/mysql/patches/017-createtagsyncuser.sql | 45 ------------------ .../db/mysql/patches/018-createtagsyncuser.sql | 45 ++++++++++++++++++ .../db/oracle/patches/017-createtagsyncuser.sql | 50 -------------------- .../db/oracle/patches/018-createtagsyncuser.sql | 50 ++++++++++++++++++++ .../postgres/patches/018-createtagsyncuser.sql | 42 ++++++++++++++++ .../patches/018-createtagsyncuser.sql | 36 ++++++++++++++ .../sqlserver/patches/018-createtagsyncuser.sql | 40 ++++++++++++++++ 7 files changed, 213 insertions(+), 95 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/8af01a99/security-admin/db/mysql/patches/017-createtagsyncuser.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/patches/017-createtagsyncuser.sql b/security-admin/db/mysql/patches/017-createtagsyncuser.sql deleted file mode 100644 index 02a5285..0000000 --- a/security-admin/db/mysql/patches/017-createtagsyncuser.sql +++ /dev/null @@ -1,45 +0,0 @@ --- Licensed to the Apache Software Foundation (ASF) under one or more --- contributor license agreements. See the NOTICE file distributed with --- this work for additional information regarding copyright ownership. --- The ASF licenses this file to You under the Apache License, Version 2.0 --- (the "License"); you may not use this file except in compliance with --- the License. You may obtain a copy of the License at --- --- http://www.apache.org/licenses/LICENSE-2.0 --- --- Unless required by applicable law or agreed to in writing, software --- distributed under the License is distributed on an "AS IS" BASIS, --- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. --- See the License for the specific language governing permissions and --- limitations under the License. - -drop procedure if exists create_tag_sync; - -delimiter ;; -create procedure create_tag_sync() begin -DECLARE loginID bigint(20); - /* check tables exist or not */ - if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_portal_user') then - if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_portal_user_role') then - if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_user') then - /* check record for login id rangertagsync exist or not */ - if not exists (select * from x_portal_user where login_id = 'rangertagsync') then - INSERT INTO x_portal_user(create_time,update_time,added_by_id,upd_by_id,first_name,last_name,pub_scr_name,login_id,password,email,status,user_src,notes) VALUES (UTC_TIMESTAMP(),UTC_TIMESTAMP(),NULL,NULL,'rangertagsync','','rangertagsync','rangertagsync','f5820e1229418dcf2575908f2c493da5','rangertagsync',1,0,NULL); - end if; - set loginID = (select id from x_portal_user where login_id = 'rangertagsync'); - if not exists (select * from x_portal_user_role where user_id =loginID ) then - INSERT INTO x_portal_user_role(create_time,update_time,added_by_id,upd_by_id,user_id,user_role,status) VALUES (UTC_TIMESTAMP(),UTC_TIMESTAMP(),NULL,NULL,loginID,'ROLE_SYS_ADMIN',1); - end if; - if not exists (select * from x_user where user_name = 'rangertagsync') then - INSERT INTO x_user(create_time,update_time,added_by_id,upd_by_id,user_name,descr,status) values (UTC_TIMESTAMP(), UTC_TIMESTAMP(),NULL,NULL,'rangertagsync','rangertagsync',0); - end if; - end if; - end if; - end if; - -end;; - -delimiter ; -call create_tag_sync(); - -drop procedure if exists create_tag_sync; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/8af01a99/security-admin/db/mysql/patches/018-createtagsyncuser.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/patches/018-createtagsyncuser.sql b/security-admin/db/mysql/patches/018-createtagsyncuser.sql new file mode 100644 index 0000000..02a5285 --- /dev/null +++ b/security-admin/db/mysql/patches/018-createtagsyncuser.sql @@ -0,0 +1,45 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +drop procedure if exists create_tag_sync; + +delimiter ;; +create procedure create_tag_sync() begin +DECLARE loginID bigint(20); + /* check tables exist or not */ + if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_portal_user') then + if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_portal_user_role') then + if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_user') then + /* check record for login id rangertagsync exist or not */ + if not exists (select * from x_portal_user where login_id = 'rangertagsync') then + INSERT INTO x_portal_user(create_time,update_time,added_by_id,upd_by_id,first_name,last_name,pub_scr_name,login_id,password,email,status,user_src,notes) VALUES (UTC_TIMESTAMP(),UTC_TIMESTAMP(),NULL,NULL,'rangertagsync','','rangertagsync','rangertagsync','f5820e1229418dcf2575908f2c493da5','rangertagsync',1,0,NULL); + end if; + set loginID = (select id from x_portal_user where login_id = 'rangertagsync'); + if not exists (select * from x_portal_user_role where user_id =loginID ) then + INSERT INTO x_portal_user_role(create_time,update_time,added_by_id,upd_by_id,user_id,user_role,status) VALUES (UTC_TIMESTAMP(),UTC_TIMESTAMP(),NULL,NULL,loginID,'ROLE_SYS_ADMIN',1); + end if; + if not exists (select * from x_user where user_name = 'rangertagsync') then + INSERT INTO x_user(create_time,update_time,added_by_id,upd_by_id,user_name,descr,status) values (UTC_TIMESTAMP(), UTC_TIMESTAMP(),NULL,NULL,'rangertagsync','rangertagsync',0); + end if; + end if; + end if; + end if; + +end;; + +delimiter ; +call create_tag_sync(); + +drop procedure if exists create_tag_sync; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/8af01a99/security-admin/db/oracle/patches/017-createtagsyncuser.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/017-createtagsyncuser.sql b/security-admin/db/oracle/patches/017-createtagsyncuser.sql deleted file mode 100644 index 7b72262..0000000 --- a/security-admin/db/oracle/patches/017-createtagsyncuser.sql +++ /dev/null @@ -1,50 +0,0 @@ --- Licensed to the Apache Software Foundation (ASF) under one or more --- contributor license agreements. See the NOTICE file distributed with --- this work for additional information regarding copyright ownership. --- The ASF licenses this file to You under the Apache License, Version 2.0 --- (the "License"); you may not use this file except in compliance with --- the License. You may obtain a copy of the License at --- --- http://www.apache.org/licenses/LICENSE-2.0 --- --- Unless required by applicable law or agreed to in writing, software --- distributed under the License is distributed on an "AS IS" BASIS, --- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. --- See the License for the specific language governing permissions and --- limitations under the License. - -DECLARE - v_count number:=0; - loginID number:=0; - sql_stmt VARCHAR2(1000); - first_name VARCHAR2(20):='rangertagsync'; - scr_name VARCHAR2(20):='rangertagsync'; - login_name VARCHAR2(20):='rangertagsync'; - password VARCHAR2(50):='f5820e1229418dcf2575908f2c493da5'; - user_role VARCHAR2(50):='ROLE_SYS_ADMIN'; - email VARCHAR2(20):='rangertagsync'; -BEGIN - select count(*) into v_count from user_tables where table_name IN('X_PORTAL_USER','X_PORTAL_USER_ROLE','X_USER'); - if (v_count = 3) then - v_count:=0; - select count(*) into v_count from x_portal_user where login_id = login_name; - if (v_count = 0) then - sql_stmt := 'INSERT INTO x_portal_user(ID,CREATE_TIME,UPDATE_TIME,FIRST_NAME,LAST_NAME,PUB_SCR_NAME,LOGIN_ID,PASSWORD,EMAIL,STATUS,USER_SRC) VALUES (X_PORTAL_USER_SEQ.nextval,sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),:1,NULL,:2,:3,:4,:5,1,0)'; - EXECUTE IMMEDIATE sql_stmt USING first_name,scr_name,login_name,password,email; - commit; - end if; - select id into loginID from x_portal_user where login_id = login_name; - if (loginID > 0) then - sql_stmt := 'INSERT INTO x_portal_user_role(id,create_time,update_time,user_id,user_role,status) VALUES (X_PORTAL_USER_ROLE_SEQ.nextval,sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),:1,:2,1)'; - EXECUTE IMMEDIATE sql_stmt USING loginID,user_role; - commit; - end if; - v_count:=0; - select count(*) into v_count from x_user where user_name = login_name; - if (v_count = 0) then - sql_stmt := 'INSERT INTO x_user(id,create_time,update_time,user_name,descr,status) values (X_USER_SEQ.nextval,sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),:1,:2,0)'; - EXECUTE IMMEDIATE sql_stmt USING login_name,login_name; - commit; - end if; - end if; -end;/ http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/8af01a99/security-admin/db/oracle/patches/018-createtagsyncuser.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/018-createtagsyncuser.sql b/security-admin/db/oracle/patches/018-createtagsyncuser.sql new file mode 100644 index 0000000..7b72262 --- /dev/null +++ b/security-admin/db/oracle/patches/018-createtagsyncuser.sql @@ -0,0 +1,50 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +DECLARE + v_count number:=0; + loginID number:=0; + sql_stmt VARCHAR2(1000); + first_name VARCHAR2(20):='rangertagsync'; + scr_name VARCHAR2(20):='rangertagsync'; + login_name VARCHAR2(20):='rangertagsync'; + password VARCHAR2(50):='f5820e1229418dcf2575908f2c493da5'; + user_role VARCHAR2(50):='ROLE_SYS_ADMIN'; + email VARCHAR2(20):='rangertagsync'; +BEGIN + select count(*) into v_count from user_tables where table_name IN('X_PORTAL_USER','X_PORTAL_USER_ROLE','X_USER'); + if (v_count = 3) then + v_count:=0; + select count(*) into v_count from x_portal_user where login_id = login_name; + if (v_count = 0) then + sql_stmt := 'INSERT INTO x_portal_user(ID,CREATE_TIME,UPDATE_TIME,FIRST_NAME,LAST_NAME,PUB_SCR_NAME,LOGIN_ID,PASSWORD,EMAIL,STATUS,USER_SRC) VALUES (X_PORTAL_USER_SEQ.nextval,sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),:1,NULL,:2,:3,:4,:5,1,0)'; + EXECUTE IMMEDIATE sql_stmt USING first_name,scr_name,login_name,password,email; + commit; + end if; + select id into loginID from x_portal_user where login_id = login_name; + if (loginID > 0) then + sql_stmt := 'INSERT INTO x_portal_user_role(id,create_time,update_time,user_id,user_role,status) VALUES (X_PORTAL_USER_ROLE_SEQ.nextval,sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),:1,:2,1)'; + EXECUTE IMMEDIATE sql_stmt USING loginID,user_role; + commit; + end if; + v_count:=0; + select count(*) into v_count from x_user where user_name = login_name; + if (v_count = 0) then + sql_stmt := 'INSERT INTO x_user(id,create_time,update_time,user_name,descr,status) values (X_USER_SEQ.nextval,sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),:1,:2,0)'; + EXECUTE IMMEDIATE sql_stmt USING login_name,login_name; + commit; + end if; + end if; +end;/ http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/8af01a99/security-admin/db/postgres/patches/018-createtagsyncuser.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/patches/018-createtagsyncuser.sql b/security-admin/db/postgres/patches/018-createtagsyncuser.sql new file mode 100644 index 0000000..0c98963 --- /dev/null +++ b/security-admin/db/postgres/patches/018-createtagsyncuser.sql @@ -0,0 +1,42 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +select 'delimiter start'; +CREATE OR REPLACE FUNCTION create_tag_sync() +RETURNS void AS $$ +DECLARE + is_exist_x_portal_user integer := 0; + is_exist_x_portal_user_role integer := 0; + is_exist_x_user integer := 0; + loginID BIGINT := 0; +BEGIN + select count(*) into is_exist_x_portal_user from pg_class where relname='x_portal_user'; + select count(*) into is_exist_x_portal_user_role from pg_class where relname='x_portal_user_role'; + select count(*) into is_exist_x_user from pg_class where relname='x_user'; + IF is_exist_x_portal_user > 0 AND is_exist_x_portal_user_role > 0 AND is_exist_x_user > 0 THEN + IF not exists (select * from x_portal_user where login_id = 'rangertagsync') THEN + INSERT INTO x_portal_user(create_time,update_time,added_by_id,upd_by_id,first_name,last_name,pub_scr_name,login_id,password,email,status,user_src,notes) VALUES (current_timestamp,current_timestamp,NULL,NULL,'rangertagsync','','rangertagsync','rangertagsync','f5820e1229418dcf2575908f2c493da5','rangertagsync',1,0,NULL); + END IF; + select id into loginID from x_portal_user where login_id = 'rangertagsync'; + IF not exists (select * from x_portal_user_role where user_id =loginID ) THEN + INSERT INTO x_portal_user_role(create_time,update_time,added_by_id,upd_by_id,user_id,user_role,status) VALUES (current_timestamp,current_timestamp,NULL,NULL,loginID,'ROLE_SYS_ADMIN',1); + END IF; + IF not exists (select * from x_user where user_name = 'rangertagsync') THEN + INSERT INTO x_user(create_time,update_time,added_by_id,upd_by_id,user_name,descr,status) values (current_timestamp, current_timestamp,NULL,NULL,'rangertagsync','rangertagsync',0); + END IF; + END IF; +END; +$$ LANGUAGE plpgsql; +select create_tag_sync(); +select 'delimiter end'; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/8af01a99/security-admin/db/sqlanywhere/patches/018-createtagsyncuser.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlanywhere/patches/018-createtagsyncuser.sql b/security-admin/db/sqlanywhere/patches/018-createtagsyncuser.sql new file mode 100644 index 0000000..9b1ab09 --- /dev/null +++ b/security-admin/db/sqlanywhere/patches/018-createtagsyncuser.sql @@ -0,0 +1,36 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +BEGIN +DECLARE loginID BIGINT = 0; +IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_portal_user') THEN + IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_portal_user_role') THEN + IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_user') THEN + IF NOT EXISTS(select * from x_portal_user where login_id = 'rangertagsync') THEN + INSERT INTO dbo.x_portal_user(create_time,update_time,added_by_id,upd_by_id,first_name,last_name,pub_scr_name,login_id,password,email,status,user_src,notes) VALUES (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,NULL,NULL,'rangertagsync','','rangertagsync','rangertagsync','f5820e1229418dcf2575908f2c493da5','rangertagsync',1,0,NULL); + END IF; + select id into loginID from dbo.x_portal_user where login_id = 'rangertagsync'; + IF NOT EXISTS (select * from x_portal_user_role where user_id =loginID ) THEN + INSERT INTO dbo.x_portal_user_role(create_time,update_time,added_by_id,upd_by_id,user_id,user_role,status) VALUES (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,NULL,NULL,loginID,'ROLE_SYS_ADMIN',1); + END IF; + IF NOT EXISTS (select * from x_user where user_name = 'rangertagsync') THEN + INSERT INTO dbo.x_user(create_time,update_time,added_by_id,upd_by_id,user_name,descr,status) values (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,NULL,NULL,'rangertagsync','rangertagsync',0); + END IF; + END IF; + END IF; +END IF; +END +GO +exit \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/8af01a99/security-admin/db/sqlserver/patches/018-createtagsyncuser.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlserver/patches/018-createtagsyncuser.sql b/security-admin/db/sqlserver/patches/018-createtagsyncuser.sql new file mode 100644 index 0000000..b3c466b --- /dev/null +++ b/security-admin/db/sqlserver/patches/018-createtagsyncuser.sql @@ -0,0 +1,40 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_portal_user') +BEGIN + IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_portal_user_role') + BEGIN + IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_user') + BEGIN + IF NOT EXISTS(select * from x_portal_user where login_id = 'rangertagsync') + BEGIN + INSERT INTO x_portal_user(create_time,update_time,added_by_id,upd_by_id,first_name,last_name,pub_scr_name,login_id,password,email,status,user_src,notes) VALUES (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,NULL,NULL,'rangertagsync','','rangertagsync','rangertagsync','f5820e1229418dcf2575908f2c493da5','rangertagsync',1,0,NULL); + END + DECLARE @loginID bigint + set @loginID=(select id from x_portal_user where login_id = 'rangertagsync') + IF NOT EXISTS (select * from x_portal_user_role where user_id =@loginID ) + BEGIN + INSERT INTO x_portal_user_role(create_time,update_time,added_by_id,upd_by_id,user_id,user_role,status) VALUES (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,NULL,NULL,@loginID,'ROLE_SYS_ADMIN',1); + END + IF NOT EXISTS (select * from x_user where user_name = 'rangertagsync') + BEGIN + INSERT INTO x_user(create_time,update_time,added_by_id,upd_by_id,user_name,descr,status) values (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,NULL,NULL,'rangertagsync','rangertagsync',0); + END + END + END +END +GO +exit \ No newline at end of file
