PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

On-line Analytical Processing (OLAP)

DBA Stuff Part - 2

SQL> cl scr 
 
SQL> SET VERIFY OFF 
SQL> cl scr 
 
SQL> SET SQLPROMPT SCOTT 
SCOTTSET SQLPROMPT SCOTT> 
SCOTT>cl scr 
 
SCOTT>CREATE USER DBASEC 
  2   IDENTIFIED BY DBASEC; 
 IDENTIFIED BY DBASEC 
               * 
ERROR at line 2: 
ORA-01031: insufficient privileges  
 
 
SCOTT>COLUMN Empno FORMAT 9999 
SCOTT>COLUMN MGR FORMAT 9999 
SCOTT>COLUMN Sal FORMAT 9999 
SCOTT>COLUMN Comm FORMAT 9999 
SCOTT>COLUMN Deptno FORMAT 99 
SCOTT>cl scr 
 
SCOTT>SELECT * FROM Emp; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 
14 rows selected. 
 
SCOTT>GRANT SELECT 
  2   ON Emp 
  3   TO DBASEC; 
 
Grant succeeded. 
 
SCOTT>SPOOL OFF 
SQL> SET SQLPROMPT SCOTT> 

SCOTT>cl scr 
 
SCOTT>DESC USER_TAB_PRIVS_MADE 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 GRANTEE                                   NOT NULL VARCHAR2(30) 
 TABLE_NAME                                NOT NULL VARCHAR2(30) 
 GRANTOR                                   NOT NULL VARCHAR2(30) 
 PRIVILEGE                                 NOT NULL VARCHAR2(40) 
 GRANTABLE                                          VARCHAR2(3) 
 HIERARCHY                                          VARCHAR2(3) 
 
SCOTT>COLUMN GRANTEE FORMAT A15 
SCOTT>COLUMN "Table" FORMAT A15 
SCOTT>COLUMN GRANTOR FORMAT A15 
SCOTT>COLUMN PRIVILEGE FORMAT A15 
SCOTT>SELECT 
  2   GRANTEE, 
  3   TABLE_NAME "Table", 
  4   GRANTOR, 
  5   PRIVILEGE 
  6  FROM USER_TAB_PRIVS_MADE; 
 
no rows selected 
 
SCOTT> 
SCOTT>cl scr 
 
SCOTT>COLUMN Empno FORMAT 9999 
SCOTT>COLUMN Sal FORMAT 9999 
SCOTT>COLUMN Comm FORMAT 9999 
SCOTT>COLUMN MGR FORMAT 9999 
SCOTT>COLUMN Deptno FORMAT 99 
SCOTT>cl scr 
 
SCOTT>SELECT * FROM Emp; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    

 
14 rows selected. 
 
SCOTT>GRANT SELECT 
  2   ON Emp 
  3   TO DBASEC; 
 
Grant succeeded. 
 
SCOTT>SELECT 
  2   GRANTEE, 
  3   TABLE_NAME "Table", 
  4   GRANTOR, 
  5   PRIVILEGE 
  6  FROM USER_TAB_PRIVS_MADE; 
 
GRANTEE         Table           GRANTOR         PRIVILEGE                        
--------------- --------------- --------------- ---------------                  
DBASEC          EMP             SCOTT           SELECT                           
 
SCOTT>GRANT SELECT, DELETE 
  2   ON Dept 
  3   TO DBASEC; 
 
Grant succeeded. 
 
SCOTT>SELECT 
  2   GRANTEE, 
  3   TABLE_NAME "Table", 
  4   GRANTOR, 
  5   PRIVILEGE 
  6  FROM USER_TAB_PRIVS_MADE; 
 
GRANTEE         Table           GRANTOR         PRIVILEGE                        
--------------- --------------- --------------- ---------------                  
DBASEC          DEPT            SCOTT           SELECT                           
DBASEC          EMP             SCOTT           SELECT                           
DBASEC          DEPT            SCOTT           DELETE                           
 
SCOTT>SELECT * FROM Dept; 
 
DEPTNO DNAME          LOC                                                        
------ -------------- -------------                                              
    10 ACCOUNTING     NEW YORK                                                   
    20 RESEARCH       DALLAS                                                     
    30 SALES          CHICAGO                                                    
    40 OPERATIONS     BOSTON                                                     
 
SCOTT>ROLLBACK; 
 
Rollback complete. 
 
SCOTT>cl scr 
 
SCOTT>GRANT INSERT 
  2   ON Dept 

  3   TO DBASEC; 
 
Grant succeeded. 
 
SCOTT>SELECT * FROM Dept; 
 
DEPTNO DNAME          LOC                                                        
------ -------------- -------------                                              
    10 ACCOUNTING     NEW YORK                                                   
    20 RESEARCH       DALLAS                                                     
    30 SALES          CHICAGO                                                    
    40 OPERATIONS     BOSTON                                                     
 
SCOTT>INSERT INTO SCOTT.Dept 
  2   VALUES(50, 'SHIPPING', 'CHENNAI'); 
 
1 row created. 
 
SCOTT>SELECT * FROM Dept; 
 
DEPTNO DNAME          LOC                                                        
------ -------------- -------------                                              
    50 SHIPPING       CHENNAI                                                    
    10 ACCOUNTING     NEW YORK                                                   
    20 RESEARCH       DALLAS                                                     
    30 SALES          CHICAGO                                                    
    40 OPERATIONS     BOSTON                                                     
 
SCOTT>COMMIT; 
 
Commit complete. 
 
SCOTT>REVOKE SELECT 
  2   ON Emp 
  3   FROM DBASEC; 
 
Revoke succeeded. 
 
