Oracle Database 10g SQL Fundamentals II - Practice 1

 

Oracle Database 10g SQL Fundamentals II - Practice 1
1) Controlling User Access

CREATE USER
GRANT
CREATE ROLE
ALTER USER
REVOEK

1. What privilege should a user be given to log on to the Oracle server?
Is this a system or an object privilege?
 ---> System privilege

2. What privilege should a user be given to create tables?
 ---> Resource

3. If you create a table, who can pass along privileges to other users on your table?
 sys, system, and you

4. You are the DBA. You are creating many users who require the same system privileges.
What should you use to make your job easier?
 ---> Role

5. What command do you use to change your password?

6.
 GRANT SELECT ON DEPARTMENTS TO OTHER

7.
SELECT *
FROM DEPARTMENTS
;

8.
INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
VALUES(500, 'Education')
;
COMMIT
;

INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
VALUES(510, 'Human Resources')
;
COMMIT
;

9.
CREATE SYNONYM AR09_DEPTS
FOR DEPARTMENTS
;

10.
SELECT *
FROM AR09_DEPTS
;

11.
SELECT TABLE_NAME
FROM USER_TABLES
WHERE TABLE_NAME IN ('JOB_HISTORY'
,'EMPLOYEES'
,'JOBS'
,'DEPARTMENTS'
,'LOCATIONS'
,'REGIONS'
,'COUNTRIES')

12.
SELECT TABLE_NAME, OWNER
FROM ALL_TABLES
;

13.
REVOKE SELECT ON DEPARTMENTS
FROM AR09
;

14.
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID IN (500, 510)
;

COMMIT
;