Document Type | Technical Information
Category | Administration
Applicable Product Versions | Tibero5, Tibero6, Tibero7
Document Number | TADTI214
Overview
This document describes a BMT scenario to verify the proper operation of the user management function in the TAC environment.
Method
User account creation, password setting, and privilege assignment possible
Execution
| Execution Order | Scenario |
|---|---|
| 1 | Connect to tbsql |
| 2 | Create user and set password |
| 3 | Assign privileges |
| 4 | Verify privilege information |
Result
| Execution Order | Scenario |
|---|---|
| 1 | Connect to tbsql |
| tbsql sys/tibero | |
| 2 | Create user and set password |
CREATE USER TEST IDENTIFIED BY 'TEST'; USER 'TEST' CREATED.
SELECT USERNAME FROM DBA_USERS WHERE USERNAME='TEST'; USERNAME ---------------- TEST 1 ROW SELECTED | |
| 3 | Assign privileges |
GRANT RESOURCE TO TEST; GRANTED. | |
| 4 | Verify privilege information |
COL GRANTEE FOR A30 COL GRANTED_ROLE FOR A30 COL ADMIN_OPTION FOR A30 SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE ='TEST';
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE 1 row selected. |
Blocking access for users without privileges to other users' tables
Execution
| Execution Order | Scenario |
|---|---|
| 1 | Create user (test1) |
| 2 | Grant resource and connect privileges to user (test1) |
| 3 | Create table (tbl) for user (test1) |
| 4 | Insert data into table (tbl) for user (test1) |
| 5 | Connect as user (test1) |
| 6 | Query table (tbl) as user (test1) |
| 7 | Create another user (test2) |
| 8 | Grant resource and connect privileges to another user (test2) |
| 9 | Connect as another user (test2) |
| 10 | Attempt to query test1's table (tbl) as user (test2) |
Result
| Execution Order | Scenario |
|---|---|
| 1 | Create user (test1) |
CREATE USER TEST1 IDENTIFIED BY 'TEST1'; User 'TEST1' created. | |
| 2 | Grant resource and connect privileges to user (test1) |
GRANT CONNECT , RESOURCE TO TEST1; GRANTED. | |
| 3 | Create table (tbl) for user (test1) |
CREATE TABLE TEST1.TBL (ID NUMBER); TABLE 'TEST1.TBL' CREATED. | |
| 4 | Insert data into table (tbl) for user (test1) |
INSERT INTO TEST1.TBL VALUES (1);
COMMIT; COMMIT COMPLETED. | |
| 5 | Connect as user (test1) |
conn TEST1/TEST1 Connected to Tibero. | |
| 6 | Query table (tbl) as user (test1) |
SELECT * FROM TEST1.TBL; ID ------------------- 1 1 ROW SELECTED. | |
| 7 | Create another user (test2) |
conn SYS/TIBERO
CREATE USER TEST2 IDENTIFIED BY 'TEST2'; | |
| 8 | Grant resource and connect privileges to another user (test2) |
GRANT CONNECT , RESOURCE TO TEST2; GRANTED. | |
| 9 | Connect as another user (test2) |
conn TEST2/TEST2 Connected to Tibero. | |
| 10 | Attempt to query test1's table (tbl) as user (test2) |
SELECT * FROM TEST1.TBL; TBR-8033: SPECIFIED SCHEMA OBJECT WAS NOT FOUND. SELECT * FROM TEST1.TBL ^ |
Confirm access for users granted privileges to other users' tables
Execution
| Execution Order | Scenario |
|---|---|
| 1 | Adjust column format and line size |
| 2 | Check privileges on table (TEST1.TBL) |
| 3 | Grant select privilege on table (TEST1.TBL) to another user (TEST2) |
| 4 | Verify select privilege on table (TEST1.TBL) granted to another user (TEST2) |
Result
| Execution Order | Scenario |
|---|---|
| 1 | Adjust column format and line size |
conn SYS/TIBERO
| |
| 2 | Check privileges on table (TEST1.TBL) |
SELECT * FROM DBA_TAB_PRIVS WHERE OWNER ='TEST1' AND TABLE_NAME = 'TBL'; 0 ROW SELECTED. | |
| 3 | Grant select privilege on table (TEST1.TBL) to another user (TEST2) |
GRANT SELECT ON TEST1.TBL TO TEST2; GRANTED. | |
| 4 | Verify select privilege on table (TEST1.TBL) granted to another user (TEST2) |
SELECT * FROM DBA_TAB_PRIVS WHERE OWNER = 'TEST1' AND TABLE_NAME = 'TBL';
-------------------- -------------------- -------------------- -------------------- -------------------- ---- ---------------- |