SCOTT>REVOKE SELECT, INSERT, DELETE 
  2   ON Dept 
  3   FROM DBASEC; 
 
Revoke succeeded. 
 
SCOTT>cl scr 
 
SCOTT>SELECT 
  2   GRANTEE, 
  3   TABLE_NAME "Table", 
  4   GRANTOR, 
  5   PRIVILEGE 
  6  FROM USER_TAB_PRIVS_MADE; 
 
no rows selected 
 
SCOTT>cl scr 

 
SCOTT>CREATE ROLE EmpSec; 
CREATE ROLE EmpSec 
* 
ERROR at line 1: 
ORA-01031: insufficient privileges  
 
 
SCOTT>CREATE ROLE EmpSec; 
 
Role created. 
 
SCOTT>GRANT SELECT, UPDATE 
  2   ON Emp 
  3   TO EmpSec; 
 
Grant succeeded. 
 
SCOTT>GRANT EmpSec 
  2   TO DBASEC; 
 
Grant succeeded. 
 
SCOTT>GRANT SELECT 
  2   ON Dept 
  3   TO EmpSec; 
 
Grant succeeded. 
 
SCOTT>REVOKE SELECT 
  2   ON Emp 
  3   FROM EmpSec; 
 
Revoke succeeded. 
 
SCOTT>DESC USER_ROLE_PRIVS 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 USERNAME                                           VARCHAR2(30) 
 GRANTED_ROLE                                       VARCHAR2(30) 
 ADMIN_OPTION                                       VARCHAR2(3) 
 DEFAULT_ROLE                                       VARCHAR2(3) 
 OS_GRANTED                                         VARCHAR2(3) 
 
SCOTT>SELECT 
  2   USERNAME, 
  3   GRANTED_ROLE 
  4  FROM USER_ROLE_PRIVS; 
 
USERNAME                       GRANTED_ROLE                                      
------------------------------ ------------------------------                    
SCOTT                          CONNECT                                           
SCOTT                          EMPSEC                                            
SCOTT                          RESOURCE                                          
 
SCOTT>DESC ROLE_SYS_PRIVS 

 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 ROLE                                      NOT NULL VARCHAR2(30) 
 PRIVILEGE                                 NOT NULL VARCHAR2(40) 
 ADMIN_OPTION                                       VARCHAR2(3) 
 
SCOTT>COLUMN ROLE FORMAT A10 
SCOTT>COLUMN PRIVILEGE FORMAT A20 
SCOTT>SELECT 
  2   ROLE, 
  3   PRIVILEGE 
  4  FROM ROLE_SYS_PRIVS; 
 
ROLE       PRIVILEGE                                                             
---------- --------------------                                                  
CONNECT    CREATE VIEW                                                           
CONNECT    CREATE TABLE                                                          
CONNECT    ALTER SESSION                                                         
CONNECT    CREATE CLUSTER                                                        
CONNECT    CREATE SESSION                                                        
CONNECT    CREATE SYNONYM                                                        
CONNECT    CREATE SEQUENCE                                                       
CONNECT    CREATE DATABASE LINK                                                  
RESOURCE   CREATE TYPE                                                           
RESOURCE   CREATE TABLE                                                          
RESOURCE   CREATE CLUSTER                                                        
 
ROLE       PRIVILEGE                                                             
---------- --------------------                                                  
RESOURCE   CREATE TRIGGER                                                        
RESOURCE   CREATE OPERATOR                                                       
RESOURCE   CREATE SEQUENCE                                                       
RESOURCE   CREATE INDEXTYPE                                                      
RESOURCE   CREATE PROCEDURE                                                      
 
16 rows selected. 
 
SCOTT>DESC ROLE_TAB_PRIVS 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 ROLE                                      NOT NULL VARCHAR2(30) 
 OWNER                                     NOT NULL VARCHAR2(30) 
 TABLE_NAME                                NOT NULL VARCHAR2(30) 
 COLUMN_NAME                                        VARCHAR2(30) 
 PRIVILEGE                                 NOT NULL VARCHAR2(40) 
 GRANTABLE                                          VARCHAR2(3) 
 
SCOTT>SELECT 
  2   ROLE, 
  3   PRIVILEGE 
  4  FROM ROLE_TAB_PRIVS; 
 
ROLE       PRIVILEGE                                                             
---------- --------------------                                                  
EMPSEC     UPDATE                                                                
EMPSEC     SELECT                                                                

 
SCOTT>DROP ROLE EmpSec; 
 
Role dropped. 
 
SCOTT>cl scr 
 
SCOTT>CREATE SYNONYM Employee 
  2   FOR SCOTT.Emp; 
 
Synonym created. 
 
SCOTT>DESC Employee 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 EMPNO                                     NOT NULL NUMBER(4) 
 ENAME                                              VARCHAR2(10) 
 JOB                                                VARCHAR2(9) 
 MGR                                                NUMBER(4) 
 HIREDATE                                           DATE 
 SAL                                                NUMBER(7,2) 
 COMM                                               NUMBER(7,2) 
 DEPTNO                                    NOT NULL NUMBER(2) 
 
SCOTT>SELECT * FROM Employee; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 
14 rows selected. 
 
SCOTT>GRANT SELECT 
  2   ON Employee 
  3   TO DBASEC; 
 
Grant succeeded. 
 
SCOTT>CREATE PUBLIC SYNONYM Employee 
  2   FOR SCOTT.Emp; 

CREATE PUBLIC SYNONYM Employee 
* 
ERROR at line 1: 
ORA-01031: insufficient privileges  
 
 
SCOTT>CREATE PUBLIC SYNONYM Employee 
  2   FOR SCOTT.Emp; 
 
Synonym created. 
 
SCOTT>SPOOL OFF 

Go Back