Oracle create user using simple shell script 使用簡單 shell 腳本 新增 oracle database 帳號

用vi或vim建立script

$ vi create_schema.sh

腳本內容有建立資料庫帳號、授權使用者可連線與資源權限

最後修改oracle預設密碼到期日，從90天改為無限期

若不修改預設90天，則將PROFILE_PASSWORD_LIFT_TIME改為其他值即可

#!/bin/sh

CRE_USER=admin
DEF_PASS=123qweasdzxc
DEF_TBS=USERS
#if Y : change limit default profile password_lift_time to unlimited
#else nothing to do. (oracle default limit is 90)
PROFILE_PASSWORD_LIFT_TIME=Y

# create user and grant privileges
sqlplus -s / as sysdba <<EOF
create user ${CRE_USER} identified by "${DEF_PASS}" account unlock;
grant resource, create session to ${CRE_USER};
alter user ${CRE_USER} quota unlimited on ${DEF_TBS};
EOF

# test connect and privileges
sqlplus ${CRE_USER}/${DEF_PASS} <<EOF
create table t1 ( c1 int );
insert into t1 values (1);
update t1 set c1 = 2;
delete from t1;
drop table t1 purge;
EOF

# limit default profile PASSWORD_LIFE_TIME to unlimited
if [[ $PROFILE_PASSWORD_LIFT_TIME == "Y" ]]; then
sqlplus -s / as sysdba <<EOF
alter profile default limit PASSWORD_LIFE_TIME unlimited;
EOF
fi

rm -f create_schema.sh