USER/AUTH

create_user_stmt ::=
    'CREATE USER' user_name 'IDENTIFIED BY' password
    [ 'PASSWORD POLICY' ( 'NONE' | 'LOW' | 'HIGH' ) ]
    [ 'WITH AUTH KEY' '(' auth_key_spec ')' ]

auth_key_spec ::=
    "key='" pem_public_key "',"
    "valid_before='" YYYY-MM-DD "',"
    "comment='" text "'"

-- 基本的なユーザー作成
CREATE USER app_user IDENTIFIED BY 'App#1234';

-- パスワードポリシーの指定
CREATE USER ops_user IDENTIFIED BY 'Ops@Strong1' PASSWORD POLICY HIGH;

-- AUTH KEYとともに作成（公開鍵認証）
CREATE USER app_user IDENTIFIED BY 'App#1234'
WITH AUTH KEY (
    key='-----BEGIN PUBLIC KEY-----\nMFkw...(省略)...==\n-----END PUBLIC KEY-----\n',
    valid_before='2047-12-31',
    comment='initial key'
);

drop_user_stmt ::= 'DROP USER' user_name

DROP USER old_user;

-- パスワードの変更
alter_user_pwd_stmt ::=
    'ALTER USER' user_name 'IDENTIFIED BY' new_password
    [ 'PASSWORD POLICY' ( 'NONE' | 'LOW' | 'HIGH' ) ]

-- パスワードの変更
ALTER USER app_user IDENTIFIED BY 'NewPass#456';

-- パスワードとポリシーを同時変更
ALTER USER app_user IDENTIFIED BY 'NewPass#456' PASSWORD POLICY HIGH;

user_connect_stmt ::= 'CONNECT' user_name '/' password

CONNECT app_user/App#1234;

grant_stmt  ::= 'GRANT'  priv_list 'ON' object_ref 'TO'   user_name
revoke_stmt ::= 'REVOKE' priv_list 'ON' object_ref 'FROM' user_name

priv_list  ::= priv_value ( ',' priv_value )*
object_ref ::= 'DATABASE' database_name
             | 'TABLE' ['database_name.'] owner_name '.' table_name
             | ['database_name.'] owner_name '.' table_name

-- テーブルのDML権限
GRANT SELECT ON sensor_log TO reader;
GRANT SELECT, INSERT ON sys.sensor_log TO writer;
GRANT ALL ON sys.sensor_log TO app_user;

-- 権限の取り消し
REVOKE INSERT ON sys.sensor_log FROM writer;
REVOKE ALL ON sys.sensor_log FROM app_user;

-- DDL権限（CREATE + DROP）
GRANT DDL ON DATABASE factory_a TO deploy_user;

-- 個別のDDL権限
GRANT CONNECT ON DATABASE factory_a TO app_user;
GRANT CREATE ON DATABASE factory_a TO create_user;
GRANT DROP   ON DATABASE factory_a TO drop_user;
GRANT ALTER  ON DATABASE factory_a TO ops_user;

-- 運用権限
GRANT BACKUP ON DATABASE factory_a TO backup_user;
GRANT MOUNT  ON DATABASE MACHBASEDB TO mount_user;
GRANT USAGE  ON DATABASE factory_a_backup TO report_user;

-- すべてのデータベース権限
GRANT ALL ON DATABASE factory_a TO admin_user;

-- 権限の取り消し
REVOKE BACKUP ON DATABASE factory_a FROM backup_user;

# ECDSA P-256鍵の生成
openssl ecparam -name prime256v1 -genkey -noout -out app_user.key
openssl ec -in app_user.key -pubout -out app_user.pub
chmod 600 app_user.key

# RSA 2048-bit鍵の生成
openssl genrsa -out app_user_rsa.key 2048
openssl rsa -in app_user_rsa.key -pubout -out app_user_rsa.pub
chmod 600 app_user_rsa.key

# PEMをSQLインライン形式へ変換（改行を\nにする）
awk '{printf "%s\\n", $0}' app_user.pub

alter_user_add_auth_key_stmt ::=
    'ALTER USER' user_name 'ADD AUTH KEY' '(' auth_key_spec ')'

auth_key_spec ::=
    "key='" pem_public_key "',"
    "valid_before='" YYYY-MM-DD "',"
    "comment='" text "'"

ALTER USER app_user ADD AUTH KEY (
    key='-----BEGIN PUBLIC KEY-----\nMFkw...(省略)...==\n-----END PUBLIC KEY-----\n',
    valid_before='2047-12-31',
    comment='primary key'
);

alter_user_activate_key_stmt   ::= 'ALTER USER' user_name 'ACTIVATE AUTH KEY ID'   key_id
alter_user_deactivate_key_stmt ::= 'ALTER USER' user_name 'DEACTIVATE AUTH KEY ID' key_id

ALTER USER app_user DEACTIVATE AUTH KEY ID 3;
ALTER USER app_user ACTIVATE AUTH KEY ID 3;

alter_user_alter_key_stmt ::=
    'ALTER USER' user_name 'ALTER AUTH KEY ID' key_id
    "VALID_BEFORE='" YYYY-MM-DD "'"

ALTER USER app_user ALTER AUTH KEY ID 3 VALID_BEFORE='2048-06-30';

alter_user_drop_key_stmt ::=
    'ALTER USER' user_name 'DROP AUTH KEY ID' key_id

ALTER USER app_user DROP AUTH KEY ID 3;

SELECT key_id, user_name, key_algo, key_param, activated, valid_before, comment
  FROM V$USER_AUTH_KEYS
 WHERE user_name = 'APP_USER'
 ORDER BY key_id;