24

You can query the system catalog with a recursive query, in particular pg_auth_members: WITH RECURSIVE cte AS ( SELECT oid FROM pg_roles WHERE rolname = 'maxwell' UNION ALL SELECT m.roleid FROM cte JOIN pg_auth_members m ON m.member = cte.oid ) SELECT oid FROM cte; BTW, INHERIT is the default behavior of CREATE ROLE and doesn't have to ...


19

The privilege on DATABASE only grants general connection rights to the database and no more. A user with just that privilege can only see what the general public is allowed to see. To grant read access to all tables, you also need privileges on all schemas and tables: GRANT USAGE ON SCHEMA public TO myuser; -- more schemas? GRANT SELECT ON ALL TABLES IN ...


18

Short version: SELECT a.oid FROM pg_authid a WHERE pg_has_role('maxwell', a.oid, 'member'); Here we use a version of pg_has_role that takes a role name as the subject and role oid to test for membership, passing member mode so we test for inherited memberships. The advantage of using pg_has_role is that it uses PostgreSQL's internal caches of role ...


16

This is a simplified version of Craig Ringer's answer that a non superuser can use directly: SELECT oid, rolname FROM pg_roles WHERE pg_has_role( 'maxwell', oid, 'member'); pg_roles is essentially a view on pg_authid accessible to public, as it doesn't reveal passwords, contrary to pg_authid. The base oid is even exported into the view. When not ...


13

I think there is no reliable solution for your problem since Application Name is modifiable parameter that cam be changed by any user. Here is how to change it within SSMS: In Connect to Database Object dialog choose Options, open Additional Connection Parameters and choose any name for Application Name like this: Now sys.dm_exec_sessions DMV and ...


11

You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using SSMS. For example: CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1 ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN() = N'SQLLogin1' AND ...


9

If you put the database into Restricted User mode, then only members of the fixed server roles sysadmin or dbcreator, or members of the fixed database role db_owner can access the database: ALTER DATABASE my_app_db SET RESTRICTED_USER If you want to force existing connections to be closed: ALTER DATABASE my_app_db SET RESTRICTED_USER WITH ROLLBACK ...


9

There are couple of ways that you can restrict access to a database : Using LOGON Trigger - but only temporarily (enable before upgrade and then disable it). Refer to my answer here for more details including a script. You can shutdown IIS on the webservers so that no connections are made using the application. This is called "Applicaiton downtime" Keep ...


9

Honestly, sa or a disabled SQL Server account with absolutely minimum permissions is the best choice. As to why? Well, I could write out three or four paragraphs on that, or I can share my favorite article on What account should own the databases and why. A very thorough explanation covering all the bases. Highly recommended. Here's a core excerpt: (...


8

Is there an underlying reason why databases cannot be owned by a secondary principal? I'm not 100% certain of the reason for this restriction, though I suspect it has to do with the ability to impersonate (i.e. become) that SID. There are certain operations that can reach outside of the instance (e.g. to the OS, file system, etc) and the logic is usually ...


6

I am wondering if Grant Control on Schema will cover everything I need Will Grant Control give me everything I need as a Database developer. But you didn't write what exactly you need as database developer. If you update your question with your tasks to accomplish, I'll answer on that. For now I can give you only general answer. Having CONTROL on ...


5

You can use the function fn_my_permissions and impersonate the user to see which objects they have access to. You may want to expand the types to procedures and the various types of functions... USE Your_Database; GO EXECUTE AS USER = N'the_user_name'; GO SELECT s.name, o.name, p.[permission_name] FROM sys.objects AS o INNER JOIN sys....


5

Unless you have cross-database ownership chaining enabled (off by default) and have the same User in both databases, then giving access to an object in one database does not imply anything permission-wise to other databases. Of course, I am not recommending that cross-database ownership chaining be enabled, and neither is Microsoft. Instead, you should ...


5

Unfortunately that's not that easy because there is no "select any view" privilege (as e.g. Oracle offers). If you can put all views into a single schema, then this could be achieved by granting the select privilege on that schema. Something like this: First you need to create a role that can be used for this create role view_reader; The role only ...


4

