Transcript Enrolling and Monitoring Database Users

7202ICT – Database Administration
Lecture 10
Enrolling and Monitoring Database
Users
Part 2
CONTROLLING DATABASE PRIVILEGES
• In order to control database users it is necessary to grant them
privileges to access the database and objects within the database,
and also to allow them specific system privileges.
• DBA Control of Privileges
 Provide the user the right to perform a type of operation
 Enable and restrict access and changes to data
 Enable and restrict the ability to perform system functions and
change database structures
 Grant the privileges to individual users and roles
 Grant privileges to all users (PUBLIC)
Types of Privileges
• SYSTEM PRIVILEGE :
– A privilege or a right to perform a particular
action, or to perform a particular action on a
particular type of action
• OBJECT PRIVILEGE :
– A privilege or right to perform a particular
action on a specific TABLE, VIEW,
SEQUENCE, PROCEDURE, FUNCTION or
PACKAGE
Roles
Privileges can be maintained and controlled by
creating roles that consist of a named group of
privileges.
Useful Properties of Roles
 Reduced number of privilege grants
 Dynamic privilege management
 Selective availability of privileges
 Application awareness
SYSTEM PRIVILEGES
It is necessary to grant system privileges to users in order to allow
them to perform a particular database operation or class of operations.
A system privilege is the right to execute a type of command.
Types of System Privileges
 In one’s own schema
• The privilege to create a table in one’s own schema
• The privilege to create a sequence in ones own schema
 On all objects of a specified type
• The privilege to create a table in any schema
 On the system or a user
• The privilege to create user
• The privilege to create a session (connect to the database)
SYSTEM PRIVILEGES
•
System privileges are not specific to a named schema object or structure. They are
specific to a particular operation or class of operations on a type of object or
structure.
•
For example, the system privilege “SELECT ANY TABLE” gives the user the right
to query any table in the database. An object privilege gives a user the right to
query a specific table, such as SCOTT.EMP
•
There are more than 70 distinct system privileges available. Some examples are
shown below.
•
SYS PRIV
SESSION
TABLE
COMMAND
CREATE SESSION
CREATE TABLE
TABLE
SELECT ANY TABLE
OPERATIONS PERMITTED
Allows the grantee to connect to the database
Allows the grantee to create tables in their
own schema.
Allows the grantee to query any table or view
in any schema in the database.
Syntax for the GRANT command
,
GRANT
,
System_priv
role
TO
user
role
PUBLIC
• Where:
–
–
–
–
–
WITH ADMIN OPTION
System_priv Is a system privilege to be granted
Role
Is a role name to be granted
TO
Identifies the users or roles to which the system privileges and roles are granted
PUBLIC
Grants system privileges or roles to all users
WITH ADMIN OPTION
Allows the grantee to grant the system privilege or role to
other users or roles. It you grant a role WITH ADMIN
OPTION, the grantee can also alter or drop the role.
– To grant a system privilege, you must have been granted the privilege with the ADMIN
OPTION.
Examples :
• Grant the user “SCOTT”, the privilege to connect to the database
and create tables in his own schema.
SQL> GRANT CREATE SESSION, CREATE TABLE TO SCOTT;
• Grant the user “SCOTT” the privilege to alter any table in any
schema.
SQL> GRANT ALTER ANY TABLE TO SCOTT;
•
WITH ADMIN OPTION
 The grantee can grant or revoke the system privilege to or from any other user in
the database
 The grantee can further grant the system privilege WITH ADMIN OPTION
 Grants made WITH ADMIN OPTION are not hierarchical. Revoking does not
cascade
 Privileges made WITH ADMIN OPTION cannot be granted to roles
DISPLAYING SYSTEM PRIVILEGES
• The system privileges that have been granted can be displayed by
querying the DBA_SYS_PRIVS data dictionary view.
• Example:
SQL> SELECT * FROM SYS_DBA_PRIVS;
GRANTEE_NAME
-------------SCOTT
SCOTT
SCOTT
SCOTT
SYS
SYSTEM
PRIVILEGE
---------------CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE VIEW
UNLIMITED TABLESPACE
UNLIMITED TABLESPACE
ADM
-----NO
NO
NO
NO
YES
YES
REVOKING SYSTEM PRIVILEGES
• In order to revoke system privileges, it is necessary to
use the REVOKE command.
• Syntax:
,
REVOKE
,
System_priv
role
FROM
user
role
PUBLIC
 The options for REVOKE have the same meaning as
for the GRANT command
 A system privilege can be revoked by a user other than
the grantor
REVOKING SYSTEM PRIVILEGES
 Privileges that are granted or revoked become effective
