A user in his schema can alter any table or select table if he has only CREATE SESSION privilege.
Workaround Example:
--------------------
1.Create one user TEST_USER and I grant CREATE SESSION Privilege.
SQL> create user test_user identified by t;
User created.
SQL> grant create session to test_user;
Grant succeeded.
SQL> alter user test_user quota 5M on users;
User altered.
This quota is only needed to create table for the user test_user. I am creating table in the test_user schema as another user who have create any table privilege.
2.I create a Table TEST_TABLE for TEST_USER.
SQL> create table test_user.test_table ( col1 number);
Table created.
3.Connect to TEST_USER and do operation.
SQL> conn test_user/t
Connected.
SQL> insert into test_user.test_table values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_schema_stats('TEST_USER', dbms_stats.auto_sample_size);
PL/SQL procedure successfully completed.
SQL> alter table test_table add col2 number;
Table altered.
SQL> desc test_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 NUMBER
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SQL> exec dbms_stats.gather_schema_stats('TEST_USER');
PL/SQL procedure successfully completed.
SQL> insert into test_table values(2,4);
1 row created.
SQL> commit;
Commit complete.
SQL> select num_rows from user_tables where table_name='TEST_TABLE';
NUM_ROWS
----------
1
SQL> exec dbms_stats.gather_schema_stats('TEST_USER');
PL/SQL procedure successfully completed.
SQL> select num_rows from user_tables where table_name='TEST_TABLE';
NUM_ROWS
----------
2
SQL> drop table test_table;
table dropped.
So a user can do everything with the table that resides in his schema.
No comments:
Post a Comment