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;