immediately
GRANT
A
REVOKE
B
C
A
B
RESULT
C
A
B
C
OBJECT PRIVILEGES
• Different object privileges allow the use of specific SQL statements by
users.
– SQL Statements Permitted by Object Privileges
•
•
•
•
•
•
•
•
•
•
SELECT
SELECT… FROM object (table or view)
SQL statements using a sequence
UPDATE
UPDATE object (table or view)
INSERT
INSERT INTO object (table or view)
ALTER
ALTER object (table or sequence)
CREATE TRIGGER ON object (table or view)
DELETE
DELETE FROM object (table or view)
TRUNCATE object (tables only)
EXECUTE
EXECUTE object (procedure or function)
INDEX
CREATE INDEX ON object (tables only)
• Object privileges can be granted to users by using the GRANT
command.
Syntax of the GRANT Command
,
GRANT
Object_priv
,
Object TO
ON
Schema.
user
role
PUBLIC
WITH GRANT OPTION
•
Where:
– Object_priv Is an object privilege to be granted
– ON
Identifies the object on which the privileges are granted. if the “schema.”
prefix is not used then ORACLE assumes the current user’s schema.
– TO
Identifies the users or roles to which the object privilege is granted
– PUBLIC
Grants object privileges to all users
– WITH GRANT OPTION
Allows the grantee to grant the object privileges to other
users and roles. The grantee must be a user or PUBLIC.
GRANT OPTION cannot be granted to a role.
Example:
• To grant the privilege to query the accounts table to
users “SUE” and ‘RICH”.
SQL> GRANT SELECT ON ACCOUNTS TO SUE, RICH;
• To grant privileges on an object, the object must be in your
schema or you must have been granted the object WITH
GRANT OPTION
• An object owner can grant any object privilege on the object
to any other user or role in the database
• If the grant includes the WITH GRANT OPTION, the
grantee can further grant the object privilege to other users
• The owner of an object automatically acquires all object
privileges on that object
DISPLAYING OBJECT PRIVILEGES
• The object privileges that have been granted can be displayed by
querying the data dictionary.
• Available to DBAs
– DBA_TAB_PRIVS
• Available to the User
– USER_TAB_PRIVS
– USER_TAB_PRIVS_MADE
– USER_TAB_PRIVS_RECD
All privileges on all tables in
the database
Privileges on tables for
which the user Is the owner,
grantor, or grantee
All privileges on tables
owned by the user
All privileges on tables for
which the user is the grantee
Example:
SQL> SELECT * FROM DBA_TAB_PRIVS;
GRANTEE OWNER TABLE_NAME
-------------- ---------- -------------------BILL
SCOTT EMP
BILL
SCOTT EMP
TED
SCOTT EMP
GRANTOR PRIVILEGE
--------------- ---------------SCOTT
SELECT
SCOTT
INSERT
SCOTT
SELECT
GRA
------NO
NO
NO
REVOKING OBJECT PRIVILEGES
• Privileges on objects can be revoked from users by using the REVOKE
command.
• Syntax of the REVOKE command
,
REVOKE
Object_priv
,
Object FROM
ON
Schema.
•
user
role
PUBLIC
CASCADE CONSTRAINTS
Where
– Object_priv Is an object privilege to be revoked
– ON
Identifies the object on which the privileges are revoked. If the “schema.”
prefix is not used then ORACLE assumes the current user’s schema.
– TO
Identifies the users or roles on which the object privilege is revoked
– PUBLIC
Revokes object privileges from all users
– CASCADE CONSTRAINTS
Drops any referential constraints defined by using the
previously granted REFERENCES privilege. Because the user is no longer allowed to have
foreign keys pointing to the object then those foreign keys must be dropped.
REVOKING OBJECT PRIVILEGES
• Grantors can revoke privileges from only those users to whom they had
granted the privileges in the first place.
• Revoking an object privilege may have a cascading effect that should be
investigated before a REVOKE statement is issued.
GRANT
A
REVOKE
B
C
A
B
RESULT
C
A
B
C
USING DATABASE ROLES
• The management of database privileges can be
simplified by using database roles. A role is group of
related privileges that are contained together under a
given role name. The role (and its associated privileges)
can then be granted to a user or even another role.
Role Properties
 Roles can consist of both system and object privileges
 They are not owned by anyone, therefore do not exist in any
schema
 A role may be granted to any user or role, except itself (not even
indirectly)
 Roles can be enabled or disabled for each individual authorized
user
 It is possible to enforce password authorization on roles so that
they may be enabled only if the password is known
 Role descriptions are all contained in the data dictionary
Benefits of using roles
There are many benefits of using roles for management of system and
object privileges in the database.
– Reduced Granting of Privileges
 Many privileges can be granted or revoked (for many users) with one single statement
 Roles can be granted to new database users, eliminating the need to remember all of the
