Database Security in Oracle

1
Database Security in Oracle

• CSC 330 Topics in Databases
• Biliana Kaneva
• Deepa Subramanian

2
ORACLE user

• SYSTEM/SYS (ORACLE system users)
• Creating and Dropping Users
• create user username identified by
  passwordexternally
• drop user username cascade
• Changing password
• alter user username identified by password
• SQLPLUS prompt
• password

3
Roles

• A set of privileges
• The CONNECT role
• The RESOURCE role
• The DBA role

4
Roles(continued...)

• Creating and Dropping roles
• create role role_name not identifiedidentified
• by passwordexternally
• drop role role_name
• Granting and Revoking Privileges to/from Roles
• GRANT ANY PRIVILEGE,CREATE SESSION,SYNONYM,VIEW
• Granting and Revoking Roles
• grant role to userrole with admin option
• revoke role to userrole

5
Security in Roles

• Roles and Passwords
• alter role role_name identified
• by passwordexternally
• alter role role_name not identified
• Enabling and Disabling Roles
• set rolerole_nameidentified by
  password,ALLNONE

6
Grant Privileges on Objects

• Tables and Views
• INSERT, UPDATE, DELETE, SELECT
• Tables only
• ALTER, REFERENCES, INDEX , ALL
• Procedures, Functions, etc.
• EXECUTE
• grant privilege on object to userrole
• with grant option

7
Synonyms

• Alias assigned to a table
• create synonym alias for username.table_name
• Public Synonyms
• grant privilege on Table_name to public
• create public synonym Table_name for
  user.Table_name

8
Security By User

• See your salary in a table
• create view MySalary as
• select from Employees
• where SUBSTR(Name,1,INSTR(NAME, )-1) USER
• connect Tony
• select from Manager.MySalary

9
Granting Limited Resources

• TABLESPACE - a file or set of files used to store
  ORACLE data
• Quota on TABLESPACE
• alter user username quota integer unlimited
  on TableSpace

10
Finding Your Own Tables

• DICTIONARY (DICT)
• USER_TABLES, USER_VIEWS, USER_CATALOG,
  USER_SYNONYMS
• select Table_Name from USER_TABLES
• select View_Name from USER_VIEWS
• select Synonym_Name from USER_SYNONYMS
• select Table_Name from USER_CATALOG