I created new role named "support" in my PostgreSQL. Now I need grant "READ ONLY" permissions for this role an ALL exists databases/tables.  Also I need automatically granted same permissions on each DB that will created in future.  I unsuccessfully tried next queries for grant permissions in new databases (Can not select from new database tables ):  ALTER DEFAULT PRIVILEGES FOR ROLE support GRANT SELECT ON TABLES TO PUBLIC; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES to support;   I looked at , but it about all tables within single database. It not enough for me.
124237
I want to  GRANT SELECT     ON ALL TABLES     TO foo;   But postgres tells me I have a syntax error at "TO". It expects a GRANT like:  GRANT SELECT     ON ALL TABLES     IN SCHEMA public     TO foo;   We have a handful of tables that have been sharded such that public, shard123, and shard124 all have a table my_table, and public.my_table is the parent of both shard123.my_table and shard124.my_table. I've GRANTed SELECT to foo for public schema, but foo doesn't have permission for any of the shard schemas.  I've also done:  ALTER DEFAULT PRIVILEGES     FOR ROLE dba     GRANT SELECT     ON TABLES     TO foo;   ...which should automatically grant for any new tables and schemas made from now on.  There are 1000 &lt; n &lt; 10000 shards/schemas, so GRANTing separately for each shard/schema is impractical.  How can I GRANT to a role for all existing tables across all existing schemas (all shards)?  Or even better, can I GRANT for all schemas LIKE 'shard%'?
66228
One of the physical standby databases in my environment is open in read-only mode and used for reporting purposes . I want to give users access only to this particular standby, not to primary db.   The best approach I found so far is to create AFTER LOGON ON DATABASE trigger which checks v$instance (or maybe v$database), and throws error for depends on  values of host_name/instance_role columns  .   I wonder if there is a better way to do it.
39078
DBMS allows the admin to grant table access to a user using something like:  GRANT ALL ON mydb.mytbl1, mydb.mytbl2 TO 'someuser'@'somehost';   However, is it possible to grant all tables to a user and explicitly block access to some of them? For example (The BLOCK key word is a fake one and is only used for illustration.):  GRANT ALL ON mydb.* TO 'someuser'@'somehost'; BLOCK ALL ON mydb.mytbl3 TO 'someuser'@'somehost';   Another idea is to add a where clause to the GRANT statement. However, it seems that the GRANT statement syntax does not allow that ().  This kind of access control might be convenient if the admin trust the user in most cases (e.g. 1000 tables) and only want to block access to a small number of top sensitive tables.    Could you please tell me whether such kind of mechanism exists?
4736
I have been asked by a client to implement some views for reporting purposes, the views will be accessed via PowerBI, Excel and SSRS. The designated user will only have access to the view and no underlying tables must be available to the user.  The problem I have encountered in that the SQL within the view touches 3 different schemas (All within the same database):   Pupil Provider  Security   The view is:  CREATE VIEW dbo.vTestPermissions AS SELECT a.Column1,        b.Column1,        c.Column1 FROM Pupil.Table1 a JOIN Provider.Table2 b ON a.Column1 = b.Column1 JOIN Security.Table3 c ON a.Column1 = c.Column1   The owners of the Tables/View are as follows:   Pupil.Table1 - Owner Pupil Provider.Table2 - Owner Provider Security.Table3 - Owner Security vTestPermissions - Owner dbo   When I select from the view I get an error:     The SELECT permission was denied on the object 'table3', database   'TEST', schema 'Security'   I have tried giving SELECT permission with and without the GRANT option to the schema, and the tables, but that makes the underlying tables available to the user.  Any help on this will be greatly appreciated.
40280
I used pg_restore to load my postgres db with a dump file. I connected to my db with my user :  sudo -u arajguru psql dump  select current_user;  current_user  --------------  arajguru   Now I was able to see all the newly created tables:  dump=&gt; \dt                List of relations  Schema |       Name        | Type  |  Owner    --------+-------------------+-------+----------  public | Approvals         | table | arajguru  public | Approvers         | table | arajguru  public | Conditions        | table | arajguru  public | Entities          | table | arajguru  public | EntityDefinitions | table | arajguru  public | Projects          | table | arajguru  public | Rules             | table | arajguru  public | run_history       | table | arajguru (8 rows)   But when I try to fire a select * query, it gave me this error:  dump=&gt; select * from Approvals; ERROR:  relation "approvals" does not exist LINE 1: select * from Approvals;   What can be the reason for this error? Please help.
16756
I have a view which is selecting rows from a table in a different database.  I'd like to grant select access to the view, but not direct access to the base table.  The view has a where clause restricting the number of rows.  Can I grant select to the view and not the base table, or do I need to switch to a stored procedure? I would rather not do it the latter way.
94234
Window account is getting the below error when trying to connect into the SQL instance via management studio 2014.  The SELECT permission was denied on the object 'configurations', database 'mssqlsystemresource', schema 'sys' (Error 229)  The SQL instance is 2014, the user doesn't get the error if I make the login sysadmin.   The login has connect permission to the instance and even if i give it dbowner to the master database it cannot connect.   Default database is master also.  It is not an orphaned user I have tried the GRANT VIEW DEFINITION - no luck I have checked and the login doesnt have any DENY permissions   When i go into the properties of the login > Securables > effective tab > it gives the error:  The SELECT permission was denied on the object 'fn_my_permissions', database 'mssqlsystemresource', schema 'sys'.  So that is obviously related also   Any ideas?
101965
I'm attempting to create a stored procedure that can be used as a proxy for creating databases where users do not have rights for creating databases.  See  for more info  When I attempt to execute the stored proc, SQL Server generates a security error:  Msg 262, Level 14, State 1, Line 1 CREATE DATABASE permission denied in database 'master'.   This is the code:  CREATE LOGIN DatabaseCreator WITH PASSWORD='Pa$$w0rd'; /* REPLACE WITH A SECURE PASSWORD! */ GO CREATE USER DatabaseCreator FOR LOGIN DatabaseCreator; GO EXEC sys.sp_addsrvrolemember 'DatabaseCreator','sysadmin'; GO CREATE PROCEDURE dbo.CreateDatabase (     @DatabaseName SYSNAME ) WITH EXECUTE AS 'DatabaseCreator' AS BEGIN     SET NOCOUNT ON;     DECLARE @cmd NVARCHAR(max);     IF COALESCE(@DatabaseName,'') &lt;&gt; ''     BEGIN         SET @cmd = 'CREATE DATABASE ' + QUOTENAME(@DatabaseName) + ';'          EXEC sys.sp_executesql @cmd;     END     ELSE     BEGIN         SET @cmd = 'INVALID DATABASE NAME';         RAISERROR (@cmd, 0, 1);     END END; GO EXEC master.dbo.CreateDatabase 'MyDatabaseName';   msg 262 appears to be a generic Permission Denied message.  Since DatabaseCreator has sysadmin privileges, this should not be happening.  Unless I'm missing something.
8163
I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a staging server.  pg_dump -h localhost -U adminuser --data-only --column-inserts maindb &gt; maindb.sql   How do I delete all data in the staging server database first, before restoring the data from the production dump?   I want to delete all data only so I don't have to drop and create the database and all that stuff. I just want to remove data and insert new data that is all.  I don't have the option to drop and create the database for several reasons. I will have to remove all data and just insert only, so whatever it takes to find how to do this, am willing to go for it but need help obviously to start with.  I also need to automate this process. Will automate "dumping data from production db", then "deleting data on staging db", and then "restoring data to staging db". I just need help on the "deleting data on staging db" part.  I am running on PostgreSQL 9.5.2
70671
I want to do a final pg_dumpall on a server, then copy the output to a different server and restore the db there.  How can I avoid modification to the database during pg_dumpall?
49413
My laptop has a small hard drive. I want to load a big data set. I have moved the entire /usr/local/var/postgres to the external hd, and symlinked it back. Now I'm getting this error,      psql: could not connect to server: Socket operation on non-socket     Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?   I have now found the problem.  Changing the default data directory causes Postgres to refuse connections to the server. The online Manual is a little [sic!] unspecific about "config files in the same directory"…  The solution I used (with psql) is to:            CREATE TABLESPACE tablespacename OWNER username LOCATION '/Path/to/Folder'   then,      CREATE DATABASE databasename OWNER username TABLESPACE tablespacename   and finally      psql databasename &lt; /Path/to/databasedump.sql   NOTE to future visitors of this question; please see the warning below from Craig Ringer regarding how to solve this problem properly.
98965
It seems that I have the same problem as Yasser in this post     but I am running SQL Server 2014.   Basically when I right click on Server Roles I only get a menu with three options, "Start Power Shell, Reports, Refresh".   The command CREATE SERVER ROLE works. I am logged in as "sa". Any advice would be really helpful.  Thank you.
73046
We have a SQL Server 2008 database and have restricted all tables and views away from a specific user ID.  Over the years we have granted back tables and views one at a time based on user need.    We had to do it this way because the vendor provides read/write access to the public role out of the box, so we had to create a role for this user, remove all access and grant back only what they needed.    Today we are going to create a replicated database containing only these tables and views so the user can run reporting on without degrading the production system.  The issue is I'm not sure what tables and views this user has access to, because many people have granted access one at a time over the years.  Is there a query to check this users access?  PS Im an apps developer so please feel free to explain this as elementary as needed.    EDIT - How the role and access was originally created    ------------------------------ -- CREATE ROLE db_finrep_deny ------------------------------  DECLARE @RoleName sysname set @RoleName = N'db_finrep_deny' IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R') Begin     DECLARE @RoleMemberName sysname     DECLARE Member_Cursor CURSOR FOR     select [name]     from sys.database_principals      where principal_id in (          select member_principal_id          from sys.database_role_members          where role_principal_id in (             select principal_id             FROM sys.database_principals where [name] = @RoleName  AND type = 'R' ))      OPEN Member_Cursor;      FETCH NEXT FROM Member_Cursor     into @RoleMemberName      WHILE @@FETCH_STATUS = 0     BEGIN          exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName          FETCH NEXT FROM Member_Cursor         into @RoleMemberName     END;      CLOSE Member_Cursor;     DEALLOCATE Member_Cursor; End GO -- dropping the role itself IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'db_finrep_deny' AND type = 'R') DROP ROLE [db_finrep_deny] GO   ------------------------------ -- Grant access (read/write/select/upd) to only select tables ------------------------------ CREATE ROLE [db_finrep_deny] AUTHORIZATION [dbo] GO exec sp_addrolemember 'db_finrep_deny', 'finrep' GO  -- run the dynamic sql generated by this statement select  'DENY SELECT, INSERT, UPDATE, DELETE ON '         + ss.name         + '.'         + st.name         + ' TO db_finrep_deny;' from sys.tables st inner join sys.schemas ss on st.schema_id=ss.schema_id where st.name not in ('TABLE1','TABLE2','TABLE3','TABLE4') order by ss.name, st.name;  grant select on dbo.vwFINMASTER to db_finrep_deny;     Aaron - this is what I get when I run your query, it shows only views, but no tables    (No column name)    name    permission_name dbo     dtproperties    REFERENCES dbo     vwFINMASTER     SELECT dbo     vwLEDGERVIEW    SELECT
98597
In PostgreSQL 9.5, I have tables with columns in the form   prefix_last_modified timestamp without time zone NOT NULL DEFAULT  (clock_timestamp() AT TIME ZONE 'UTC')   I was looking for a way to set the last modified value automatically updated at each update of the rows, and I found  that defined the function:  CREATE OR REPLACE FUNCTION update_modified_column()  RETURNS TRIGGER AS $$ BEGIN     NEW.modified = now();     RETURN NEW;  END; $$ language 'plpgsql';   Now, I'd like to know if there is any way to pass the column name to the PostgreSQL function and to execute it to the NEW row? E.g.  CREATE OR REPLACE FUNCTION update_modified_column(varchar column)        RETURNS TRIGGER AS $$     BEGIN         NEW.column = now();         RETURN NEW;      END; $$ language 'plpgsql';
83751
When I try to execute a view that includes tables from different schemas an ORA-001031 Insufficient privileges is thrown.  These tables have execute permission for the schema where the view was created.  If I execute the view's SQL Statement it works.  What am I missing?
63403
I'm building a backend system using MongoDB. Now I want to restrict access on certain numbers of database collections to this system. For example I have users, admin_users, pageviews and I want this system to be able to read and write only admin_users and pageviews collections but no read or write access on users colection. Is there any ways to do so?
24879
The short installation instruction for WordPress () state that:     Create a database for WordPress on your web server, as well as a MySQL user who has all privileges for accessing and modifying it.   While setting up a new blog professionally I was wondering how that maps to what the MySQL database user privileges/permissions configuration offers me:   Data: SELECT, INSERT, UPDATE, DELETE Definition: CREATE, ALTER, DROP Extra: INDEX More:  LOCK TABLES REFERENCES CREATE TEMPORARY TABLES CREATE VIEW SHOW VIEW CREATE ROUTINE EXECUTE ALTER ROUTINE    I'm pretty sure for the first three groups, I named them Data, Definition and Extra here. But what about the others below the More entry? Normally I would say, those are not needed, but I would like to get a second opinion.