新建資料庫使用者，授權以及設定密碼有效期

1、新建立使用者，並授予連線許可權
create user TEST identified by TEST;
grant connect to TEST;

 

--2  建立同義詞
select 'create synonym  TEST.'||t.object_name || ' for bhomswas.'|| t.object_name ||';' from

user_objects t where t.object_type in
('PROCEDURE','TABLE','VIEW','FUNCTION','TRIGGER')

--3  授予函式許可權
select 'grant execute on '|| t.object_name ||' to TEST;' from user_objects t where t.object_type

in
('PROCEDURE','TABLE','VIEW','FUNCTION','TRIGGER')
and t.object_name  in
(upper('Pro_Rpt_Dh_Team_Total'),
upper('Pro_Rpt_Dh_Well_Dynamic'),
upper('Pro_Rpt_Dh_Workload_Total'),
upper('Pro_SYN_TEST_xx_dayreport'),
upper('Pro_TESToiltotal_hz')
)

--4  23  授予表許可權
select 'grant select,update,delete,insert on '|| t.object_name ||' to TEST;' from user_objects t

where t.object_type in
('TABLE','VIEW')
and t.object_name in
('TEST_TOURREPORT',
'TEST_TESTOILTOURREPORTLIST',
'TEST_WELLFRACTACIDEXTRUBASE',
'TEST_PROCEDUREPARAM'
)

--5 
select 'grant select on '|| t.object_name ||' to TEST;' from user_objects t where t.object_type

in
('TABLE','VIEW')
and t.object_name not in
('TEST_TOURREPORT',
'TEST_TESTOILTOURREPORTLIST',
'TEST_WELLFRACTACIDEXTRUBASE',
'DH_BASE_PROCEDUREPARAM'
)

--6  新建資原始檔，設定有限期為7天
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

create profile TEST_profile
limit PASSWORD_LIFE_TIME 7;

alter user TEST profile TEST_profile;