SQL Server 2005 – Fixing ‘Do Not Have Permission to Use dbo’ Error When Creating View

permissionssql-server-2005users

I want to create a user with minimal required access to two databases within a server. The user will be used by someone from a third party integrating a Business Intelligence tool into our database. They have specified that they will only need to create views.

So I created a new user on the server, then used the database properties on each database to grant them the SELECT and CREATE VIEW privileges on that database.

I then logged in on SSMS using that user, and tried to create a view but I got a message saying that

'dbo' either does not exist , or I do not have permission to use it.

I tried to figure this problem out myself by trial and error but I just don't understand user permissions on sql server. What am I doing wrong and what extra/different steps do I need to take?

Remember, I don't want them to have more control than the minimum they need.

Best Answer

Your user should have the permission to modify the dbo schema. You can accomplish that by assigning the permission to a role the User is a member of:

So its like:

CREATE ROLE Limitedaccess; 
GO 

GRANT CREATE VIEW TO Limitedaccess; 
GO 

GRANT SELECT ON SCHEMA::dbo TO Limitedaccess; 
GO 

CREATE USER user WITHOUT LOGIN; --- this would be the user here
GO 

EXEC sp_addrolemember 'Limitedaccess', 'User'; 
GO

Once permission is granted, re-run the previous CREATE VIEW statement. It will now succeed.

GRANT ALTER ON SCHEMA::dbo TO Limitedaccess; 
GO

Related Solutions

ORA-01031 while creating a view as DBA


create table usr_t_user_reg
(username varchar2(30),
db_instance  varchar2(30),
primary key(username,db_instance)
)
/

CREATE OR REPLACE VIEW usr_v_user_not_reg AS
  SELECT username "User", db_instance "Instance",
    (
      CASE
        WHEN username IN
        (
          SELECT username
          FROM sys.dba_users
          MINUS
          SELECT username
          FROM usr_t_user_reg
        ) THEN 'not registered'
        WHEN username IN
        (
          SELECT username
          FROM usr_t_user_reg
          MINUS
          SELECT username
          FROM sys.dba_users
        ) THEN 'no longer present'
      END
    ) "Status"
  FROM usr_t_user_reg
  WHERE db_instance = 'TEST_DB'
    AND username NOT IN
    (
      SELECT username
      FROM usr_t_user_reg
      INTERSECT
      SELECT username
      FROM sys.dba_users
    )
/

Running the script with sqlplus gives the following error message

Table created.

          FROM sys.dba_users
               *
ERROR at line 8:
ORA-01031: insufficient privileges

this is one of the errors where the error description of the Oracle Database Error Messages 11g Release 2does not give a usefule hint

ORA-01031: insufficient privileges

Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login.

Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label.

In the Oracle Database SQL Language Reference 11g Release 2 you can find the following prerequisite for creating a view

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

Neither SELECT_CATALOG_ROLE role nor DBA role is sufficient for creating your view.

So issue a

GRANT SELECT ON DBA_USERS TO your_user;

and now your view will work.

Instead of the object privilege SELECT ON DBA_USERS you can grant the system privilege SELECT ANY DICTIONARY. In contrast to SELECT_CATALOG_ROLE this is not a role but a system privilege.

Note that the error message spots to line 8 of the CREATE VIEW statement that is the line that actually contains the object that causes the error.

Sql-server – Grant access to all objects (with a few exceptions) to a role

The sad truth is that you cannot do anything computerwise about the 'sa' activities. This is because the 'sa' (or a member of the sysadmin server role) are defined to be able to do pretty much anything.

So, of course, you should only have sysadmin's that you can trust to do their best to adhere to the standards for the database. But even the best sysadmin is imperfect. So you might set up some auditing to report on changes made in the database.

(Or, more low tech, periodically check your views for a restricted table name in a view.)

So it depends on what you are concerned about and how much effort you need to put into protecting the secure resources.

In terms of views that expose secure information, you can DENY permissions on a view. Even if a sysadmin created the view, the DENY of the view for those without approval will prevent their access.

Perhaps any high security views could be named something like SecureViewXYZZY so as to emphasize the reason for those views and the restriction of who can use them.

Best Answer

Related Solutions

ORA-01031 while creating a view as DBA

Sql-server – Grant access to all objects (with a few exceptions) to a role

Related Question