...
CREATE USER
create_user_stmt:
| Code Block |
|---|
create_user_stmt ::= 'CREATE USER' user_name 'IDENTIFIED BY' password |
사용자를 생성하는 구문은 다음과 같다.
| Code Block |
|---|
--예제 CREATE USER |
...
...
...
| Ui text box | ||
|---|---|---|
| ||
비밀번호의 최대 길이는 8글자이다. |
...
DROP USER
drop_user_stmt:
| Code Block |
|---|
drop_user_stmt ::= 'DROP USER' user_name |
사용자를 삭제하는 구문은 다음과 같다. SYS 사용자는 삭제할 수 없으며, 삭제 대상 사용자가 이미 생성한 테이블이 있을 경우에는 에러를 나타낸다.
| Code Block |
|---|
--예제 DROP USER old_user |
ALTER USER
...
alter_user_pwd_stmt:
| Code Block |
|---|
alter_user_pwd_stmt ::= 'ALTER USER' user_name 'IDENTIFIED BY' password |
사용자는 아래의 구문을 통해 비밀번호를 변경할 수 있다.
| Code Block |
|---|
--예제 ALTER USER |
...
user1 IDENTIFIED BY |
...
password |
CONNECT
user_connect_stmt:
| Code Block |
|---|
user_connect_stmt: 'CONNECT' user_name '/' password |
사용자는 응용 프로그램을 종료하지 않고, 다음의 구문을 통해 다른 사용자로 재접속할 수 있다.
| Code Block |
|---|
--예제 CONNECT |
...
user1/ |
...
password; |
사용자
...
| Code Block |
|---|
############################################3
# Connect SYS
############################################3
Create user demo identified by 'demo';
drop user demo;
create user demo1 identified by 'demo1';
create user demo2 identified by 'demo2';
alter user demo2 identified by 'demo22';
create table demo1_table (id integer);
create bitmap index demo1_table_index1 on demo1_table(id);
insert into demo1_table values(99991);
insert into demo1_table values(99992);
insert into demo1_table values(99993);
select * from demo1_table;
#Error
drop user SYS;
############################################
# Connect DEMO1
############################################
connect demo1/demo1;
#Error
alter user demo2 identified by 'demo22';
alter user demo1 identified by demo11;
connect demo2/demo22;
#Error: wrong password
connect demo1/demo11234;
# Correct password
connect demo1/demo11;
create table demo1_table (id integer);
create bitmap index demo1_table_index1 on demo1_table(id);
insert into demo1_table values(1);
insert into demo1_table values(2);
insert into demo1_table values(3);
select * from demo1_table;
select * from demo1.demo1_table;
############################################
# Connect SYS again
############################################
connect SYS/MANAGER;
select * from demo1_table;
select * from demo1.demo1_table;
#Error
drop user demo1;
connect demo1/demo11;
drop table demo1_table;
connect SYS/MANAGER;
drop user demo1; |
수행 결과는 다음과 같다.
...
관리 예제
위의 쿼리를 수행한 예제와 그 결과를 나타냈다.
| Code Block | ||
|---|---|---|
| ||
############################################ # Connect SYS : SYS 계정으로 접속함. ############################################ Mach> create user demo identified by 'demo'; Created successfully. Mach> |
...
drop user demo; Dropped successfully. Mach> |
...
create user demo1 identified by 'demo1'; Created successfully. Mach> create user demo2 identified by 'demo2'; Created successfully. Mach> |
...
alter user demo2 identified by 'demo22'; Altered successfully. Mach> |
...
create table demo1_table (id integer); Created successfully. Mach> |
...
create bitmap index demo1_table_index1 on demo1_table(id); Created successfully. Mach> |
...
insert into demo1_table values(99991); 1 row(s) inserted. Mach> insert into demo1_table values(99992); 1 row(s) inserted. Mach> insert into demo1_table values(99993); 1 row(s) inserted. Mach> |
...
select * from demo1_table;
ID
--------------
99993
99992
99991
[3] row(s) selected.
|
...
|
...
#Error: 자기 자신을 Drop 할 수 없음. Mach> drop user SYS; [ERR-02083 : Drop user error. You cannot drop yourself(SYS).] |
...
############################################ # Connect DEMO1 ############################################ Mach> connect demo1/demo1; Connected successfully. |
...
#Error: 일반 유저는 다른 사용자의 비밀번호를 바꿀 수 없다. Mach> alter user demo2 identified by 'demo22'; [ERR-02085 : ALTER user error. The user(DEMO2) does not have ALTER privileges.] Mach> |
...
alter user demo1 identified by demo11; Altered successfully. Mach> |
...
connect demo2/demo22;
Connected successfully.
|
...
|
...
#Error: wrong password
|
...
Mach> connect demo1/demo11234; [ERR-02081 : User authentication error. Invalid password (DEMO11234).] |
...
# Correct password Mach> connect demo1/demo11; Connected successfully. Mach> |
...
create table demo1_table (id integer); Created successfully. Mach> |
...
create bitmap index demo1_table_index1 on demo1_table(id); Created successfully. Mach> |
...
insert into demo1_table values(1); 1 row(s) inserted. Mach> insert into demo1_table values(2); 1 row(s) inserted. Mach> insert into demo1_table values(3); 1 row(s) inserted. Mach> |
...
select * from demo1_table; ID -------------- 3 2 1 [3] row(s) selected. Mach> select * from demo1.demo1_table; ID -------------- 3 2 1 [3] row(s) selected. |
...
############################################ # Connect SYS again ############################################ Mach> connect SYS/MANAGER; Connected successfully. Mach> |
...
select * from demo1_table; ID -------------- 99993 99992 99991 [3] row(s) selected. Mach> |
...
select * from demo1.demo1_table;
ID
--------------
3
2
1
[3] row(s) selected. |
...
#Error: demo1 유저에 속한 테이블이 존재함. Mach> drop user demo1; [ERR-02084 : DROP user error. The user's tables still exist. Drop those tables first.] Mach> |
...
connect demo1/demo11; Connected successfully. Mach> |
...
drop table demo1_table; Dropped successfully. Mach> |
...
connect SYS/MANAGER; Connected successfully. Mach> |
...
drop user demo1;
Dropped successfully. |
...