Database Security

Lecture 2

Dr. Abdulrazzak Ali

 Database Security Levels
◼ Relational database: collection of related
data files
◼ Data file: collection of related tables
◼ Table: collection of related rows (records)
◼ Row: collection of related columns (fields)
A

Database Security and Auditing ch1

 Database Security Levels (continued)

Database Security and Auditing ch1

 Menaces to Databases
◼Securityvulnerability: a weakness in any information
system component

Database Security and Auditing ch1

 Documentation of User Administration
◼ Part of the administration process
◼ Reasons to document:
 Providea paper trail
 Ensure administration consistency
◼ What to document:
 Administration policies, staff and management
A

 Security procedures
 Procedure implementation scripts or programs
 Predefined roles description

Database Security and Auditing ch3

 Documentation of User Administration

Database account access procedure Database user account application form

Database Security and Auditing ch3

 Operating System Authentication

◼ Many databases (including

Microsoft SQL Server 2000)
depend on OS to authenticate
users
◼ Once an intruder is inside the OS, it is
easier to access the database
◼ Centralize administration of users
A

◼ Ideally, Users must be

authenticated at each level

Database Security and Auditing ch3

 Creating Users
◼ Must be a standardized, well-documented, and
securely managed process.
◼ Several ways in Oracle:
1. CREATE USER Statement from iSQLPlus
2. Oracle Enterprise Manager: GUI administration
tool using database authentication
A

3. Creating an Oracle User Using External

(Operating System) Authentication
4. SQL developer

Database Security and Auditing ch3

 Creating Users
◼ In Oracle10g, use the CREATE USER
statement:
 Partof the a Data Definition Language (DDL)
 Account can own different objects
◼ IDENTIFIED clause
 TellsOracle how to authenticate a user account
 BY PASSWORD option: encrypts and stores an
A
assigned password in the database
 EXTERNALLY option: user is authenticated by
the OS
 GLOBALLY AS option: depends on authentication
through centralized user management method

Database Security and Auditing ch3

 Creating Users