You can get some information about the last change to a table with xmin, eg: select max(xmin::text::bigint) from t; But you need to be aware of many caveats including modulo and wraparound and frozen xids. testbed: set role dba; create role stack; grant stack to dba; create schema authorization stack; set role stack; -- create or replace function f(...


4

Here is my take on it. It works for one specific user or all users. select a.oid as user_role_id , a.rolname as user_role_name , b.roleid as other_role_id , c.rolname as other_role_name from pg_roles a inner join pg_auth_members b on a.oid=b.member inner join pg_roles c on b.roleid=c.oid where a.rolname = 'user_1'


4

Read the manual on GRANT and ALTER TABLE. There is no USAGE privilege for tables. You would see an error message if you tried your GRANT command. You need permissions on the schema, too. Here USAGE is right. But you certainly want to revoke CREATE. Ownership for sequences that are owned by serial columns is changed to the new table owner automatically. But ...


4

You can't cut off a specific client, as already detailed in the other answers. The solution is to remove the access privileges to the production systems from the developer's accounts. Any change must be scripted and a dba will run the script. Deployment is performed by a sysadmin; devs produce a package they give to someone with proper privileges and devs ...


4

In the ideal sense, this is a process / policy / management issue. Even if someone knows the password, if it is against company policy for anyone but a DBA to connect to Production (well, you might have a Release Engineering team and/or sys admins, etc), and there are penalties for breaking the rules, then that should be sufficient (assuming that such rules ...


3

If you were using SQL Server, what you are looking for is something like: USE tempdb; CREATE TABLE MenuHeaders ( MenuHeaderID INT NOT NULL CONSTRAINT PK_MenuHeader PRIMARY KEY CLUSTERED IDENTITY(1,1) , MenuText VARCHAR(255) ); CREATE TABLE MenuSubHeaders ( MenuSubHeaderID INT NOT NULL CONSTRAINT PK_MenuSubHeader PRIMARY KEY ...


3

If you work with Windows Authentication, you can add your domain users to a domain group and add this group as a login to SQL Server. Then give that login the desired permissions on the relevant databases.


3

Database roles are security principals that are wholly contained within their respective database and are not shared or visible to other databases. So any roles and users that are in database X have no knowledge of database Y. To accomplish your goal, you'll need to recreate the role in database Y and add all the appropriate users to this database and role....


3

You could use the session information function inet_client_addr(), with limited reach. It reports the IP the server is receiving the current connection from. This is only useful with direct connections to the database, though. As proof of concept: SELECT CASE WHEN inet_client_addr() << '192.168.1.32/30'::inet THEN 'foo' ELSE 'bar' ...


3

If you have a set group of users that would access the database or just one or two application accounts that make the database connection you could simply disable the login(s) and kill all connections, and then just re-enable the login(s) when you are done.


3

I will add though that allowing root from remote clients (especially allowing root from '%' ) is VERY BAD security wise


3

Two possible problems You didn't specify the -p option. Without -p the client will not prompt for a password and will attempt to connect without one The database may not be configured to allow remote login for root. You may have to login on the server and enable remote access (i.e. GRANT ALL ON *.* TO root@'%' IDENTIFIED BY '...';)


3

I did something like this a long time ago. Yes it is possible. No it isn't too hard on the technical side. I don't think you will have problems in the areas you are asking about. Really this is pretty basic stuff once you figure out how acls are supposed to behave. The issue you will run into has to do with consistently enforcing the acls. It is very ...


3

You might want to verify this in SQLPlus. If it still doesn't disconnect you then verify your assumptions by running the following after the trigger finishes: SELECT 'Check This' FROM dual WHERE sys_context('USERENV','SESSION_USER') = 'xx'; SELECT 'Check This' FROM dual WHERE sys_context('USERENV','IP_ADDRESS') <> '10.0.30.219'; SELECT 'Check This' ...


3

I've had to solve this specific problem and realized there's not way to say "all tables except these". What I did to work around this was create an internal database, restricted_tables. It had a couple of simple table schema: CREATE TABLE `tables` ( `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', `TABLE_NAME` varchar(64) NOT NULL DEFAULT '' ) ENGINE=...


3

Also addressing the question in the comments. Role public Per documentation: The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Bold emphasis mine. Membership in public cannot be ...


Only top voted, non community-wiki answers of a minimum length are eligible