USER CREATION AND DELETION.
SQL> create user tom identified by jerry
2 default tablespace users
3 temporary tablespace temp
4 quota 20m on users;
User created.
Above command creates a user tom with password jerry.
The tablespace in which tom will store his data is "USERS".
The tablespace used for storing temporary segments will be "TEMP".
And amount of space which tom can use on "USERS" tablespace is 20M.
SQL> drop user tom cascade;
SQL> alter user scott identified by tiger account unlock;
User altered.
The above command unlocks the "SCOTT" user with password "TIGER".
SQL> select username, account_status, default_tablespace,
temporary_tablespace, profile from dba_users
where username = 'TOM'
The above query shows the account information related to "TOM".
ALTERING TABLEPSACE QUOTA
SQL> select * from dba_ts_quotas where username = 'TOM';
SQL> alter user tom quota 30m on users;
User altered.
SQL> select * from dba_ts_quotas where username = 'TOM';
T
GRANTING AND REVOKING PRIVILEGES.
SQL> grant create table to tom;
Grant succeeded.
SQL> grant create session to tom;
Grant succeeded.
SQL> grant create any table, create tablespace to tom;
Grant succeeded.
SQL> revoke create any table from tom;
Revoke succeeded.
SQL> revoke create tablespace from tom;
Revoke succeeded.
SQL> grant select, insert, update, delete on scott.emp to tom;
Grant succeeded.
SQL> revoke update,delete on scott.emp from tom;
Revoke succeeded.
ROLES
SQL> grant create any table,
2 alter any table,
3 drop any table,
4 select any table,
5 update any table,
6 delete any table
7 to manager;
SQL> grant create any index,
2 alter any index,
3 drop any index
4 to manager;
Grant succeeded.
SQL> grant alter session,
2 restricted session
3 to manager;
Grant succeeded.
SQL> grant create tablespace,
2 alter tablespace,
3 drop tablespace,
4 unlimited tablespace
5 to manager;
Grant succeeded.
SQL>create role manager;
SQL> grant select, insert, update, delete on scott.dept to manager;
Grant succeeded.
SQL> grant manager to tom;
Grant succeeded.
SQL> select * from dba_sys_privs where grantee = 'TOM';
SQL> select * from dba_sys_privs where grantee = 'MANAGER'
SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs
2 where grantee = 'TOM';
SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs
where grantee = 'MANAGER'
SQL> select * from dba_roles where role = 'MANAGER';
SQL> select * from dba_role_privs where grantee = 'TOM';
SQL> select * from role_sys_privs where role = 'MANAGER';
SQL> select * from role_tab_privs where role = 'MANAGER';
SQL> revoke manager from tom;
Revoke succeeded.
PROFILES
SQL> create profile developer limit
failed_login_attempts 3
password_lock_time unlimited
password_life_time 30
password_reuse_time 30
password_grace_time 5
idle_time 30;
Profile created.
SQL> alter user tom profile developer;
User altered.
SQL> select * from dba_profiles where profile = 'DEVELOPER';
SQL> drop profile developer cascade;
Profile dropped.
SQL> create user tom identified by jerry
2 default tablespace users
3 temporary tablespace temp
4 quota 20m on users;
User created.
Above command creates a user tom with password jerry.
The tablespace in which tom will store his data is "USERS".
The tablespace used for storing temporary segments will be "TEMP".
And amount of space which tom can use on "USERS" tablespace is 20M.
SQL> drop user tom cascade;
SQL> alter user scott identified by tiger account unlock;
User altered.
The above command unlocks the "SCOTT" user with password "TIGER".
SQL> select username, account_status, default_tablespace,
temporary_tablespace, profile from dba_users
where username = 'TOM'
Code:
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE -------- -------------- ------------------ -------------------- --------- TOM OPEN USERS TEMP DEFAULT
The above query shows the account information related to "TOM".
ALTERING TABLEPSACE QUOTA
SQL> select * from dba_ts_quotas where username = 'TOM';
Code:
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO --------------- -------- ----- --------- ------ ---------- --- USERS TOM 0 20971520 0 2560 NO
SQL> alter user tom quota 30m on users;
User altered.
SQL> select * from dba_ts_quotas where username = 'TOM';
T
Code:
ABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO --------------- -------- ----- --------- ------ ---------- --- USERS TOM 0 31457280 0 3840 NO
SQL> grant create table to tom;
Grant succeeded.
SQL> grant create session to tom;
Grant succeeded.
SQL> grant create any table, create tablespace to tom;
Grant succeeded.
SQL> revoke create any table from tom;
Revoke succeeded.
SQL> revoke create tablespace from tom;
Revoke succeeded.
SQL> grant select, insert, update, delete on scott.emp to tom;
Grant succeeded.
SQL> revoke update,delete on scott.emp from tom;
Revoke succeeded.
ROLES
SQL> grant create any table,
2 alter any table,
3 drop any table,
4 select any table,
5 update any table,
6 delete any table
7 to manager;
SQL> grant create any index,
2 alter any index,
3 drop any index
4 to manager;
Grant succeeded.
SQL> grant alter session,
2 restricted session
3 to manager;
Grant succeeded.
SQL> grant create tablespace,
2 alter tablespace,
3 drop tablespace,
4 unlimited tablespace
5 to manager;
Grant succeeded.
SQL>create role manager;
SQL> grant select, insert, update, delete on scott.dept to manager;
Grant succeeded.
SQL> grant manager to tom;
Grant succeeded.
SQL> select * from dba_sys_privs where grantee = 'TOM';
Code:
GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- TOM CREATE TABLE NO TOM CREATE SESSION NO
Code:
GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- MANAGER DELETE ANY TABLE NO MANAGER CREATE ANY TABLE NO MANAGER DROP TABLESPACE NO MANAGER ALTER TABLESPACE NO MANAGER ALTER ANY INDEX NO MANAGER DROP ANY TABLE NO MANAGER DROP ANY INDEX NO MANAGER UPDATE ANY TABLE NO MANAGER ALTER SESSION NO MANAGER SELECT ANY TABLE NO MANAGER RESTRICTED SESSION NO MANAGER CREATE ANY INDEX NO MANAGER ALTER ANY TABLE NO MANAGER UNLIMITED TABLESPACE NO MANAGER CREATE TABLESPACE NO 15 rows selected.
SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs
2 where grantee = 'TOM';
Code:
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE -------- ------ ------------ --------- --------- TOM SCOTT EMP SCOTT SELECT TOM SCOTT EMP SCOTT INSERT
where grantee = 'MANAGER'
Code:
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE -------- ------ ------------ --------- --------- MANAGER SCOTT DEPT SCOTT UPDATE MANAGER SCOTT DEPT SCOTT SELECT MANAGER SCOTT DEPT SCOTT INSERT MANAGER SCOTT DEPT SCOTT DELETE
SQL> select * from dba_roles where role = 'MANAGER';
Code:
ROLE PASSWORD AUTHENTICAT ------------------------------ -------- ----------- MANAGER NO NONE
SQL> select * from dba_role_privs where grantee = 'TOM';
Code:
GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- TOM MANAGER NO YES
SQL> select * from role_sys_privs where role = 'MANAGER';
Code:
ROLE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- MANAGER DROP TABLESPACE NO MANAGER CREATE ANY TABLE NO MANAGER DELETE ANY TABLE NO MANAGER ALTER TABLESPACE NO MANAGER DROP ANY TABLE NO MANAGER ALTER ANY INDEX NO MANAGER UPDATE ANY TABLE NO MANAGER DROP ANY INDEX NO MANAGER ALTER SESSION NO MANAGER RESTRICTED SESSION NO MANAGER SELECT ANY TABLE NO MANAGER CREATE TABLESPACE NO MANAGER UNLIMITED TABLESPACE NO MANAGER ALTER ANY TABLE NO MANAGER CREATE ANY INDEX NO 15 rows selected.
SQL> select * from role_tab_privs where role = 'MANAGER';
Code:
ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRA -------- ------- ------------ ------------- ----------- --- MANAGER SCOTT DEPT DELETE NO MANAGER SCOTT DEPT UPDATE NO MANAGER SCOTT DEPT SELECT NO MANAGER SCOTT DEPT INSERT NO
SQL> revoke manager from tom;
Revoke succeeded.
PROFILES
SQL> create profile developer limit
failed_login_attempts 3
password_lock_time unlimited
password_life_time 30
password_reuse_time 30
password_grace_time 5
idle_time 30;
Profile created.
SQL> alter user tom profile developer;
User altered.
SQL> select * from dba_profiles where profile = 'DEVELOPER';
Code:
PROFILE RESOURCE_NAME RESOURCE LIMIT ----------- -------------- -------- ----- DEVELOPER COMPOSITE_LIMIT KERNEL DEFAULT DEVELOPER SESSIONS_PER_USER KERNEL DEFAULT DEVELOPER CPU_PER_SESSION KERNEL DEFAULT DEVELOPER CPU_PER_CALL KERNEL DEFAULT DEVELOPER LOGICAL_READS_PER_SESSION KERNEL DEFAULT DEVELOPER LOGICAL_READS_PER_CALL KERNEL DEFAULT DEVELOPER IDLE_TIME KERNEL 30 DEVELOPER CONNECT_TIME KERNEL DEFAULT DEVELOPER PRIVATE_SGA KERNEL DEFAULT DEVELOPER FAILED_LOGIN_ATTEMPTS PASSWORD 3 DEVELOPER PASSWORD_LIFE_TIME PASSWORD 30 DEVELOPER PASSWORD_REUSE_TIME PASSWORD 30 DEVELOPER PASSWORD_REUSE_MAX PASSWORD DEFAULT DEVELOPER PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT DEVELOPER PASSWORD_LOCK_TIME PASSWORD UNLIMITED DEVELOPER PASSWORD_GRACE_TIME PASSWORD 5 16 rows selected.
SQL> drop profile developer cascade;
Profile dropped.
.jpg)