CREATE USER {name}
IDENTIFIED {BY password | EXTERNALLY |
GLOBALLY as ‘external_name’}
[DEFAULT TABLESPACE {tbspname}]
[TEMPORARY TABLESPACE {tmpname}]
[QUOTA {integer {K|M} ON {tbspname}]
A

[PROFILE {pname}]
[PASSWORD EXPIRE]
[ACCOUNT {lock | unlock}]

Database Security and Auditing ch3

 Creating Users (cont.)

Database Security and Auditing ch3

 Creating Users (cont.)
◼ DEFAULT TABLESPACE clause: specifies default
storage for the user.
◼ TEMPORARY TABLESPACE clause
◼ QUOTA clause: tells Oracle DB how much storage
space a user is allowed for a specified tablespace
A

◼ PROFILE clause: indicates the profile used for

limiting database resources and enforcing password
policies
Database Security and Auditing ch3
 Creating Users (cont.)
◼ PASSWORD EXPIRE clause:
tells Oracle to expire the user
password and prompts the
user to enter a new password
◼ ACCOUNT clause: enable or
disable account
◼ ALTER USER: modifies a user
A
account
◼ Oracle Enterprise Manager:
GUI administration tool

Create a new user

Database Security and Auditing ch3

 Creating Users (cont.)
CREATE USER STUDENTA
IDENTIFIED BY TRUE#1
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA 10M ON USERS A

PROFILE DEFAULT
ACCOUNT UNLOCK;

Database Security and Auditing ch3

 Creating User Using External (Operating
System) Authentication
◼ Depends on an external party to authenticate the
user
◼ Example
 Verify account belongs to ORA_DBA group
 Set the Windows registry string
OSAUTH_PREFIX_DOMAIN to FALSE
A

 View setting of the OS_AUTHENT_PREFIX

initialization parameter
 Change OS_AUTHENT_PREFIX to NULL

Database Security and Auditing ch3

 ◼ Steps (continued):
 Create an Oracle user
 Provide new user with CREATE SESSION
privilege
◼ Advantage: allows administrators to use one generic
user to run maintenance scripts without a password
A

Database Security and Auditing ch3

Step 2: You must set the windows registry string OSAUTH_PREFIXDOMAIN
to false. Use “regedit” from run, and navigate to HKEY_LOCAL_MACHINE,
SOFTWARE, ORACLE, HOME1 (or 2). Create one if the parameter does not
exist.
 Creating User Using External (Operating System)
Authentication
Step 3: SQL> SHOW PARAMETER PREFIX
Change the OS_AUTHENT_PREFIX initialization
parameter value to NULL.
Step 4: Create an Oracle user with the same name as
the windows user name that is used for external
authentication. A

SQL> CREATE USER user_name IDENTIFIED

EXTERNALLY

Database Security and Auditing ch3

 Creating User Using External (Operating System)
Authentication
Step 5: Provide new user with CREATE SESSION
privilege
SQL>GRANT CREATE SESSION To
EXTERNAL_USER;
Grant succeeded.
A

Step 6: Log off the Oracle SYS or SYSTEM account and

windows account.
Step 7: log in again using user_name.
Step 8: From command line type sqlplus
Database Security and Auditing ch3
 Creating a SQL Server User

◼ Create a login ID first; controls access to

SQL Server system
◼ Associate login ID with a database user
◼ Must be member of fixed server roles
(SYSADMIN or SECURITYADMIN) A

◼ Two types of login IDs:

 Windows Integrated (trusted) login
 SQL Server login

Database Security and Auditing ch3

 Creating a SQL Server User

SQL Server Enterprise Manager SQL Server login properties

Database Security and Auditing ch3

 Removing Users

◼ Simple process
◼ Make a backup first
◼ Obtain a written request (for auditing
purposes)
A

Database Security and Auditing ch3

 Removing an Oracle User

◼ DROP command
◼ CASCADE option: when user owns
database objects
◼ Recommendations:
 Backup the account for one to three months
A

 Listing all owned objects

 Lock the account or revoke the CREATE
SESSION privilege

Database Security and Auditing ch3

 SQL Server: Removing Windows
Integrated Logins
◼ Command line: SP_DENYLOGIN system
stored procedure
◼ Enterprise Manager:
 Highlight
the desired login
 Choose Delete from the Action menu
A

Database Security and Auditing ch3

 Modifying Users
◼ Modifications involve:
 Changing passwords
 Locking an account
 Increasing a storage quota

◼ ALTER USER DDL statement

◼ Oracle Enterprise Manager: graphical tool
A

Database Security and Auditing ch3

 SQL Server: Modifying Windows Integrated
Login Attributes

◼ Command line:
 SP_DEFAULTDB system stored procedure
 SP_DEFAULTLANGUAGE stored procedure

◼ Enterprise Manager:
A

 Expand the security container

 Select desired login
 Properties (on the Action Menu)

Database Security and Auditing ch3

 Database security

Data security system security

System Security
covers access and use of the database at
the system level, such as:
◼ the username and password
◼ the disk space allocated to users,
◼ and the system operations that users can
perform
Data security
covers access and use of the database
objects and the actions that those users
can have on the objects such as
selecting data from a table or retrieving a
value from a sequence
User Security
◼ AAA security model:
 Authentication: verifying the identity of someone
(a user, device, or an entity) who wants to access
data, resources, or applications.
◼ It give us initial access & many authentication methods
 Authorization: Access limits for authenticated
users
◼ Least privileges principle: user must be able to access
only the information and resources that are necessary
for its legitimate purpose and no more
 Accounting: auditing
User Security
◼ DB Account
◼ Privileges
◼ Roles
◼ profiles
Privileges
◼ Privileges are the right to execute particular
SQL statements. The database administrator
(DBA) is a high-level user with the ability to grant
users access to the database and its objects
◼ Example:
▪ The ability to connect to the database
▪ The ability to create a user
▪ The ability to create a table

◼ System privileges: Gaining access to the

database
◼ Object privileges: Manipulating the content of the
database objects
schema
◼ A schema is a collection of objects, such
as tables, views, and sequences.
◼ The schema is owned by a database user
who create it and has the same name as
that user.
System Privileges
◼ System privileges can be given to a user by another user who
has administrator privileges or by a user who has the right to
grant a system privilege
◼ More than 200 privileges are available such as:
– Creating new users
– Removing users
– Removing tables
– Backing up tables
◼ Special Administrative privileges: required for an administrator
to perform basic database operations are granted through two
special system privileges
• SYSDBA privilege: can do anything
• SYSOPER privilege: sub-admin access, can perform:
– Backup, recover, startup, shutdown

– No access to data itself

 System Privilege Operations Authorized

CREATE USER Grantee can create other Oracle

users (a privilege required for a
DBA role).
DROP USER Grantee can drop another user.
DROP ANY TABLE Grantee can drop a table in any
schema.
BACKUP ANY TABLE Grantee can back up any table in
any schema with the export
utility
CREATE ANY TABLE Grantee can create tables in any
schema.
SELECT ANY TABLE Grantee can query tables, views,
or snapshots in any schema
Granting and Revoking User
Privileges
◼ Permit or deny access to data or to
perform database operations
◼ In Oracle:
 System privileges:
◼ Granted only by a database administrator
◼ Granted by a user with administration privileges

 Object privileges:
◼ Granted to a user by the schema owner
◼ Granted by a user with GRANT privileges
Database Security and Auditing 38
Granting and Revoking User
Privileges (continued)
◼ In Oracle (continued):
 Grant a privilege using the DCL GRANT
statement
 Revoke a privilege using the DCL REVOKE
statement:
◼ ADMIN option
◼ GRANT option

 Oracle Enterprise Manager Security

Database Security and Auditing 39

User System Privileges
◼ Once a user is created, the DBA can grant
specific system privileges to a user.
GRANT privilege
TO user [WITH ADMIN OPTION] ;
◼ WITH ADMIN OPTION: it means give grantee
right to grant the same privileges to other users
◼ An application developer, for example, may
have the following system privileges:
– CREATE SESSION
– CREATE TABLE
– CREATE SEQUENCE
– CREATE VIEW
– CREATE PROCEDURE
Granting System Privileges

◼ The DBA can grant a user specific system

privileges.
◼ Example:
GRANT create session, create table,
create sequence, create view
TO Scott;
Grant succeeded.
Object Privileges

◼ An object privilege is a privilege or right to

perform a particular action on a specific
(object) table, view, sequence, or procedure
◼ Each object has a particular set of grantable
privileges. The table in the next slide lists the
privileges for various objects
Object Privileges
Object Privileges
◼ Object privileges vary from object to object.
◼ An owner has all the privileges on the object.
◼ An owner can give specific privileges on that
owner’s object.
◼ Syntax:
GRANT object_privilege [(columns)]
ON object
TO user
[WITH GRANT OPTION];
◼ If the grant includes WITH GRANT OPTION, then the
grantee can further grant the object privilege to other users;
otherwise, the grantee can use the privilege but cannot
grant it to other users.
Granting and Revoking User
Privileges (continued)

Database Security and Auditing 45

Granting and Revoking User
Privileges (continued)

Database Security and Auditing 46

Granting Object Privileges
◼ Grant query privileges on the EMPLOYEES
table.
GRANT select
ON employees
TO Norah, Sarah;
Grant succeeded.
◼ Grant privileges to update specific columns to
users and roles.
GRANT update (department_name, location_id)
ON departments
TO Scott, manager;
Grant succeeded.
How to Revoke Object Privileges

◼ Syntax:
REVOKE privilege ,ALL
ON object
FROM user,role,PUBLIC;

Example:
REVOKE select, insert
ON departments
FROM Scott;
.
What Is a Role?
◼ A role is a named group of related privileges that
can be granted to the user.
◼ This method makes it easier to revoke and
maintain privileges.
◼ user can have access to several roles, and
several users can be assigned the same role
◼ Pre-defined roles:
– DBA: it has all system privileges (which SYS/SYSTEM have)
– RESOURCE: Enables a user to create certain types of
objects in his own schema
– CONNECT: Enables a user to connect to the database.
Grant this role to any user or application that needs
database access.
Creating and Assigning a Role

◼ First, the DBA must create the role. Then

the DBA can assign privileges to the role
and users to the role.
Syntax
CREATE ROLE role;
Creating and Granting Privileges to
a Role
◼ Create a role
CREATE ROLE manager;

◼ Grant system privileges to a role

GRANT create table, create view
TO manager;

◼ Grant a role to users

GRANT manager TO Maha, Nora;
Changing Your Password

◼ The DBA creates your user account and initializes your

password.
◼ You can change your password by using the
ALTER USER statement.
◼ Syntax
ALTER USER user IDENTIFIED BY newpassword;
EX:
◼ ALTER USER Scott
IDENTIFIED BY lion;
User altered.
Using the WITH GRANT OPTION and
PUBLIC Keywords

◼ Give a user authority to pass along privileges.

GRANT select, insert
ON departments
TO Scott
WITH GRANT OPTION;
Grant succeeded.
• Allow all users on the system to query data from
Alice’s DEPARTMENTS table.
GRANT select
ON alice.departments
TO PUBLIC;
Grant succeeded.
Defining and Using Profiles
◼ Profile:
 Describes limitation of database resources
 Defines database users behavior
 Prevents users from wasting resources

Database Security and Auditing 54

Creating Profiles in Oracle
◼ Define two elements of security:
 Restriction
on resources
 Implementation of password policies

◼ CREATE PROFILE statement

◼ To view all created profiles, query the data
dictionary view DBA_PROFILES
◼ Resource Manager tool: creates different
CPU usage policies
Database Security and Auditing 55
Creating Profiles in Oracle
(continued)

Database Security and Auditing 56

Creating Profiles in Oracle
(continued)
◼ ALTER PROFILE: modifies a limit for a
profile
◼ ALTER USER: assigns a profile to a user
◼ Oracle Enterprise Manager Security Tool:
view all details about users and profiles in
a GUI

Database Security and Auditing 57

Creating Profiles in Oracle
(continued)

Database Security and Auditing 58

Designing and Implementing
Password Policies
◼ Password is the key to open a user
account; strong passwords are harder to
break
◼ User authentication depends on
passwords
◼ Hacker violations begin with breaking a
password

Database Security and Auditing 59

What Is a Password Policy?
◼ Set of guidelines:
 Enhances the robustness of a password
 Reduces the likelihood of password breaking

◼ Deals with:
 Complexity
 Change frequency
 Reuse

Database Security and Auditing 60

Designing Password Policies
◼ Complexity: set of guidelines for creating
passwords
◼ Aging: how long a password can be used
◼ Usage: how many times a password can
be used
◼ Storage: storing a password in an
encrypted manner

Database Security and Auditing 61

Implementing Password
Policies
◼ Oracle; using profiles:
 CREATE PROFILE
 Oracle Enterprise Manager
 PASSWORD_VERIFY_FUNCTION

Database Security and Auditing 62

Implementing Password
Policies (continued)

Database Security and Auditing 63

Example
◼ CREATE PROFILE new_profile
LIMIT PASSWORD_REUSE_MAX 10
PASSWORD_REUSE_TIME 30;

CSCE 522 - Farkas 64

CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K;
CSCE 522 - Farkas 65
Exampl 3;
CREATE PROFILE app_user2 LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 5
PASSWORD_LOCK_TIME 1/24
INACTIVE_ACCOUNT_TIME 30;

CSCE 522 - Farkas 66

 Auditing
◼ Auditing is a DBMS facility that enables DBAs to
track the use of database resources and
privileges.
◼ Auditing is enabled, the DBMS will produce an
audit trail of database operations.
◼ Each audited database operation produces an
audit trail of information including:
A

 what database object was impacted

 who performed the operation, and when.

◼ Auditing is crucial because you may need to find

an instance of an unauthorized access by an
authorized use
 Auditing
◼Some common items that can be audited by DBMS
audit facilities include:
 Login and logoff attempts (both successful and
unsuccessful)
 Database server restarts
 Commands issued by users with system administrator
privileges
 Attempted integrity violations (where changed or inserted
A

data does not match a referential, unique, or check

constraint)
 SELECT, INSERT, UPDATE, and DELETE operations

Database Security and Auditing ch1

 Auditing (cont.)
 Stored procedure executions
 Unsuccessful attempts to access a database or a table
(authorization failures)
 Changes to system catalog tables
 Row-level operations

Database Security and Auditing ch1

Server Setup
• Auditing is a default feature of the Oracle server. The
initialization parameters that influence its behaviour can be
displayed using the SHOW PARAMETER SQL*Plus
command.
SQL> SHOW PARAMETER AUDIT

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\DB10G\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
• Auditing is disabled by default, but can enabled by setting the
AUDIT_TRAIL static parameter, which has the following
allowed values.
• db or true - Auditing is enabled, with all audit records stored in the database audit trial
(SYS.AUD$).
• db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
Server Setup
• To enable auditing and direct audit records to the database
audit trail, we would do the following. (use sys)
CONNECT sys/password AS SYSDBA
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 289406976 bytes

Fixed Size 1248600 bytes
Variable Size 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
Audit Options
• First we create a new user called AUDIT_TEST.
SQL> CREATE USER audit_test IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
SQL> GRANT connect TO audit_test;
SQL> GRANT create table, create procedure TO audit_test;

• Next we audit all operations by the AUDIT_TEST user..

SQL> AUDIT ALL BY audit_test BY ACCESS;
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE,
DELETE TABLE BY audit_test BY ACCESS;
SQL> AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
Audit Options
• These options audit all DDL and DML, along with
some system events.
• DDL (CREATE, ALTER & DROP of objects)
• DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
• SYSTEM EVENTS (LOGON, LOGOFF etc.)

SQL> CONN audit_test/password

SQL> CREATE TABLE test_tab ( id NUMBER);
SQL> INSERT INTO test_tab (id) VALUES (1);
SQL> UPDATE test_tab SET id = id;
SQL> SELECT * FROM test_tab;
SQL> DELETE FROM test_tab;
View Audit Trail
The most basic view of the database audit trail is
provided by the DBA_AUDIT_TRAIL view.
SQL> SELECT username, extended_timestamp, owner, obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'AUDIT_TEST’
ORDER BY extended_timestamp;
USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME

---------- ----------------------------------- ---------- ---------- ----------------------------

AUDIT_TEST 16-FEB-2006 14:16:55.435000 +00:00 AUDIT_TEST TEST_TAB CREATE TABLE

AUDIT_TEST 16-FEB-2006 14:16:55.514000 +00:00 AUDIT_TEST TEST_TAB INSERT

AUDIT_TEST 16-FEB-2006 14:16:55.545000 +00:00 AUDIT_TEST TEST_TAB UPDATE

AUDIT_TEST 16-FEB-2006 14:16:55.592000 +00:00 AUDIT_TEST TEST_TAB SELECT

AUDIT_TEST 16-FEB-2006 14:16:55.670000 +00:00 AUDIT_TEST TEST_TAB DELETE

AUDIT_TEST 16-FEB-2006 14:17:00.045000 +00:00 AUDIT_TEST TEST_TAB DROP TABLE

Note
• The database audit trail must be deleted, or
archived, on a regular basis to prevent the
SYS.AUD$ table growing to an unnacceptable size.
• Only DBAs should have maintenance access to the
audit trail. Auditing modifications of the data in the
audit trail itself can be achieved using the following
statement.

SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

DML Action Auditing Architecture

◼ Data Manipulation Language (DML):

companies use auditing architecture for
DML changes
◼ DML changes can be performed on two
levels:
 Row level
 Column level

◼ Fine-grained auditing (FGA)

Database Security and Auditing: Protecting Data Integrity and Accessibility 76
DML Action Auditing Architecture
(continued)
❑ The first approach, we create an audit trail for DML
activities that are occurring on table. The action is recorded
before the statement is applied to the table.

Database Security and Auditing: Protecting Data Integrity and Accessibility 77

DML Action Auditing Architecture
(continued)
❑ The other approach is to register all column values either
before or after the DML statement is applied to the table.

Database Security and Auditing: Protecting Data Integrity and Accessibility 78

 DML Action Auditing Architecture
(continued)
◼ When an UPDATE statement that modifies only one
column is appled to a table,
◼ The DML auditing mechanism can record all values
for every column in the table.
◼ This is called row-level auditing.
◼ If we are interested in recording the values of the
modified columns, we use column-level auditing.

Database Security and Auditing: Protecting Data Integrity and Accessibility

79
Oracle Triggers
◼ Stored PL/SQL procedure executed whenever:
 DML operation occurs
 Specific database event occurs
◼ Six DML events (trigger timings): INSERT,
UPDATE, and DELETE
◼ Purposes:
 Audits, controlling invalid data
 Implementing business rules, generating
values
Database Security and Auditing: Protecting Data Integrity and Accessibility 80
Oracle Triggers (continued)

Database Security and Auditing: Protecting Data Integrity and Accessibility 81

Oracle Triggers (continued)
◼ CREATE TRIGGER
◼ Executed in a specific order:
 STATEMENT LEVEL triggers before
COLUMN LEVEL triggers
 BEFORE triggers before AFTER triggers
◼ USER_TRIGGERS data dictionary view: all
triggers created on a table
◼ A table can have unlimited triggers: do not
overuse them

Database Security and Auditing: Protecting Data Integrity and Accessibility 82

Oracle Triggers (continued)
Syntax:
CREATE [or REPLACE ] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT [OR] | UPDATE [ OR] | DELETE }
{ OF col_name]
ON table_name
[FOR EACH ROW]
[REFERENCE {OLD [as] old | NEW [AS] new |
PARENT [as} parent}]
WHEN (condition)
{ pl/sql_block | call_procedure_statement };

