Oracle Study Notes (4)

1. control user access
1. Create and modify user Creating Users
Create/alter user new_user identified by password;
Example: create user user_1 indentified by pwd_1
Alter user user_1 identified by pwd_2
2. grant permissions to users
Grant privilege [, privilege] to user [, user | role, public...]
Some system permissions:
Create session/table/sequence/view/procedure

Alter/delete/execute/index/insert/references/select/update
Grant object_priv [(columns)]
On object
To {user | role | public}
[With grant option]
For example:
Grant user_1 the permission to query tt1 tables.
Grant select on tt1 table to user_1
Grant user_1 the permission to modify column 1 (column 1, column 2) in table 1
Grant update (column 1, column 2) on table 1 to user_1;
Grant the query permission to user_1 and grant the permission to other users.
Grant select
On tt1 table
To user_1
With grant option
Grant permissions to all users
Grant select
On Table 1
To public;

Authorize user_1
Grant create session to user_1;

2. Create a role and authorize it
1. Create a role
Create role mangager;
Example: create role test_role1;
2. Authorize a role
Grant create table, create view to manager;
Example: grant create table to test_role1;

3. Grant a role to a user
Grant manager to user_1, user_2...
Grant test_user1 to user_1;

3. Cancel User Permissions
Revoke {privilege [, privilege...] | all}
On object
From {user [, user...] | role | public}
[Cascade constraints];

Revoke select on table 1 from user_1;

Iv. Database Links
Create public database link hq.acme.com using 'sales ';
Select * from emp@hq.acme.com;

V. oracle union, intersection, and difference
The data type of the retrieved column must be compatible.
1. Get the Union
Union: sorts data, compresses duplicate records, and union all does not.
Select employee_id, job_id from employess
Union
Select employee_id, job_id from job_history;
Retrieve all Union sets without removing duplicate data
Select employee_id, job_id from employess
Union all
Select employee_id, job_id from job_history;

2. Intersection
Select employee_id, job_id from employess
Intersect
Select employee_id, job_id from job_history;

3. difference set
Table employess removes data owned by the table
Select employee_id, job_id from employess
Minus
Select employee_id, job_id from job_history;

Vi. Date and Time Functions
Time Difference
Select tz_offset ('us/Eastern ') from dual;

Alter session set time_zone = '-8:0 ';
Select sessiontimezone, current_date from dual;
Alter session set time_zone = '-8:0 ';
Select sessiontimezone, current_timestamp from dual;

Alter session set time_zone = '-8:0 ';
Select current_timestamp, localtimestamp from dual;

Select dbtimezone, sessiontimezone from dual;
Select from_tz (timestamp '2017-03-23 08:00:00 ', '3: 00') from dual;

Select to_timestamp ('2017-02-01 11:00:00 ', 'yyyy-MM-DD HH: MI: ss') from dual;

Select to_timestamp_tz ('2017-02-01 11:00:00 ', 'yyyy-MM-DD HH: MI: ss tzh: tzm') from dual;
To_ymininterval ()