SYNONYM

SYNONYM

SYNONYM is an alternate name for an object. It is a schema object.
Syntax
CREATE synonym E1 fro Employees;

SELECT * FROM Employees;
SELECT * FROM E1; 

Rules:

When you call the synonym name it is called the table name itself.

The insertion operation performed in the synonym means the data inserted in the table.

There are multiple number of synonym created in the single table.

The synonym name alteration is possible by using another synonyms name.

CREATE synonym E2 for E1;

Now E2 will call the E1
    E1 will call the table Employees;

DROP SYNONYM E2;-- This is the command for drop synonym

RENAME E1 to E5;-- You can change the synonym name it means rename the old synonym name.

When table is dropped means synonym does not dropped.

After drop the table the synonym is call means Error ORA-00980 Synonym translation is no longer valid.

When you drop the synonym name means synonym only dropped table does not dropped.

Without table synonym is created but its synonym call means error occur.

Two Types 

Private Synonym : You cant access the synonym for any schema.
Public Synonym  : You can access the synonym for any schema.  

A normal synonym is called private synonym whereas a public synonym is created by a keyword public.

A private synonym is accessible within your schema and a public synonym is accessible to any schema in the database.

CREATE SYNONYM system privilege is required to create a private synonym and CREATE PUBLIC SYNONYM system privilege is required to create a public synonym

DCL DATA CONTROL LANGUAGE

DCL DATA CONTROL LANGUAGE

GRANT
REVOKE

DATABASE: 
Its a collection of schema(users).

To Find the database user

SQL> SELECT name from v$database;

NAME                                                                
------                                                           
XE                                                                              
To Find the how many users in the database

SQL> SELECT * FROM all_users;

USERNAME                          USER_ID CREATED                               
------------------------------ ---------- ---------                             
XS$NULL                        2147483638 29-MAY-14                             
SUGA                                   50 11-JAN-17                             
ANBU                                   49 09-MAR-16                             
PARTHI                                 48 09-MAR-16                             
APEX_040000                            47 29-MAY-14                             
APEX_PUBLIC_USER                       45 29-MAY-14                             
FLOWS_FILES                            44 29-MAY-14                             
HR                                     43 29-MAY-14                             
MDSYS                                  42 29-MAY-14                             
ANONYMOUS                              35 29-MAY-14                             
CTXSYS                                 32 29-MAY-14                             
APPQOSSYS                              30 29-MAY-14                             
DBSNMP                                 29 29-MAY-14                             
ORACLE_OCM                             21 29-MAY-14                             
DIP                                    14 29-MAY-14                             
OUTLN                                   9 29-MAY-14                             
SYSTEM                                  5 29-MAY-14                             
SYS                                     0 29-MAY-14                             

To Create a new user.

SQL> CREATE user KARTHI identified by admin;
User created.

To Give permission for newly created user.

SQL> GRANT CONNECT, RESOURCE TO KARTHI;
Grant succeeded.

To Change the Password in their login

SQL> ALTER USER KARTHI identified by open;
User altered.

To Give permission for access data.

SQL> GRANT SELECT ON EMPLOYEES TO KARTHI;
Grant succeeded.

To Remove the permissions for access the data.

SQL> REVOKE SELECT ON Employees FROM KARTHI;
Revoke succeeded.

To Give all Privillages like INSERT, DELETE,UPDATE

SQL> GRANT INSERT,DELETE,UPDATE ON Employees to karthi;
Grant succeeded.

To view all Privillages and who gives permission to whom.

SQL> select * from user_tab_privs;

GRANTEE   OWNER      TABLE_NAME     GRANTOR    PRIVILEGE     GRA HIE                            
--------  ---------  -------------  ---------  ------------- --- ---                            
KARTHI    HR         EMPLOYEES      HR         UPDATE        NO  NO                             
KARTHI    HR         EMPLOYEES      HR         INSERT        NO  NO                             
KARTHI    HR         EMPLOYEES      HR         DELETE        NO  NO                             

3 rows selected.

To remove all permissions.

SQL> revoke all on employees from karthi;
Revoke succeeded.

To give permission to all users.

SQL> GRANT SELECT ON EMPLOYEES TO public;
Grant succeeded.

To remove permission to all users.

SQL> revoke SELECT ON EMPLOYEES from public;
Revoke succeeded.

ROLE: Group of USER

SQL> create role r1;
Role created.

SQL> GRANT R1 to karthi,parthi,anbu;
Grant succeeded.

SQL> GRANT SELECT ON EMPLOYEES TO r1;
Grant succeeded.

SQL> revoke r1 from parthi;
Revoke succeeded.

SQL> drop role r1;
Role dropped.

To give permissions for all tables.If DBA Permission granted means the user can create an another user.

SQL> grant dba to karthi;
Grant succeeded.

To remove permissions for all tables.

SQL> revoke dba from  karthi;
Revoke succeeded.

SQL> drop user karthi;
User dropped.