Showing posts with label security. Show all posts
Showing posts with label security. Show all posts

Wednesday, May 8, 2013

WITH GRANT OPTION vs. WITH ADMIN OPTION

Role or system privileges are granted using WITH ADMIN OPTION, and table-specific privileges are granted using WITH GRANT OPTION.

Revoking a privilege granted using WITH GRANT will cascade revoke any and all privileges assigned by the target user to other users, but revoking a privilege granted using WITH ADMIN will only revoke privileges for the target user, leaving intact all the othwer users target user might have granted privileges to.

Example:

WITH GRANT OPTION

DBA can grant object privileges using with grant option (select,update,insert) to A, and A, in turn, grants those privileges to B.

DBA--------->A--------->B

DBA can only revoke the privileges from A but not from B, because WITH GRANT OPTION implies that we can only revoke those privileges that we have granted. Only the user who granted privileges can revoke them. But if DBA revoke A's privileges, the priveleges will cascade revoke from B as well.



WITH ADMIN OPTION

We grant the system privileges to A using with admin option(CREATE TABLE,CREATE SESSION)

DBA------>A-------->B

Here DBA can revoke privileges from either A or B. But if DBA only revokes privileges from A, they won't be cascade revoked from B.

Users with ADMIN OPTION for a system privilege can revoke the privilege of any other database user, the revoker does not have to be the same user who originally granted the privilege.

Monday, April 29, 2013

Issue cloning user privileges

I was using the script below to clone the user privileges of user1 and assign them to user2:
select 'grant ' || PRIVILEGE || ' on ' || owner || '.' || TABLE_NAME || ' to ' || 'USER2' || decode(GRANTABLE,'YES',' with grant option;','NO',';') from
  dba_tab_privs
where
  GRANTEE = upper('USER1')
union
select
  'grant ' ||GRANTED_ROLE || ' to ' || 'USER2' || ';'
from
  dba_role_privs
where
  GRANTEE = upper('USER1')
union
select
  'grant ' || PRIVILEGE || ' to ' || 'USER2' || ';'
from
  dba_sys_privs
where
  GRANTEE = upper('USER1')
union
select
  'alter user ' || 'USER2' || ' quota ' || decode(MAX_BYTES,-1,'UNLIMITED', to_char(MAX_BYTES/(1024*1024),'9999') || 'M') || ' on ' || TABLESPACE_NAME || ';'
from
  dba_ts_quotas
where
  USERNAME = upper('USER1');

Everything seems to be working fine and both users had identical privileges, yet, USER2 was not able to see all the tables that USER1 could see.

The issue turned out to be that bothe users wer granted two roles ROLE1 and ROLE2, but the only difference was that ROLE2 was a default role for both users, but ROLE1 was default only for USER1.

SQL> SELECT GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE FROM dba_role_privs WHERE grantee='USER1';

GRANTED_ROLE                   ADM DEF
------------------------------ --- ---
ROLE1                          NO  YES
ROLE2                          NO  YES


SQL> SELECT GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE FROM dba_role_privs WHERE grantee='USER2';

GRANTED_ROLE                   ADM DEF
------------------------------ --- ---
ROLE1                          NO  NO
ROLE2                          NO  YES

Running the sql below fixed the issue

ALTER USER USER2 DEFAULT ROLE ALL;
or


ALTER USER USER2 DEFAULT ROLE ROLE1, ROLE2;
Caution: Running


ALTER USER USER2 DEFAULT ROLE ROLE1;
would set ROLE1 as a default role but remove all other roles (in this case ROLE2) from default role.

Tuesday, February 26, 2013

SELECT_CATALOG_ROLE vs. SELECT ANY DICTIONARY

SELECT_CATALOG_ROLE is more restrictive than SELECT ANY DICTIONARY. Although both have privileges to select from the dictionary views, SELECT ANY DICTIONARY allows the user to see the source code of package bodies and triggers which are normally avilable to the DBAs.

SELECT ANY DICTIONARY is a system privelege, while SELECT_CATALOG_ROLE is a role.

SELECT_CATALOG_ROLE allows the user to query V$SESSION but not to create a procedure. In order to create an object on the base object, the user must have the direct grant on the base object, not through a role.

So while both allow the users to query V$DATAFILE, the role does not allow the users to create objects; the system privilege does.

Note: In case where the user is granted SELECT ANY TABLE but when parameter O7_DICTIONARY_ACCESSIBILITY is set to false, the user can access tables in any schema, except SYS, in other words cannot access data dictionary tables, granting the user SELECT_CATALOG_ROLE will enable him to access dictionary objects. Another way is to set O7_DICTIONARY_ACCESSIBILITY to true.

Tuesday, July 3, 2012

SQL Server roles

There are two types of roles when it comes to databases - server roles and database roles. Server roles should only be assigned for the advanced users, such as database administrators. Here are some server roles:

Role Description
sysadmin Can perform any task in SQL Server
serveradmin Can set server-wide configuration options, can shut down the server
setupadmin Can manage linked servers and startup procedures.
securityadmin Can manage logins and database permissions, read logs, change passwords
processadmin Can manage processes running in SQL Server
dbcreator Can create, alter, and drop databases
diskadmin Can manage disk files
bulkadmin Can execute BULK INSERT statements
public Every SQL Server user account belongs to this server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on an object when you want the object to be available to all users


Database roles are security roles that exist on a database level, as opposed to the server level. There are two kinds: fixed roles and flexible roles. Fixed roles automatically exist in each database. Adding a user to a fixed role will not change user's permissions in any other database. Flexible database-level roles are simply defined as roles that you create in the database.
Here are the fixed database roles:
Role Description
db_owner can make extensive modifications to all aspects of the database. A user who is a db_owner can even drop the database. Here are some actions that can be performed by the user in db_owner role:
  • Creating users
  • Adding them to roles
  • Creating tables/views and stored procedures
  • Adding security settings for tables, views, and stored procedures
db_securityadmin Can modify role permissions and manage permissions with the exception of adding users to db_owner or other fixed database roles. that requires membership is db_owner role
db_accessadmin Users in this role have the ability to change database access. They can grant and revoke access to Windows logins, Windows groups and SQL Server logins. The users that they grant access to will be members of the Public role and will have all the privileges associated with that role
db_backupoperator Can create database backups but do not have permissions to restore the databases they backup. Only users in db_owner database role or in sysadmin server role or dbcreator server role can restored databases
db_ddladmin Can execute DDL commands (CREATE, ALTER, DROP) within the current database. Those users are able to create, or change, new tables, views, indexes and stored procedures. However, they cannot create users or manage security
db_datawriterCan INSERT, UPDATE, and DELETE data from any user created table
db_datareader Can read all data from all the user tables
public Every database user is a member of the Public role. If a user does not have any explicit permissions on a database object, they will inherit the permissions of the Public role. It is important to note that users cannot be removed from the Public role.