Database Security and Auditing: Protecting Data Integrity and Accessibility 83

 Example 1
CREATE OR REPLACE TRIGGER TRG_EMPLOYEES_BUR
BEFORE UPDATE ON APP_TBL2
FOR EACH ROW
DELARE

V_OPERATION VARCHAR2(20);
BEGIN
----- comment lines
-inserting , updating and deleting
-variables that are set to TRUE automatically
-by oracle, based on the action DML operation
-That fired the trigger. If an INSERT fires
-The trigger, then INSERTING is set TRUE and
-So forth for the UPDATE and DELETE.

Database Security and Auditing: Protecting Data Integrity and Accessibility 84

Example 1 (continued)
IF INSERTING THEN
V_OPERATION := ‘INSERT’ ;
ELSEIF UPDATING THEN
V_OPERATION := ‘UPDATE’;
ELSE
V_OPERATION := ‘DELETE’;
….
END;

-View all trigger created on a table by using the USER_TRIGGER data

dictionary view. The body of the trigger is contained in the TRIGGER_BODY
column.
❑Syntax:
SQL:> DESC USER_TRIGGER
Database Security and Auditing: Protecting Data Integrity and Accessibility 85
Oracle Triggers (continued)

Database Security and Auditing: Protecting Data Integrity and Accessibility 86

INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1);
INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Larry',
50001);
COMMIT;
BEGIN
DBMS_FGA.add_policy(
object_schema => ‘SCOTT',
object_name => 'EMP',
policy_name => 'SALARY_CHK_AUDIT',
audit_condition => 'SAL > 50000',
audit_column => 'SAL');
END;
CSCE 522 - Farkas 87
CONN SCOTT/password
SELECT sal FROM emp WHERE ename = 'Tim';
SELECT sal FROM emp WHERE ename = 'Larry';

CONN sys/password AS SYSDBA

SELECT sql_text
FROM dba_fga_audit_trail;

SQL_TEXT
------------------------------------------
SELECT sal FROM emp WHERE ename = 'Larry'

CSCE 522 - Farkas 88

Thank you…