individual privileges required
 Simplifies privilege management is systems with many users. Many tables or both
Granting Privileges without Roles
(12 GRANT statements required)
Granting Privileges using Roles
(7 GRANT statements required)
USERS
USERS
Role
PRIVILEGES
PRIVILEGES
Benefits of using roles
There are many benefits of using roles for management of system and
object privileges in the database.
– Dynamic Privilege Management
 Change privileges of a role as responsibilities change
 Change the privileges of many users by changing one role
– Selective Availability of Privileges
 Enable and disable roles to turn privileges on and off temporarily
 Employ application awareness by having application programs query the data
dictionary and enable and disable roles as needed within the application
– Additional benefits
 Object privileges can be managed without the need to worry about cascading
revokes
Building Security Models Using Roles
• It is possible to build up a complex security model that
vastly simplifies security management in the database.
This is done with the use of roles.
• Create a role for each application in the database
(application role)
• Create a role for each category of user in the
database (user role)
• Grant only application roles (no object or system
privileges) to user roles
• Grant user roles and application roles to database
users as required
CREATING ROLES
• In order to create database roles it is necessary to use the CREATE ROLE
command.
• Syntax of the CREATE ROLE Command
CREATE ROLE role
NOT IDENTIFIED
IDENTIFIED
•
Where:
– Role
– NOT IDENTIFIED
– IDENTIFIED
– BY password
– EXTERNALLY
BY password
EXTERNALLY
Name of the role to be created
Users granted the role do not need to be verified by ORACLE to
enable it
Indicates that the users granted the role must be verified
by ORACLE to enable the role
Specifies the password that authorizes enabling the role
Specifies that ORACLE will verify user access to the role using an
operating system utility
Example :
SQL>CREATE ROLE ACCT_PAYABLE IDENTIFIED BY APAYB;
• You must have the CREATE ROLE system privilege in order to
create a role.
ALTERING ROLES
• In order to modify the security options on an existing role it is
necessary to use the ALTER ROLE command.
• Syntax of the ALTER ROLE Command
ALTER ROLE role
NOT IDENTIFIED
IDENTIFIED
BY password
EXTERNALLY
• Options for the ALTER ROLE command are the same as for CREATE
ROLE
• You need to have been granted the role WITH ADMIN OPTION, or you
need the ALTER ANY ROLE system privilege in order to alter a role
ENABLING AND DISABLING ROLES
• It is possible to enable and disable roles in order to make available
or restrict certain privileges to users, without granting and
revoking the roles.
• Properties of Enabling and Disabling Roles
 Can only enable or disable roles that have been granted
 Privileges granted to role are not available to the user while the
role is disabled
 Enabling a role may require password authorization
 The SET ROLE command specifies an exclusive set of roles to be
enabled. Any role not included in the command will be
automatically disabled after the command is executed
 Role enabling and disabling can be performed from any
application developing language that supports embedded SQL
Syntax for the SET ROLE Command
,
SET ROLE
role
ALL
IDENTIFIED BY
password
,
EXCEPT
•
•
•
•
•
Where:
Role
ROLE
NONE
Is a role to be enabled for the current session. Any granted
roles that are not listed are disabled for the current session
password
Is the password for the role. If a role has a password it must be
supplied
ALL EXCEPT Enables all roles granted to you for the current session, except
those listed in the EXCEPT clause. You cannot use this option
to enable password protected roles
NONE
Disables all roles granted to you for the current session
Example :
SQL> SET ROLE ALL EXCEPT ACCT_PAYABLE;
Setting Default Roles For A User
•
•
It is possible to roles that will be enabled for a user by default, by using the
ALTER USER command.
Syntax of the ALTER USER Command
ALTER USER user
,
DEFAULT ROLE
role
IDENTIFIED BY password
ALL
,
EXCEPT
•
Where:
– user
– DEFAULT ROLE
– ALL
– NONE
ROLE
NONE
Is the user to be altered
Establishes default roles for the user. All default roles are enabled
when a user logs on
Makes all the roles granted to the user DEFAULT roles except any
contained in the EXCEPT clause
Makes none of the user’s roles DEFAULT roles
Displaying Information About Roles
The following data dictionary views contain information
about privileges granted to roles, roles granted to users etc.
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
ROLE_ROLE_PRIVS
SESSION_ROLES
USER_ROLE_PRIVS
DBA_SYS_PRIVS
DBA_ROLES
System privileges granted to roles
Table privileges granted to roles
Roles granted to other roles
Roles that the user currently has enabled
Roles granted to the user
System privileges granted to users and to roles
All roles existing in the database